Глава 7

эквивалентно условию:

DATE COL BETWEEN TRUNC (SYSDATE) AND TRUNC (SYSDATE) ПЛЮС ОДНИ СУТКИ МИНУС ОДНА СЕКУНДА

После этого понятно, как использовать конструкцию BETWEEN.

select * from t

where date col between trunc(sysdate) and trunc(sysdate)+1-1/(1*24*60*60)

Примечаще: выражение1/(1*24*60*60) означает часть суток, равную одной секунде. Вычитание 1 отбросит один день, 1/24 - один час, а 1/(24*60) - одну минуту.

При этом все функции переносятся в правую часть оператора сравнения, что позволяет использовать индекс по столбцу DATE COL (по сути условие эквивалентно исходному, WHERE TRUNC(DATE COL) = TRUNC(SYSDATE)). По возможности, надо -бегать применения функций к столбцам базы данных в условиях. Это позволит не только использовать индексы, но и сократит объем вычислений, которые необходимо в1пол-нять серверу. В предыдущем случае, когда используется условие:

between trunc(sysdate) and trunc(sydate)+l/(1*24*60*60)

значения вычисляются в запросе только один раз, а затем можно просто искать соответствующие значения ключа по индексу. При использовании условия TRUNC(DATE COL) = TRUNC(SYSDATE) выражение TRUNC(DATE COL) придется вычислять для каждой строки во всей таблице (индекс ведь не используется).

Случай 5

При использовании индекса работа только замедляется. Я видел это часто: разработчики предполагают, что индекс всегда ускоряет выполнение запроса. Поэтому они создают небольшую таблицу, анализируют ее и обнаруживают, что оптимизатор не использует индекс. В данном случае оптимизатор делает правильный выбор. Сервер Oracle (при использовании оптимизатора, основанного на стоимости) будет использовать индекс, только если в этом есть смысл. Рассмотрим следующий пример:

ops$tkyte@ORA8I.WORLD> create table t

2 (x, у null, primary key (x))

3 as

4 select rownum x, username

5 from all users

6 where rownum <= 100

Table created.

ops$tkyte@ORA8I.WORLD> analyze table t compute statistics; Table analyzed.

ops$tkyte@ORA8I.WORLD> analyze table t compute statistics for all indexes; Table analyzed.

Индексы 383

ops$tkyte@ORA8I.WORLD> set autotrace on explain ops$tkyte@ORA8I.WORLD> select count(y) from t where x < 50;

COUNT(Y) 49

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=l Card=l Bytes=18)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF T (Cost=l Card=50 Bytes = 900)

Оптимизатор, основанный на стоимости, обращается к таблице и определяет, что придется выбирать 50 процентов ее строк. Делать это по индексу медленно; придется читать блок индекса, а затем обрабатывать все строки, на которые он ссылается, причем для каждой строки придется читать блок базы данных. Намного эффективнее просто прочитать все строки в блоке и найти те 50 процентов, которые надо обрабатывать. Теперь, если немного изменить пример:

ops$tkyte@ORA8I.WORLD> set autotrace off ops$tkyte@ORA8I.WORLD> insert into t

2 select rowmum+100, username

3 from all users

41231 rows created.

ops$tkyte@ORA8I.WORLD> analyze table t compute statistics; Table analyzed.

ops$tkyte@ORA8I.WORLD> analyze table t compute statistics for all indexes; Table analyzed.

ops$tkyte@ORA8I.WORLD> set autotrace on explain ops$tkyte@ORA8I.WORLD> select count(y) from t where x < 50;

COUNT (Y) 49

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=l Bytes=21)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (BY INDEX ROWID) OF I (Cost=3 Card=50

3 2 INDEX (RANGE SCAN) OF SYS C0038226 (UNIQUE) (Cost=2

Оптимизатор понимает , что теперь по условию будет выбираться около 0,1 процента строк, поэтому, определенно, есть смысл использовать индекс.

Этот пример показывает, во-первых, что индексы должны использоваться не всегда. Прежде чем делать выводы, убедитесь, что доступ по индексу действительно будет выполняться быстрее. А во-вторых, насколько важна актуальная статистическая информа-

Глава 7

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

Случай 6

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

ops$tkyte@ORA8I.WORLD> insert into t

2 select rownum+100, username

3 from all users

41231 rows created.

ops$tkyte@ORA8I.WORLD> set autotrace on explain ops$tkyte@ORA8I.WORLD> select count(y) from t where x < 50;

COUNT(Y)

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=l Card=l Bytes=18)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF T (Cost=l Card=50 Bytes=900)

ops$tkyte@ORA8I.WORLD> set autotrace off

ops$tkyte@ORA8I.WORLD> analyze table t compute statistics; Table analyzed.

ops$tkyte@ORA8I.WORLD> analyze table t compute statistics for all indexes; Table analyzed.

ops$tkyte@ORA8I.WORLD> set autotrace on explain ops$tkyte@ORA8I.WORLD> select count(y) from t where x < 50;

COUNT(Y)

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=l Bytes=21)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (BY INDEX ROWID) OF T (Cost = 3 Card = 50 Bytes = 1050)

3 2 INDEX (RANGE SCAN) OF SYS C0038227 (UNIQUE) (Cost = 2 Card=50)