Performance impact of Localprefixedindex and Localnonprefixedindex on select statements

Before comparing the effects of the two indexes on the select statement, you must first understand what is Localprefixedindex and what is Localnonprefixedindex. Actually, these two types

Before comparing the effects of the two indexes on the select statement, you must first understand what the Local prefixed index is and what the Local nonprefixed index is. Actually, these two types

1. clarify the concepts of the two indexes

Before comparing the effects of the two indexes on the select statement, you must first understand what the Local prefixed index is and what the Local nonprefixed index is. In fact, both types of indexes belong to the partition local index. Therefore, these two types of indexes may only appear on the partition table.

1.1 What is Local prefixed index

It refers to the column in the index, that is, the partition key column of the partition table, or the column in the index, which contains the partition key value column of the table and is the prefix

The index of the local partition located at the beginning of the index.

For example, the emp table is partitioned by time range, and the partition key column is create_time. If the column in the partition index is create_time,

Or it is a local composite index of the (create_time, emp_no) column.

1.2 What is Local nonprefixed index

After understanding what is Local prefixedindex, it is easy to understand what is Local nonprefixed index.

It refers to the column in the index, which does not contain the partition key column of the partition table, or the local partition index where the partition key value column is not in the front position.

For example, the emp table is partitioned by time range, and the partition key column is create_time. If the column in the partition index does not contain the create_time column, or is like (emp_no, create_time) the create_time column does not have a local partition index located before the index.

2. How to query the index type

View: DBA_PART_INDEXES

LOCALITY field: whether the record is a LOCAL index

ALIGNMENT field: whether the record is PREFIXED index or NON_PREFIXED Index

3. Prepare and verify the test environment 3.1. Create a partition table

Create table tivoli. li_db_session_t (

Dbname

Allsess

Activess

Timstap date)

Timstap)

To_date

To_date

To_date

To_date

To_date

To_date

To_date

To_date

To_date

To_date

To_date

To_date

To_date

To_date

To_date

To_date

To_date

To_date

To_date

);

Insert 4406727 rows of data. The entire table size is 312 MB.

3.2 Create indexes in five scenarios

-- Local prefixed index type 1:

-- Local prefixed index type 2:

-- Local nonprefixed index type 1:

-- Local nonprefixed index type 2:

-- Global index:

(This index is safe and consistent with Tivoli. li_idx_04. Therefore, two indexes cannot coexist. You must delete Tivoli. li_idx_04 before creating the Tivoli. li_idx_05 index)

Create index Tivoli. li_idx_05on tivoli. li_db_session_t (dbname, allsess, timstap, activess );

3.3 perform statistical analysis on tables and Indexes

Begin

Dbms_stats.gather_table_statstabnameestimate_percent

End;

3.4 verify the type of the created index

The output result is as follows:

INDEX_NAME

PARTITIONING_TYPE

SUBPARTITIONING_TYPE

LOCALITY

ALIGNMENT

LI_IDX_01

RANGE

NONE

LOCAL

PREFIXED

LI_IDX_02

RANGE

NONE

LOCAL

PREFIXED

LI_IDX_03

RANGE

NONE

LOCAL

NON_PREFIXED

LI_IDX_04

RANGE

NONE

LOCAL

NON_PREFIXED

The LI_IDX_05 query has no results because it has not been created. In fact, if LI_IDX_05 is not a partition index, even if the index is created, it will not appear in the DBA_PART_INDEXES view.

4. Performance Comparison under five index types

Use a select statement as the test statement.

4.1 scenario 1: local prefixed type, index column: Table partition key column

SQL> set autotrace traceonly

SQL> set linesize 999

SQL> select/* + index (t li_idx_01) */* from tivoli. li_db_session_t t where t. allsess = 28 and t. dbname = 'costdb' and t. timstap> to_date ('2017-01-01 ', 'yyyy-mm-dd') and t. timstap <to_date ('2017-01-20 ', 'yyyy-mm-dd') and t. activess = 4;

498 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3409921846

Bytes ----------------------------------------------------------------------------------------------------------------------

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

Bytes ----------------------------------------------------------------------------------------------------------------------

| 0 | select statement | 10 | 200 | 208 K (1) | 00:41:38 |

| 1 | partition range iterator | 10 | 200 | 208 K (1) | 00:41:38 | 11 | 12 |

| * 2 | table access by local index rowid | LI_DB_SESSION_T | 10 | 200 | 208 K (1) | 00:41:38 | 11 | 12 |