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

Оптимизатор 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.

"Строку в столбец" и наоборот. Транспонирование таблиц. PIVOT и UNPIVOT. В т.ч. для нескольких полей

Далее в тексте есть обновление статьи для нескольких полей

До версии 11 задача транспонирования решалась довольно сложно.

В версии 11 в синтаксисе оператора SELECTпоявились фразы PIVOT и UNPIVOT , которые выполняют такое транспонирование прозрачно и просто.
План запроса получается простой и эффективный -- запрос будет хорошо выполняться даже на больших объёмах. Прежние варианты с самосоединением таблиц были бы менее эффективны

Приведу пример использования:

Имеем таблицу курсов валют к рублю:

ISO_CODE	RATE_DATE	RATE_VALUE
EUR	01.02.2012	39,9678
EUR	02.02.2012	39,6777
EUR	03.02.2012	39,7664
UAH	01.02.2012	3,7731
UAH	02.02.2012	3,7881
UAH	03.02.2012	3,7547
USD	01.02.2012	30,3131
USD	02.02.2012	30,4067
USD	03.02.2012	30,1855

Хотим получить такую таблицу

RATE_DATE	RATE_EUR	RATE_UAH	RATE_USD
01.02.2012	39,9678	3,7731	30,3131
02.02.2012	39,6777	3,7881	30,4067
03.02.2012	39,7664	3,7547	30,1855

В 11g это можно сделать таким запросом:

select * from (select ISO_CODE, RATE_DATE, RATE_VALUE from EXCH_RATES)

pivot (max (RATE_VALUE)
       for ISO_CODE in ('EUR' as RATE_EUR, 'UAH' as RATE_UAH, 'USD' as RATE_USD)
      )




Комментарии:

1. Первый select * from ( ) обязателен

2. Внутри фразы pivot (  ) сначала должна быть агрегатная функция, даже если фраза group by явно не указана. PIVOT выполнит неявный group by по всем полям, кроме поля в агрегатной функции и полей после FOR. Это необходимо, чтобы обработать ситуацию, когда там окажется несколько строк. Если строка одна -- не важно, что писать, MIN, MAX, SUM, AVG...

План:

Синтаксис описан в доке по SELECT
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm
Искать pivot_clause

UNPIVOT выполняет обратное преобрзование

UPDATE 15.06.2015
Как транспонировать несколько полей ?


В приведённом выше примере мы транспонировали только одно поле -- RATE_VALUE превратилось в RATE_EUR, RATE_UAH, RATE_USD

Но бывают задачи, когда нужно транспонировать несколько полей, ниже я привожу пример, связанный тоже с курсами валют, но курсы взяты с рынка Форекс, и там строка курса содержит несколько значений OPEN (значение на момент начала интервала), MIN (минимальное, которое достигалось в интервале), MAX (максимальное, которое достигалось в интервале), CLOSE (каким значением курса интервал закончился), VOLUME (объём операций)

В этом случае, названия колонок нам нужно собрать перемножением множества значений ('OPEN', 'MIN', 'MAX', 'CLOSE', 'VOLUME') на множество значений названий пар валют

Этот запрос можно написать так, что в каждой колонке сначала будет название пары валют (поле из FOR) и через подчёркивание название поля, которое мы транспонируем

Сначала приведу запрос

select * from (select * from RATES)
pivot (avg (QOPEN)   as QOPEN,
       avg (QMIN)    as QMIN,
       avg (QMAX)    as QMAX,
       avg (QCLOSE)  as QCLOSE,
       avg (QVOLUME) as QVOLUME
       
       for (CURPAIR) in ('EURGBP' as EURGBP
                       , 'EURPLN' as EURPLN
                       , 'EURUSD' as EURUSD)
      )
order by 1

Алиасы полей, помеченные зелёным фоном -- обязательны

Вот таблица для тестирования

create table RATES (
    CURPAIR     varchar2(16) not null,
    SNAPDATE    date not null,
    QOPEN       number  not null,
    QMAX        number  not null,
    QMIN        number  not null,
    QCLOSE      number  not null,
    QVOLUME     number  not null);

И её заполнение

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:00', 'YYYY.MM.DD HH24:MI') ,0.7208,0.721,0.7208,0.721,5      );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:01', 'YYYY.MM.DD HH24:MI') ,0.7209,0.721,0.7209,0.721,4      );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:02', 'YYYY.MM.DD HH24:MI') ,0.7211,0.7211,0.7209,0.721,8     );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:03', 'YYYY.MM.DD HH24:MI') ,0.7209,0.721,0.7209,0.7209,5     );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:04', 'YYYY.MM.DD HH24:MI') ,0.721,0.721,0.7209,0.7209,2      );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:05', 'YYYY.MM.DD HH24:MI') ,0.721,0.721,0.721,0.721,1        );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:06', 'YYYY.MM.DD HH24:MI') ,0.7209,0.721,0.7209,0.7209,5     );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:07', 'YYYY.MM.DD HH24:MI') ,0.721,0.721,0.7209,0.7209,14     );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:08', 'YYYY.MM.DD HH24:MI') ,0.721,0.721,0.7209,0.7209,4      );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:00', 'YYYY.MM.DD HH24:MI') ,4.0937,4.0947,4.0937,4.0947,2    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:01', 'YYYY.MM.DD HH24:MI') ,4.0946,4.0946,4.0946,4.0946,1    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:02', 'YYYY.MM.DD HH24:MI') ,4.0947,4.0954,4.0947,4.0954,5    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:03', 'YYYY.MM.DD HH24:MI') ,4.0966,4.0966,4.0966,4.0966,3    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:04', 'YYYY.MM.DD HH24:MI') ,4.0955,4.0993,4.0955,4.0961,11   );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:05', 'YYYY.MM.DD HH24:MI') ,4.0964,4.0973,4.0961,4.0973,7    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:06', 'YYYY.MM.DD HH24:MI') ,4.0974,4.0976,4.0974,4.0976,2    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:07', 'YYYY.MM.DD HH24:MI') ,4.0974,4.0974,4.0973,4.0973,2    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:08', 'YYYY.MM.DD HH24:MI') ,4.0974,4.0974,4.0973,4.0973,2    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:09', 'YYYY.MM.DD HH24:MI') ,4.0974,4.0974,4.0974,4.0974,1    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:00', 'YYYY.MM.DD HH24:MI') ,1.1352,1.1354,1.1351,1.1353,6    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:01', 'YYYY.MM.DD HH24:MI') ,1.1354,1.1355,1.1353,1.1355,14   );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:02', 'YYYY.MM.DD HH24:MI') ,1.1354,1.1355,1.1354,1.1355,8    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:03', 'YYYY.MM.DD HH24:MI') ,1.1354,1.1355,1.135,1.1351,9     );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:05', 'YYYY.MM.DD HH24:MI') ,1.1352,1.1352,1.1352,1.1352,1    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:06', 'YYYY.MM.DD HH24:MI') ,1.1351,1.1352,1.1351,1.1351,3    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:07', 'YYYY.MM.DD HH24:MI') ,1.1352,1.1352,1.1351,1.1352,3    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:08', 'YYYY.MM.DD HH24:MI') ,1.1351,1.1352,1.1351,1.1351,3    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:09', 'YYYY.MM.DD HH24:MI') ,1.135,1.1351,1.135,1.1351,4      );

commit;

Быстрая перестройка UNUSABLE INDEX

Это простой запрос, который составляет скрипт для перестройки UNUSABLE индексов.
Наличие таких индексов может являться причиной падения производительности, если параметр БД SKIP_UNUSABLE_INDEXES установлен в TRUE. В этом случае БД не генерирует ошибку при попытке использования битого индекса и пробует обходиться без него -- или использует другой индекс (если такой есть), или TABLE FULL SCAN

Проверить его значение можно в SQL*PLUS
SQL> show parameter unusable

Выполните скрипт ниже в TOAD или SQL Navigator, в результате будет одна колонка со списком команд alter index ***** rebuild

select 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild;' as TOAD_CMD from DBA_INDEXES a

where INDEX_TYPE in ('NORMAL', 'BITMAP', 'FUNCTION-BASED NORMAL') and PARTITIONED = 'NO' and STATUS <> 'VALID'
union all
select 'alter index '||b.INDEX_OWNER||'.'||b.INDEX_NAME||' rebuild partition '||b.PARTITION_NAME||';' as TOAD_CMD
from DBA_INDEXES a, DBA_IND_PARTITIONS b
where a.OWNER = b.INDEX_OWNER and a.INDEX_NAME = b.INDEX_NAME and a.PARTITIONED = 'YES' and a.INDEX_TYPE in ('NORMAL', 'BITMAP', 'FUNCTION-BASED NORMAL') and b.STATUS = 'UNUSABLE';