Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand the incomplete index of oracle 12c partition table". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "how to understand the incomplete index of oracle 12c partitioned table"!

According to the business characteristics, some partition tables have hot data only in recent months, even in the current month. At this time, indexing on other inactive partitions is a waste of storage space, and it will also affect the performance of some global indexes. Oracle 12c provides the function of building indexes only on some partitions, and the emergence of incomplete indexes perfectly solves this problem.

Create a partition table

Create table part1

(id int, code int,name varchar2)

Indexing off

Partition by range (id)

(partition p1 values less than (1000)

Partition p2 values less than (2000)

Partition p3 values less than (3000) indexing on

);

Oracle uses the keyword indexing as the switch for incomplete indexes. In this table-building statement, the table-level indexing defaults to on, and if the indexing attribute of the partition is not specified, the table-level parameters are inherited. The table-building statement here turns off the table-level indexing and shows that the partition P3 indexing is on, then the indexing of the other three partitions p1 and p2 is off. This makes it possible to build an incomplete index on the P3 partition.

This keyword attribute can be obtained by looking at the indexing of dba_tab_partitions

SYS@cdbtest1 (MING) > COL PARTITION_NAME FOR A30

SYS@cdbtest1 (MING) > select PARTITION_NAME,indexing from dba_tab_partitions where table_owner='MING' AND TABLE_NAME='PART1'

PARTITION_NAME INDE

-

P1 OFF

P2 OFF

P3 ON

You can see that only P3 displays the specified ON, and other partitions integrate table-level indexing attributes.

Insert data

Begin

For i in 1.. 2999 loop

Insert into part1 values (iMagna iMagna Mingshuo')

End loop

Commit

End

/

Create a global index

Create index id_part1_global on part1 (id) global indexing full

Create index code_part1_global on part1 (code) global indexing partial

SYS@cdbtest1 (MING) > col INDEX_NAME for A30

SYS@cdbtest1 (MING) > select index_name,partitioned,indexing from dba_indexes where index_name in ('ID_PART1_GLOBAL','CODE_PART1_GLOBAL')

INDEX_NAME PAR INDEXIN

-

CODE_PART1_GLOBAL NO PARTIAL

ID_PART1_GLOBAL NO FULL

Indexing full is the default, and although the P1 Personality P2 partition indexing is OFF, if indexing full is specified when the index is created, the index will still be created based on all partitions

If indexing partial is specified, the index is selectively created based on the partition where indexing is ON, based on the indexing property of the partition.

That is, the indexing parameter specified when the index is created has a higher priority and overrides the indexing attribute in the table definition.

If it is not specified when the index is created, then it takes effect according to the default indexing full, and when the index is created, the indexing partial is specified according to the indexing attribute when the table is defined.

Here, id_part1_global is a full index and code_part1_global is an incomplete index.

In this regard, use the implementation plan to confirm it.

MING@ming (MING) > explain plan for select count (id) from part1

Explained.

MING@ming (MING) > set line 200

MING@ming (MING) > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

-

Plan hash value: 2604063405

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 13 | 5 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 13 |

| | 2 | INDEX FAST FULL SCAN | ID_PART1_GLOBAL | 1 | 13 | 5 (0) | 00:00:01 |

-

9 rows selected.

The id column is a normal global index, so the index is scanned quickly.

There is an incomplete index on the code column. Count (code) take a look at the execution plan:

Delete the index on the previous id column first to avoid interference

Drop index ID_PART1_GLOBAL

MING@ming (MING) > explain plan for select count (code) from part1

Explained.

MING@ming (MING) > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

- -

Plan hash value: 1564279961

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |

-

| | 0 | SELECT STATEMENT | | 1 | 13 | 549 (1) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 13 |

| | 2 | VIEW | VW_TE_2 | 2 | 26 | 549 (1) | 00:00:01 |

| | 3 | UNION-ALL | | |

| | * 4 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | PART1 | 1 | 26 | 1 (0) | 00:00:01 | 3 | 3 |

| | * 5 | INDEX RANGE SCAN | CODE_PART1_GLOBAL | 1 | | 1 (0) | 00:00:01 |

| | 6 | PARTITION RANGE ITERATOR | | 1 | 26 | 548 (1) | 00:00:01 | 1 | 2 |

| | * 7 | TABLE ACCESS FULL | PART1 | 1 | 26 | 548 (1) | 00:00:01 | 1 | 2 |

-

Predicate Information (identified by operation id):

4-filter ("PART1". "ID" > = 2000 AND "PART1". "ID" 2600)

7-filter ("CODE" > 2600)

10 rows selected.

Looking at the execution plan, we can see that the P3 partition does use the CODE_PART1_GLOBAL index, the other partitions use a full table scan, and there are two result sets union all.

Let's take a look at the partition index.

Drop index CODE_PART1_GLOBAL

Create index id_part1_partial on part1 (id) local indexing partial

Create index code_part1_partial on part1 (code) local indexing full

SYS@cdbtest1 (MING) > select index_name,partitioned,indexing from dba_indexes where index_name in ('ID_PART1_PARTIAL','CODE_PART1_PARTIAL')

INDEX_NAME PAR INDEXIN

-

ID_PART1_PARTIAL YES PARTIAL

CODE_PART1_PARTIAL YES FULL

SYS@cdbtest1 (MING) > col HIGH_VALUE for A15

SYS@cdbtest1 (MING) > col PARTITION_NAME for A20

SYS@cdbtest1 (MING) > col index_name for A25

SYS@cdbtest1 (MING) > set line 150

SYS@cdbtest1 (MING) > select INDEX_NAME,HIGH_VALUE,PARTITION_NAME,STATUS,LEAF_BLOCKS,NUM_ROWS from dba_ind_partitions where INDEX_NAME in ('ID_PART1_PARTIAL','CODE_PART1_PARTIAL')

INDEX_NAME HIGH_VALUE PARTITION_NAME STATUS LEAF_BLOCKS NUM_ROWS

--

CODE_PART1_PARTIAL 1000 P1 USABLE 3 999

CODE_PART1_PARTIAL 2000 P2 USABLE 3 1000

CODE_PART1_PARTIAL 3000 P3 USABLE 3 1000

ID_PART1_PARTIAL 1000 P1 UNUSABLE 00

ID_PART1_PARTIAL 2000 P2 UNUSABLE 00

ID_PART1_PARTIAL 3000 P3 USABLE 3 1000

6 rows selected.

As you can see from the status and num_rows columns, indexing full does have a higher priority and does not specify indexing. According to the table definition, the index on the id column exists only in the P3 partition, and the code column index has it in each partition.

Let's also take a look at the performance of incomplete indexes in the execution plan:

MING@ming (MING) > select count (code) from part1 where code2900)

Note

-

-dynamic statistics used: dynamic sampling (level=2)

Statistics

11 recursive calls

4 db block gets

44 consistent gets

4 physical reads

0 redo size

543 bytes sent via SQL*Net to client

607 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

MING@ming (MING) > select count (id) from part1 where id > 1900

Execution Plan

Plan hash value: 712638347

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |

-

| | 0 | SELECT STATEMENT | | 1 | 13 | 277 (1) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 13 |

| | 2 | VIEW | VW_TE_2 | 1422 | 18486 | 277 (1) | 00:00:01 |

| | 3 | UNION-ALL | | |

| | 4 | PARTITION RANGE SINGLE | | 1 | 25 | 1 (0) | 00:00:01 | 3 | 3 |

| | * 5 | INDEX RANGE SCAN | ID_PART1_PARTIAL | 1 | 25 | 1 (0) | 00:00:01 | 3 | 3 |

| | 6 | PARTITION RANGE SINGLE | | 1421 | 35525 | 276 (1) | 00:00:01 | 2 | 2 |

| | * 7 | TABLE ACCESS FULL | PART1 | 1421 | 35525 | 276 (1) | 00:00:01 | 2 | 2 |

-

Predicate Information (identified by operation id):

5-access ("PART1". "ID" > = 2000 AND "PART1". "ID" 1900)

When id > 1900, it will span two partitions, P2 will scan partitions, P3 will scan incomplete indexes, and two result sets union all.

Change the indexing property of the table

For example, when I need the indexing attribute of P2Die P3 to be ON,P2, it is OFF, which can be modified by the following statement:

Alter table part1 modify partition P2 indexing on

In the actual production environment, if you do not understand this incomplete index before, you will not set the table level indexing to OFF in the table-building statement I tested above. Later, if you want to use this new feature, you need to modify the partition indexing. In this case, you need the above statement to change the partition level to OFF.

MING@ming (MING) > COL PARTITION_NAME FOR A30

MING@ming (MING) > select PARTITION_NAME,indexing from dba_tab_partitions where table_owner='MING' AND TABLE_NAME='PART1'

PARTITION_NAME INDE

-

P1 OFF

P2 ON

P3 ON

We should pay attention to the validity of the index here.

If you previously changed the partition indexing from ON to OFF, the pre-existing incomplete index on the partition will be invalidated, in other words, the usable or unusable state of the existing incomplete index corresponds to the ON or OFF of indexing. Indexing changes from OFF to ON, index from unusable to usable,indexing, from ON to OFF, and index from usable to unusable.

At this point, I believe you have a deeper understanding of "how to understand the incomplete index of oracle 12c partitioned tables". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.