Расширенная статистика оптимизатора

Оптимизатор Oracle умеет собирать расширенную статистику -- статистику по группе колонок или по функции.
Как и обычная статистика, это может помочь определить селективность и выбрать наиболее подходящий индекс.

Пример:

SQL> create table H (a number not null, b number not null) pctfree 95;

SQL> insert into H (A, B) select case when rownum >= 5001 then 1 else 0 end, case when rownum <= 5001 then 1 else 0 end from dual connect by level <= 10001;

SQL> commit;

SQL> create index HI1 on H (A, B) pctfree 95;

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (null, 'H', cascade => true);

Я намеренно указал PCTFREE 95 чтобы сделать таблицу большой при маленьком числе строк и завысить в этом примере стоимость ввода-вывода.

Распределение данных а таблице будет таково:
select A, B, count(*) from H group by A, B

A	B	COUNT(*)
0	1	5000
1	0	5000
1	1	1

Посмотрим план трёх операторов:

select * from H where A=0 and B=1
select * from H where A=1 and B=0
select * from H where A=1 and B=1

Oracle сейчас генерирует одинаковые планы с одинаковой Cardinality = 3334 и с полным просмотром по всем трём запросам

Но глядя на распределение данных я бы хотел получить полный просмотр только для первых двух запросов и индексный доступ для третьего запроса -- мне там нужна всего одна строка.



Решим эту проблему с помощью создания расширенной статистики

Создадим группу колонок (А, B) и соберём по ней расширенную статистику

SQL> var cg_name varchar2(30);

SQL> exec :cg_name := DBMS_STATS.CREATE_EXTENDED_STATS(null, 'H', '(A,B)');

PL/SQL procedure successfully completed.

SQL> print cg_name

CG_NAME

-----------------------------------
SYS_STUNA$6DVXJXTP05EH56DTIR0X

SQL> exec DBMS_STATS.SET_TABLE_PREFS (null, 'H', 'METHOD_OPT', 'FOR COLUMNS A,B,(A,B) SIZE SKEWONLY');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (null, 'H', cascade => true);

PL/SQL procedure successfully completed.

Посмотрим, что происходит с планами:

Для первых двух запросов
select * from H where A=0 and B=1
select * from H where A=1 and B=0
планы такие:

Всё верно -- мы ожидаем возвращения 5000 строк и подсчитанный оптимизатором Cardinality = 5000. Выбран FULL SCAN, что верно для нашей таблицы -- это действительно дешевле, учитывая что наш индекс HI1 "шире", чем таблица

Для третьего запроса:
select * from H where A=1 and B=1
выбран индексный доступ и верно определено CARDINALITY = 1

Вывод: расширенная статистика по группе колонок может быть полезна в ряде случаев.

Посмотреть на статистику и гистограммы можно в: USER_TAB_COL_STATISTICS и  USER_TAB_HISTOGRAMS.