Common oracle information Summary 1

1. Range partitioning: A range partition maps data to each partition based on the range. This range is determined by the partition key you specified when creating the partition. This partitioning method is the most commonly used, and the partition key usually uses the date. For example, you may partition the sales data by month. When using range partitions, consider the following rules: 1. Each partition must have a values less then clause, which specifies an upper limit not included in the partition. Any record with the partition key value equal to or greater than the upper limit will be added to the next higher partition. 2. All partitions except the first partition have an implicit lower limit. This value is the upper limit of the previous partition. 3. MAXVALUE is defined in the highest partition. MAXVALUE indicates an uncertain value. This VALUE is higher than the VALUE of any partition key in other partitions. It can also be understood as a VALUE higher than the value less then specified in any partition and a null VALUE. Example 1: Assume that there is a CUSTOMER table with 200000 rows of data. We partition the table using mermer_id and store 100000 rows for each partition, we save each partition to a separate tablespace so that data files can span multiple physical disks. The following code creates tables and partitions: create table customer (CUSTOMER_ID number not null primary key, FIRST_NAME VARCHAR2 (30) not null, LAST_NAME VARCHAR2 (30) not null, PHONE VARCHAR2 (15) 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) Example 2: CREATE by Time TABLE ORDER_ACTIVITIES (ORDER_ID NUMBER (7) 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') TABLESPACEORD_TS01, PARTITION ORD_ACT_PART02 values less than (TO_DATE ('01-JUN-2003 ', 'dd-MON-YYYY ') TABLESPACE ORD_TS02, PARTITION ORD_ACT_PART02 VALUE S less than (TO_DATE ('01-JUL-2003 ', 'dd-MON-YYYY') TABLESPACE ORD_TS03) Example 3: maxvalue create table RangeTable (idd int primary key, iNAME VARCHAR (10), grade INT) partition by range (grade) (PARTITION part1 values less then (1000) TABLESPACE Part1_tb, PARTITION part2 values less then (MAXVALUE) TABLESPACE Part2_tb ); II. list partition: This partition has only a few values in a column. Based on this feature, we can use list partitions. Example 1 create table PROBLEM_TICKETS (PROBLEM_ID NUMBER (7) not null primary key, DESCRIPTION VARCHAR2 (2000), CUSTOMER_ID NUMBER (7) 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 Example 2 create table ListTable (id int primary key, name VAR CHAR (20), area VARCHAR (10) partition by list (area) (PARTITION part1 VALUES ('guangdong', 'beijing') TABLESPACE Part1_tb, PARTITION part2 VALUES ('shanghai', 'nanjing') TABLESPACE Part2_tb);) 3. hash partition: This type of partition uses the hash algorithm on the column value to determine which partition the row is placed in. When the column value does not have an appropriate condition, hash partitions are recommended. Hash partitioning is a type of partitioning that distributes data evenly by specifying the Partition Number, because the size of these partitions is consistent by performing hash partitioning on the I/O device. 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) Abbreviation: create table emp (empno NUMBER (4), ename VARCHAR2 (30), sal NUMBER) partition by hash (empno) PARTITIONS 8 store in (emp1, emp2, emp3, emp4, emp5, emp6, emp7, and emp8); the primary mechanism of hash partitioning is to use the hash algorithm to calculate Which partition should a record be inserted to? The hash function is the most important in the hash algorithm. If you want to use hash partitions in Oracle, you only need to specify the number of partitions. We recommend that the number of partitions use the N power of 2 to make the data distribution in each shard more even. 4. combined range hash partitions are based on range partitions and list partitions. A table first partitions a range by a column and then partitions a list by a column, A partition is called a subpartition. 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 ('1970-01-01 ', 'yyyy-MM-DD') TABLESPACE rptfact2009 (SUBPARTITION P1SUB1 VALUES ('active') TABLESPACE rptfact2009, SUBPARTITION P1SUB2 VALUES ('inactive') TABLESPACE rptfact2009), PARTITION P2 Values less than (TO_DATE ('1970-03-01 ', 'yyyy-MM-DD') TABLESPACE rptfact2009 (SUBPARTITION P2SUB1 VALUES ('active') TABLESPACE rptfact2009, SUBPARTITION P2SUB2 VALUES ('inactive') TABLESPACE rptfact2009) 5. composite range hash partition: This type of partition is based on range partitions and hash partitions. the table first partitions the range by a column, and then hash partitions by a column. Create table dinya_test (transaction_id number primary key, item_id number (8) not null, item_description varchar2 (300), transaction_date date) partition by range (transaction_date) subpartition by hash (transaction_id) subpartitions 3 store in (dinya_space01, dinya_space02, dinya_space03) (partition part_01 values less than (to_date ('2017-01-01 ', 'yyyy-mm-dd ')), partition part_02 values less than (to_d Ate ('1970-01-01 ', 'yyyy-mm-dd'), partition part_03 values less than (maxvalue); (5 ). some maintenance operations on TABLE partitions: 1. ADD partitions the following code adds a P3 partition alter table sales add partition P3 values less than (TO_DATE ('2017-06-01 ', 'yyyy-MM-DD '); note: the partition boundary added above should be higher than the last partition boundary. The following code adds an P3SUB1 subpartition alter table sales modify partition P3 add subpartition P3SUB1 VALUES ('complete') to the P3 PARTITION of the sales table '); ii. Run the following code to delete a PARTITION: alter table sales drop partition P3; run the following code to delete a P4SUB1 SUBPARTITION: alter table sales drop subpartition P4SUB1. Note: if the partition to be deleted is the only partition in the table, the partition cannot be deleted. to delete the partition, you must delete the table. 3. truncation a partition refers to the deletion of data in a partition, which does not delete or delete data in other partitions. If a table has only one partition, it can be truncated. Run the following code to PARTITION: alter table sales truncate partition P2; run the following code to PARTITION: alter table sales truncate subpartition P2SUB2; 4. merge partitions to combine adjacent partitions into one partition. The result partition uses the boundary of High-score partitions. It is worth noting that, partitions cannot be merged into partitions with lower boundaries. Run the following code to MERGE P1 P2 PARTITIONS: alter table sales merge partitions P1, P2 into partition P2; 5. Split a PARTITION and split a partition into two new PARTITIONS, after splitting, the original partition no longer exists. Note that HASH partitions cannot be split. Alter table sales sb1_partition P2 AT (TO_DATE ('1970-02-01 ', 'yyyy-MM-DD') INTO (PARTITION p21. PARTITION P22); 6. Join PARTITION (coalesca) A combined partition joins data in a hash partition to another partition. When the data in the hash partition is large, you can add a hash partition and then join the partition. It is worth noting that, join partitions can only be used in hash partitions. Run the following code TO create a join PARTITION: alter table sales coalesca partition; 7. RENAME a table partition: run the following code TO change p21-alter table sales rename partition p21-p2; 8. Related query cross-PARTITION 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); queries the number of partitions in the table SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME = 'tablename' query the 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 -- display information of all database partition tables: select * from DBA_PART_TABLES -- display information of all partition tables accessible to the current user: select * from ALL_PART_TABLES -- display the information of all the partition tables of the current user: select * from USER_PART_TABLES -- display the table partition information and display the detailed partition information of all the partition tables of the database: select * from DBA_TAB_PARTITIONS -- display detailed partition information of all partition tables accessible to the current user: select * from ALL_TAB_PARTITIONS -- display detailed partition information of all partition tables of the current user: select * from USER_TAB_PARTITIONS -- display subpartition information show subpartition information of all database combined partition tables: select * from DBA_TAB_SUBPARTITIONS -- display the subpartition information of all the combined partition tables accessible by the current user: select * from ALL_TAB_SUBPARTITIONS -- display the subpartition information of all the combined partition tables of the current user: select * from USER_TAB_SUBPARTITIONS -- display the partition column information of all partition tables of the database: select * from DBA_PART_KEY_COLUMNS -- display information about the partition columns of all partition tables accessible to the current user: select * from ALL_PART_KEY_COLUMNS -- display information about the partition columns of all partition tables of the current user: select * from USER_PART_KEY_COLUMNS -- display the subpartition column information of all database partition tables in the subpartition column: select * from DBA_SUBPART_KEY_COLUMNS -- display the subpartition column information of all the partition tables accessible to the current user: select * from ALL_SUBPART_KEY_COLUMNS -- display the information of subpartition columns in all the partition tables of the current user: select * from USER_SUBPART_KEY_COLUMNS -- How to query all the partition tables in the oracle database select * from user_tables a where. partitioned = 'yes' -- the data for deleting a table is truncate table table_name; -- the data for deleting a partition in a partition table is alter table table_name truncate partition p5; partition ;----------------------------------------------------------------------------------------------------------------------------------------------

Index:

1. General indexes:

Create index index_name on table (col_name );

2. Oracle partition index details

Syntax: Table Index

Create [UNIQUE | BITMAP] INDEX [schema.] index_name

ON [schema.] table_name [tbl_alias]

(Col [ASC | DESC]) index_clause index_attribs

Index_clses:

There are two situations:

1. Local Index

The storage location of the index information depends on the Partition information of the parent table. In other words, to create such an index, the parent table must be Partition.

1.1 The index information is stored in the tablespace where the partition of the parent table is located. However, you can only create a HashTable or composite partition table in the parent table.

Local store in (tablespace)

1.2 you can only create a HashTable or composite partition table in the parent table. The specified number of partitions must 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 The index information is stored in the tablespace where the partition of the parent table is located. This syntax is the simplest and is also the most commonly used partition index creation method.

Local

1.4. The number of specified partitions must be the same as that 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 storage location of the index information is completely irrelevant to the Partition information of the parent table. It doesn't even matter if the parent table is a partition table. Syntax:

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 partition table, you must update Global Index to delete all partitions of the parent table. Otherwise, the Index information is incorrect.

Alter TABLE TableName Drop PARTITION PartitionName Update Global Indexes

-- Query Indexes

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

Sequence 1. type description:

The range partitioning method is also the most common partitioning method. It uses a combination of a field or several fields to partition from small to large according to the specified range description, we store the INSERT data in the specified partition.

List partitioning is generally based on a range with more List partitions. List partitioning is a way to List partitions. Generally, some regions, states, or encoding of specified rules are divided.

Hash partitioning method, which has no fixed rules. It is managed by ORACLE and only needs to INSERT values. ORACLE will automatically partition partitions based on a set of HASH algorithms, you only need to tell ORACLE that there are several partitions.

 

Partitions can be combined in pairs. Before the ORACLE 11G, the combination must start with range as the primary partition. ORACLE currently supports a maximum of two levels of partitions, but this level is enough for us to use.

Here, I only use the simplest partition method to create a partition to illustrate the problem. Let's take the range partition to illustrate the problem (the basic creation statement is as follows ):

Create table TABLE_PARTITION (COL1 NUMBER, COL2 VARCHAR2 (10) partition by range (COL1) (partition TAB_PARTOTION_01 values less than (450000), partition TAB_PARTOTION_02 values less than (900000 ), partition TAB_PARTOTION_03 values less than (1350000), partition TAB_PARTOTION_04 values less than (1800000), partition TAB_PARTOTION_OTHER values less THAN (MAXVALUE ));

 

This partition table creates four fixed-length partitions. Ideally, it stores 450000 pieces of data, and the extended partition is a partition that exceeds this amount. When we find that the extended partition has data, you can perform the SPLIT operation on the extended partitions. Here we will introduce some common Partition Table query functions. We will insert some data first.

Insert into TABLE_PARTITION (COL1, COL2) VALUES (1, 'Data test'); insert into TABLE_PARTITION (COL1, COL2) VALUES (23, 'Data test '); insert into TABLE_PARTITION (COL1, COL2) VALUES (449000, 'Data test'); insert into TABLE_PARTITION (COL1, COL2) VALUES (450000, 'Data test '); insert into TABLE_PARTITION (COL1, COL2) VALUES (1350000, 'Data test'); insert into TABLE_PARTITION (COL1, COL2) VALUES (900000, 'Data test '); insert into TABLE_PARTITION (COL1, COL2) VALUES (1800000-1, 'Data test'); COMMIT; SQL> SELECT * FROM TABLE_PARTITION partition (TAB_PARTOTION_01 );

COL1 COL2 ---------- ----------------- 1 Data Test 23 data test 449000 data test

Note that the first partition contains data such as 1, 23, and 44900, that is, ORACLE finds the partition by itself during insertion, in fact, the sub-table management of partitions can also be completed by the program. ORACLE provides you with a set of sub-tables, you can complete it by yourself. The rest of the data is queried by yourself.

2. partition applications:

Generally, a table is larger than 2 GB in size. We recommend that you use a partition table in ORACLE. You need to create an index for all partitions. When it comes to partition indexes, it can be divided into global indexes and partition indexes, that is: global index and local index. The former is the index method used to create an index in a partitioned table by default, and does not partition the index (the index is also a table structure, and partitions are also required when the index is large, the global index can be modified as a partition index, but it is different from the local index. The partition method of the former is completely created according to the custom method and has nothing to do with the table structure, therefore, the global indexes of Partitioned Tables are shown in the following two commonly used diagrams on the Internet:

2.1 Non-partitioned indexes for Partitioned Tables (this is a bit of a wrap, but it is a table partition, but its index is not partitioned ):

CREATE syntax (directly CREATE): create index <index_name> ON <partition_table_name> (<column_name> );

2.2 PARTITION indexes for PARTITION tables: create index INX_TAB_PARTITION_COL1 ON TABLE_PARTITION (COL1) global partition by range (COL1) (PARTITION IDX_P1 values less than (1000000 ), PARTITION IDX_P2 values less than (2000000), PARTITION IDX_P3 values less than (MAXVALUE ));

2.3 LOCAL index structure:

Syntax:

Create index INX_TAB_PARTITION_COL1 ON TABLE_PARTITION (COL1) LOCAL;

You can also define the partition according to the partition structure of the partition table. The index partition will be renamed.

The bitmap index on a partition can only be a LOCAL index, not a GLOBAL index.

2.4. Index comparison methods:

Generally, LOCAL indexes are easier to use and have a low maintenance cost. In addition, LOCAL indexes are created based on partitions, similar to creating indexes within a sub-table, in this way, the overhead is mainly divided into partitions, which are well managed and widely used in OLAP systems. The relative GLOBAL index is a GLOBAL index, you can adjust the category of a partition based on the actual situation, instead of defining the partition structure one by one. The maintenance cost is higher, and more is used in the OLTP environment, here the so-called OLTP and OLAP are also relative, not special projects, there is no absolute division concept, in the application process based on the actual situation, to improve the overall operational performance.

3. Common views:

1. query the partition tables of the current user:

SELECT * FROM USER_PART_TABLES;

2. query the partition indexes of the current user:

SELECT * FROM USER_PART_INDEXES;

3. query the partition information of the current user's partition index:

SELECT * FROM USER_IND_PARTITIONS t where t. INDEX_NAME =?

4. query the partition information of the current user's partition table:

SELECT * FROM USER_TAB_PARTITIONS t where t. TABLE_NAME = ?;

5. query the data volume in a partition:

Select count (*) FROM TABLE_PARTITION PARTITION (TAB_PARTOTION_01 );

 

6. query indexes and create partitions on the columns of tables:

SELECT * FROM USER_PART_KEY_COLUMNS;

 

7. query the information of a subpartition under a user (data is available only when a subpartition is created ):

SELECT * FROM USER_TAB_SUBPARTITIONS;

 

4. Maintenance operations:

4.1 delete partitions

Alter table TABLE_PARTITION drop partition TAB_PARTOTION_03;

If it is a global index, because the partition structure of the global index and the table can be inconsistent, otherwise, the entire global index will fail. When deleting the partition, statement:

Alter table TABLE_PARTITION drop partition TAB_PARTOTION_03 update global indexes;

 

4.2 partition Merge (delete a partition from the middle, or merge the two partitions to reduce the number of partitions)

The RANGE in the process of merging and deleting partitions is a bit like that, But merging partitions does not delete data. For LIST and HASH partitions, they are also different from RANGE partitions. The syntax is as follows:

Alter table TABLE_PARTITION merge partitions TAB_PARTOTION_01, TAB_PARTOTION_02 into partition MERGED_PARTITION;

 

 

4.3. Partition separation (generally, partitions are separated from extended partitions)

Alter table TABLE_PARTITION split partition TAB_PARTOTION_OTHERE AT (2500000) INTO (PARTITION TAB_PARTOTION_05, PARTITION TAB_PARTOTION_OTHERE );

 

4.4 create a new partition (if the partition data cannot provide a range, an error will be reported during insertion, and you need to add partitions to expand the range)

Generally, extended partitions are separated. If the TAB_PARTOTION_OTHER partition is not created during table creation, when the data is large (according to the preceding rule, if the number exceeds 1800000, a new partition should be created for storage.) You can create a new partition, for example:

In order to test, we delete the extended partitions and then create new partitions (due to ORACLE requirements, data in partitions cannot overlap, that is, data with the same partition field cannot be stored in different partitions at the same time ):

Alter table TABLE_PARTITION drop partition TAB_PARTOTION_OTHER;

Alter table TABLE_PARTITION add partition TAB_PARTOTION_06 values less than (2500000 );

 

Create a new subpartition under the partition as follows (RANGE partition, if it is a LIST or HASH partition, you can change the creation method to the corresponding method ):

Alter table <table_name> modify partition <partition_name> add subpartition <user_define_subpartition_name> values less (....);

 

4.5 modify partition name (modify relevant attribute information)

Alter table TABLE_PARTITION rename partition MERGED_PARTITION TO MERGED_PARTITION02;

 

4.6. Swap partitions (fast data exchange is actually a switch segment name pointer)

First, create an exchange table with the same structure as the original table. If there is data, it must meet the conditions for the corresponding partition to be exchanged:

Create table TABLE_PARTITION_2 as select * FROM TABLE_PARTITION WHERE 1 = 2;

Then swap the data in the first partition:

Alter table TABLE_PARTITION exchange partition TAB_PARTOTION_01 with table TABLE_PARTITION_2 including indexes;

At this time, we will find that the data in the first partition is instantly exchanged with TABLE_PARTITION_2, which is faster than the TRUNCATE, because this process does not perform data transfer, but only modifies the segment name, it is irrelevant to the actual data volume.

 

If it is a SUBPARTITION, it can also be exchanged with external tables. You only need to change the keyword to SUBPARTITION.

 

4.7 clear partition data

 

Alter table <table_name> truncate partition <partition_name>;

Alter table <table_name> TRUNCATE subpartition <subpartition_name>;

 

9. Disk fragment Compression

Disk compression is performed on a partition in the Partition Table. After a large number of UPDATE and DELETE operations are performed on the internal data of the partition, disk compression is required for a certain period of time. Otherwise, during query, if you SCAN data through full scan, empty blocks will also be scanned. to compress the disk on the table, you need to perform row migration. Therefore, you must first perform the following operations:

Alter table <table_name> enable row movement;

 

The syntax for compressing a partition in a partitioned table is:

Alter table <table_name> modify partition <partition_name> shrink space; compression of common tables: alter table <table_name> shrink space; compression of indexes is also required, and indexes are also tables: alter index <index_name> shrink space;

 

10. re-analyze the partition table and re-analyze the index

After the table is compressed, you need to re-analyze the table and index to re-analyze the table. There are two methods:

Before ORACLE 10 Gb, use:

BEGIN dbms_stats.gather_table_stats (USER, UPPER ('<table_name>'); END;

 

After 10 Gb of ORACLE, you can use:

Analyze table <table_name> compute statistics;

 

Re-ANALYZE the INDEX and modify the two methods respectively. For example, you can use gather_index_stats In the first method, and change the second method to analyze index. However, recompile is usually used:

When partitioned tables are indexed and partitioned, you need to re-compile the indexes of each partition. Here, we use the LOCAL index as an example (the partition of each index is the same as that of the table partition, the default partition name is the same as the table partition name ):

Alter index <index_name> rebuild partition <partition_name>;

For a global index, you can modify it according to the partition name locked by the global index. If no partition exists, the recompilation method is the same as that for a common single-Table index:

Alter index <index_name> REBUILD;

 

11. recompile the associated object

The above table and index are re-compiled, especially after the table is compressed, row migration will be generated. This process may cause some views and process objects to become invalid. At this time, re-compile it.

 

 

12. Expansion: if a new partition is created in the HASH partition, you can re-HASH it:

Alter table <table_name> COALESCA PARTITION

 

 

5. Regression Summary: When to create a partition, partition category, index, and how to correspond to SQL

 

1. Creation Time

 

As described above, we recommend that you create partitions for tables larger than 2 GB.

The partitioning method depends on the actual situation to improve the overall performance.

Partition fields must be frequently used to extract data. Otherwise, multiple partitions will be traversed during the extraction process, which is slower than no partition.

Select an appropriate partition field. The data is evenly distributed to each partition. The partition range can be quickly located based on the partition field.

Generally, business operations should be completed within the same partition.

 

 

2. Partition category

 

Partitions mainly include RANGE, LIST, and HASH;

RANGE is partitioned by the RANGE of values, which is also the most commonly used partition. Note that in a variable-length numeric string, many people will think of it as a number type and distinguish it by number, this will cause uneven distribution.

LIST is used to LIST partitions. It generally exists as a LIST partition (of course, it can also be used as a primary partition after 11 GB of ORACLE). Based on the RANGE, if the data needs to continue partitioning and the Data Volume Based on the RANGE is relatively fixed, but it is large, further partitioning can be performed according to certain rules.

HASH only specifies the number of partitions. The partition details are completed by ORACLE. You can re-distribute data by adding HASH partitions.

 

Note: partition fields cannot be partitioned after Function Conversion. For example, TO_NUMER (COL_NAME) is used to partition a numeric string field.

 

3. index category

General score: GLOBAL index and LOCAL index, Qian he can be divided into: GLOBAL Non-partition index, and GLOBAL partition index.

 

GLOBAL non-partition indexes are generally not recommended because a large index tree is used to map a table. This process is not faster than non-partition indexes.

GLOBAL partition index: If you want to use the index to search for data, first locate the internal partition of the index, then find the ROWID in the index, and then return to the table to extract data.

The LOCAL index corresponds to the number of partitions one by one. It can be said that the partition in the Partition Table is located first, or the index partition is located first, because they correspond one to one. After finding the corresponding partition, A collection of index data within a partition.

 

4. Corresponding applications

When a partition table, index, or partition index needs to take advantage of its performance advantages, the most basic thing is to extract data, it is necessary to first narrow the data scope to a situation where it will not be too slow even if a full scan is performed.

Therefore, SQL must have a WHERE condition for this field on the partition to quickly locate the data inside the partition and try to locate it in a partition (this is related to the partition creation rule ).

Creating a partition itself does not have the feed performance. Therefore, it is necessary to make good use of it to improve the performance. In the necessary RAC cluster, if multiple partitions are used to extract data, appropriate parallel extraction can improve the extraction speed.

For the index part, we only mention how to create a partition index and how to maintain a common index. After understanding the index principle, we will be more likely to recognize the techniques used to extract data.