Оптимизатор при расчете стоимости индексного доступа к данным таблицы использует показатель под названием кластерный фактор (CF). Этот показатель выражает степень распределения записей в таблице относительно индексных ключей.
Давайте попробуем разобраться, что такое этот CF. Предположим, с помощью индекса мы получили перечень адресов (rowid) всех записей таблицы, которые нам нужны. Теперь предстоит выбрать непосредственно сами записи таблицы. В каждом rowid содержится номер блока и номер искомой записи в блоке. Если блок находится в буферном кеше, тогда его с диска читать нет необходимости. В противном случае оракл совершает физическое одиночное чтение блока с диска. Приступаем к выборке следующей записи. По ее rowid определяем, какой блок нужен. Отлично, если запись находится в том же блоке, что и предыдущая запись. Если же нужен не тот блок, что уже считан на предыдущем шаге, значит опять нужно использовать физическое одиночное чтение для нового блока.
Таким образом, если записи таблицы относительно индексных ключей распределены по блокам хаотично, ораклу придется совершать много физических одиночных чтений. Кластерный фактор как раз и выражает, какое соотношение имеется между записями индекса и расположением записей в блоках данных. Фактор - это количество чтений другого блока данных при переходе от одной записи индекса к другой. Оптимизатор кластерный фактор для индекса считает во время сбора статистики. Если этот показатель будет плохим для таблицы, то при выборке данных из нее оптимизатор для индексного доступа поставит плохую оценку.
Если значение CF близкое к количеству блоков таблицы, то это хороший индекс означающий, что записи таблицы отсортированы относительно значений индексных ключей. Если же записи распределены случайным образом, а значит, имеется большое CF значение, которое близко к количеству записей в таблице, то это плохой индекс.
А теперь разберемся, как считается CF. Создаем тестовую таблицу:
SQL> create table my_test 2 pctfree 95 pctused 5 3 as select object_id, SUBSTR(object_name,1,50) NAME, status from dba_objects 4 where rownum < 51 and owner='SYSTEM' 5 order by dbms_random.value ; SQL> select * from my_test order by rowid ; OBJECT_ID NAME STATUS ---------- -------------------------------------------------- ------- 3871 DEF$_AQERROR VALID 3635 AQ$_QUEUE_TABLES VALID 6249 HELP VALID 3881 DEF$_ERROR_PRIMARY VALID 6250 HELP_TOPIC_SEQ VALID 3884 DEF$_CALLDEST VALID 4328 LOGMNRC_GSII VALID 3880 DEF$_ERROR VALID 119151 AQ$_INTERNET_AGENT_PRIVS VALID 4310 LOGMNRC_DBNAME_UID_MAP VALID 3637 AQ$_QUEUES VALID 3883 DEF$_DESTINATION_PRIMARY VALID 3898 DEF$_TEMP$LOB VALID 3889 DEF$_LOB VALID 3888 DEF$_DEFAULTDEST_PRIMARY VALID 3870 DEF$_TRANORDER VALID 3897 DEF$_LOB_N1 VALID 3645 AQ$_SCHEDULES VALID 1941 COL VALID 3640 AQ$_QUEUES_PRIMARY VALID 3886 DEF$_CALLDEST_N2 VALID 3861 DEF$_AQCALL VALID 3896 DEF$_LOB_PRIMARY VALID 5638 DBMS_REPCAT_AUTH VALID 3885 DEF$_CALLDEST_PRIMARY VALID 5138 I_REPCAT$_SNAPGROUP1 VALID 3868 AQ$DEF$_AQCALL VALID 3641 AQ$_QUEUES_CHECK VALID 3877 AQ$_DEF$_AQERROR_E VALID 119149 AQ$_INTERNET_AGENTS VALID 5640 DBMS_REPCAT_AUTH VALID 4329 LOGMNRC_GSII VALID 3882 DEF$_DESTINATION VALID 3647 AQ$_SCHEDULES_CHECK VALID 3646 AQ$_SCHEDULES_PRIMARY VALID 3906 DEF$_PROPAGATOR VALID 4311 LOGMNRC_DBNAME_UID_MAP_PK VALID 3869 DEF$_AQCALL VALID 3878 AQ$DEF$_AQERROR VALID 1936 CATALOG VALID 3910 DEF$_ORIGIN VALID 3636 AQ$_QUEUE_TABLES_PRIMARY VALID 3887 DEF$_DEFAULTDEST VALID 3867 AQ$_DEF$_AQCALL_E VALID 3911 DEF$_PUSHED_TRANSACTIONS VALID 3908 DEF$_PROPAGATOR_TRIG VALID 3879 DEF$_AQERROR VALID 3907 DEF$_PROPAGATOR_PRIMARY VALID 4331 LOGMNRC_GSII_PK VALID 3912 DEF$_PUSHED_TRAN_PRIMARY VALID SQL> create index my_test_idx on my_test(object_id) compute statistics ;
Смотрим, какой кластерный фактор:
SQL> select index_name, blevel, leaf_blocks,distinct_keys, clustering_factor 2 from user_indexes where index_name = 'MY_TEST_IDX' ; INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR ------------------------------ ---------- ----------- ------------- ----------------- MY_TEST_IDX 0 1 50 38
Кластерный фактор – 38. А теперь попробуем сами подсчитать значение кластерного фактора (см. третью колонку):
SQL> select object_id, dbms_rowid.rowid_block_number(rowid) blk_num 2 from my_test order by object_id; OBJECT_ID BLK_NUM ---------- ---------- 1936 1463 1 1941 1461 2 3635 1460 3 3636 1463 4 3637 1460 5 3640 1461 6 3641 1462 7 3645 1461 8 3646 1462 9 3647 1462 9 3861 1461 10 3867 1463 11 3868 1462 12 3869 1463 13 3870 1461 14 3871 1460 15 3877 1462 16 3878 1463 17 3879 1463 17 3880 1460 18 3881 1460 18 3882 1462 19 3883 1460 20 3884 1460 20 3885 1461 21 3886 1461 21 3887 1463 22 3888 1461 23 3889 1461 23 3896 1461 23 3897 1461 23 3898 1461 23 3906 1462 24 3907 1464 25 3908 1463 26 3910 1463 26 3911 1463 26 3912 1464 27 4310 1460 28 4311 1463 29 4328 1460 30 4329 1462 31 4331 1464 32 5138 1462 33 5638 1461 34 5640 1462 35 6249 1460 36 6250 1460 36 119149 1462 37 119151 1460 38
Как видим, мы также получили 38.
При сборе статистики для индексов без опции parallel degree Oracle последовательно читает все ключевые значения индекса, начиная с самого нижнего листового блока, и одновременно с помощью функции sys_op_countchg подсчитываем показатель CF (кластерный фактор), как только номер блока меняется.