Блог

У меня есть две таблицы со следующими столбцами:

 Table1
{   ID            NUMBER(15),
    ROLL_NUM      VARCHAR2(9),
    BATCH_NUM     VARCHAR2(6),
    ACCT_BALANCE  NUMBER(15,2)
}

Table2
{   Table1_ID      NUMBER(15) REFERENCES TABLE1.ID,
    SEQ_NUM       NUMBER(2),
    TRANS_NUM     VARCHAR2(10),
    TRANS_AMT     NUMBER(8,2),
    TRANS_DT      DATE
}
  

В таблице 1 содержится 200 000 записей, а в таблице 2 — 500 000 записей

У меня есть простые соединения следующим образом:

SQL #1:

 SELECT A.ROLL_NUM, A.ACCT_BALANCE, B.TRANS_NUM, TRANS_AMT, TRANS_DT
FROM   TABLE1 A, TABLE2 B
WHERE  B.Table1_ID = A.ID
AND    A.BATCH_NUM = 400012
  

SQL #2:

 SELECT A.ROLL_NUM, A.ACCT_BALANCE, B.TRANS_NUM, TRANS_AMT, TRANS_DT
FROM   TABLE1 A, TABLE2 B
WHERE  B.Table1_ID = A.ID
AND    A.BATCH_NUM = '400012'
  

SQL #3:

 SELECT A.ROLL_NUM, A.ACCT_BALANCE, B.TRANS_NUM, TRANS_AMT, TRANS_DT
FROM   TABLE1 A, TABLE2 B
WHERE  B.Table1_ID = A.ID
AND    A.BATCH_NUM = TO_NUMBER('400012')
  

Ожидаемый результат от подсчета должен быть 500 000, если каждый BATCH_NUM из Table1 равен ‘400012’ и все идентификаторы совпадают в Table2.

Когда я запускаю эти запросы в Oracle (v11 или v10), SQL # 2, кажется, длится вечно, и мне пришлось остановить выполнение запроса через 10-15 минут. SQL # 1 и # 3, похоже, мгновенно выдают результаты с полными 500 000 записями менее чем за секунду. Сначала я подумал, что это проблема с индексацией, но добавление индекса ничего не делает для решения проблемы. Я попробовал этот запрос в TOAD и в SQL Developer с теми же результатами.

Я в растерянности, потому что столбец BATCH_NUM в Table1 является VARCHAR2, и вы могли бы подумать, что неявное преобразование типов данных приведет к тому, что запрос будет медленнее, а не быстрее, чем сравнение без преобразования. Кто-нибудь может это объяснить?

Я согласен, что использование неявного преобразования предотвратило бы путь доступа к индексу.

Чтобы быть уверенным, что на самом деле происходит, выполните следующее:

 explain plan for
select ......
/
  

Затем, сразу после этого, выполните:

 select * from table(dbms_xplan.display);
  

и опубликуйте результаты.

Не глядя на план выполнения, все, что я говорю, было бы предположением.

О, и, пожалуйста, опубликуйте определения всех индексов, которые существуют в таблицах.

-Отметить

Я бы предположил, что вы попали в ловушку, думая, что доступ к индексу = быстрый, полное сканирование таблицы = медленное.

Устаревшая статистика может привести к снижению вашего несогласованного времени выполнения.

вы можете проверить устаревшую статистику с помощью:

 DECLARE
   l_objlist   DBMS_STATS.objecttab;
BEGIN
   DBMS_STATS.gather_schema_stats (ownname      => USER,
                                   options      => 'LIST STALE',
                                   objlist      => l_objlist
                                  );

   FOR i IN 1 .. l_objlist.COUNT
   LOOP
      DBMS_OUTPUT.put_line (   l_objlist (i).objtype
                            || ' .. '
                            || l_objlist (i).objname
                           );
   END LOOP;
END;
  

Хорошо, после того, как мы с коллегой просмотрели планы объяснения и посмотрели на индексы, мы, наконец, нашли проблему. Я хотел бы поблагодарить Марка Бобака и Кевина Бертона за их вклад в это. Вот что мы выяснили:

Был индекс, в котором в качестве основного индекса были BATCH_NUM и два других столбца. При выполнении приведенного преобразования Oracle решает выполнить полное сканирование таблицы для всех Table1_ID в дочерней таблице, которые соответствуют BATCH_NUM и ID в родительской таблице. Выполнение полного сканирования таблицы в этом случае выполняется относительно быстро. Нет, условие поиска с BATCH_NUM в виде строки — это когда индекс терпит неудачу и приводит к «зависанию» всего запроса. Поскольку BATCH_NUM никоим образом не уникален, условие без какого-либо неявного или явного преобразования заставляет Oracle пытаться использовать индекс, и план объяснения показал, что он пытается выполнить полное сканирование индекса (которое может быть довольно большим, если таблица содержит от 500 000 до 1 000 000 строк записей).). Удаление индекса действительно помогло в решении этой проблемы.

Если должно произойти преобразование типа из числового в строковый, «обычный» индекс в столбце не может быть использован. Итак, если вы посмотрите на план объяснения для вашего второго запроса, вы, вероятно, увидите, что выполняется полное сканирование таблицы с указанием to_string(batch_num) == ‘400012’.

Если вам действительно нужно иметь возможность выполнять квалификацию по строке, вы можете создать индекс на основе функции [1] для столбца. Если вы можете использовать синтаксис, описанный в вариантах 1 или 3, вы можете использовать «обычный» индекс в столбце batch_num.

[1] —http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_indexes.htm