1.
Зачем создавать индексы на ссылочных полях (FK – foreign key) ?
* Очевидно для оптимизации процессов соединения таблиц.
* Но есть и другое, не менее важное, предназначение:
Если таблица фактов в схеме «звезда» ссылается (FK) на таблицу измерений, и на ссылочном поле нет индекса, то DML (Insert / Update / Delete ) операция над таблицей измерений заблокирует таблицу фактов на изменение целиком.
Если на ссылочном поле есть индекс, то произойдет блокировка только нужных строк из таблицы фактов, тех которые затрагивает изменение в таблице измерений.
Пример: таблица с данными продаж (факты) и таблица кассовых мест (измерения), с некоторой периодичностью происходит изменение списка кассовых мест, то на ссылочном поле кассовое место / таблица продаж, желательно создать индекс.
Если этого не сделать, то данные в продажах будут заблокированы до commit / rollback обновления списка кассовых мест.
2. Compressed index
Сжатие индексов может пригодится, если индекс состоит из нескольких столбцов, несколько первых из которых мало селективны.
В этом случае при создании индекса можно указать:
compressed N
где N – кол-во колонок.
Пример: индекс по 3 полям, первые 2 из которых малоселективны (online, status)
Индекс без сжатия
Online,0,AAAPvCAAFAAAAFaAAa
Online,0,AAAPvCAAFAAAAFaAAg
Online,0,AAAPvCAAFAAAAFaAAl
Online,2,AAAPvCAAFAAAAFaAAm
Online,3,AAAPvCAAFAAAAFaAAq
Online,3,AAAPvCAAFAAAAFaAAt
Индекс со сжатием
Online,0
AAAPvCAAFAAAAFaAAa
AAAPvCAAFAAAAFaAAg
AAAPvCAAFAAAAFaAAl
Online,2
AAAPvCAAFAAAAFaAAm
Online,3
AAAPvCAAFAAAAFaAAq
AAAPvCAAFAAAAFaAAt
В этом случае группа одинаковых данных N первых колонок будут сжаты в одну запись в индексе, а ROWID самих записей будут помещены в вспомогательную структуру. Уменьшение числа листов индекса соответственно ускорит доступ к данным.
Хочу заметить, что данный способ применим только для низко селективных столбцов. Если данные достаточно уникальны, то создание доп. структур на хранение записей только создаст дополнительные накладные расходы!
Так же стоит заметить, что без compressed первый столбцы индекса наоборот должны содержать наиболее селективные данные, чтобы Oracle за меньшее кол-во операций мог дойти до уникальных данных.
3. Advanced compress - oracle 12
Advanced compress - новый шаг развития компрессии индексов в Oracle 12.
Oracle автоматически подбирает размер компрессии на уровне каждого блока индекса, но порядок столбцов нужно все также соблюдать самостоятельно.
Пример:
0. Обычный индекс
1. Индекс с компрессией 1 - занимает 3200 блоков
2. Индекс с компрессией 2 - занимает 2816 блоков
3. ADVANCED LOW - занимает 2816 блоков, т.е. Oracle самостоятельно подобрал уровень компрессии = 2
4. ADVANCED LOW - но в начале плохосжимаемый селективный столбец. Oracle не применяет компрессии, выходит теже 3584 блоков.
Т.е. за порядком столбцов все также надо следить, можно только не делать analyze index, чтобы узнать оптимальный уровень компрессии.
drop table t$t purge;
create table t$t as select 1 c1, round( dbms_random.VALUE(1, 10)) c2, level as c3
from dual connect by level < 1000000;
create index idx_t$t_0 on t$t(c1, c2, c3, 1);
create index idx_t$t_1 on t$t(c1, c2, c3, 2) COMPRESS 1;
create index idx_t$t_2 on t$t(c1, c2, c3, 3) COMPRESS 2;
create index idx_t$t_low on t$t(c1, c2, c3, 4) COMPRESS ADVANCED LOW;
create index idx_t$t_c3_low on t$t(c3, c1, c2, 5) COMPRESS ADVANCED LOW;
select segment_type, segment_name, bytes, blocks from SYS.USER_SEGMENTS where segment_name like '%T$T%';
SEGMENT_TYPE SEGMENT_NAME BYTES BLOCKS
------------------ ----------------------- ---------- ----------
TABLE T$T 18874368 2304
INDEX IDX_T$T_0 29360128 3584
INDEX IDX_T$T_1 26214400 3200
INDEX IDX_T$T_2 23068672 2816
INDEX IDX_T$T_LOW 23068672 2816
INDEX IDX_T$T_C3_LOW 29360128 3584
6 rows selected
Скрипт определения индексов для сжатия со списком колонок.
Формула определения необходимости сжатия колонки: произведение кол-ва уникальных значений в текущей и предыдущих колонках < 200 (значение случайное)
Индексы сортируются по значению: кол-во строк в индексе / произведение кол-ва уникальных значений в текущей и предыдущих колонках * кол-во колонок для сжатия
Таким образом вначале будут индексы, которым сильней необходимо сжатие
with t as (
select /*+ MATERIALIZE */ I.TABLE_NAME, i.index_name, I.NUM_ROWS, C.COLUMN_POSITION, C.COLUMN_NAME, s.NUM_DISTINCT,
CASE WHEN
-- EXP (SUM (LN ( col )) == MULTPL(col)
EXP (SUM (LN (s.NUM_DISTINCT)) OVER(PARTITION BY I.TABLE_NAME, i.index_name ORDER BY C.COLUMN_POSITION)) <= 200
THEN
1
END as NEED_COMPRESS
from dba_indexes i
join DBA_IND_COLUMNS c on C.INDEX_OWNER = i.OWNER AND C.INDEX_NAME = i.index_name AND C.TABLE_NAME = I.TABLE_NAME
join DBA_TAB_COL_STATISTICS s on S.OWNER = i.OWNER AND S.TABLE_NAME = I.TABLE_NAME AND S.COLUMN_NAME = C.COLUMN_NAME
WHERE i.OWNER = :OWN AND i.COMPRESSION = 'DISABLED' AND i.INDEX_TYPE = 'NORMAL'
AND i.LEAF_BLOCKS > 0
order by I.TABLE_NAME, i.index_name, C.COLUMN_POSITION
)
select TABLE_NAME, index_name, NUM_ROWS, NUM_DISTINCT, COMPR_COLS, COMPR_FACTOR
from (
select TABLE_NAME, index_name, MAX(NUM_ROWS) as NUM_ROWS,
ROUND( EXP (SUM (LN (CASE WHEN NEED_COMPRESS = 1 THEN NUM_DISTINCT END))) ) as NUM_DISTINCT,
LISTAGG(CASE WHEN NEED_COMPRESS = 1 THEN COLUMN_NAME END, ', ') WITHIN GROUP (ORDER BY COLUMN_POSITION) as COMPR_COLS,
ROUND( MAX(NUM_ROWS) / SUM(CASE WHEN NEED_COMPRESS = 1 THEN NUM_DISTINCT END) ) * COUNT(DISTINCT CASE WHEN NEED_COMPRESS = 1 THEN COLUMN_POSITION END) as COMPR_FACTOR,
COUNT(DISTINCT CASE WHEN NEED_COMPRESS = 1 THEN COLUMN_POSITION END) as COLUMN_COUNT
from t
WHERE NEED_COMPRESS > 0
GROUP BY TABLE_NAME, index_name
)
order by COMPR_FACTOR DESC nulls last, TABLE_NAME, index_name
FETCH FIRST 500 ROWS ONLY;
Предварительный результат сжатия индекса можно определить средствами Oracle:
analyze index "IDX" validate structure;
select * from index_stats;
Последние 2 столбца index_stats дадут информацию о желательном уровне сжатия и % уменьшения индекса после компрессии.
4. reverse index
Обычный btree index, но данные этого индекса развернуты наоборот.
Такой индекс хорошо подходит для последовательно генерируемых данных, к примеру для первичного ключа, создаваемого по sequence.
Если использовать обычный индекс то данные будут писаться последовательно в блоки, что может увеличить конкуренцию за диск при вставке или выборке.
Пример:
Первичный ключ:
* Обычный индекс: 12345, 12346, 12347
Данные пишутся на диск последовательно.
* Реверсивный индекс: 54321, 64321, 74321
Видно, что reverse данные хорошо будут раскиданы по диску.
Что должно уменьшить число событий «buffer busy» и «read by other session»
5. Skip scan
Уникальная фича Oracle, которой нет почти ни у одной субд. Возможность поиска по правой части индекса.
Skip scan индекса может быть применен, если первые (левые или лидирующие) столбцы малоселективны. Тогда Oracle может создать логические подиндексы для каждой записи левой части индекса.
Пример:
Индекс по полям: GENDER, EMAIL – GENDER малоселективная левая часть индекса, EMAIL высокоселективная правая часть.
При запросе вида
SELECT * FROM T WHERE EMAIL = ?
Будет использоваться Skip scan, и запрос на внутреннем уровне будет преобразован к виду:
select * from T where gender = 'F' AND EMAIL = ?
union all
select * from T WHERE gender = 'M' AND EMAIL = ?
Если первый столбец достаточно селективен, что встречается чаще (см. п.2), то Skip scan использоваться не будет.
6. Способы доступа к индексу.
a. index scan - последовательное чтение по связанному списку. Данные в этом случае будут отсортированы по индексу.
b. index fast full scan - многоблочно читается сегмент целиком и выбираются блоки индекса. Может быть применено только если not null фильт или колонка. В этом случае данные получаются неотсортированными по индексу.
c. index range scan - сканирование по диапазону. Может использоваться только одно условие отличное от =
7. Характеристики индексов.
a. clustering factor index - как записи в индексе соответствуют (упорядочены) данным в таблице
* в идеале clustering factor = числу блоков, т.е. при чтении из индекса не надо прыгать по разным блокам
* если clustering factor = числу строк, то при запросе из индекса будет много переходов в разные блоки, что приведет к огромному числу чтений
Дополнительная информация по внутреннему устройству индекса в статье
Oracle: реализация btree индекса
8. Оптимизация доступа по индексу при Nested Loops.
** Обычный NL без оптимизации (Oracle 9)
Наиболее долгая операция при поиске по индексу, это рандомный доступ (scaterred) к таблице по rowid из последовательного индекса (sequencial).
В случае hdd большую часть времени будет выполняться позиционирование головки винта, чем собственно чтение данных.
----------------------------------------------
| Operation | Name | Rows |
------------------------------------------------
| SELECT STATEMENT | | 225 |
| NESTED LOOPS | | 225 |
| TABLE ACCESS BY INDEX RO|T2 | 15 |
| INDEX FULL SCAN |T2_I1 | 15 |
| TABLE ACCESS BY INDEX RO|T1 | 3K|
| INDEX RANGE SCAN |T1_I1 | 3K|
------------------------------------------------
** Prefetching (Oracle 10) - читает в буферный кэш смежные данные, в надежде, что они пригодятся
Чем хуже фактор кластеризации (на основе статистики), тем больше блоков читается за раз (multy block read)
-----------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 15 |
| 2 | NESTED LOOPS | | 1 | 225 | --225 строк, но всего 15 запросов из T1_I1 (блоки читаются не по одному, а по mbrc за раз)
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 |
| 4 | INDEX FULL SCAN | T2_I1 | 1 | 3000 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 15 | 15 |
-----------------------------------------------------------------
** batching (Oracle 11-12) - накапливается rowid и читает их потом скопом и многопоточно (multy block read)
Чем хуже фактор кластеризации (на основе реальных запросов из индекса-таблицы), тем больше блоков читается за раз (mbrc)
-----------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | |
| 1 | NESTED LOOPS | | 1 | 225 | --накапиливается несколько rowid
| 2 | NESTED LOOPS | | 1 | 225 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | -- выполняется параллельный селект
| 4 | INDEX FULL SCAN | T2_I1 | 1 | 3000 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 15 | 15 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 225 | 15 | -- выполняется параллельный селект
-----------------------------------------------------------------
Batching на HDD диске может дать 10 кратное ускорение, а на SSD до 2 раз.
9. Bitmap, bitmap join index.
https://docs.oracle.com/database/121/DWHSG/schemas.htm#DWHSG9042
+ Содержит NULL
+ Лучше использовать на столбцах с небольшим числом уникальных значений
Т.к. размер растет от числа значений:
* по X будут все возможные значений в колонке
* по Y сами строки
+ Главное преимущество: возможность комбинирования нескольких индексов при AND, OR, NOT
- при вставке блокируется часть со вставляемым значением целиком
Bitmap join индекс содержит значения пересечения левой и правой таблицы:
CREATE BITMAP INDEX sales_cust_gender_bjix
ON sales(customers.cust_gender)
FROM sales, customers
WHERE sales.cust_id = customers.cust_id;
Sales.rowid: gender(M) gender(F)
Sales.rowid1 0 0
Sales.rowid2 0 1
Sales.rowid3 1 0
...
10. Bitmap и Star Transformation
Оптимизация, при котором join заменяется на AND комбинацию bitmap индексов:
SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c, channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND c.cust_state_province = 'CA'
AND ch.channel_desc in ('Internet','Catalog')
AND t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
Запрос будет преобразован к виду:
SELECT ... FROM sales
WHERE time_id IN
(SELECT time_id FROM times
WHERE calendar_quarter_desc IN('1999-Q1','1999-Q2'))
AND cust_id IN
(SELECT cust_id FROM customers WHERE cust_state_province='CA')
AND channel_id IN
(SELECT channel_id FROM channels WHERE channel_desc IN('Internet','Catalog'));
При этом должны быть индексы на полях: sales.time_id, sales.cust_id, sales.channel_id.
Они будут объединены через BITMAP AND в один и будут использоваться для фильтрации sales:
SELECT STATEMENT
SORT GROUP BY
HASH JOIN
TABLE ACCESS FULL CHANNELS
HASH JOIN
TABLE ACCESS FULL CUSTOMERS
HASH JOIN
TABLE ACCESS FULL TIMES
PARTITION RANGE ITERATOR
TABLE ACCESS BY LOCAL INDEX ROWID SALES
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP MERGE
BITMAP KEY ITERATION
BUFFER SORT
TABLE ACCESS FULL CUSTOMERS
BITMAP INDEX RANGE SCAN SALES_CUST_BIX
BITMAP MERGE
BITMAP KEY ITERATION
BUFFER SORT
TABLE ACCESS FULL CHANNELS
BITMAP INDEX RANGE SCAN SALES_CHANNEL_BIX
BITMAP MERGE
BITMAP KEY ITERATION
BUFFER SORT
TABLE ACCESS FULL TIMES
BITMAP INDEX RANGE SCAN SALES_TIME_BIX
Если у нас bitmap join индекс, то еще лучше, не будет операции выборки из таблицы:
BUFFER SORT
TABLE ACCESS FULL CUSTOMERS
BITMAP INDEX RANGE SCAN SALES_CUST_BIX
будет одно объединение индексов:
BITMAP AND
BITMAP INDEX SINGLE VALUE SALES_C_STATE_BJIX
BITMAP MERGE
Домашняя бухгалтерия на ExtJS 4, PHP 5, SQLite 3.