Oracle Study Notes eight tablespaces and oracle Study Notes

Oracle Study Notes eight tablespaces and oracle Study Notes
A tablespace is a collection of one or more data files. All data objects are stored in a specified tablespace. However, they are mainly stored in tables, which are called tablespaces.Partition TableWhen the amount of data in a table increases, the speed of data query slows down, and the performance of applications degrades. In this case, you should consider partitioning the table. After the table is partitioned, the logical table is still a complete table, but the data in the table is physically stored in multiple tablespaces (physical files, it does not scan the entire table every time. Note: There is no way to directly convert an existing table to a partition table. Oracle allows you to divide a table into multiple partitions. You can perform queries and only access specific partitions in the table, it also stores different partitions on different disks to improve access performance and security. Each part of a table is called a "partition". You can back up and restore each partition independently, and query and update each partition by referencing the logical table name. The Table Partitioning function of Oracle brings great benefits to various applications by improving manageability, performance, and availability. In general, partitions can greatly improve the performance of some queries and maintenance operations. In addition, partitions can greatly simplify common management tasks, partitioning is a key tool for building a gigabit data system or a super high availability system. The partitioning function further segments tables, indexes, or index organization tables. The segments of these database objects are called partitions. Each partition has its own name, and you can select its own storage features. From the perspective of the database administrator, the objects in a partition have multiple segments, which can be managed collectively or separately, this makes the database administrator quite flexible in managing the objects after the partition. However, from the perspective of application process, the partitioned table is exactly the same as the non-partitioned table. The advantages of using the SQL DML command to access the partitioned table do not require any modification:

1. Enhanced availability: If a partition of the table fails, the data of the table in other partitions is still available;

2. Easy Maintenance: If a partition of the table fails, you only need to fix the partition to fix the data;

3. Balanced I/O: You can map different partitions to disks to balance I/O and improve the overall system performance;

4. Improved query performance: You can query partition objects by searching only the partitions you are concerned about, improving the search speed.

When to use partition tables: 1. the table size exceeds 2 GB.
2. The table contains historical data. New data is added to new partitions. Table partition type

Range partition 1. map rows to partitions in the range of values of one or more columns in a table. defined by the table partition Specification; partition by range (column_list) 3. defined by the partition specification of each individual partition: values less than (value_list); range partition Syntax:

PARTITION BY RANGE (column_name)(  PARTITION part1 VALUES LESS THAN(range1),  PARTITION part2 VALUES LESS THAN(range2),  ...  [PARTITION partN VALUES LESS THAN(MAXVALUE)]);

Range partition example

Create table Sales (Product_ID varchar2 (5), Sales_Cost number (10) partition by range (Sales_Cost) -- CREATE a PARTITION based on Sales_Cost (PARTITION P1 values less than (1000 ), -- includes the PARTITION P2 values less than (1000), PARTITION P3 values less than (2000), and the name of the P1, P2, and P3 partitions.

 

CREATE TABLE SALES2 (PRODUCT_ID VARCHAR2(5),SALES_DATE DATE NOT NULL,SALES_COST NUMBER(10))PARTITION BY RANGE (SALES_DATE)(  PARTITION P1 VALUES LESS THAN (DATE '2003-01-01'),  PARTITION P2 VALUES LESS THAN (DATE '2004-01-01'),   PARTITION P3 VALUES LESS THAN (MAXVALUE));

 

Hash partition 1. you can partition data that does not have a logical range. you can run the HASH function on the partition key to determine the partition to store. 3. syntax for evenly distributing data to hash partitions of different partitions

Partition by hash (column_name) PARTITIONS number_of_partitions; or partition by hash (column_name) (PARTITION part1 [TABLESPACE tbs1], PARTITION part2 [TABLESPACE tbs2],... PARTITION partN [TABLESPACE tbsN]);

Hash partition example

Create table Employee (Employee_ID varchar2 (5), Employee_Name varchar2 (20), Department varchar2 (10) partition by hash (Department) -- create the Partition key Department on the table Employee (-- create three partitions Partition D1, Partition D2, Partition D3); -- D1, D2, D3 Partition name

 

CREATE TABLE EMPLOYEE(    EMP_ID NUMBER(4),    EMP_NAME VARCHAR2(14),    EMP_ADDRESS VARCHAR2(15),    DEPARTMENT VARCHAR2(10))PARTITION BY HASH (DEPARTMENT)PARTITIONS 4; 

 

List partition 1. syntax that allows users to organize irrelevant data into list partitions

PARTITION BY LIST (column_name)(  PARTITION part1 VALUES (values_list1),  PARTITION part2 VALUES (values_list2),  ...  PARTITION partN VALUES (DEFAULT));

List partition example

Create table Employee (Emp_ID number (4), Emp_Name varchar2 (14), Emp_Address varchar2 (15) -- list partition by list (Emp_Address) created on the TABLE based on the Employee address) (Partition north values ('Chicago '), -- includes records of clerk living in Chicago Partition west values ('san Francisco', 'Los Angel'), Partition south values ('atlanta ', 'daldallas ', 'houston'), Partition east values ('newyork ', 'boston'); -- north, west ...... partition name

 

Composite partition 1. syntax for combining a range partition with a hash partition or a list partition:

PARTITION BY RANGE (column_name1)SUBPARTITION BY HASH (column_name2)SUBPARTITIONS number_of_partitions(  PARTITION part1 VALUE LESS THAN(range1),  PARTITION part2 VALUE LESS THAN(range2),  ...  PARTITION partN VALUE LESS THAN(MAXVALUE));

Composite partition example

SQL> CREATE TABLE SALES (PRODUCT_ID VARCHAR2 (5), SALES_DATE DATE NOT NULL, SALES_COST NUMBER (10) PARTITION BY RANGE (SALES_DATE) -- create a range partition subpartition by hash (PRODUCT_ID) in the Sales_Date column of the table) -- create a hash sub-partition subpartitions 5 in the Product_ID column of the table -- create five hash sub-partitions in each range PARTITION (PARTITION S1 values less than (TO_DATE ('1st/123 ', 'dd/MON/yyyy'), PARTITION S2 values less than (TO_DATE ('1949th month/123', 'dd/MON/yyyy ')), PARTITION S3 values less than (TO_DATE ('1970/123456', 'dd/MON/YYYY '), PARTITION S4 values less than (MAXVALUE); -- S1, S2, names of the four range partitions created by S3

 

 

Manipulating partitioned tables to insert data into partitioned tables is exactly the same as operating normal tables. Oracle automatically saves the data to the corresponding partition. When querying, modifying, and deleting a partition table, you can explicitly specify the partition to operate on.

Insert into SALES3 VALUES ('p001', '02-February 1, 2001 ', 2000); insert into SALES3 VALUES ('p002', '10-February 1, 2001', 2508 ); insert into SALES3 VALUES ('p003 ', '05-July-2001', 780); insert into SALES3 VALUES ('p004 ', '12-September-2001', 1080 ); SELECT * FROM SALES3 PARTITION (P3); delete from SALES3 PARTITION (P2 );

 

Partition maintenance operations query the number of partitions in the table

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName' 

The partition maintenance operation modifies partitions of Partitioned Tables. Partition maintenance type: 1. scheduled events-delete the oldest partition on a regular basis. unplanned events-partition maintenance operations for solving application or system problems: 1. add partition 2. delete partition 3. partition truncation 4. merge partitions 5. sharding partitions add partitions-add new partitions after the last partition

ALTER TABLE SALES     ADD PARTITION P4 VALUES LESS THAN (4000);

 

Delete partition-delete a specified partition and delete the data in the partition.

ALTER TABLE SALES DROP PARTITION P4;

 

Partition truncation-delete all records in a specified partition

ALTER TABLE SALES TRUNCATE PARTITION P3;

 

Merge partitions-connect two adjacent partitions of a range partition or a composite partition.

ALTER TABLE SALES MERGE PARTITIONS S1, S2 INTO PARTITION S2;

 

Split partitions-split records in a large partition into two partitions

ALTER TABLE SALES SPLIT PARTITION P2 AT (1500) INTO (PARTITION P21, PARTITION P22);