Oracle tablespace, partitioned tables, and the summary of indexes __oracle

Table Space:
Oracle's UNDOTBS01.DBF file is too big a solution
1. Prohibit undo tablespace automatic growth
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 the new table space for the system Undo_tablespace
alter system set UNDO_TABLESPACE=UNDOTBS2;

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

--View all table spaces
SELECT * FROM Dba_tablespaces

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

--Remove Tablespace
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 a table increases, the query data slows down, and the application's performance drops, 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 when querying the data, it does not scan the entire table every time.
Some of the following table partitions are available in Oracle:
Range Partitioning: This type of partition uses a set of values for a column, usually the column as a partitioning key.
Example 1: Suppose you have a customer table with data 200000 rows, we partition the 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 tables and partitions, 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 table space must first exist, and it is recommended that different partitions be placed in different table spaces.
Example 2: Assuming 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
)

Second, the 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) is not NULL PRIMARY KEY,
DESCRIPTION VARCHAR2 (2000),
CUSTOMER_ID Number (7) is not NULL,
Date_entered DATE not NULL,
STATUS VARCHAR2 (20)
)
PARTITION by LIST (STATUS)
(
PARTITION prob_active VALUES (' ACTIVE ') tablespace prob_ts01,
PARTITION prob_inactive VALUES (' INACTIVE ') tablespace PROB_TS02
)

Hash partitioning: This type of partition uses a hashing algorithm on the column value to determine which partition to put the row into. A hash partition is recommended when the value of a 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
)

Composite Range List Partitioning: This partition is based on a range partition and a list partition, the table is first scoped by a column, then the list is partitioned by a column, and the partitions in the partition are called sub partitions.
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: Using the template template
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
)
Composite Range Hash Partition: This partition is based on a range partition and a hash partition, the table is first scoped by a column, and then a column is partitioned. This is very similar to the way it is defined above, not alone.

Table partitions are transparent to the user, and when we insert the data Oracle automatically determines the inserted data and then puts it into the appropriate table partition. But sometimes when we want to query the data in a partition individually, we have to manually specify the name of the partition.
Example 1: (This sample is based on: Four, example of a compound range list partition)
Inserts a record into the sales table without specifying a table partition.
INSERT into SALES VALUES (' 00001 ', ' January-January -02 ', MB, ' 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 ')
/
Do not specify table partitions to view sales table information:
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-partitions to query sales table information:
SELECT * from SALES subpartition (P1SUB1); The results are as follows:
Example 2: (This sample is based on: Four, example of a compound range list partition)
Example 2 a table partition based on the template template, the query is slightly cumbersome.
Specify P1 table partition query sales table information:
SELECT * from SALES PARTITION (P1); The results are as follows and are consistent with the query just now.
Specify SUB1 sub-partitions to query sales table information:
SELECT * from SALES subpartition (SUB1); The following error message appears:
How to solve the above problems. We view the data dictionary of the partition information through the SYS mode as follows:
You can see that the child partitions are not called SUB1, but P1_sub1, and requery the information as shown in the following figure:

Some Maintenance actions on table partitions:
One, add partitions
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 partition boundaries added above should be higher than the last partition boundary.

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 deletes the P3 table partition:
ALTER TABLE SALES DROP PARTITION P3;

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

Iii. Truncation of partitions
Truncating a partition means deleting the data from a partition and does not delete the partition, nor does it delete the 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 child partition with the following code:
ALTER TABLE SALES TRUNCATE subpartition p2sub2;

Iv. Merging of partitions
Merge partitions combine adjacent partitions into one partition, resulting partitions with higher partition boundaries, and it is noteworthy 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. Split Division
A split partition splits a partition into two new partitions, and the original partition does not exist until it is split. 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 integration of data from the hash partition into other partitions, and when the data in the hash partition is larger, the hash partition can be added and then jointed, and it is noteworthy that the junction partition can only be used in the hash partition. Splice partitions through the following code:
ALTER TABLE SALES Coalesca PARTITION;

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

IX. Cross-section 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));

Ten, how many partitions on the query table
SELECT * from User_tab_partitions WHERE table_name= ' tablename '

Xi. Query 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 for all partitioned tables in the database:
SELECT * FROM Dba_part_tables

--Displays all partition table information accessible to the current user:
SELECT * FROM All_part_tables

--Displays information about all partitioned 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 zoning information for all partitioned tables accessible to the current user:
SELECT * FROM All_tab_partitions

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

--Displays the child partition information displays the child partition information of all the combined partition tables in the database:
SELECT * FROM Dba_tab_subpartitions

--Displays the child partition information for all combined partitioned tables accessible to the current user:
SELECT * FROM All_tab_subpartitions

--Displays the child partition information for all grouped partitioned tables for the current user:
SELECT * FROM User_tab_subpartitions

--Display the partition column to display the partition column information for all the partitioned tables in the database:
SELECT * FROM Dba_part_key_columns

--Displays the partition column information for all partitioned tables accessible to the current user:
SELECT * FROM All_part_key_columns

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

--Displays the child partition column to display the child partition column information of all partitioned tables in the database:
SELECT * FROM Dba_subpart_key_columns

--Displays the child partition column information for all partitioned tables accessible to the current user:
SELECT * FROM All_subpart_key_columns

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

--How to query 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;

--The data that deletes a partition of a partitioned table is
ALTER TABLE TABLE_NAME TRUNCATE PARTITION P5;

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

Table partitioning has the following advantages:
1, data query: The 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, facilitate backup and recovery.
4, parallelism: can be at the same time to the table DML operation, the performance of parallelism improved.
================================================

Index:
1. General Index:
CREATE INDEX index_name on table (col_name);
2, Oracle Zoning Index detailed
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 types of cases

1. Local Index
That is, 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 table space of the parent table's partition. However, it can only be created if the parent table is a Hashtable or composite partition table.
Local STORE in (tablespace)
1.2 can only be created if the parent table is a Hashtable or composite partition table. and specify the number of partitions to be the same as the number of partitions in 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]]]

1.3 Index information is stored in the table space of the parent table's partition, which is the simplest and most commonly used method of partitioning the index.
Local
1.4 and specifies 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 the parent table is not a partitioned table. 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, you must update the global index to delete one of the partitions of the parent table, 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