我有一個審計表如下。
create table "AUDIT_LOG"
(
"AUDIT_ID" NVARCHAR2(70),
"PAYMENT_IDENTIFICATION_ID" NVARCHAR2(70),
"ACCOUNT_NUMBER" NVARCHAR2(100)
PRIMARY KEY ("AUDIT_ID")
);
我有以下索引
- payment_idx on ("PAYMENT_IDENTIFICATION_ID")
- payment_id_idx on ("PAYMENT_IDENTIFICATION_ID", "AUDIT_ID")
- 主鍵 AUDIT_ID 上的 system_index
以下是我正在使用的查詢
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';
第一個查詢解釋計劃顯示了具有選項 BY INDEX ROWID BATCHED 的索引 payment_id_idx 的用法。
然而，第二個查詢解釋計劃顯示了 system_index 在主鍵 AUDIT_ID 上的使用，選項為 BY INDEX ROWID BATCHED。
我認為在這兩個查詢索引中都應該使用payment_id_idx。知道為什么第二個查詢不使用復合索引 payment_id_idx。任何幫助深表感謝。
uj5u.com熱心網友回復：
讓我們嘗試模擬一個與您類似的場景。
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.
現在讓我們插入一些演示資料，但要注意以下幾點：
AUDIT_ID 的形式是唯一的
IDxxx( 其中xxx取值從 1 到 1M )PAYMENT_IDENTIFICATION_ID 采用
LPAD和 字母形式的 10 個不同值。這里的想法是生成 10 個不同的值ACCOUNT_NUMBER 是lpad中一個字母和一個字母的隨機字串，填充70個字符。
因此
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;
/
第一個查詢
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
第二次查詢
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
謂詞資訊為您提供了許多有關訪問路徑的資訊：
在第一個查詢中：
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.
與在第一個查詢中一樣，您正在<>從主鍵創建一個索引，Oracle 將使用另一個索引。假設（如示例中所示）您幾乎沒有不同的值。請記住，如果要使用 PK 索引，它將在第一步中檢索 999999 行，然后應用過濾器，這遠低于使用第二個索引的效率。
如果強制CBO使用PK索引，可以看到
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/341144.html
上一篇：如果值不存在，則將值插入表中