Блог

У меня есть таблица аудита, как показано ниже.

 create table "AUDIT_LOG"   (   "AUDIT_ID" NVARCHAR2(70),   "PAYMENT_IDENTIFICATION_ID" NVARCHAR2(70),   "ACCOUNT_NUMBER" NVARCHAR2(100)  PRIMARY KEY ("AUDIT_ID")  );  

У меня ниже индекс

1. payment_idx вкл. («PAYMENT_IDENTIFICATION_ID»)
2. payment_id_idx вкл. («PAYMENT_IDENTIFICATION_ID», «AUDIT_ID»)
3. system_index для первичного ключа AUDIT_ID

Ниже приведены запросы, которые я использую

 Query1 :  Select * FROM  AUDIT_LOG  WHERE  PAYMENT_IDENTIFICATION_ID =   'ID124'  AND   AUDIT_IDlt;gt;'ecfdc2c3-87eb-48c9-b53c';   Query2 : Select * FROM  AUDIT_LOG  WHERE  PAYMENT_IDENTIFICATION_ID =   'ID124'  AND   AUDIT_ID='ecfdc2c3-87eb-48c9-b53c';    

Первый план объяснения запроса показывает использование индекса payment_id_idx с возможностью ПАКЕТНОЙ обработки по ИНДЕКСУ ROWID.

Однако второй запрос объясняет план, показывающий использование system_index для идентификатора первичного ключа AUDIT_ID с параметром ПО ПАКЕТУ ИДЕНТИФИКАТОРОВ СТРОК ИНДЕКСА.

Я придерживался мнения, что в обоих запросах следует использовать индекс payment_id_idx. Есть идеи, почему второй запрос не использует составной индекс payment_id_idx. Мы очень ценим любую помощь.

Давайте попробуем смоделировать сценарий, аналогичный вашему.

 SQLgt; alter session set current_schema=test ;  Session altered.  SQLgt; create table "AUDIT_LOG"  (  "AUDIT_ID" NVARCHAR2(70),  "PAYMENT_IDENTIFICATION_ID" NVARCHAR2(70),  "ACCOUNT_NUMBER" NVARCHAR2(100)  ); 2 3 4 5 6  Table created.  SQLgt; alter table audit_log add primary key ( audit_id ) ;  Table altered.  SQLgt; create index payment_idx on audit_log ("PAYMENT_IDENTIFICATION_ID");  Index created.  SQLgt; create index payment_id_idx on audit_log ("PAYMENT_IDENTIFICATION_ID", "AUDIT_ID");  Index created.  

Теперь давайте вставим некоторые демонстрационные данные, но, следуя некоторым соображениям:

• Идентификатор AUDIT_ID уникален в виде IDxxx ( где xxx принимает значения от 1 до 1 м )
• PAYMENT_IDENTIFICATION_ID принимает 10 различных значений в виде LPAD и буквы. Идея здесь состоит в том, чтобы сгенерировать 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; /  

Первый запрос

 SQLgt; set autotrace traceonly lines 220 pages 400 SQLgt; Select * FROM  AUDIT_LOG  WHERE  PAYMENT_IDENTIFICATION_ID = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'  AND  AUDIT_ID lt;gt; '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"lt;gt;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  

Второй запрос

 SQLgt; set autotrace traceonly lines 220 pages 400  SQLgt; 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"lt;gt;U'ID123482') 2 - access("PAYMENT_IDENTIFICATION_ID"=U'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  AAA')  

Доступ определяется оператором»=», и в этом случае сканирование диапазона индекса PAYMENT_IDX является наилучшим подходом. Фильтр применяется для всех строк, соответствующих условию доступа, фильтруйте те, которыеlt;gt;, из значения в AUDIT_ID.

Во втором запросе:

 1 - filter("PAYMENT_IDENTIFICATION_ID"=U'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF  FFFFFFFFFF')  2 - access("AUDIT_ID"=U'ID578520')  

Доступ осуществляется по индексу первичного ключа, который вы используете = в качестве оператора, поэтому нет лучшего способа найти строку, использующую индекс PK. Вот почему у вас есть INDEX_UNIQUE_SCAN. Фильтр исходит из доступа к таблице, так как Oracle уже определила строку по уникальному индексу первичного ключа. На самом деле, это условие не является необходимым, так как, если вы не ищете 1 или ни одной строки.

Как и в первом запросе, вы делаете a lt;gt; из индекса первичного ключа, Oracle будет использовать другой индекс. предполагая ( как в примере), что у вас очень мало различных значений. Имейте в виду, что в случае, если бы он использовал индекс PK, он получил бы 999999 строк на первом шаге, а затем применил фильтр, что гораздо менее эффективно, чем использование второго индекса.

Если вы заставите CBO использовать индекс PK, вы сможете увидеть это

 SQLgt; Select /* INDEX(a,SYS_C0076603) */ * FROM  AUDIT_LOG a  WHERE  PAYMENT_IDENTIFICATION_ID = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'  AND  AUDIT_ID lt;gt; '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"lt;gt;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