Performance optimization of Oracle

Keywords: Operation & Maintenance SQL Oracle Database

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





Posted by JukEboX on Tue, 03 Dec 2019 02:47:47 -0800