Немного полезных запросов для работы с индексами

dbstalker, 08 июня

Как вычислить ожидаемую оптимальную высоту дерева, основываясь на статистике из представления INDEX_STATS.

Источник.
SELECT name NAME,round((br_rows_len*100)/(br_blk_len*br_blks),2) BRANCH_UTILIZATION,
round(((lf_rows_len - del_lf_rows_len)*100)/(lf_blk_len*lf_blks),2) LEAF_UTILIZATION,
decode (SIGN(ceil(log(br_blk_len/(br_rows_len/br_rows),
lf_blk_len/((lf_rows_len - del_lf_rows_len)/(lf_rows - del_lf_rows))))
+ 1 - height), -1, 'YES', 'NO') CAN_REDUCE_LEVEL
from INDEX_STATS;

Выбор индексов с низкой селективностью.

Чем меньше уникальных значений в индексе, и чем больше на одно уникальное значение в индексе приходится записей в таблице, тем бесполезнее становится индекс. Если таблица большая, то нужно рассматривать вопрос об их удалении за ненадобностью.

SELECT owner,index_name, distinct_keys, round(num_rows,2) num_rows, 
    round(decode(num_rows,0,1,num_rows)/decode(distinct_keys,0,1,distinct_keys),2) rows_on_keys,
  LEAF_BLOCKS,
  CLUSTERING_FACTOR, 
  BLEVEL ,
  AVG_LEAF_BLOCKS_PER_KEY 
  FROM dba_indexes
 WHERE uniqueness<>'UNIQUE' and decode(num_rows,0,1,num_rows)/decode(distinct_keys,0,1,distinct_keys)>2
 ORDER BY decode(num_rows,0,1,num_rows)/decode(distinct_keys,0,1,distinct_keys)   desc

Описание выборки:

NUM_ROWS - количество строк в индексе

DISTINCT_KEYS - количество различающихся индексированных значений. Для индексов, которые обеспечивают соблюдение ограничений целостности UNIQUE и PRIMARY KEY, это значение совпадает с числом строк в таблице (NUM_ROWS). (Может включать строки, которые были удалены)

LEAF_BLOCKS - количество листовых блоков

CLUSTERING_FACTOR - указывает степень упорядоченности строк таблицы по отношению к значениям индекса. если это значение близко к количеству блоков, то таблица очень хорошо упорядочена. В этом случае индексные записи в отдельном листовом блоке с большой вероятностью указывают на строки в тех же блоках данных. Если же это значение близко к количеству строк в таблице, то таблица плохо упорядочена. В этом случае маловероятно, что индексные записи в одном листовом блоке указывают на сторки в тех же блоках данных

AVG_LEAF_BLOCKS_PER_KEY - округленное до ближнего целого среднее количество листовых блоков индекса, в которых содержится каждое значение. Для индексов, которые обеспечивают соблюдение ограничений целостности UNIQUE и PRIMARY KEY, это значение всегда равно 1.

Выборка индексов имеющих размер больший, чем у самой таблицы.

SELECT i.TABLE_NAME,i.index_name,si.bytes size_ind, st.bytes size_tab FROM dba_indexes i,
	   (SELECT segment_name, sum(bytes) bytes 
	      FROM dba_extents WHERE owner='MY_USER' AND segment_type='INDEX'
	     GROUP BY segment_name) si,
	   (SELECT segment_name, sum(bytes) bytes
              FROM dba_extents WHERE owner='MY_USER' AND segment_type='TABLE'
             GROUP BY segment_name) st 
WHERE i.index_name=si.segment_name AND st.segment_name=i.TABLE_NAME AND st.bytes < si.bytes

Индексы - кандидаты на изменение параметра PCTFREE.

Индекс создан на основе последовательности, то его можно пересоздать с параметром PCTFREE=0. Такие индексы можно поискать среди выборки:


SELECT a.index_name, a.table_name, a.pct_free 
  FROM DBA_INDEXES a, (SELECT index_name, table_name, count(*)
                          FROM DBA_IND_COLUMNS
                         GROUP BY index_name, table_name
                        HAVING count(*)=1) b
 WHERE a.UNIQUENESS='UNIQUE' AND a.table_owner='MY_USER'
   AND a.index_name=b.index_name AND a.table_name=b.table_name
 

Изменить pct_free можно так:


ALTER INDEX name_index REBUILD tablespace USERS_my PCTFREE 0;

Как оказалось, часть запросов "слизана" отсюда. Извинения и благодарность автору - Тыринову Анатолию.

3 комментария

Прокоментировать

Имя
9 декабря 2010 г. в 12:39

>Выбор индексов с низкой селективностью

Но ведь есть куча индексов, которые предназначены для поддержания FK, а не для реальных запросов..

Тыринов Анатолий
3 апреля 2012 г. в 15:03

Некрасиво использовать чужие наработки без указания автора! Это называется плагиат.
Оригинал моей статьи, с которой слизали часть запросов, находится тут:
http://oracle-inf.narod.ru/problem_index.html
Я не против использования моих наработок, но укажите первоисточник!

dbstalker
23 мая 2012 г. в 12:37

Если это так, то приношу свои извинения. В пост внесены изменения.

 

Новый комментарий

Я не спамер: введите суму 4+0



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Нужен поставщик Дропшиппинг
10 декабря, 1 ответа
КИНО КАФЕ!!!!!!!!!!!!!!!!
10 декабря, 1 ответа