Oracle Database efficiency tips: avoiding incorrect indexes (1)

Guidance:SometimesIndexWill causeOracle DatabaseThis problem can be effectively avoided through some methods or techniques. In this article, we will avoid using incorrect database indexes to improve the efficiency of Oracle databases.

In this example, if I want to use idx_a instead of idx_ B.

SQL> create table test
2 (a int, B int, c int, d int );
Table created.
SQL> begin
2 for I in 1 .. 50000
3 loop
4 insert into mytest values (I, I );
5 end loop;
6 commit;
7 end;
8/
PL/SQL procedure successfully completed.
SQL> create index idx_a on mytest (a, B, c );
Index created.
SQL> create index idx_ B on mytest (B );
Index created.

For example, the table mytest has fields a, B, c, d, and the joint index idx_a (a, B, c) is created on a, B, and c ), an index idx_ B (B) is created separately on B ).

Under normal circumstances, where a =? And B =? And c =? Index idx_a, where B =? Index idx_ B is used.

For example:

SQL> analyze table mytest compute statistics;
Table analyzed.
SQL> select num_Rows from user_tables where table_name = 'mytest ';
NUM_ROWS
----------
50000
SQL> select distinct_keys from user_indexes where index_name = 'idx _ ';
DISTINCT_KEYS
-------------
50000
SQL> set autotrace traceonly
SQL> select d from mytest
2 where a = 10 and B = 10 and c = 10;
Execution Plan
----------------------------------------------------------
Plan hash value: 1542625214
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time
|
--------------------------------------------------------------------------------
------

| 0 | select statement | 1 | 16 | 2 (0) | 00: 0
0: 01 |
| 1 | table access by index rowid | MYTEST | 1 | 16 | 2 (0) | 00:0
0: 01 |
| * 2 | index range scan | IDX_A | 1 | 1 (0) | 00: 0
0: 01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("A" = 10 AND "B" = 10 AND "C" = 10)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
4 consistent gets
0 physical reads
0 redo size
508 bytes sent via SQL * Net to client
492 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select d from mytest
2 where B = 500;
Execution Plan
----------------------------------------------------------
Plan hash value: 530004086
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time
|
--------------------------------------------------------------------------------
------
| 0 | select statement | 1 | 8 | 2 (0) | 00: 0
0: 01 |
| 1 | table access by index rowid | MYTEST | 1 | 8 | 2 (0) | 00:0
0: 01 |
| * 2 | index range scan | IDX_ B | 1 | 1 (0) | 00: 0
0: 01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("B" = 500)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
4 consistent gets
0 physical reads
0 redo size
508 bytes sent via SQL * Net to client
492 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed