Глава 7

ops$tkyte@ORA8I.WORLD> insert /*+ APPEND */ into t

2 select rownum+cnt from t, (select count(*) cnt from t) ; 60804 rows created.

ops$tkyte@ORA8I.WORLD> commit; Commit complete.

ops$tkyte@ORA8I.WORLD> insert /*+ APPEND */ into t

2 select rownum+cnt from t, (select count(*) cnt from t) ; 121608 rows created.

ops$tkyte@ORA8I.WORLD> commit; Commit complete.

ops$tkyte@ORA8I.WORLD> insert /*+ APPEND */ into t

2 select rownum+cnt from t, (select count(*) cnt from t) ; 243216 rows created.

ops$tkyte@ORA8I.WORLD> commit; Commit complete.

ops$tkyte@ORA8I.WORLD> alter table t add constraint t pk primary key(x)

2 /

Table altered.

Теперь определим, сколько пространства используется до и после массового удаления данных:

ops$tkyte@ORA8I.WORLD> exec show space(Т РК, user, INDEX);

Free Blocks..........................0

Total Blocks.........................1024

Unused Blocks........................5

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> delete from t where x < 250000; 249999 rows deleted.

ops$tkyte@ORA8I.WORLD> commit; Commit complete.

ops$tkyte@ORA8I.WORLD> exec show space(T PK, user, INDEX);

Free Blocks..........................520

Total Blocks.........................1024

Unused Blocks........................5

PL/SQL procedure successfully completed.

Как видите, более половины блоков индекса теперь находятся в списке свободн1х. Это означает, что блоки - абсолютно пустые (блоки в списке свободных мест индекса должны быть полностью свободны, в отличие от блоков в списке свободных мест таблицы, организованной в виде кучи).

Итак, вам продемонстрировали следующее.

Пространство в блоках индекса используется повторно, когда добавляется строка, которая может его повторно использовать.

Индексы 389

Пустой блок индекса удаляется из индексной структуры и может повторно использоваться в дальнейшем. Происхождение мифа, вероятно, связано с тем, что наличие свободного пространства в блоках индекса в индексной структуре не видно так, как в таблице. В таблице блок может оказаться в списке свободных, даже если в нем есть данные. В список свободных блоков индекса попадают только абсолютно пустые блоки; блоки, в которых находится только одна запись индекса (а остальное пространство свободно), выявить непросто.

Миф: столбцы с максимальным количеством разных значений должны указываться первыми

Кажется, это следует из соображений здравого смысла. Если предполагается создание индекса по столбцам С1, С2 таблицы со 100000 строк, при этом столбец С1 имеет 100000 уникальных значений, а столбец С2 - 25000, индекс создается по столбцам Т(С1,С2). Это означает, что столбец С1 должен указываться первым, что соответствует здравому смыслу . Фактически при сравнении векторов данных (пара значений Cl, C2 задает вектор) порядок столбцов не имеет значения. Рассмотрим следующий пример. Создадим таблицу со всеми объектами базы данных, а затем - индекс по столбцам OWNER, OBJECTTYPE и OBJECTNAME (начиная со столбца с минимальным количеством значений) и индекс по столбцам OBJECTNAME, OBJECTTYPE и OWNER:

tkyte@TKrE816> create table t

2 nologging

3 as

4 select * from all objects; Tale created.

tkyte@TKYTE816> create index t idx l on t(owner,object type,object name)

2 nologging pctfree 0; Index created.

tkyte@TKYTE816> create index t idx 2 on t(object name,object type,owner)

2 nologging pctfree 0; Index created.

tkyte@TKYTE816> select count(distinct owner), count(distinct object type),

2 count(distinct object name) , count(*)

3 from t;

(DISTINCT OWNER) (DISTINCT OBJECT TYPE) (DISTINCT OBJECT NAME) COUNT (*)

24 23 12265 21975

Теперь, чтобы показать, что по используемому пространству ни один из индексов не имеет преимущества, определим, сколько пространства они используют:

tkyte@TKYTE816> exec show space(T IDX 1, user, INDEX);

Free Blocks..........................0

Total Blocks.........................192

Глава 7

Total Bytes..........................1572864

Unused Blocks........................51

Unused Bytes.........................417792

Last Used Ext FileId................6

Last Used Ext BlockId...............4745

Last Used Block.....................13

PL/SQL procedure successfully completed.

tkyte@TKYTE816> exec show space(T IDX 2, user, INDEX);

Free Blocks..........................0

Total Blocks.........................192

Total Bytes..........................1572864

Unused Blocks........................51

Unused Bytes.........................417792

Last Used Ext FileId................6

Last Used Ext BlockId...............4937

Last Used Block......................13

PL/SQL procedure successfully completed.

Они используют одинаковый объем пространства. Однако первый индекс куда лучше будет сжиматься, если использовать сжатие ключей. Есть аргумент в пользу перечисления столбцов, начиная с наименее избирательного. Теперь сравним производительность: посмотрим, является ли какой-то из индексов более эффективным в этом см1сле. Чтобы протестировать это, я использовал блок PL/SQL с запросами, включающими подсказки (так, чтобы использовался тот или иной индекс) следующего вида:

tkyte@TKYTE816> alter session set sql trace=true; Session altered. tkyte@TKYTE816> declare

2 cnt int;

3 begin

4 for x in (select owner, object type, object name from t)

5 loop

6 select /*+ INDEX(t t idx l) */ count(*) into cnt

7 from t

8 where object name = x.object name

9 and object type = x.object type 10 and owner = x.owner;

12 select /*+ INDEX(t t idx 2) */ count(*) into cnt

13 from t

14 where object name = x.object name

15 and object type = x.object type

16 and owner = x.owner;

17 end loop;

18 end;

19 /

PL/SQL procedure successfully completed.

Эти запросы читают по индексу все строки таблицы. Отчет, сгенерированный утилитой TKPROF, показывает следующее: