Oracle Learning Note Eight table space

A tablespace table space is a collection of one or more data files, all of which are stored in the specified table space, but are primarily tables, so called table spaces. Partition TableWhen the amount of data in the table is increasing, the query data slows down and the performance of the application degrades, so 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 table spaces (physical files) so that querying the data does not always scan the entire table.Note: Tables that already exist have no methods that can be converted directly to a partitioned tableOracle allows users to divide a table into multiple partitions, allowing users to execute queries, access only specific partitions in the table, and store different partitions on different disks for improved access performance and security. Each part of the table is called a partition, and the user can back up and restore each partition independently, and can be queried and updated by referencing the logical table name. Oracle's table partitioning capabilities bring great benefits to a wide variety of applications by improving manageability, performance, and availability. In general, partitioning can greatly improve the performance of certain queries and maintenance operations. In addition, partitioning can greatly simplify common administrative tasks, where partitioning is the key tool partitioning feature for building a gigabyte data system or an ultra-high availability system that can further subdivide a table, index, or index organization table into segments, where the segments of these database objects are called partitions. Each partition has its own name, and you can choose your own storage features. From the database administrator's point of view, a partitioned object has multiple segments that can be collectively managed or managed separately, which gives the database manager considerable flexibility in managing the objects after the partition. Butfrom the application's point of view, the partitioned table is exactly the same as the non-partitioned table, without any modifications when accessing the partitioned table using the SQL DML commandAdvantages of using partitioning:

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

2, Maintenance Convenience: If a partition of the table fails, you need to repair the data, only repair the partition;

3, balanced I/O: Different partitions can be mapped to disk to balance I/O, improve the overall system performance;

4. Improve query performance: Queries on partitioned objects can search only the partitions they care about and improve the retrieval speed.

When to use partition table: 1, the size of the table is more than 2GB.
2, the table contains historical data, new data is added to the new partition. Types of table partitions

Range partition 1. Map rows to partition 2 as a range of values for a column or set of columns in a table. Defined by the partition specification of the table; partition by Range (column_list) 3. Defined by the partition specification for each individual partition: values less than ( Value_list); Syntax for range partitioning:

 by RANGE (column_name) (  values less THAN (range1),  values less THAN ( Range2),  ...   [PARTITION partn VALUES less THAN (MAXVALUE)] );

Range Partitioning Example

CREATE TABLESales (product_idvarchar2(5), Sales_cost Number(Ten)) PARTITION byRANGE (Sales_cost)--Create partitions based on Sales_cost(PARTITION P1VALUESLess THAN ( +),--includes sales cost less thanthe value of all products PARTITION P2VALUESLess THAN ( -), PARTITION P3VALUESLess THAN ( the));--P1,P2,P3 The name of the partition

CREATE TABLESALES2 (product_idVARCHAR2(5), Sales_date DATE not NULL, Sales_cost Number(Ten)) PARTITION byRANGE (sales_date) (PARTITION P1VALUESLess THAN (DATE'2003-01-01'), PARTITION P2VALUESLess THAN (DATE'2004-01-01'), PARTITION P3VALUESLess THAN (MAXVALUE));

Hash partition 1. Allow users to partition data that does not have a logical scope 2. Partition 3 is determined by executing the hash function on the partition key. Distribute data evenly across different partition hash partition syntax

   by by [Tablespace TBS1], [  Tablespace tbs2],  ...   [tablespace tbsn]);

Hash Partition Example

CREATE TABLEEmployee (employee_idvarchar2(5), Employee_Namevarchar2( -), Departmentvarchar2(Ten)) PARTITION byHASH (Department)--Create a Partition key on the table employee Department(--Create 3 partitionsPartition D1, Partition D2, Partition D3); --D1,D2,D3 The name of the partition

CREATE TABLE EMPLOYEE (    number (4),    VARCHAR2,     VARCHAR2(VARCHAR2    ),   4;

List partition 1. Allow users to organize unrelated data together the syntax of a list partition

 by LIST (column_name) (  values  (values_list1),  values  (VALUES_LIST2),  ...   VALUES (DEFAULT));

List Partitioning Example

CREATE TABLEEmployee (emp_id Number(4), Emp_namevarchar2( -), Emp_addressvarchar2( the))--list partitions created on the table based on the employee's addressPARTITION byLIST (emp_address) (Partition NorthValues('Chicago'),--A record of the employees who live in Chicago.Partition WestValues('San Francisco ', ' Los Angeles'), Partition SouthValues('Atlanta','Dallas','Houston'), Partition EastValues('New York','Boston'));--north,west ... The name of the partition

Composite partition 1. The syntax of a composite partition that ranges from a hash partition or a list partition:

by 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 partitioning Example

Sql> CREATE TABLESALES (product_idVARCHAR2(5), Sales_date DATE not NULL, Sales_cost Number(Ten)) PARTITION byRANGE (Sales_date)--in the sales_date of the tablecolumn in the Create range partition Subpartition byHASH (product_id)--in the product_id of the tablecolumns create a hash column partition subpartitions5    --in each range partitionCreate5Sub-partition (PARTITION S1VALUESLess THAN (To_date ('01/4 months/2001', 'dd/mon/yyyy')), PARTITION S2VALUESLess THAN (To_date ('01/7 months/2001', 'dd/mon/yyyy')), PARTITION S3VALUESLess THAN (To_date ('01/9 months/2001', 'dd/mon/yyyy')), PARTITION S4VALUESLess THAN (MAXVALUE));--S1,S2,S3 The name of the four range partition created

Manipulating partitioned tables inserting data into a partitioned table is exactly the same as manipulating a normal table, and Oracle automatically saves the data to the corresponding partition. You can explicitly specify the partition to manipulate when querying, modifying, and deleting partitioned tables.

INSERT  intoSALES3VALUES('P001','February-March on -2001', -);INSERT  intoSALES3VALUES('P002','October-May on -2001',2508);INSERT  intoSALES3VALUES('P003','May-July on -2001',780);INSERT  intoSALES3VALUES('P004','December-September on -2001', the);SELECT *  fromSALES3 PARTITION (P3);DELETE  fromSALES3 PARTITION (P2);

Partition maintenance operation How many partitions are on the query table

SELECT *  from WHERE table_name='tableName'

Partition maintenance Operations Modify partitions of partitioned tables. Type of partition maintenance: 1. Scheduled Events-delete the oldest partition 2 periodically. Unplanned events-Troubleshooting application or system issues partition maintenance operations are: 1. Add partition 2. partition 3. Truncate partition 4. Merge partition 5. Split partition Add partition – Add new partition after last partition

ALTER TABLE SALES      ADD VALUES Less THAN (4000);

Delete Partition – Deletes a specified partition and the partition's data is deleted

ALTER TABLE DROP PARTITION P4;

Truncate partition – Delete all records in the specified partition

ALTER TABLE TRUNCATE PARTITION P3;

Merge partitions-connect two contiguous partitions of a range partition or a composite partition

ALTER TABLE   into PARTITION S2;

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

ALTER TABLE SALES SPLIT PARTITION P2 at ( PARTITION P21, PARTITION P22);

Oracle Learning Note Eight table space