Функциональный индекс

[Dmitry Paramonov]

Есть табличка хранения биллинга (партиционная)
поля = дата, кому, откуда, длит.
Какой индекс лучше к ней создать, чтоб выполнять быстро запрос
(чтобы время запроса не зависело сильно от числа строк)
select to_char(data,'DD.MM.YYYY') from billing group by to_char
(data,'DD.MM.YYYY')
создание локального функционального индекса на
to_char(data,'DD.MM.YYYY') не дает эффекта.
(query_rewrite... параметры высталены)

если создавать локальный bitmap функцмональный индекс, то план запроса
такого вида:
SELECT STATEMENT, GOAL = CHOOSE 2251 9033 63231
SORT GROUP BY 2251 9033 63231
PARTITION RANGE ALL
TABLE ACCESS BY LOCAL INDEX ROWID CALL BILLING 1717 110411
772877
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX FULL SCAN CALL D
и запрос выполняется дольше.
Можно ли решить проблему индексами?

[Sergey Borodin]

А почему нельзя группировать по TRUNC(DATA) ?

Post by Dmitry Paramonov
Есть табличка хранения биллинга (партиционная)
поля = дата, кому, откуда, длит.
Какой индекс лучше к ней создать, чтоб выполнять быстро запрос
(чтобы время запроса не зависело сильно от числа строк)
select to_char(data,'DD.MM.YYYY') from billing group by to_char
(data,'DD.MM.YYYY')
создание локального функционального индекса на
to_char(data,'DD.MM.YYYY') не дает эффекта.
(query_rewrite... параметры высталены)
если создавать локальный bitmap функцмональный индекс, то план запроса
SELECT STATEMENT, GOAL = CHOOSE 2251 9033 63231
SORT GROUP BY 2251 9033 63231
PARTITION RANGE ALL
TABLE ACCESS BY LOCAL INDEX ROWID CALL BILLING 1717 110411
772877
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX FULL SCAN CALL D
и запрос выполняется дольше.
Можно ли решить проблему индексами?

[Dmitry Paramonov]

А какая разница все равно
TABLE ACCESS FULL - вот этого я хочу избежать

Sergey Borodin пишет:
SB> А почему нельзя группировать по TRUNC(DATA) ?

SB>> Есть табличка хранения биллинга (партиционная)
SB>> поля = дата, кому, откуда, длит.
SB>> Какой индекс лучше к ней создать, чтоб выполнять быстро запрос
SB>> (чтобы время запроса не зависело сильно от числа строк)
SB>> select to_char(data,'DD.MM.YYYY') from billing group by to_char
SB>> (data,'DD.MM.YYYY')
SB>> создание локального функционального индекса на
SB>> to_char(data,'DD.MM.YYYY') не дает эффекта.
SB>> (query_rewrite... параметры высталены)
SB>>
SB>> если создавать локальный bitmap функцмональный индекс, то план
запроса
SB>> такого вида:
SB>> SELECT STATEMENT, GOAL = CHOOSE 2251 9033 63231
SB>> SORT GROUP BY 2251 9033 63231
SB>> PARTITION RANGE ALL
SB>> TABLE ACCESS BY LOCAL INDEX ROWID CALL BILLING 1717 110411
SB>> 772877
SB>> BITMAP CONVERSION TO ROWIDS
SB>> BITMAP INDEX FULL SCAN CALL D
SB>> и запрос выполняется дольше.
SB>> Можно ли решить проблему индексами?

[Denis Rudakov]

Fri Jun 04 2004 15:21, Dmitry Paramonov wrote to Sergey Borodin:

DP> From: Dmitry Paramonov <***@ros.ru>

DP> А какая разница все равно
DP> TABLE ACCESS FULL - вот этого я хочу избежать

А что значит - эффекта не дает? FBI используется?

--
dannis.

[Dmitry Paramonov]

Denis Rudakov пишет:
DR> Fri Jun 04 2004 15:21, Dmitry Paramonov wrote to Sergey Borodin:

DP>> From: Dmitry Paramonov <***@ros.ru>

DP>> А какая разница все равно
DP>> TABLE ACCESS FULL - вот этого я хочу избежать

DR> А что значит - эффекта не дает? FBI используется?

Нет

[Sergey Balter]

Post by Dmitry Paramonov
если создавать локальный bitmap функцмональный индекс, то план запроса
SELECT STATEMENT, GOAL = CHOOSE 2251 9033 63231
SORT GROUP BY 2251 9033 63231
PARTITION RANGE ALL
TABLE ACCESS BY LOCAL INDEX ROWID CALL BILLING 1717 110411
772877
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX FULL SCAN CALL D
и запрос выполняется дольше.
Можно ли решить проблему индексами?
--

Цель, как я понимаю - не избежать выполнения Table Full Scan,
а снизить затраты ресурсов сервера или ускорить
получение отклика на запрос наподобие
SELECT DISTINCT Trunc(Data) FROM Billing

Дело тут не в _функциональном_ индексе
а, вообще, в _индексе_. Даже если у тебя будет в таблице
поле, к примеру, DateOnly где будет храниться
"чистая" дата без времени, и сделать по нему
индекс, проблему это, скорее всего, не решит.

Это связано с тем, что Оракл не может эффективно
найти все уникальные значения в НЕУНИКАЛЬНОМ
индексе. Оракл вынужден читать подряд
все значения DateOnly, сортировать, и
затем отобрать уникальные. Так вот, обычно быстрее
прочитать DateOnly прямо из таблицы, чем из индекса.
Это связано с тем, что чтение из таблицы может
происходить одновременно несколькими блоками,
(Multiblock read) тогда как чтение индекса происходит
поблочно. Хотя, с другой стороны, использование
Full Scan по индексу, кажется, позволяет избежать
сортировки, но я думаю, что это практически
не важно в данной ситуации - ну сколько там
у тебя дней в таблице? ну год? ну пять? 1500 дат,
значит, основные затраты ложатся на выборку.

Правда, я мало работал с битмап-индексами, но
кажется (судя по плану запроса) там та же история,
что и с "традиционными" B-tree.

Можно попытаться ускорить запрос, используя
хинт /*+ INDEX_FFS(Billing индекс) */
(Это Fast Full Scan)
Тогда Оракл попытается использовать метод
мультиблочного считывания. Для этого ОБЯЗАТЕЛЬНО
чтобы поле было NOT NULL - я думаю, это реально
в твоем случае. Для обычных (не битмап) индексов
это ускоряет запрос процентов на 30.

Однако, я полагаю, что это все малоэффективно и не
сможет РАДИКАЛЬНО ускорить обработку запроса.

Если приведенный тобой запрос типичен для биллинговой
системы, нужно МОДЕРНИЗИРОВАТЬ СТРУКТУРЫ И
ЛОГИКУ.

Я предлагаю создать еще одну таблицу AllDays с
полем DateOnly, создать на нее УНИКАЛЬНЫЙ индекс
и row-триггером на BILLING дополнять ее при вставке
новой записи.

Тогда получаем следующие выгоды:
- получить все даты можно мгновенно и без затрат
по сравнени с нынешней ситуацией
- не нужны доп. индексы и доп. поля в таблице BILLING
Получаем следующий недостаток:
- нужно как-то строить и поддерживать вспомогательную
таблицу AllDays

Для того, чтобы максимально оптимизировать добавление
записей в таблицу AllDays, (что очень критично для
биллинговой системы), можно сделать так

триггер на
before insert
on table Billing
for each row
begin
My_Package.NewDate(Trunc(:NEW.Data));
end;

create or replace package body My_Package

LastDate Date := To_Date('01.01.01');

procedure NewDate(ADate Date)
is
ACnt pls_integer;
begin
if LastDate <> ADate then
SELECT Count(*) INTO ACnt FROM AllDays WHERE DateOnly = ADate;
if ACnt = 0 then
INSERT INTO AllDays(DateOnly) VALUES(ADate);
LastDate := ADate;
end if;
end if;
end;

end;


Идея здесь в том, чтобы снизить количество обращений к БД
_изнутри_ PL/SQL - это основной источник тормозов в
этой ситуации. Поскольку у тебя идет последовательная
вставка в основную таблицу, фактически обращения к
таблице AllDays будет происходить раз в сутки.

Я думаю, что очистка Billing от старых записей происходит
какими-то пакетными операциями? значит надо втулить
туда и соответствующее удаление из AllDays

Это лучше сделать statement-триггером на on Billing after delete

С уважением,
Сергей Балтер
Донецк, "СВОД"

P. S. Напиши, помогло ли

[Dmitry Paramonov]

Sergey Balter пишет:
SB>> если создавать локальный bitmap функцмональный индекс, то план
запроса
SB>> такого вида:
SB>> SELECT STATEMENT, GOAL = CHOOSE 2251 9033 63231
SB>> SORT GROUP BY 2251 9033 63231
SB>> PARTITION RANGE ALL
SB>> TABLE ACCESS BY LOCAL INDEX ROWID CALL BILLING 1717 110411
SB>> 772877
SB>> BITMAP CONVERSION TO ROWIDS
SB>> BITMAP INDEX FULL SCAN CALL D
SB>> и запрос выполняется дольше.
SB>> Можно ли решить проблему индексами?
SB>> --

SB> Цель, как я понимаю - не избежать выполнения Table Full Scan,
SB> а снизить затраты ресурсов сервера или ускорить
SB> получение отклика на запрос наподобие
SB> SELECT DISTINCT Trunc(Data) FROM Billing

Да, но при Table Full Scan таблицы в несколько(не говоря о десятках)
милллионов строк о какой прозводительности сервера может идти речь.

SB> Дело тут не в _функциональном_ индексе
SB> а, вообще, в _индексе_. Даже если у тебя будет в таблице
SB> поле, к примеру, DateOnly где будет храниться
SB> "чистая" дата без времени, и сделать по нему
SB> индекс, проблему это, скорее всего, не решит.

SB> Это связано с тем, что Оракл не может эффективно
SB> найти все уникальные значения в НЕУНИКАЛЬНОМ
SB> индексе. Оракл вынужден читать подряд
SB> все значения DateOnly, сортировать, и
SB> затем отобрать уникальные. Так вот, обычно быстрее
SB> прочитать DateOnly прямо из таблицы, чем из индекса.
SB> Это связано с тем, что чтение из таблицы может
SB> происходить одновременно несколькими блоками,
SB> (Multiblock read) тогда как чтение индекса происходит
SB> поблочно. Хотя, с другой стороны, использование
SB> Full Scan по индексу, кажется, позволяет избежать
SB> сортировки, но я думаю, что это практически
SB> не важно в данной ситуации - ну сколько там
SB> у тебя дней в таблице? ну год? ну пять? 1500 дат,
SB> значит, основные затраты ложатся на выборку.

SB> Правда, я мало работал с битмап-индексами, но
SB> кажется (судя по плану запроса) там та же история,
SB> что и с "традиционными" B-tree.

SB> Можно попытаться ускорить запрос, используя
SB> хинт /*+ INDEX_FFS(Billing индекс) */
SB> (Это Fast Full Scan)
SB> Тогда Оракл попытается использовать метод
SB> мультиблочного считывания. Для этого ОБЯЗАТЕЛЬНО
SB> чтобы поле было NOT NULL - я думаю, это реально
SB> в твоем случае. Для обычных (не битмап) индексов
SB> это ускоряет запрос процентов на 30.

Да, постановка Not NULL увеличила быстроту запроса в 3 раза
Вместо Table Full Scan план поменялся на Index Full Scan
SB> Однако, я полагаю, что это все малоэффективно и не
SB> сможет РАДИКАЛЬНО ускорить обработку запроса.

SB> Если приведенный тобой запрос типичен для биллинговой
SB> системы, нужно МОДЕРНИЗИРОВАТЬ СТРУКТУРЫ И
SB> ЛОГИКУ.

Да, я думал над этим: или сделать материализованное представление,
обновляемое раз в день, или триггер на таблицу, как в твоем примере.
Просто хотелось разобраться, когда какие индексы работают

SB> Я предлагаю создать еще одну таблицу AllDays с
SB> полем DateOnly, создать на нее УНИКАЛЬНЫЙ индекс
SB> и row-триггером на BILLING дополнять ее при вставке
SB> новой записи.

SB> Тогда получаем следующие выгоды:
SB> - получить все даты можно мгновенно и без затрат
SB> по сравнени с нынешней ситуацией
SB> - не нужны доп. индексы и доп. поля в таблице BILLING
SB> Получаем следующий недостаток:
SB> - нужно как-то строить и поддерживать вспомогательную
SB> таблицу AllDays

SB> Для того, чтобы максимально оптимизировать добавление
SB> записей в таблицу AllDays, (что очень критично для
SB> биллинговой системы), можно сделать так

SB> триггер на
SB> before insert
SB> on table Billing
SB> for each row

SB> create or replace package body My_Package

SB> LastDate Date := To_Date('01.01.01');

SB> procedure NewDate(ADate Date)
SB> is
SB> ACnt pls_integer;

SB> end;


SB> Идея здесь в том, чтобы снизить количество обращений к БД
SB> _изнутри_ PL/SQL - это основной источник тормозов в
SB> этой ситуации. Поскольку у тебя идет последовательная
SB> вставка в основную таблицу, фактически обращения к
SB> таблице AllDays будет происходить раз в сутки.

SB> Я думаю, что очистка Billing от старых записей происходит
SB> какими-то пакетными операциями? значит надо втулить
SB> туда и соответствующее удаление из AllDays

SB> Это лучше сделать statement-триггером на on Billing after delete

SB> С уважением,
SB> Сергей Балтер
SB> Донецк, "СВОД"

SB> P. S. Напиши, помогло ли