Я согласен с Томом Китом в том, что полное сканирование таблицы не является злом источник, но только если таблица относительно небольшая. Таким образом, наличие дополнительного индекса такой таблицы избыточно. Тем не менее, таблицу со 100 000 записей не следует считать маленькой, но план объяснения из такой таблицы показывает выполненное полное сканирование таблицы. Итак, я провел небольшой эксперимент на своем ноутбуке с локально установленным Oracle:
1) сначала создал my_table:
CREATE TABLE my_table(
"ID" NUMBER NOT NULL ENABLE,
"INVOICE_NO" VARCHAR2(10),
CONSTRAINT "test _PK" PRIMARY KEY ("ID")
)
2) Затем создал индекс для столбца invoice_no (потому что будет фильтровать его):
CREATE INDEX "my_table_index1" ON my_table (invoice_no)
3) Затем вставил 100 тыс. записей:
DECLARE
mod_val NUMBER;
BEGIN
FOR i IN 1..100000 LOOP
mod_val := MOD(i,6);
IF (mod_val = 0) THEN
INSERT INTO my_table (ID, INVOICE_NO) VALUES (i, '5570-110');
ELSIF (mod_val = 1) THEN
INSERT INTO my_table (ID, INVOICE_NO) VALUES (i, '5570-111');
ELSIF (mod_val = 2) THEN
INSERT INTO my_table (ID, INVOICE_NO) VALUES (i, '5570-112');
ELSIF (mod_val = 3) THEN
INSERT INTO my_table (ID, INVOICE_NO) VALUES (i, '5570-113');
ELSIF (mod_val = 4) THEN
INSERT INTO my_table (ID, INVOICE_NO) VALUES (i, '5570-114');
ELSIF (mod_val = 5) THEN
INSERT INTO my_table (ID, INVOICE_NO) VALUES (i, '5570-115');
END IF;
END LOOP;
COMMIT;
END;
4) Затем обновил одну случайную запись (только для того, чтобы подчеркнуть выбор):
BEGIN
UPDATE my_table SET INVOICENO = 'exception' WHERE id = 50000;
COMMIT;
END;
5) Затем выполняется выбор с планом объяснения:
EXPLAIN PLAN FOR
SELECT * FROM my_table WHERE invoice_no = 'exception';
6) Затем схватил статистику:
SELECT * FROM TABLE(dbms_xplan.display);
7) и получил результаты:
"PLAN_TABLE_OUTPUT"
"Plan hash value: 3804444429"
" "
"------------------------------------------------------------------------------"
"| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |"
"------------------------------------------------------------------------------"
"| 0 | SELECT STATEMENT | | 83256 | 1626K| 103 (1)| 00:00:02 |"
"| 1 | TABLE ACCESS FULL| MY_TABLE | 83256 | 1626K| 103 (1)| 00:00:02 |"
"------------------------------------------------------------------------------"
" "
"Note"
"-----"
" - dynamic sampling used for this statement (level=2)"
Вывод: Странно и пахнет "волшебством", почему Oracle решил не использовать индекс для поля invoice_no и просканировал 83256 записей? Я согласен, что мой ноутбук не перегружен одновременными пользователями, таблица не очень большого размера (содержат числа и varchars), однако мне не нравится эта магия, и я хотел бы знать причины такого поведения :)
<Сильного> UPDATE : Я просто добавил некоторое значение манекена (см ниже) invoice_no поля для всех записей - просто увеличить размер таблицы, однако таблицы полных остатков сканирования: «aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa»
ОБНОВЛЕНИЕ 2: я также выполнил анализ таблицы, но результат тот же:
ANALYZE TABLE my_table COMPUTE STATISTICS;
UPDATE3: пытался принудительно использовать индекс, но результат тот же (может быть, неправильный синтаксис?):
EXPLAIN PLAN FOR
SELECT /*+ INDEX(my_table my_table_index1) */ * FROM my_table t WHERE invoice_no = 'exception'
UPDATE4: Наконец-то удалось «сказать Oracle» использовать индекс — выполнить новую процедуру сбора статистики таблицы:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME=>user
, TABNAME=>'my_table');
END;
Вот результат объяснения плана:
"--------------------------------------------------------------------------------------"
"| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |"
"-----------------------------------------------------------------------------------------------"
"| 0 | SELECT STATEMENT | | 1 | 294 | 5 (0)| 00:00:01 |"
"| 1 | TABLE ACCESS BY INDEX ROWID| MY_TABLE | 1 | 294 | 5 (0)| 00:00:01 |"
"|* 2 | INDEX RANGE SCAN | my_table_index1 | 1 | | 4 (0)| 00:00:01 |"
"-----------------------------------------------------------------------------------------------"
" "
"Predicate Information (identified by operation id):"
"---------------------------------------------------"
" "
" 2 - access(""INVOICE_NO""='exception')"
Итак, похоже, что Oracle решает использовать какой-то подход к запросам в какой-то момент и не обновляет его, даже если ситуация изменилась. Я согласен с этим, но странно, почему он не выбрал правильный подход для этого тестового примера, когда я только что создал, вставил и выполнил выбор. Должны ли мы всегда выполнять DBMS_STATS.GATHER_TABLE_STATS, по крайней мере, в начале, чтобы указать Oracle использовать лучший подход к запросам?
ANALYZE TABLEдля сбора статистики. Он устарел и больше не должен использоваться. Вместо этого используйтеdbms_stats.gather_table_stats(). - person a_horse_with_no_name schedule 15.10.2012