Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 index skip scan  [new]
Alex98rus
Member

Откуда:
Сообщений: 6
Всем добрый день!

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.5.0


Не понимаю почему оптимизатор не выбирает skip scan индекса, если в условии (in) более 1го значения. Вот тестовый пример:
create table test as 
select case when rownum < 29000 then 'M' else 'F' end GENDER, rownum R, rpad('*',75,'*') data
from DUAL
connect by level <= 30000;

create index IDX_TEST on test (GENDER, R);
exec dbms_stats.gather_table_stats(ownname => user, tabname =>'TEST', cascade=>true);

Запросы с результатами:
1:
SQL> set autotrace traceonly;
SQL> select * from test T where R = 4700;


Execution Plan
----------------------------------------------------------
Plan hash value: 977265687

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    82 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    82 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_TEST |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("R"=4700)
       filter("R"=4700)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        712  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
2:
SQL> select * from test T where R in (4700, 4800);


Execution Plan
----------------------------------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |   164 |   102   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |     2 |   164 |   102   (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"=4700 OR "R"=4800)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        370  consistent gets
          0  physical reads
          0  redo size
        844  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
3:
SQL> select /*+ index_SS (t idx_test) */ * from test T where R in (4700, 4800);


Execution Plan
----------------------------------------------------------
Plan hash value: 977265687

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     2 |   164 |    89   (2)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     2 |   164 |    89   (2)| 00:00:02 |
|*  2 |   INDEX SKIP SCAN           | IDX_TEST | 30000 |       |    15   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("R"=4700 OR "R"=4800)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         79  consistent gets
          0  physical reads
          0  redo size
        844  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

Получается, что index skip scan имеет не только меньше consistent gets, но и меньший cost. Тем не менее, выбирается full table scan.. Почему?
21 ноя 11, 14:09    [11631849]     Ответить | Цитировать Сообщить модератору
 Re: index skip scan  [new]
kinky cat
Member

Откуда: с дивана23
Сообщений: 1235
Alex98rus,

потому что у CBO обшибся в оценке селективности R in (4700, 4800)
посчтитайте более точные гистограммы
см пар-р METHOD_OPT в DBMS_STATS.GATHER_TABLE_STATS
21 ноя 11, 14:32    [11632102]     Ответить | Цитировать Сообщить модератору
 Re: index skip scan  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Судя по filter("R"=4700 OR "R"=4800) и большому LIO - это больше похоже на index full scan. А вот настоящий skip scan будет вот так:
SQL> select /*+ index_SS (t idx_test) */ * from test T where R in (select column_value from table(sys.OdciNumberList(4700, 4800)));


Execution Plan
----------------------------------------------------------
Plan hash value: 866108696

---------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |          |     1 |    85 |   479   (1)| 04:53:30 |
|*  1 |  HASH JOIN RIGHT SEMI                  |          |     1 |    85 |   479   (1)| 04:53:30 |
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|          |  8168 | 16336 |    37   (0)| 00:22:42 |
|   3 |   TABLE ACCESS BY INDEX ROWID          | TEST     | 30000 |  2431K|   441   (0)| 04:30:30 |
|   4 |    INDEX SKIP SCAN                     | IDX_TEST | 30000 |       |    75   (0)| 00:46:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("R"=VALUE(KOKBF$))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        727  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed
21 ноя 11, 14:55    [11632318]     Ответить | Цитировать Сообщить модератору
 Re: index skip scan  [new]
Alex98rus
Member

Откуда:
Сообщений: 6
kinky cat,

Если честно, не хотелось бы собирать гистограммы, да и сдаётся мне что всё же дело не в них...

Тем не менее, если попробовать:
exec dbms_stats.gather_table_stats(ownname => user, tabname =>'TEST', cascade=>true, METHOD_OPT => 'FOR ALL COLUMNS SIZE 254');
То результаты будут теми же.
21 ноя 11, 15:01    [11632353]     Ответить | Цитировать Сообщить модератору
 Re: index skip scan  [new]
Alex98rus
Member

Откуда:
Сообщений: 6
wurdu,
согласен по поводу full scan'a, но это особо не меняет суть вопроса. почему оптимизатор в упор перестает использовать индекс (range, full, skip - без разницы) когда я просто добавляю более одного условия?

интересная интерпретация запроса с sys.OdciNumberList, на днях обязательно почитаю что это за тип такой :))
21 ноя 11, 15:23    [11632533]     Ответить | Цитировать Сообщить модератору
 Re: index skip scan  [new]
A.
Guest
Wurdu, так это у тебя index full scan.
21 ноя 11, 15:23    [11632534]     Ответить | Цитировать Сообщить модератору
 Re: index skip scan  [new]
A.
Guest
Wurdu, проверь реальный план возможно там был NL и "настоящий" ISS
21 ноя 11, 15:27    [11632557]     Ответить | Цитировать Сообщить модератору
 Re: index skip scan  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
A.
Wurdu, проверь реальный план возможно там был NL и "настоящий" ISS
Согласен. За счет cardinality feedback он собственно и стал, как я рассчитывал.
---------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |          |     1 |    85 |   479   (1)| 04:53:30 |
|*  1 |  HASH JOIN RIGHT SEMI                  |          |     1 |    85 |   479   (1)| 04:53:30 |
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|          |  8168 | 16336 |    37   (0)| 00:22:42 |
|   3 |   TABLE ACCESS BY INDEX ROWID          | TEST     | 30000 |  2431K|   441   (0)| 04:30:30 |
|   4 |    INDEX SKIP SCAN                     | IDX_TEST | 30000 |       |    75   (0)| 00:46:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("R"=VALUE(KOKBF$))
21 ноя 11, 15:45    [11632713]     Ответить | Цитировать Сообщить модератору
 Re: index skip scan  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Опять ерунду написал. Вот собственно реальный план.
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |          |       |       |    42 (100)|          |
|   1 |  NESTED LOOPS                           |          |     2 |   170 |    42   (3)| 00:25:46 |
|   2 |   SORT UNIQUE                           |          |     2 |     4 |    37   (0)| 00:22:42 |
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|          |     2 |     4 |    37   (0)| 00:22:42 |
|   4 |   TABLE ACCESS BY INDEX ROWID           | TEST     |     1 |    83 |     4   (0)| 00:02:28 |
|*  5 |    INDEX SKIP SCAN                      | IDX_TEST |     1 |       |     3   (0)| 00:01:51 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("R"=VALUE(KOKBF$))
21 ноя 11, 15:57    [11632830]     Ответить | Цитировать Сообщить модератору
 Re: index skip scan  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Alex98rus
wurdu,
согласен по поводу full scan'a, но это особо не меняет суть вопроса. почему оптимизатор в упор перестает использовать индекс (range, full, skip - без разницы) когда я просто добавляю более одного условия?

интересная интерпретация запроса с sys.OdciNumberList, на днях обязательно почитаю что это за тип такой :))
У меня под рукой только 11.2.0.2 и, судя по всему, inlist iterator + index skip scan он делать не умеет. Поэтому я и использовал табличную функцию для nested loop.
21 ноя 11, 16:16    [11632985]     Ответить | Цитировать Сообщить модератору
 Re: index skip scan  [new]
Timur Akhmadeev
Member

Откуда:
Сообщений: 509
Alex98rus
Тем не менее, выбирается full table scan.. Почему?

Это недоработка CBO. В случае in-list он вообще не рассматривает skip scan.
21 ноя 11, 16:17    [11632993]     Ответить | Цитировать Сообщить модератору
 Re: index skip scan  [new]
Flok
Member

Откуда:
Сообщений: 258
explain plan set statement_id='SKIP_2' for select * from test T where GENDER in ('M', 'F') and R in ( 4700, 4800);
select * from table(dbms_xplan.display('PLAN_TABLE', 'SKIP_2', 'ALL'))

1	Plan hash value: 259634226
2	 
3	-----------------------------------------------------------------------------------------
4	| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
5	-----------------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT             |          |     2 |   170 |     1   (0)| 00:00:01 |
7	|   1 |  INLIST ITERATOR             |          |       |       |            |          |
8	|   2 |   TABLE ACCESS BY INDEX ROWID| TEST     |     2 |   170 |     1   (0)| 00:00:01 |
9	|*  3 |    INDEX RANGE SCAN          | IDX_TEST |     2 |       |     1   (0)| 00:00:01 |
10	-----------------------------------------------------------------------------------------
11	 
12	Query Block Name / Object Alias (identified by operation id):
13	-------------------------------------------------------------
14	 
15	   1 - SEL$1
16	   2 - SEL$1 / T@SEL$1
17	   3 - SEL$1 / T@SEL$1
18	 
19	Predicate Information (identified by operation id):
20	---------------------------------------------------
21	 
22	   3 - access(("GENDER"='F' OR "GENDER"='M') AND ("R"=4700 OR "R"=4800))
23	 
24	Column Projection Information (identified by operation id):
25	-----------------------------------------------------------
26	 
27	   1 - "GENDER"[CHARACTER,4], "R"[NUMBER,22], "T"."DATA"[VARCHAR2,300]
28	   2 - "GENDER"[CHARACTER,4], "R"[NUMBER,22], "T"."DATA"[VARCHAR2,300]
29	   3 - "T".ROWID[ROWID,10], "GENDER"[CHARACTER,4], "R"[NUMBER,22]


explain plan set statement_id='FULL' for select * from test T where R in (4700, 4800);
select * from table(dbms_xplan.display('PLAN_TABLE', 'FULL', 'ALL'))

1	Plan hash value: 1357081020
2	 
3	--------------------------------------------------------------------------
4	| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
5	--------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT  |      |     2 |   170 |    76   (2)| 00:00:01 |
7	|*  1 |  TABLE ACCESS FULL| TEST |     2 |   170 |    76   (2)| 00:00:01 |
8	--------------------------------------------------------------------------
9	 
10	Query Block Name / Object Alias (identified by operation id):
11	-------------------------------------------------------------
12	 
13	   1 - SEL$1 / T@SEL$1
14	 
15	Predicate Information (identified by operation id):
16	---------------------------------------------------
17	 
18	   1 - filter("R"=4700 OR "R"=4800)
19	 
20	Column Projection Information (identified by operation id):
21	-----------------------------------------------------------
22	 
23	   1 - "T"."GENDER"[CHARACTER,4], "R"[NUMBER,22], 
24	       "T"."DATA"[VARCHAR2,300]
21 ноя 11, 16:26    [11633074]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить