Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 использование FBI  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34630
Есть такой вопрос по использованию function-based index и индекса по выражению, который вроде бы как является подмножеством FBI.

Допустим, есть таблица документов DOC. Есть в нём поле STATE_ID, ну и большинство документов находится в состоянии 0.
Скажем, половина таблицы. Остальные значения селективны и равномерно размазаны по таблице. Т.е. имеем ещё S состояний
и N-N0 записей, по (N-N0)/S записей на одно состояние, и S большое.

Хочется естественно убрать из индекса пол-таблицы, которая в состоянии 0, всё равно неселективно.
Ну, делаем индекс на выражение:

create index IDX_DOC_STATE on DOC(case when STATE_ID in (0) then null else STATE_ID end);

null уходит из индекса, и всё вроде здорово.

Вопрос теперь в том, как использовать такой индекс в запросах.

Если бы индекс был
create or replace 
function DOC_STATE ( STATE_ID IN DOC.STATE_ID%type ) return DOC.STATE_ID%type deterministic
as 
begin
  return case when STATE_ID in (0) then null else STATE_ID end;
end;

create index IDX_DOC_STATE on DOC(DOC_STATE (STATE_ID));

То вполне понятно, что запрос обязан быть

select * from DOC where DOC_STATE( STATE_ID) = 25;

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

Вопрос в том, если индекс такой
create index IDX_DOC_STATE on DOC(case when STATE_ID in (0) then null else STATE_ID end);


достаточно ли будет запроса
select * from DOC where STATE_ID = 25;


или же нам обязательно использовать выражение в запросе
select * from DOC where  case when STATE_ID in (0) then null else STATE_ID end = 25;


Заранее благодарен...
25 окт 13, 17:05    [15033460]     Ответить | Цитировать Сообщить модератору
 Re: использование FBI  [new]
paolo77
Member

Откуда:
Сообщений: 20
Надо использовать выражение.
25 окт 13, 17:17    [15033520]     Ответить | Цитировать Сообщить модератору
 Re: использование FBI  [new]
ORA__SQL
Member

Откуда: Moscow
Сообщений: 1774
MasterZiv,
Вместо того, чтобы постить на форум скрипты, сразу гони их в sqlplus и смотри планы
25 окт 13, 17:26    [15033586]     Ответить | Цитировать Сообщить модератору
 Re: использование FBI  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34630
ORA__SQL
MasterZiv,
Вместо того, чтобы постить на форум скрипты, сразу гони их в sqlplus и смотри планы


Думаешь, я не смортел ?
Но, понимаешь ли, то, что в конкретном запросе в плане не используется индекс, не значит, что индекс не может использоваться
в принципе. Обратное справедливо, если бы я увидел индекс в плане, я бы этот пост не писал.
25 окт 13, 18:37    [15033887]     Ответить | Цитировать Сообщить модератору
 Re: использование FBI  [new]
asdasdad
Guest
MasterZiv,

Более того, помнится, что в определенных версиях и для некоторых выражений даже указание такого же выражения в select, как и в create index, этот индекс не подхватывало. Приходилось подсматривать выражение в метаданных и именно его использовать в select.
25 окт 13, 18:45    [15033919]     Ответить | Цитировать Сообщить модератору
 Re: использование FBI  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10040
Как уже было сказано, надо использовать выражение. А чтобы в выражении было меньше буковок используй:

create index IDX_DOC_STATE on NULLIF(STATE_ID,0);


SY.
25 окт 13, 18:46    [15033927]     Ответить | Цитировать Сообщить модератору
 Re: использование FBI  [new]
-2-
Member

Откуда:
Сообщений: 15330
MasterZiv
Хочется естественно убрать из индекса пол-таблицы, которая в состоянии 0, всё равно неселективно.
и что, много быстрее запросы стали?
25 окт 13, 20:26    [15034215]     Ответить | Цитировать Сообщить модератору
 Re: использование FBI  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34630
-2-,

Ну, где-то -- да.
Где-то выяснилось в итоге, что индек ни на фиг вообще не нужен, поскольку не используется вообще в принципе, был создан "про запас".
Но это к вопросу-то не относится.
25 окт 13, 21:23    [15034469]     Ответить | Цитировать Сообщить модератору
 Re: использование FBI  [new]
Elic
Member

Откуда:
Сообщений: 29976
MasterZiv
или же нам обязательно использовать выражение в запросе
Обязательно. Для удобства ненаписания мега-выражений можно использовать:
  • представление
  • 11g: виртуальная колонка
  • 12c: невидимая виртуальная колонка
  • 26 окт 13, 08:33    [15035676]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    MasterZiv
    Member

    Откуда: Питер
    Сообщений: 34630
    Elic
    MasterZiv
    или же нам обязательно использовать выражение в запросе
    Обязательно. Для удобства ненаписания мега-выражений можно использовать:
  • представление
  • 11g: виртуальная колонка
  • 12c: невидимая виртуальная колонка


  • О, спасибо за ценные соображения...
    26 окт 13, 11:26    [15035813]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    dbms_photoshop
    Member

    Откуда: sqlmdx.net
    Сообщений: 5151
    Elic
  • 12c: невидимая виртуальная колонка
  • Не совсем понятно в чем профит от невидимости в случае ТС.
    Цель же наоборот использовать колонку вместо выражения.
    А с другой стороны невидимая виртуальность вполне документированными средствами достигается и в 11.
    26 окт 13, 14:57    [15036014]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    SY
    Member

    Откуда: Middlebury, CT USA
    Сообщений: 10040
    Elic
    Для удобства ненаписания мега-выражений можно использовать


    Все хорошо, но лично меня напрягает необходимость перечисления полей в инсерте в 11G. Ну и соответственно в 11G необходимо будет перелопатить весь код на предмет INSERT INTO table без перечисления полей.

    SY.

    Сообщение было отредактировано: 26 окт 13, 15:37
    26 окт 13, 15:30    [15036043]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    Elic
    Member

    Откуда:
    Сообщений: 29976
    dbms_photoshop
    Elic
  • 12c: невидимая виртуальная колонка
  • Не совсем понятно в чем профит от невидимости в случае ТС.
    В наименьшем (по документации - нулевом?) влиянии на уже существующий код.
    Недостатки видимости уже показал Соломон.
    dbms_photoshop
    А с другой стороны невидимая виртуальность вполне документированными средствами достигается и в 11.
    Не томи, намекни.
    26 окт 13, 20:51    [15036513]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    SY
    Member

    Откуда: Middlebury, CT USA
    Сообщений: 10040
    Elic
    Не томи, намекни.


    Cоздать FBI --> посмотреть имя виртуальной колонки. При желании переименовать:


    SQL> drop table tbl purge;
    
    Table dropped.
    
    SQL> create table tbl as select empno,ename,sal,comm from emp;
    
    Table created.
    
    SQL> create index tbl_idx1 on tbl(sal + comm);
    
    Index created.
    
    SQL> select column_name from user_tab_cols where table_name = 'TBL' and hidden_column = 'YES';
    
    COLUMN_NAME
    --------------------------------------------------------------------------------------------------
    SYS_NC00005$
    
    SQL> alter table tbl rename column SYS_NC00005$ to sal_plus_comm;
    
    Table altered.
    
    SQL> explain plan for
      2  select * from tbl where sal_plus_comm = 5000;
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------
    
    Plan hash value: 3798903459
    
    ------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |          |     1 |    15 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TBL      |     1 |    15 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN                  | TBL_IDX1 |     1 |       |     1   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------
    
    ---------------------------------------------------
    
       2 - access("SAL"+"COMM"=5000)
    
    14 rows selected.
    
    SQL> insert into tbl select empno,ename,sal,comm from emp;
    
    14 rows created.
    
    SQL>
    


    SY.

    Сообщение было отредактировано: 26 окт 13, 21:49
    26 окт 13, 21:49    [15036593]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    Elic
    Member

    Откуда:
    Сообщений: 29976
    SY
    Cоздать FBI --> посмотреть имя виртуальной колонки. При желании переименовать
    Спасибо.
    Чтобы "такое" использовать в тиражируемом коде, переименовать пришлось бы. Но чует сердце, что подобная недокументальщина чревата скорыми OERI.
    26 окт 13, 22:08    [15036625]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    SY
    Member

    Откуда: Middlebury, CT USA
    Сообщений: 10040
    Кстати:

    TABLE ACCESS BY INDEX ROWID BATCHED

    Новая примочка в 12C: если понял правильно, проверяется наличие блоков для ROWID из INDEX RANGE SCAN в buffer cache и делается prefetch отсутствующих.

    SY.
    26 окт 13, 22:15    [15036645]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    wurdu
    Member

    Откуда: Владивосток
    Сообщений: 4441
    SY
    Кстати:

    TABLE ACCESS BY INDEX ROWID BATCHED

    Новая примочка в 12C: если понял правильно, проверяется наличие блоков для ROWID из INDEX RANGE SCAN в buffer cache и делается prefetch отсутствующих.

    SY.
    По-моему, это несколько другое. Префетчи / vector io в разном виде есть как минимум с 10g. А про batched Oracle пишет:
    Oracle® Database SQL Tuning Guide 12c Release 1 (12.1)
    The BATCHED access shown in Step 1 means that the database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.
    27 окт 13, 01:55    [15037148]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    dbms_photoshop
    Member

    Откуда: sqlmdx.net
    Сообщений: 5151
    Elic
    Не томи, намекни.
    13909817
    28 окт 13, 12:11    [15040412]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    MasterZiv
    Member

    Откуда: Питер
    Сообщений: 34630
    dbms_photoshop
    Elic
  • 12c: невидимая виртуальная колонка
  • Не совсем понятно в чем профит от невидимости в случае ТС.
    Цель же наоборот использовать колонку вместо выражения.


    12 у нас просто тупо нет пока, поэтому оно не подойдёт в любом случае.
    А вот просто виртуальная колонка подойдёт -- если человек захочет в запросе использовать индекс по этому полю, и гарантированно будет знать, что неселективные значения поля у него на входе поиска не используются, он возмёт вместо обычного поля учечённое по значениям виртуальное. Таким образом, не надо будет дублировать выражение индекса по всему коду, и, если выражение после этого поменяется, не надо будет менять в 20 местах, можно будет только поменять индекс и виртуальную колонку.
    28 окт 13, 12:39    [15040581]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    MasterZiv
    Member

    Откуда: Питер
    Сообщений: 34630
    SY
    Кстати:

    TABLE ACCESS BY INDEX ROWID BATCHED

    Новая примочка в 12C: если понял правильно, проверяется наличие блоков для ROWID из INDEX RANGE SCAN в buffer cache и делается prefetch отсутствующих.

    SY.


    Скорее, это т.н. векторная обработка -- накопление ключей/ROWID в буфере, сортировка его в порядке следования соотв. индекса и затем выборка по всем ключам вектора стразу. Таким образом экономится проход вниз по дереву, не всегда, но часто.
    28 окт 13, 12:45    [15040613]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    Elic
    Member

    Откуда:
    Сообщений: 29976
    dbms_photoshop
    Elic
    Не томи, намекни.
    13909817
    Ясно. Но я не нашёл в доке намёка, что так создаётся именно колонка. Более того, rename "портит" содержимое *_stat_extensions.
    28 окт 13, 13:20    [15040834]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    dbms_photoshop
    Member

    Откуда: sqlmdx.net
    Сообщений: 5151
    Elic
    Более того, rename "портит" содержимое *_stat_extensions.
    Ну это из-за бестолковых decode(substr(c.name в определении вьюхи.
    В реальной практике, подобные переименования никаких неожиданных последствий не порождали.
    28 окт 13, 13:41    [15041012]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    Elic
    Member

    Откуда:
    Сообщений: 29976
    dbms_photoshop
    В реальной практике
    Я правильно понимаю, что это работает в production?
    28 окт 13, 13:46    [15041050]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    dbms_photoshop
    Member

    Откуда: sqlmdx.net
    Сообщений: 5151
    Elic,

    Да.
    28 окт 13, 14:01    [15041162]     Ответить | Цитировать Сообщить модератору
     Re: использование FBI  [new]
    MasterZiv
    Member

    Откуда: Питер
    Сообщений: 34630
    Прикольно.

    DROP TABLE ZOP;
    
    CREATE TABLE ZOP
     (	ZOP_ID NUMBER(15,0), 
        AORDER_ID NUMBER(15,0), 
        ZOPDATE DATE DEFAULT SYSDATE, 
        ZOPSTATE_ID NUMBER(15,0), 
        ZOPSTATE_THIN NUMBER(15,0) GENERATED ALWAYS AS (CASE ZOPSTATE_ID WHEN 300 THEN NULL WHEN 400 THEN NULL WHEN 500 THEN NULL ELSE ZOPSTATE_ID END),
        constraint PK_ZOP PRIMARY KEY (ZOP_ID)
     );
    /
    
    create or replace 
    function ZOP_OWNER ( pi_id IN ZOP.aorder_id%type ) return MERCHANT.merchant_id%type deterministic
    as 
      v_merchant_id MERCHANT.merchant_id%type;
    begin
      select ao.merchant_id 
      into v_merchant_id
      from AORDER ao
      where ao.aorder_id = pi_id;
      
      RETURN v_merchant_id;
    end;
    /
     
    CREATE INDEX IDX_ZOP_OWNER_DATE ON ZOP (ZOP_OWNER(AORDER_ID), ZOPDATE);
    
    CREATE INDEX IDX_ZOP_ZOPSTATE_THIN_DT ON ZOP (ZOPSTATE_THIN, ZOPDATE);
    
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (1,1,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (2,2,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (3,3,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (4,4,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (5,5,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (6,6,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (7,7,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (8,8,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (9,9,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (10,10,sysdate,100);
    
    commit;
    
    select zop.*,ZOP_OWNER(AORDER_ID) as owner from ZOP zop;
    


    Так работает.

    А так

    DROP TABLE ZOP;
    
    CREATE TABLE ZOP
     (	ZOP_ID NUMBER(15,0), 
        AORDER_ID NUMBER(15,0), 
        ZOPDATE DATE DEFAULT SYSDATE, 
        ZOPSTATE_ID NUMBER(15,0), 
        ZOPSTATE_THIN NUMBER(15,0) GENERATED ALWAYS AS (CASE ZOPSTATE_ID WHEN 300 THEN NULL WHEN 400 THEN NULL WHEN 500 THEN NULL ELSE ZOPSTATE_ID END),
        constraint PK_ZOP PRIMARY KEY (ZOP_ID)
     );
    /
    
    create or replace 
    function ZOP_OWNER ( pi_zop_id IN ZOP.zop_id%type ) return MERCHANT.merchant_id%type deterministic
    as 
      v_merchant_id MERCHANT.merchant_id%type;
    begin
      select ao.merchant_id 
      into v_merchant_id
      from ZOP zop
      join AORDER ao on ao.aorder_id = zop.aorder_id
      where zop.zop_id = pi_zop_id;
      
      RETURN v_merchant_id;
    end;
    /
     
    CREATE INDEX IDX_ZOP_OWNER_DATE ON ZOP (ZOP_OWNER(ZOP_ID), ZOPDATE);
    
    CREATE INDEX IDX_ZOP_ZOPSTATE_THIN_DT ON ZOP (ZOPSTATE_THIN, ZOPDATE);
    
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (1,1,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (2,2,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (3,3,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (4,4,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (5,5,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (6,6,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (7,7,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (8,8,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (9,9,sysdate,100);
    insert into ZOP (zop_id, aorder_id, ZOPDATE, zopstate_id ) values (10,10,sysdate,100);
    
    commit;
    


    уже нет...

    ORA-04091 table is mutating, trigger/function may not see it

    и всё тут.
    31 окт 13, 22:16    [15061513]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Oracle Ответить