Shulou(Shulou.com)06/01 Report--

One. Introduce the index types of partitioned tables and briefly describe the applicable scenarios for each type. Two. Verify that the combined partitioning index has the difference between partitioning keys and speak with data.

The following instructions are an introduction to indexes for partitioned tables. (thinking that physical storage properties can better understand the description of the following index)

1. Local and global indexes

Local index: the index partition key value is equal to the table partition key value local prefix: in the index definition, the table partition key is the leading column of the index. Local non-prefix: in an index definition, the partitioning key of a table is not the leading column of the index.

Global partitioned index: the partitioned index is not local. Global partitioned indexes can also be used on non-partitioned tables.

Global non-partitioned index: the index is not partitioned.

two。 Verify the difference between local partitioned indexes with partitioning keys.

SQL > SELECT * FROM v$version

BANNER-Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

SQL > create table parttest (owner varchar2 (20) not null, object_id number not null, object_name varchar2 (32), created date) partition by list (owner) (partition part1 values ('SYS'), partition part2 values (' OUTLN'), partition part3 values ('SYSTEM'), partition part4 values (' SUN'), partition part5 values ('SQLTXPLAIN'), partition part6 values (' APPQOSSYS'), partition part7 values ('DBSNMP'), partition part8 values (' SQLTXADMIN'), partition part9 values ('DIP') Partition part10 values ('ORACLE_OCM'), partition part11 values (default)) /

DROP TABLE parttest

Insert into parttest select owner,object_id,object_name,created from DBA_OBJECTS; commit

The index does not contain the partition key create index idx_nopartkey on parttest (created) local nologging

-- the index contains partitioning keys

Create index idx_partkey on parttest (created,owner) local nologging; create index idx_partkey2 on parttest (object_NAME,owner) local nologging; create index idx_partkey3 on parttest (owner,object_NAME) local nologging; create index idx_nopartkey2 on parttest (object_NAME) local nologging

-- collect statistical information SQL > exec dbms_stats.gather_table_stats ('SUN','PARTTEST',cascade= > true,no_invalidate= > false,method_opt= >' for all columns size 1'> dbms_stats.auto_sample_size,degree= > 24)

PL/SQL procedure successfully completed.

The analysis process is divided into the following aspects 1. The index with partition key value is used for query, but no partition condition 2. 2 is added in the where condition. Query with the index with partition key value, but add partition condition 3. 3 to the where condition. Use the index without partitioning key to query, but do not add partitioning condition 4. 4 in the where condition. Query with an index without a partition key, but add a partition bar 5. 5 in the where condition. Query with an index with a partition key value, but add a partition bar to the where condition (the same as the 4 index key, but with an index key value) 6. Query with an index with a partition key value, but add a partition bar in the where condition (the same as the 4 index key, but with a leading key value) 7. Use the index with partition key value for cross-partition query, but add the partition condition in the where condition (same as the index key of 4, but with index leading key value) 8. Use an index with a partition key value for cross-partition query, but add a partition bar in the where condition (the same as the index key for 4, but with a leading key value) 9. Do cross-partition queries with indexes with partition key values, but add partition conditions to the where condition (same as index keys for 4, but with index key values)

The first case: query with the index with partition key value, but the partition condition set autotrace traceonly SELECT object_name FROM parttest WHERE object_name LIKE 'OR%' is not added to the where condition

Execution Plan-Plan hash value: 3693814982

-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |-| 0 | SELECT STATEMENT | | 3 | 57 | 12 (0) | 00:00: 01 | 1 | PARTITION LIST ALL | | 3 | 57 | 12 (0) | 00:00:01 | 1 | 11 | * 2 | INDEX RANGE SCAN | IDX_PARTKEY2 | 3 | 57 | 12 (0) | 00:00:01 | 1 | 11 |-- -

Predicate Information (identified by operation id):-

2-access ("OBJECT_NAME" LIKE 'OR%') filter ("OBJECT_NAME" LIKE' OR%')

Statistics-1 recursive calls 0 db block gets 23 consistent gets 0 physical reads 0 redo size 3768 bytes sent via SQL*Net to client 589 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 105 rows processed

The second case: query with the index with partition key value, but add the partition condition set autotrace traceonly SELECT object_name FROM parttest WHERE object_name LIKE 'OR%' AND owner='SYS' to the where condition

Execution Plan-Plan hash value: 2753556796

-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | | Pstart | Pstop |-| 0 | SELECT STATEMENT | | 2 | 46 | 2 (0) | ) | 00:00:01 | 1 | PARTITION LIST SINGLE | | 2 | 46 | 2 (0) | 00:00:01 | KEY | KEY | * 2 | INDEX RANGE SCAN | IDX_PARTKEY2 | 2 | 46 | 2 (0) | 00:00:01 | 1 | 1 |--

Predicate Information (identified by operation id):-

2-access ("OBJECT_NAME" LIKE 'OR%' AND "OWNER" =' SYS') filter ("OBJECT_NAME" LIKE 'OR%')

Statistics-1 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 2279 bytes sent via SQL*Net to client 556 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 58 rows processed Query with indexes without partition key values But there is no partition condition in the where condition.

Set autotrace traceonly SELECT object_name FROM parttest WHERE created=to_date ('2014-12-15 22-12-15-22-22-29-22-12-15-22-29-22-12-15-22-29-12-15-22-29-12-15-22-29-12-15-22-29-12-15-22-29-12-15-22-29-12-15-22-29-12-15-12-15-22-12-15-22-29-12-15-22-12-15-12-15-12-15-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-15-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22

Execution Plan-Plan hash value: 646636157

-| Id | Operation | | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |-| -| 0 | SELECT STATEMENT | | 35 | 945 | 13 (0) | 00:00:01 | 1 | PARTITION LIST ALL | 35 | 945 | 13 (0) | 00:00:01 | 1 | 11 | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | PARTTEST | 35 | 945 | 13 (0) | 00:00:01 | 1 | 11 | * 3 | INDEX RANGE SCAN | IDX_NOPARTKEY | 35 | 12 (0) | 00:00:01 | | 1 | 11 |-|

Predicate Information (identified by operation id):-

3-access ("CREATED" = TO_DATE ('2014-12-15 22 TO_DATE 29 syyyy-mm-dd hh34:mi:ss'))

Statistics-1 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 1780 bytes sent via SQL*Net to client 545 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 41 rows processed fourth case Query with indexes without partition key values But the partition condition is added to the where condition

Set autotrace traceonly SELECT object_name FROM parttest a WHERE created=to_date ('2014-12-15 22-12-15-22-22-29-22-22-22-22-22-22-22-12-15-12-15-22-29-22-22-22-22-22-22-29-22-22-22-22-29-22-22-29-22-22-22-29-22-22-29-22-22-12-15-22-12-15-22-12-15-22-12-15-12-15-22-12-15-22-12-15-22-22-29-12-15-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-12-15 22-12-15

Execution Plan-Plan hash value: 3242664717

-| Id | Operation | | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |-| -| 0 | SELECT STATEMENT | 28 | 868 | 2 (0) | 00:00:01 | 1 | PARTITION LIST SINGLE | 28 | 868 | 2 (0) | 00:00:01 | KEY | KEY | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | PARTTEST | 28 | 868 | 2 (0) | 00:00:01 | 1 | 1 | * 3 | INDEX RANGE SCAN | IDX_NOPARTKEY | 28 | 1 | 1 (0) | 00:00:01 | | 1 | 1 |-|

Predicate Information (identified by operation id):-

3-access ("CREATED" = TO_DATE ('2014-12-15 22 TO_DATE 29 syyyy-mm-dd hh34:mi:ss'))

Statistics-0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 1191 bytes sent via SQL*Net to client 534 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 21 rows processed

Fifth case: query with an index with a partition key value, but add a partition bar to the where condition (the same as the 4 index key, but with the index key value)

SELECT object_name FROM parttest a WHERE created=to_date ('2014-12-15 22-12-15-22-22-29-22-22-22-22-22-22-12-15-22-29-22-22-12-15-22-29-22-12-15-22-12-15-22-29-22-12-15-12-15-22-29-22-12-15-22-29-22-12-15-12-15-22-29-12-15-22-12-15-12-15-12-15-12-15-22-22-12-15-22-22-12-15 22 AND owner='SYS'; Execution Plan (' 2014-12-15 22 AND owner='SYS'; Execution Plan-Plan hash value: 1150146376

-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |-| -| 0 | SELECT STATEMENT | 28 | 868 | 2 (0) | 00:00:01 | 1 | PARTITION LIST SINGLE | 28 | 868 | 2 (0) | 00:00:01 | KEY | KEY | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | PARTTEST | 28 | 868 | 2 (0) | 00:00:01 | 1 | 1 | * 3 | INDEX RANGE SCAN | IDX_PARTKEY | 17 | 1 | 1 | 1 | 0 | 00:00:01 | 1 | 1 | |-

Predicate Information (identified by operation id):-

3-access ("CREATED" = TO_DATE ('2014-12-15 22 TO_DATE 29 syyyy-mm-dd hh34:mi:ss') AND "OWNER" =' SYS')

Statistics-0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 1191 bytes sent via SQL*Net to client 534 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 21 rows processed sixth case Query with an index with partition key values But in the where condition, add the partition bar (same as the index key of 4, except with the leading index key value) set autotrace traceonly SELECT object_name FROM parttest a WHERE created=to_date ('2014-12-15 22 HH24:MI:SS') AND owner='SYS' (' YYYY MMMI DD HH24:MI:SS')

Execution Plan-Plan hash value: 1150146376

-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |-| -| 0 | SELECT STATEMENT | 28 | 868 | 2 (0) | 00:00:01 | 1 | PARTITION LIST SINGLE | 28 | 868 | 2 (0) | 00:00:01 | KEY | KEY | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | PARTTEST | 28 | 868 | 2 (0) | 00:00:01 | 1 | 1 | * 3 | INDEX RANGE SCAN | IDX_PARTKEY | 17 | 1 | 1 | 1 | 0 | 00:00:01 | 1 | 1 | |-

Predicate Information (identified by operation id):-

3-access ("CREATED" = TO_DATE ('2014-12-15 22 TO_DATE 29 syyyy-mm-dd hh34:mi:ss') AND "OWNER" =' SYS')

Statistics-0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 1191 bytes sent via SQL*Net to client 534 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 21 rows processed

Seventh case: cross-partition query with an index with a partition key value, but add a partition condition to the where condition (same as the index key of 4, but with index leading key value) set autotrace traceonly SELECT object_name FROM parttest a WHERE object_name LIKE 'OR%' AND owner IN (' SYS','SUN') Execution Plan-Plan hash value: 1341146800

-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) ) | Time | Pstart | Pstop |-| 0 | SELECT STATEMENT | | 1 | | 25 | 3 (0) | 00:00:01 | 1 | INLIST ITERATOR | 2 | PARTITION LIST ITERATOR | | 1 | 25 | 3 (0) | 00:00:01 | KEY (I) | KEY (I) | | * 3 | INDEX RANGE SCAN | IDX_PARTKEY3 | 1 | 25 | 3 (0) | 00:00:01 | KEY (I) | KEY (I) |-|

Predicate Information (identified by operation id):-

3-access (("OWNER" = 'SUN' OR "OWNER" =' SYS') AND "OBJECT_NAME" LIKE 'OR%') filter ("OBJECT_NAME" LIKE' OR%')

Statistics-1 recursive calls 0 db block gets 8 consistent gets 1 physical reads 0 redo size 2540 bytes sent via SQL*Net to client 567 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 62 rows processed

Eighth case: cross-partition query with an index with a partition key value, but add a partition bar to the where condition (same as the index key of 4, but with a leading key value) set autotrace traceonly SELECT object_name FROM parttest a WHERE object_name LIKE 'OR%' AND owner IN (' SYS','SUN')

Execution Plan-Plan hash value: 2095150599

-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | | Pstart | Pstop |-| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0 | ) | 00:00:01 | 1 | PARTITION LIST INLIST | | 1 | 25 | 3 (0) | 00:00:01 | KEY (I) | KEY (I) | | * 2 | INDEX RANGE SCAN | IDX_PARTKEY2 | 1 | 25 | 3 (0) | 00:00:01 | KEY (I) | KEY (I) |-

Predicate Information (identified by operation id):-

2-access ("OBJECT_NAME" LIKE 'OR%') filter ("OBJECT_NAME" LIKE' OR%')

Statistics-209 recursive calls 2 db block gets 180 consistent gets 0 physical reads 0 redo size 2497 bytes sent via SQL*Net to client 567 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 13 sorts (memory) 0 sorts (disk) 62 rows processed

Case 9: cross-partition query with index with partition key value, but add partition condition to where condition (same as index key of 4, but with index key value)

Set autotrace traceonly SELECT object_name FROM parttest a WHERE object_name LIKE 'OR%' AND owner IN (' SYS','SUN')

Execution Plan-Plan hash value: 2097624711

-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |- -| 0 | SELECT STATEMENT | | 1 | 25 | 5 (0) | 00:00:01 | 1 | PARTITION LIST INLIST | 1 | 25 | 5 (0) | 00:00:01 | KEY (I) | KEY (I) | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | PARTTEST | 1 | 25 | 5 (0) | 00:00:01 | KEY (I) | KEY (I) | | * 3 | INDEX RANGE SCAN | IDX_NOPARTKEY2 | 3 | | | 3 (0) | 00:00:01 | KEY (I) | KEY (I) |- -

Predicate Information (identified by operation id):-

3-access ("OBJECT_NAME" LIKE 'OR%') filter ("OBJECT_NAME" LIKE' OR%')

Statistics-1 recursive calls 0 db block gets 27 consistent gets 1 physical reads 0 redo size 2497 bytes sent via SQL*Net to client 567 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 62 rows processed

Summary:

1. In the use of partition representation, the WHERE condition is best with a partition key, otherwise it will lose the meaning of partition. A partition is physically a table, and full-partition table scans have more IO reads than all non-partition table scans. In the case of 2.WHERE with partition, whether a single partition has a partition key or not does not seem to make much sense. In the case of cross-partition scanning, the index with leading partition key is efficient. To sum up, if you need to create a combined index, it is recommended that you create a partitioned index with a leading partitioning key.

3. The test of creating a global partitioned index on a non-partitioned table is different from a normal index, which seems to be of little significance and the usage scenario is unknown.

CREATE TABLE gpart AS select owner,object_id,object_name,created from DBA_OBJECTS; SELECT distinct TO_char (created,'YYYY-MM-DD') FROM gpart

Exec dbms_stats.gather_table_stats ('SUN','GPART',cascade= > true,no_invalidate= > false,method_opt= >' for all columns size 1'> dbms_stats.auto_sample_size,degree= > 24)

Create index idx_gpart1 ON gpart (created) nologging; DROP INDEX idx_gpart1

Set autotrace traceonly SELECT * FROM gpart t WHERE created > TO_DATE ('2015-04-02 (' 2015-04-02)

Execution Plan-Plan hash value: 4136711861

-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1005 | 36180 | 13 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | GPART | 1005 | 36180 | 13 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN | IDX_GPART1 | 1005 | | 4 (0) | 00:00:01 |-

Predicate Information (identified by operation id):-

2-access ("CREATED" > TO_DATE ('2015-04-02 00 access,' syyyy-mm-dd hh34:mi:ss'))

Statistics-1 recursive calls 0 db block gets 34 consistent gets 0 physical reads 0 redo size 9616 bytes sent via SQL*Net to client 644 bytes received via SQL*Net from client 13 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 174rows processed create index idx_gpart2 On gpart (created) global partition by range (created) (partition GLOBAL1 values less than (TO_DATE ('2014-12-15') ), partition GLOBAL2 values less than (TO_DATE ('2015-03-11)), partition GLOBAL3 values less than (TO_DATE (' 2015-03-24)), partition GLOBAL4 values less than (TO_DATE ('2015-04-01)), partition GLOBAL5 values less than (MAXVALUE)) nologging DROP INDEX idx_gpart2

Set autotrace traceonly SELECT * FROM gpart t WHERE created > TO_DATE ('2015-04-02 (' 2015-04-02)

Execution Plan-Plan hash value: 4217733073

-| Id | Operation | Name | Rows | Bytes | Cost ( % CPU) | Time | Pstart | Pstop |-| 0 | SELECT STATEMENT | | 1005 | 36180 | 13 (0) | 00:00:01 | 1 | PARTITION RANGE SINGLE | | 1005 | 36180 | 13 (0) | 00:00:01 | 5 | 5 | 2 | TABLE ACCESS BY INDEX ROWID | GPART | 1005 | 36180 | 13 (0) | 00:00:01 | * 3 | INDEX RANGE SCAN | IDX_GPART2 | 1005 | 4 (0) | 00:00:01 | 5 | 5 |- -

Predicate Information (identified by operation id):-

3-access ("CREATED" > TO_DATE ('2015-04-02 00 access,' syyyy-mm-dd hh34:mi:ss'))

Statistics-1 recursive calls 0 db block gets 34 consistent gets 0 physical reads 0 redo size 5769 bytes sent via SQL*Net to client 644 bytes received via SQL*Net from client 13 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 174rows processed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.