I have got an audit table as below.
create table "AUDIT_LOG"
(
"AUDIT_ID" NVARCHAR2(70),
"PAYMENT_IDENTIFICATION_ID" NVARCHAR2(70),
"ACCOUNT_NUMBER" NVARCHAR2(100)
PRIMARY KEY ("AUDIT_ID")
);
I have below index
- payment_idx on ("PAYMENT_IDENTIFICATION_ID")
- payment_id_idx on ("PAYMENT_IDENTIFICATION_ID", "AUDIT_ID")
- system_index on primary key AUDIT_ID
Below are query i am be using
Query1 :
Select * FROM
AUDIT_LOG
WHERE
PAYMENT_IDENTIFICATION_ID =
'ID124'
AND
AUDIT_ID<>'ecfdc2c3-87eb-48c9-b53c';
Query2 :
Select * FROM
AUDIT_LOG
WHERE
PAYMENT_IDENTIFICATION_ID =
'ID124'
AND
AUDIT_ID='ecfdc2c3-87eb-48c9-b53c';
First query explain plan show the usage of index payment_id_idx having option BY INDEX ROWID BATCHED.
However second query explain plan showing usage of system_index on primary key AUDIT_ID with option BY INDEX ROWID BATCHED.
I was of the opinion that in the both query index payment_id_idx should be used. Any idea why second query is not using composite index payment_id_idx. Any help is much appreciated.
CodePudding user response：
Let's try to simulate a scenario similar to yours.
SQL> alter session set current_schema=test ;
Session altered.
SQL> create table "AUDIT_LOG"
(
"AUDIT_ID" NVARCHAR2(70),
"PAYMENT_IDENTIFICATION_ID" NVARCHAR2(70),
"ACCOUNT_NUMBER" NVARCHAR2(100)
); 2 3 4 5 6
Table created.
SQL> alter table audit_log add primary key ( audit_id ) ;
Table altered.
SQL> create index payment_idx on audit_log ("PAYMENT_IDENTIFICATION_ID");
Index created.
SQL> create index payment_id_idx on audit_log ("PAYMENT_IDENTIFICATION_ID", "AUDIT_ID");
Index created.
Now let's insert some demo data, but following some considerations:
AUDIT_ID is unique in the form of
IDxxx( wherexxxtakes values from 1 to 1M )PAYMENT_IDENTIFICATION_ID takes 10 distinct values in the form of
LPADand a letter. The idea here is to generate 10 distinct valuesACCOUNT_NUMBER is a random string of one letter and one letter in lpad to fill up 70 characters.
Thus
declare
begin
for i in 1 .. 1000000
loop
insert into audit_log values
( 'ID'||i||'' ,
case when i between 1 and 100000 then lpad('A',50,'A')
when i between 100001 and 200000 then lpad('B',50,'B')
when i between 200001 and 300000 then lpad('C',50,'C')
when i between 300001 and 400000 then lpad('D',50,'D')
when i between 400001 and 500000 then lpad('E',50,'E')
when i between 500001 and 600000 then lpad('F',50,'F')
when i between 600001 and 700000 then lpad('G',50,'G')
when i between 700001 and 800000 then lpad('H',50,'H')
when i between 800001 and 900000 then lpad('I',50,'I')
when i between 900001 and 1000000 then lpad('J',50,'J')
end ,
lpad(dbms_random.string('U',1),70,'B')
);
end loop;
commit;
end;
/
First Query
SQL> set autotrace traceonly lines 220 pages 400
SQL> Select * FROM
AUDIT_LOG
WHERE
PAYMENT_IDENTIFICATION_ID = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
AND
AUDIT_ID <> 'ID123482'; 2 3 4 5 6
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 272803615
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 20M| 3767 (1)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| AUDIT_LOG | 100K| 20M| 3767 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PAYMENT_IDX | 100K| | 1255 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("AUDIT_ID"<>U'ID123482')
2 - access("PAYMENT_IDENTIFICATION_ID"=U'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAA')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16982 consistent gets
2630 physical reads
134596 redo size
12971296 bytes sent via SQL*Net to client
73843 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
Second query
SQL> set autotrace traceonly lines 220 pages 400
SQL> Select * FROM
AUDIT_LOG
WHERE
PAYMENT_IDENTIFICATION_ID = 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'
AND
AUDIT_ID ='ID578520'; 2 3 4 5 6
Execution Plan
----------------------------------------------------------
Plan hash value: 303326437
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 219 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| AUDIT_LOG | 1 | 219 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0076603 | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PAYMENT_IDENTIFICATION_ID"=U'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
FFFFFFFFFF')
2 - access("AUDIT_ID"=U'ID578520')
Statistics
----------------------------------------------------------
9 recursive calls
6 db block gets
9 consistent gets
7 physical reads
1080 redo size
945 bytes sent via SQL*Net to client
515 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
The predicate information gives you a lot of information regarding the access paths:
In the first query:
1 - filter("AUDIT_ID"<>U'ID123482')
2 - access("PAYMENT_IDENTIFICATION_ID"=U'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAA')
The access is determined by the "=" operator, and in this case a range scan of the index PAYMENT_IDX is the best approach. The filter comes for all the rows that match the access condition, filter those which are <> from the value in AUDIT_ID.
In the second query:
1 - filter("PAYMENT_IDENTIFICATION_ID"=U'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
FFFFFFFFFF')
2 - access("AUDIT_ID"=U'ID578520')
The access is by the primary key index, as you are using = as operator, so there is no better way to find the row that using the PK index. That is why you have an INDEX_UNIQUE_SCAN. The filter comes from the table access, as Oracle has already determined the row from the unique primary key index. Actually, that condition is not necessary as unless you look for 1 or no rows.
As in the first query, you are making a <> from the primary key index, Oracle will use the other index. assuming ( like in the example ) that you have very few distinct values. Keep in mind that in case it were to use the PK index, it would retrieve 999999 rows in the first step, then applying the filter, which is far less efficient than using the second index.
If you force the CBO to use the PK index, you can see it
SQL> Select /* INDEX(a,SYS_C0076603) */ * FROM
AUDIT_LOG a
WHERE
PAYMENT_IDENTIFICATION_ID = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
AND
AUDIT_ID <> 'ID123482'; 2 3 4 5 6
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3265638686
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 20M| 207K (1)| 00:00:17 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| AUDIT_LOG | 100K| 20M| 207K (1)| 00:00:17 |
|* 2 | INDEX FULL SCAN | SYS_C0076603 | 999K| | 3212 (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PAYMENT_IDENTIFICATION_ID"=U'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AA')
2 - filter("AUDIT_ID"<>U'ID123482')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
218238 consistent gets
18520 physical reads
1215368 redo size
12964630 bytes sent via SQL*Net to client
73873 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed