Overview of the Oracle partitioned table tablespace and Index

Summary of Oracle's tablespaces, partitioned tables, and indexes
Keywords: Oracle, table space, partition table, index
The first time last week to do database testing encountered a lot of questions to summarize:

Table Space:
Oracle's UNDOTBS01.DBF file is too large to solve
1. No automatic growth of undo Tablespace
ALTER DATABASE datafile ' FULL_PATH\UNDOTBS01.DBF ' autoextend off;
2.--Create a new small space for the undo Tablespace
Create undo tablespace undotBS2 DataFile ' Full_path\undotbs02. DBF ' size 100m;

--Set a new tablespace for the system undo_tablespace
alter system set UNDO_TABLESPACE=UNDOTBS2;

--Drop the old table space
Drop tablespace undotbs1 including contents;

--View all table space scenarios
SELECT * FROM Dba_tablespaces

--Create TABLE space
Create Tablespace HRPM0
DataFile '/oradata/misdb/hrpm0. DBF ' size 5m autoextend on next 10m MaxSize Unlimited

--Delete Table space
DROP tablespace data01 including CONTENTS and datafiles;

--Modify Table space size
ALTER DATABASE datafile '/PATH/NADDATE05.DBF ' resize 100M

Partition table:

When 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.
The following table partitions are available in Oracle:
One, Range partitioning: This type of partition is a set of values that uses a column, which is typically used as a partition key.
Example 1: Suppose there is a customer table with data 200000 rows, we partition this table through CUSTOMER_ID, each partition stores 100000 rows, and we save each partition in a separate tablespace so that the data file can span multiple physical disks. Here is the code to create the table and partition, as follows:
CREATE TABLE CUSTOMER
(
customer_id number not NULL PRIMARY KEY,
First_Name VARCHAR2 (+) not NULL,
Last_Name VARCHAR2 (+) not NULL,
PHONE VARCHAR2 () not NULL,
EMAIL VARCHAR2 (80),
STATUS CHAR (1)
)
PARTITION by RANGE (customer_id)
(
PARTITION cus_part1 VALUES less THAN (100000) tablespace cus_ts01,
PARTITION cus_part2 VALUES less THAN (200000) tablespace CUS_TS02
)
Note: When you create a table for partitioning, the tablespace must exist first, and it is recommended that different partitions be placed in different tablespaces.
Example 2: Suppose there is a order_activities table, every 6 months to clean up the order, we can partition the table by month, the partition code is as follows:
CREATE TABLE order_activities
(
ORDER_ID Number (7) is not NULL,
Order_date DATE,
Total_amount number,
CUSTOTMER_ID Number (7),
PAID CHAR (1)
)
PARTITION by RANGE (order_date)
(
PARTITION ord_act_part01 VALUES Less THAN (to_date (' 01-may-2003 ', ' dd-mon-yyyy ') tablespace ord_ts01,
PARTITION ord_act_part02 VALUES Less THAN (to_date (' 01-jun-2003 ', ' dd-mon-yyyy ') tablespace ord_ts02,
PARTITION ord_act_part02 VALUES Less THAN (to_date (' 01-jul-2003 ', ' dd-mon-yyyy ') tablespace ord_ts03
)

Two, list partition: This partition is characterized by a column of only a few values, based on this feature we can use the list partition.  
example 1: 
CREATE TABLE problem_tickets 
( 
  problem_id number (7) not NULL PRIMARY key, 
  DESCRIPTION VARCHAR2 (+),  
  customer_id number (7) not null, 
  DATE _entered DATE not null, ,
  STATUS VARCHAR2  
)  
PARTITION by LIST (status)  
(& nbsp
  PARTITION prob_active      VALUES (' ACTIVE ')         tablespace prob_ts01, 
  PARTITION prob_inactive    VALUES (' INACTIVE ')       tablespace prob_ts02 
)

Hash partitioning: This type of partition uses a hash algorithm on column values to determine which partition the row is placed in. Hash partitioning is recommended when the value of the column does not have an appropriate condition. Take a look at the following examples:
Example 1:
CREATE TABLE hash_table
(
COL Number (8),
INF VARCHAR2 (100)
)
PARTITION by HASH (COL)
(
PARTITION PART01 tablespace hash_ts01,
PARTITION PART02 tablespace HASH_TS02,
PARTITION PART03 tablespace Hash_ts03
)

Four, compound range list partition: This partition is based on the range partition and the list partition, the table first by a column for the scope partition, and then by a column for the list partition, partition is called sub-partition.
Example 1:
CREATE TABLE SALES
(
product_id VARCHAR2 (5),
Sales_date DATE,
Sales_cost Number (10),
STATUS VARCHAR2 (20)
)
PARTITION by RANGE (sales_date)
Subpartition by LIST (STATUS)
(
PARTITION P1 VALUES Less THAN (to_date (' 2003-01-01 ', ' yyyy-mm-dd ') tablespace p1_ts
(
Subpartition p1sub1 VALUES (' ACTIVE ') tablespace subp1_ts1,
Subpartition p1sub2 VALUES (' INACTIVE ') tablespace subp1_ts2
),
PARTITION P2 VALUES Less THAN (to_date (' 2003-03-01 ', ' yyyy-mm-dd ') tablespace p2_ts
(
Subpartition p2sub1 VALUES (' ACTIVE ') tablespace subp2_ts1,
Subpartition p2sub2 VALUES (' INACTIVE ') tablespace subp2_ts2
)
)
Example 2: Working with Template templates
CREATE TABLE SALES
(
product_id VARCHAR2 (5),
Sales_date DATE,
Sales_cost Number (10),
STATUS VARCHAR2 (20)
)
PARTITION by RANGE (sales_date)
Subpartition by LIST (STATUS)
Subpartition TEMPLATE
(
Subpartition SUB1 VALUES (' ACTIVE ') tablespace subp1_ts1,
Subpartition SUB2 VALUES (' INACTIVE ') tablespace subp2_ts2
)
(
PARTITION P1 VALUES Less THAN (to_date (' 2003-01-01 ', ' yyyy-mm-dd ') tablespace p1_ts,
PARTITION P2 VALUES Less THAN (to_date (' 2003-03-01 ', ' yyyy-mm-dd ') tablespace p2_ts
)
V. Compound-scoped HASH partition: This partition is based on a range partition and a hash partition, and the table is first scoped by a column and then hashed by a column. Similar to the definition above, this is not a separate example.

Table partitioning is transparent to users, and when we insert data, Oracle automatically determines which data is inserted and then puts it into the appropriate table partition. But sometimes when we want to query the data in a partition separately, we must manually specify the name of the partition.
Example 1: (This example is based on: Four, example of a composite range list partition)
Inserting a record into the sales table does not have to specify a table partition.
INSERT into SALES VALUES (' 00001 ', ' January-January -02 ', ' ACTIVE ')
/
INSERT into SALES VALUES (' 00002 ', ' January-January -01 ', $, ' ACTIVE ')
/
INSERT into SALES VALUES (' 00003 ', ' January-February -03 ', ' INACTIVE ')
/
INSERT into SALES VALUES (' 00004 ', ' April-February -03 ', ' INACTIVE ')
/
INSERT into SALES VALUES (' 00005 ', ' April-February -02 ', ' INACTIVE ')
/
To view the sales table information without specifying a table partition:
SELECT * from SALES; The results are as follows:
Specify P1 table partition query sales table information:
SELECT * from SALES PARTITION (P1); The results are as follows:
Specify P1SUB1 sub-partition Query sales table information:
SELECT * from SALES subpartition (P1SUB1); The results are as follows:
Example 2: (This example is based on: iv. example of a composite range list partition)
Example 2 a table partition based on template templates, the query is slightly cumbersome.
Specify P1 table partition query sales table information:
SELECT * from SALES PARTITION (P1); The results are as follows, consistent with the query just now.
Specify SUB1 sub-partition Query sales table information:
SELECT * from SALES subpartition (SUB1); The following error message appears:
How to solve the above problem? We view the data dictionary of partition information through the SYS mode, as follows:
You can see that the sub-partition is not called SUB1, but rather p1_sub1, re-querying the information, as shown in:

Some maintenance operations on table partitioning:
First, add the partition
The following code adds a P3 partition to the sales table
ALTER TABLE SALES ADD PARTITION P3 VALUES less THAN (to_date (' 2003-06-01 ', ' yyyy-mm-dd '));
Note: The above partition boundaries should be added above the last partition bounds.

The following code adds a P3SUB1 sub-partition to the P3 partition of the sales table
ALTER TABLE SALES MODIFY PARTITION P3 ADD subpartition p3sub1 VALUES (' complete ');

Second, delete the partition
The following code removes the P3 table partition:
ALTER TABLE SALES DROP PARTITION P3;

The P4SUB1 sub-partition was removed in the following code:
ALTER TABLE SALES DROP subpartition p4sub1;
Note: If the deleted partition is the only partition in the table, then this partition will not be deleted and you must delete the table if you want to delete this partition.

Third, truncate the partition
Truncating a partition means deleting data from a partition, and does not delete the partition, nor does it delete data from other partitions. You can truncate a table even if it has only one partition. Truncate the partition with the following code:
ALTER TABLE SALES TRUNCATE PARTITION P2;
Truncate the sub-partition with the following code:
ALTER TABLE SALES TRUNCATE subpartition p2sub2;

Iv. Merging of partitions
Merging partitions is the merging of adjacent partitions into a single partition, with the result that partitions are bounded by higher partitions, and it is worth noting that partitions cannot be merged into lower-bound partitions. The following code implements the merge of the P1 P2 partition:
ALTER TABLE SALES MERGE partitions p1,p2 into PARTITION P2;

V. Splitting partitions
Split partition splits a partition into two new partitions, after which the original partition no longer exists. Note You cannot split a hash-type partition.
ALTER TABLE SALES sblit PARTITION P2 at (to_date (' 2003-02-01 ', ' yyyy-mm-dd '))
Into (PARTITION p21,partition P22);

Vi. junction Zoning (Coalesca)
Combined partitioning is the data in the hash partition into other partitions, when the data in the hash partition is relatively large, you can increase the hash partition and then engage, it is worth noting that the bonded partition can only be used in the hash partition. Use the following code to make a bonded partition:
ALTER TABLE SALES Coalesca PARTITION;

Vii. Renaming table Partitions
The following code changes P21 to P2
ALTER TABLE SALES RENAME PARTITION P21 to P2;

Nine, cross-divisional query
Select SUM (*) from (
(SELECT COUNT (*) cn from T_TABLE_SS PARTITION (p200709_1)
UNION ALL
Select COUNT (*) cn from T_TABLE_SS PARTITION (p200709_2));

X. How many partitions are on the query table
SELECT * from user_tab_partitions WHERE table_name= ' TableName '

Xi. Querying index Information
Select Object_name,object_type,tablespace_name,sum (Value)
From V$segment_statistics
where Statistic_name in (' physical reads ', ' physical write ', ' logical reads ') and object_type= ' INDEX '
GROUP BY Object_name,object_type,tablespace_name
ORDER BY 4 DESC

--Displays information about all partitioned tables in the database:
SELECT * FROM Dba_part_tables

--Displays all partition table information that the current user can access:
SELECT * FROM All_part_tables

--Displays information about all the partition tables for the current user:
SELECT * FROM User_part_tables

--Display table partition information displays detailed partition information for all partitioned tables in the database:
SELECT * FROM Dba_tab_partitions

--Displays detailed partitioning information for all partitioned tables that the current user can access:
SELECT * FROM All_tab_partitions

--Displays detailed partitioning information for all partition tables for the current user:
SELECT * FROM User_tab_partitions

--Display sub-partition information displays sub-partition information for all the combined partition tables of the database:
SELECT * FROM Dba_tab_subpartitions

--Displays sub-partition information for all the combined partitioned tables that the current user can access:
SELECT * FROM All_tab_subpartitions

--Displays sub-partition information for all the combined partition tables of the current user:
SELECT * FROM User_tab_subpartitions

--Show partition column displays the partition column information for all partitioned tables in the database:
SELECT * FROM Dba_part_key_columns

--Displays the partition column information for all partitioned tables that the current user can access:
SELECT * FROM All_part_key_columns

--Displays the partition column information for all partition tables for the current user:
SELECT * FROM User_part_key_columns

--Show sub-partition columns displays sub-partition column information for all partitioned tables in the database:
SELECT * FROM Dba_subpart_key_columns

--Displays sub-partition column information for all partitioned tables that the current user can access:
SELECT * FROM All_subpart_key_columns

--Displays sub-partition column information for all partitioned tables of the current user:
SELECT * FROM User_subpart_key_columns

--How to query out all the partitioned tables in the Oracle database
SELECT * from User_tables a where a.partitioned= ' YES '

--deleting data from a table is
TRUNCATE TABLE table_name;

--Delete partition table data for a partition is
ALTER TABLE TABLE_NAME TRUNCATE PARTITION P5;

Note: The partition is selected according to the specific situation.

Table partitioning has the following advantages:
1, data query: Data is stored on multiple files, reducing the I/O load, query speed increased.
2, Data pruning: the preservation of historical data is very ideal.
3, Backup: The large table of data into multiple files, easy to backup and recovery.
4. Parallelism: DML operations can be performed at the same time in the table, and the performance of parallelism is improved.
================================================

Index:
1. General Index:
CREATE INDEX index_name on table (col_name);
2. Detailed Oracle partition index
Syntax: Table Index
CREATE [unique| BITMAP] INDEX [schema.] Index_name
on [schema.] table_name [Tbl_alias]
(Col [ASC | DESC]) Index_clause index_attribs

Index_clauses:
The following two cases

1. Local index 
   The location of the index information depends on the partition information of the parent table, in other words, creating such an index must ensure that the parent table is partition 
1.1 The index information is stored in the tablespace where the parent table's partition resides. However, only the parent table can be created as a Hashtable or composite partition table.  
    LOCAL STORE in (tablespace)  
1.2 can only be created in the parent table as Hashtable or composite partition table. and specify the number of partitions to be consistent with the number of partitions of the parent table  
LOCAL STORE in (tablespace)   (PARTITION [partition        [Logging| nologging]       [Tablespace {tablespace| default}]       [PCTFREE int]       [PCTUSED int]        [Initrans int]       [Maxtrans int]        [STORAGE storage_clause]       [STORE in {tablespace_name| default]       [Subpartition [Subpartition [Tablespace TABLESPACE]]]

The

1.3 index information resides in the table space of the parent table's partition, which is the simplest and most commonly used partitioning index creation method.  
    local 
1.4 and specify the number of PARTITION to be consistent with the PARTITION of the parent table  
Local (PARTITION [ partition 
[logging| nologging] 
[tablespace {tablespace| default}] 
[PCTFREE int] 
[pctused int] 
[Initrans int] 
[Maxtrans int] 
[ STORAGE storage_clause] 
[STORE in {tablespace_name| default] 
[subpartition [Subpartition [tablespace tablespace]]]

Global Index
The location of the index information is completely irrelevant to the partition information of the parent table. Even if the parent table is not a partitioned table, it doesn't matter. The syntax is as follows:
GLOBAL PARTITION by RANGE (col_list)
(PARTITION PARTITION VALUES less THAN (value_list)
[Logging| Nologging]
[Tablespace {tablespace| DEFAULT}]
[PCTFREE int]
[Pctused int]
[Initrans int]
[Maxtrans int]
[STORAGE Storage_clause])
However, in this case, if the parent table is a partitioned table, a partition of the parent table must be updated to update the global index, otherwise the index information is incorrect
ALTER TABLE TableName DROP PARTITION partitionname Update Global Indexes

--Query Index
Select Object_name,object_type,tablespace_name,sum (Value)
From V$segment_statistics
where Statistic_name in (' physical reads ', ' physical write ', ' logical reads ') and object_type= ' INDEX '
GROUP BY Object_name,object_type,tablespace_name
ORDER BY 4 DESC

Overview of the Oracle partitioned table tablespace and Index