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

Indexing is a database search optimization tool that we often use. Appropriate business operation scenarios Using appropriate indexing schemes can significantly improve overall system performance and user experience. In Oracle, indexes include many types. Different types of indexes adapt to different system environments and access scenarios. Unique Index is one of the indexes we use frequently.

The biggest difference between the unique index and the normal index is that a layer of unique constraints is added to the index column. The data column to which the unique index is added can be empty, but it must be unique as long as there are data values.

So, what is the difference between using a unique index and a general index? Here is a series of demonstration experiments to illustrate.

1. Preparation of experimental environment

For consistency and possible diversity, select the same columns of data values to join different types of index structures.

SQL> select * from v$version where rownum create table t as select * from dba_objects;

Table created

//Make sure that data_object_id and object_id have the same value;

SQL> update t set data_object_id=object_id;

72581 rows updated

SQL> commit;

Commit complete

//General index

SQL> create index idx_t_normalid on t(object_id);

Index created

//Uniqueness index

SQL> create unique index idx_t_uniid on t(data_object_id);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

2. Volume capacity comparison

In environment preparation, we set the index column values to be exactly the same to avoid differences due to external reasons. Next we check the capacity comparison information in the data dictionary.

First, check the index segment information.

SQL> select segment_name, segment_type, bytes, blocks, extents from dba_segments where segment_name in ('IDX_T_NORMALID','IDX_T_UNIID');

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS EXTENTS

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

IDX_T_NORMALID INDEX 983040 120 15

IDX_T_UNIID INDEX 917504 112 14

The normal index consumes slightly more space than the unique index. The index idx_t_normalid occupies 15 partitions and 120 blocks. 14 blocks slightly larger than idx_t_uniid.

It should be noted in this place that the partition occupied by a segment in the data dictionary is the space allocated by the Oracle system and does not mean that it is all used up. The two index structures may differ slightly, but an extra extent is allocated.

Index leaf structure, check the contents of the data dictionary.

SQL> select index_name, index_type, UNIQUENESS, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name in ('IDX_T_NORMALID','IDX_T_UNIID');

INDEX_NAME INDEX_TYPE UNIQUENESS BLEVEL LEAF_BLOCKS DISTINCT_KEYS

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

IDX_T_UNIID NORMAL UNIQUE 1 106 51330

IDX_T_NORMALID NORMAL NONUNIQUE 1 113 51330

The difference between the two is not big, normal index space consumption is slightly larger than unique index.

Conclusion: From the data dictionary, we can know that the space consumption of unique index is smaller than that of general index. Since we are using the same experimental data, this small difference may mean that there are slight differences in storage structure between the two index types.

3. Violation of constraint experiments

As a unique index, it plays a role of constraint strictly when adding, creating and performing dml operations.

SQL> insert into t select * from t where rownum select * from t where data_object_id=1000;

plan of implementation

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

Plan hash value: 335537167

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

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

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

| 0 | SELECT STATEMENT | | 1 | 101 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 101 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | IDX_T_UNIID | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("DATA_OBJECT_ID"=1000)

statistics

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

528 recursive calls

0 db block gets

86 consistent gets

10 physical reads

0 redo size

1200 bytes sent via SQL*Net to client

376 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

1 rows processed

Here, we see a feature of Unique Index, which is the difference in execution plans when the equals operation is performed. For Unique Index, an equality query results in only one row of values or none, so there is no need for the traditional Index Range Scan operation. Here, the Index Unique Scan operation is used in the execution plan, which directly locates the specified record item and returns the rowid record.

In general, Index Range Scan operation is usually used when performing equal sign retrieval. Scan horizontally along the leaf nodes of the index tree until you get a list of rowids whose indexes match the column values of the conditional index.

From the perspective of cost, although there are certain differences in the implementation plan operation mode between the two, the actual cost difference is not significant. CPU cost and execution time are the same. There are some differences between block read operations (logical read and physical read), which I think stem from the slight difference between the two index structures, so there must be some difference in the number of blocks read.

5. Range Search Experiment

What is the difference in execution plan and cost when we do range search of index columns?

--Range matching

SQL> select * from t where object_id>=1000 and object_id=1000 AND "OBJECT_ID" select * from t where data_object_id>=1000 and data_object_id=1000 AND "DATA_OBJECT_ID"

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.