The previous life of the analytic index of performance optimization of Oracle
Summary: in oracle, index is like a book's catalog, which can speed up the query speed and improve the query efficiency. In oracle, b-tree index is used to store the index. B-number index is a tree structure with branches and leaves. Index is divided into root node, branch node and leaf node.
Internal information of index
-----Create table
SQL> create table index_test as select * from user_objects; Table created
---Create an index on a table's fields
SQL> create index inx_test on index_test(object_id); Index created
-- analysis table
SQL> analyze index inx_test validate structure; Index analyzed
---View internal information of index
SQL> select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats; HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS ---------- ---------- ---------- ---------- ---------- ----------- 1 8 0 1 48 0 SQL> select count(*) from index_test; COUNT(*) ---------- 48
----Randomly select several pieces of data as the basis of subsequent DML operation
SQL> select * from(select object_id from index_test order by dbms_random.value()) where rownum<5; OBJECT_ID ---------- 52647 53095 52646 51151
----Select the first record as an update operation
SQL> update index_test set object_id=11111 where object_id=52647; 1 row updated
----Analysis table
SQL> analyze index inx_test validate structure; Index analyzed
----View internal information of inode
SQL> select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats; HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS ---------- ---------- ---------- ---------- ---------- ----------- 1 8 0 1 49 1 ------Try one. Insert operation SQL> insert into index_test select * from index_test; 48 rows inserted ---Analysis table SQL> analyze index inx_test validate structure; Index analyzed ----View internal information of inode SQL> select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats; HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS ---------- ---------- ---------- ---------- ---------- ----------- 1 8 0 1 96 0
The practice of index access mode
(1) index unique scan
----Create table
SQL> create table a as select object_id,object_name,object_type from dba_objects; Table created
---View table structure
SQL> desc a; Name Type Nullable Default Comments ----------- ------------- -------- ------- -------- OBJECT_ID NUMBER Y OBJECT_NAME VARCHAR2(128) Y OBJECT_TYPE VARCHAR2(19) Y
-- analysis table
SQL> analyze table a compute statistics; Table analyzed
----Create a unique index on the obkect? ID field of a table
SQL> create unique index ind_a on a(object_id); Index created SQL> set autot traceonly exp; Cannot SET AUTOT
----Execute query statement and view execution plan
SQL> select * from a where object_id=258;
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------------------------------------------------------------------- -------------------
258 DUAL TABLE
SQL> EXPLAIN PLAN FOR select * from a where object_id=258;
Explained
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2087649606
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00
| 1 | TABLE ACCESS BY INDEX ROWID| A | 1 | 36 | 2 (0)| 00:00
|* 2 | INDEX UNIQUE SCAN | IND_A | 1 | | 1 (0)| 00:00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=258)
14 rows selected(2) fast index full scan
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as scott@ORCL SQL> EXPLAIN PLAN FOR select * from a; Explained SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2248738933 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50858 | 1787K| 73 (3)| 00:00:01 | | 1 | TABLE ACCESS FULL| A | 50858 | 1787K| 73 (3)| 00:00:01 | -------------------------------------------------------------------------- 8 rows selected SQL> select count(*) from a where object_id is null; COUNT(*) ---------- 0 //Simply modify the properties of some columns, eliminate the interference of NULL, and you will go fast index full scan SQL> alter table a nodify(object_id not null); table altered SQL> EXPLAIN PLAN FOR select object_id from a; Explained SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 672397539 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 50858 | 198K| 26 (4)| 00:00:01 | | 1 | INDEX FAST FULL SCAN| IND_A | 50858 | 198K| 26 (4)| 00:00:01 | ------------------------------------------------------------------------------ 8 rows selected
(3) index full scan
SQL> EXPLAIN PLAN FOR select * from a order by object_id; Explained SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3699785968 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50858 | 1787K| 467 (1)| 00:00 | 1 | TABLE ACCESS BY INDEX ROWID| A | 50858 | 1787K| 467 (1)| 00:00 | 2 | INDEX FULL SCAN | IND_A | 50858 | | 108 (2)| 00:00 -------------------------------------------------------------------------------- 9 rows selected
(4) interval scanning
If the interval value of the index column is involved, interval scanning can be used. For example, the commonly used between condition will take interval scanning
SQL> EXPLAIN PLAN FOR select * from a where object_id between 2000 and 2050;
Explained
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1070634250
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 1656 | 3 (0)| 00:00
| 1 | TABLE ACCESS BY INDEX ROWID| A | 46 | 1656 | 3 (0)| 00:00
|* 2 | INDEX RANGE SCAN | IND_A | 46 | | 2 (0)| 00:00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=2000 AND "OBJECT_ID"<=2050)
14 rows selected