Оптимизатор всегда полностью просматривает таблицу eben, хотя выбирает только 3 строки

Question

У меня есть таблица Foo, которая была создана следующим образом.

CREATE TABLE foo AS SELECT * FROM all_objects;

CREATE INDEX foo_I1 ON foo(owner,object_type,status);
exec dbms_stats.gather_table_stats('hr','foo',method_opt=>'FOR ALL COLUMNS size AUTO');

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

select  * from foo where status='INVALID';
select  * from foo where status='VALID';

status='VALID' выбирает около 71000 строк в таблице из 71780 строк. он выполняет полное сканирование таблицы. это понятно. но в случае status='INVALID', который выбирает только 3 строки, выполняется полное сканирование таблицы. Это также получает строки A и E очень разные.

ПЛАН: одинаковый для обоих запросов.

SQL_ID gdhy9j91gu9sm, дочерний номер 0

выберите /*+ collect_plan_statistics */ * из foo, где status='VALID'

Plan hash value: 1245013993

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     50 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS FULL| FOO  |      1 |  71773 |     50 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STATUS"='VALID')

Пожалуйста, объясните это поведение. Версия базы данных: 11.2 г оракул.

Answer 1

Отсутствующая гистограмма, вероятно, вызывает полное сканирование таблицы. Гистограммы обычно создаются только в том случае, если данные искажены и столбец использовался в соответствующем предикате.

Иногда вам нужно выполнить запрос перед сбором статистики, чтобы Oracle знал, что этот столбец достаточно важен, чтобы заслужить гистограмму.

select * from foo where status='INVALID';
exec dbms_stats.gather_table_stats('hr','foo',method_opt=>'FOR ALL COLUMNS size AUTO');

Перезапустите SELECT и теперь он может использовать гистограмму. С помощью гистограммы Oracle знает, что INVALID возвращает небольшое количество строк, и индекс будет полезен:

explain plan for select * from foo where status='INVALID';
select * from table(dbms_xplan.display);

Plan hash value: 1520589999

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |     1 |   134 |   217   (0)| 00:00:01|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| FOO    |     1 |   134 |   217   (0)| 00:00:01|
|*  2 |   INDEX SKIP SCAN                   | FOO_I1 |     1 |       |   216   (0)| 00:00:01|
---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("STATUS"='INVALID')
       filter("STATUS"='INVALID')