2909 consistent gets 258 physical reads 0 redo size 1991367 bytes sent via SQL*Net to client 148387 bytes received via SQL*Net from client 1335 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20001 rows processed

tkyte@TKYTE816> select * from DISORGANIZED where x between 20000 and 40000; 20001 rows selected. Elapsed: 00:00:23.34

Execution Flan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF DISORGANIZED

2 1 INDEX (RANGE SCAN) OF DISORGANIZED PK (UNIQUE)

Statistics

0 recursive calls

0 db block gets

21361 consistent gets

1684 physical reads

0 redo size

1991367 bytes sent via SQL*Net to client

148387 bytes received via SQL*Net from client

1335 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

20001 rows processed

Это просто невероятно - насколько существенно может влиять на результат физическое размещение данных! Подведем итоги:

В моей базе данных с размером блока 8 Кбайт каждая из этих таблиц занимает 1088 блоков. Запрос к дезорганизованной таблице демонстрирует рассчитанный ранее результат: выполнено более 20000 логических операций ввода/вывода. Каждый блок мы обрабатывали 20 раз! С другой стороны, при физическом размещении близких данных рядом количество логических операций заметно уменьшается. Вот отличный пример того, почему простые правила так сложно сформулировать: в одном случае использование индекса эффективно, в других - нет. Учтите это в следующий раз, когда будете сбрасывать данные из производственной системы и переносить в среду разработки - это

поможет ответить на часто возникающий вопрос: Почему на этой машине работает не так - они же идентичны? . Они не идентичны.

Чтобы завершить этот пример, давайте посмотрим, что происходит при полном просмотре дезорганизованной таблицы:

tkyte@TKYTE816> select /*+ FULL(DISORGANIZED) */ *

2 from DISORGANIZED

3 where x between 20000 and 40000; 20001 rows selected.

Elapsed: 00:00:01.42

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=162 Card=218 Bytes-2

1 0 TABLE ACCESS (FULL) OF DISORGANIZED (Cost=162 Card=218 В

Statistics

0 recursive calls

15 db block gets

2385 consistent gets

404 physical reads

0 redo size

1991367 bytes sent via SQL*Net to client

148387 bytes received via SQL*Net from client

1335 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

20001 rows processed

Это показывает, что в данном случае способ физического хранения данных на диске вполне допускает полный просмотр. Возникает вопрос: как это учесть? Ответ простой: используйте оптимизатор, основанный на стоимостей он сделает это автоматически. Представленный выше пример выполнялся в режиме оптимизации RULE, поскольку статистическая информация о таблице не собиралась. Единственный раз, когда использовался оптимизатор, основанный на стоимости, - при вводе подсказки о полном просмотре таблицы, - мы попросили его сгенерировать конкретный план. Проанализировав таблицы, можно увидеть часть информации, используемой сервером Oracle для оптимизации представленных выше запросов:

tkyte@TKYTE816> analyze table colocated

2 compute statistics

3 for table

4 for all indexes

5 for all indexed columns

Table analyzed.

tkyte@TKYTE816> analyze table disorganized 2 compute statistics

3 for table

4 for all indexes

5 for all indexed columns

Table analyzed.

Теперь давайте получим часть используемой сервером Oracle информации. В частности, нас будет интересовать значение столбца CLUSTERING FACTOR в представлении USER INDEXES. Руководство Oracle Reference Manual утверждает, что этот столбец имеет следующий смысл.

Показывает, насколько упорядочены строки в таблице по значениям индекса:

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

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

Значение CLUSTERING FACTOR (показатель кластеризации) - показатель того, насколько упорядочена таблица в соответствии с индексом. Посмотрим на информацию об использовавшихся нами индексах:

tkyte@TKYTE816> select a.index name,

2 b.num rows,

3 b.blocks,

4 a.clustering factor

5 from user indexes a, user tables b

6 where index name in (COLOCATED PK, DISORGANIZED PK)

7 and a.table name = b.table name

INDEX NAME NUM ROWS BLOCKS CLUSTERING FACTOR

COLOCATED PK 100000 1063 1063

DISORGANIZED PK 100000 1064 99908

Индекс COLOCATED PK - классический пример хорошо упорядоченной табли-ц1 , а вот DISORGANIZEPK - как раз классический пример неупорядоченной таблицы . Интересно разобраться, как это влияет на работу оптимизатора. Если попытаться выбрать 20000 строк, сервер Oracle теперь использует полный просмотр таблицы для обоих запросов (выбор 20 процентов строк по индексу - неоптимальный план даже для очень хорошо упорядоченной таблицы). Однако если выбирать 10 процентов данных таблицы:

tkyte@TKYTE816> select * from COLOCATED where x between 20000 and 30000;

10001 rows selected. Elapsed: 00:00:00.11