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

Откуда: СПБ
Сообщений: 154
Добрый день,
никак не могу заставить использовать конкатенацию или inlinst iterator для хорошо селективного запроса, но с индексным доступом SKIP SCAN.
Как форсировать SKIP SCAN и конкатенацию хинтом?

Версия:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Тестовый пример:
+
drop table tmp_tst purge;
create table tmp_tst as 
select level as id, '****' as pad, trunc(level/5000) as pid
from dual
connect by level < 10000
;

create index i_tmp_tst on tmp_tst(pid, id);

begin
dbms_stats.gather_table_stats(ownname => user, tabname => 'tmp_tst');
end;


Запрос по 1 значения - skip scan:
explain plan for
select * from tmp_tst where id = :a;
SELECT * FROM table(DBMS_XPLAN.DISPLAY); 

Plan hash value: 285570937
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |     1 |    12 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TMP_TST   |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | I_TMP_TST |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ID"=TO_NUMBER(:A))
       filter("ID"=TO_NUMBER(:A))
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold



По 2 значениям - full scan:
explain plan for
select * from tmp_tst where id IN( :1 , :2 );
SELECT * FROM table(DBMS_XPLAN.DISPLAY); 

Plan hash value: 4175485756
 
-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |     2 |    24 |    10  (10)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| TMP_TST |     2 |    24 |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("ID"=TO_NUMBER(:1) OR "ID"=TO_NUMBER(:2))
       filter("ID"=TO_NUMBER(:1) OR "ID"=TO_NUMBER(:2))
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


С хинтом USE_CONCAT тоже самое:
explain plan for
select /*+ USE_CONCAT */ * from tmp_tst where id IN( :1 , :2 );
SELECT * FROM table(DBMS_XPLAN.DISPLAY); 

Plan hash value: 4175485756
 
-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |     2 |    24 |    10  (10)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| TMP_TST |     2 |    24 |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("ID"=TO_NUMBER(:1) OR "ID"=TO_NUMBER(:2))
       filter("ID"=TO_NUMBER(:1) OR "ID"=TO_NUMBER(:2))
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
11 окт 19, 12:15    [21992023]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 442
Похоже, сломали что-то в 10.2. В 10.1 ещё работало, но опять же надо форсить хинтом. Я лично склонен считать это багом, т.к. в случае ниже skip scan явно выгоднее и по стоимости и по производительности.

drop table t1 purge;

create table t1 as
select mod(rownum, 2) gender, rownum id, rpad(' ', 1000) pad
from dual
connect by level < 100000;

create index i1 on t1(gender, id);

begin
  dbms_stats.gather_table_stats(ownname => user, tabname => 't1', cascade => true);
end;
/

alter session set statistics_level=all;

select /*+ use_concat */ * from t1 where id in (1,2);
select * from table(dbms_xplan.display_cursor(null, null, 'iostats last cost'));

alter session set optimizer_features_enable = '10.1.0.5';
select /*+ use_concat */ * from t1 where id in (1,2);
select * from table(dbms_xplan.display_cursor(null, null, 'iostats last cost'));


------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |      1 |        |  3918 (100)|      2 |00:00:00.13 |   14290 |  14286 |
|*  1 |  TABLE ACCESS STORAGE FULL| T1   |      1 |      2 |  3918   (1)|      2 |00:00:00.13 |   14290 |  14286 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ID"=1 OR "ID"=2))


---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |     8 (100)|      2 |00:00:00.01 |   12 |         5 |
|   1 |  CONCATENATION               |      |      1 |        |            |      2 |00:00:00.01 |   12 |         5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |      1 |      1 |     4   (0)|      1 |00:00:00.01 |    7 |         5 |
|*  3 |    INDEX SKIP SCAN           | I1   |      1 |      1 |     3   (0)|      1 |00:00:00.01 |    6 |         0 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T1   |      1 |      1 |     4   (0)|      1 |00:00:00.01 |    5 |         0 |
|*  5 |    INDEX SKIP SCAN           | I1   |      1 |      1 |     3   (0)|      1 |00:00:00.01 |    4 |         0 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"=2)
       filter("ID"=2)
   5 - access("ID"=1)
       filter("ID"=1)
11 окт 19, 17:18    [21992427]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
pihel
Member

Откуда: СПБ
Сообщений: 154
Alexander Anokhin,

это уже чтото, но все равно слабо рабочий вариант из-за alter session
если прописать хинт в запросе, то concatenation все равно не срабатывает:
explain plan for
select /*+ USE_CONCAT OPTIMIZER_FEATURES_ENABLE('10.1.0.1') */ * from tmp_tst where id IN( :1 , :2 );
SELECT * FROM table(DBMS_XPLAN.DISPLAY); 

-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |     2 |    24 |    10  (10)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| TMP_TST |     2 |    24 |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
11 окт 19, 17:37    [21992451]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 442
Да, видел. Почему хинт не работает не знаю. Могу предположить, что хинт OPT_PARAM проверяется позже, чем USE_CONCAT, но это как-то сомнительно.
11 окт 19, 17:50    [21992459]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
j2k
Member

Откуда: Новосибирск
Сообщений: 548
Alexander Anokhin, ну у оптимизатора в тестовом примере стоимость конкатенации больше получается на 1, т.е. если ручками переписать запрос на
select * from tmp_tst t where id = ...
union 
select * from tmp_tst t where id = ...

то в плане будет конкатенация, но стоимость на еденичку выше, чем у фулскана (10 против 9)
14 окт 19, 19:15    [21994041]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 442
Вот такой должен хинт быть
select /*+ use_concat(OR_PREDICATES(1)) */
       *
  from t1
 where id in (1, 2);

---------------------------------------------------------------------------
| Id  | Operation                            | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |        |     8 (100)|
|   1 |  CONCATENATION                       |      |        |            |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |      1 |     4   (0)|
|*  3 |    INDEX SKIP SCAN                   | I1   |      1 |     3   (0)|
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |      1 |     4   (0)|
|*  5 |    INDEX SKIP SCAN                   | I1   |      1 |     3   (0)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"=2)
       filter("ID"=2)
   5 - access("ID"=1)
       filter("ID"=1)

Можно было и раньше догадаться, он в аутлайне был.

С 12.2 работает новый or expansion, новый хинт OR_EXPAND. Как его с этим примером подружить не нашёл.
24 окт 19, 19:32    [22002040]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
Melkomyagkii_newbi
Member

Откуда: из прошлого
Сообщений: 1737
select * from v$version;

explain plan for
select /*+ index_ss(tmp_tst) */ * from tmp_tst where id IN( :1 , :2 );
SELECT * FROM table(DBMS_XPLAN.DISPLAY); 

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE	12.2.0.1.0	Production                                                                  0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0


Explained.


PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 285570937
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |     2 |    24 |    27   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TMP_TST   |     2 |    24 |    27   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | I_TMP_TST |     2 |       |    26   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
 
   2 - filter("ID"=TO_NUMBER(:1) OR "ID"=TO_NUMBER(:2))
24 окт 19, 20:14    [22002069]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 442
Melkomyagkii_newbi, не хочу тебя расстраивать, но это Index Full Scan :-(
24 окт 19, 21:21    [22002101]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
pihel
Member

Откуда: СПБ
Сообщений: 154
Alexander Anokhin,

спасибо, на 18 тоже работает
25 окт 19, 10:33    [22002314]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
Melkomyagkii_newbi
Member

Откуда: из прошлого
Сообщений: 1737
Alexander Anokhin
Melkomyagkii_newbi, не хочу тебя расстраивать, но это Index Full Scan :-(


и правда, обман чтобы набрать классы прям какой-то(
25 окт 19, 16:33    [22002825]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9632
Alexander Anokhin
Вот такой должен хинт быть


И что ты считаешь INDEX SKIP SCAN + TABLE ACCESS BY INDEX ROWID BATCHED выполнeнный дважды (один в поисках id = 1 второй в поисках id = 2) а затем CONCATENATION подразумевающий SORT/HASH лучше:

SQL> explain plan for select /*+ index_ss(t1,i1) */ * from t1 where id in (1,2);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 319309540

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |     2 |  2018 |   259   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1   |     2 |  2018 |   259   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | I1   |     2 |       |   258   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - filter("ID"=1 OR "ID"=2)

14 rows selected.

SQL> 


SY.

Сообщение было отредактировано: 25 окт 19, 18:19
25 окт 19, 18:15    [22002950]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
Melkomyagkii_newbi
Member

Откуда: из прошлого
Сообщений: 1737
SY,

лучше. там действительно практически фулл скан получается.

drop table t1 purge;

create table t1 as
select mod(rownum, 2) gender, rownum id, rpad(' ', 1000) pad
from dual
connect by level < 1000000;

create index i1 on t1(gender, id);

begin
  dbms_stats.gather_table_stats(ownname => user, tabname => 't1', cascade => true);
end;
/

alter session set statistics_level=all;



SQL_ID  gah2j7aj82up6, child number 0
-------------------------------------
select /*+ TST1 index(t1 i1) */ * from t1 where id IN( 1 , 2 )
 
Plan hash value: 294074833
 
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |      1 |        |  2596 (100)|      2 |00:00:00.17 |    2581 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1   |      1 |      2 |  2596   (1)|      2 |00:00:00.17 |    2581 |      1 |
|*  2 |   INDEX FULL SCAN                   | I1   |      1 |      2 |  2595   (1)|      2 |00:00:00.17 |    2580 |      0 |
----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("ID"=1 OR "ID"=2))
 


SQL_ID  bf676hn089fbd, child number 0
-------------------------------------
select /*+ TST3 use_concat(OR_PREDICATES(1)) */ * from t1 where id IN( 
1 , 2 )
 
Plan hash value: 1503868165
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |    10 (100)|      2 |00:00:00.01 |      16 |
|   1 |  CONCATENATION                       |      |      1 |        |            |      2 |00:00:00.01 |      16 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |      1 |      1 |     5   (0)|      1 |00:00:00.01 |       8 |
|*  3 |    INDEX SKIP SCAN                   | I1   |      1 |      1 |     4   (0)|      1 |00:00:00.01 |       7 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |      1 |      1 |     5   (0)|      1 |00:00:00.01 |       8 |
|*  5 |    INDEX SKIP SCAN                   | I1   |      1 |      1 |     4   (0)|      1 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("ID"=2)
       filter("ID"=2)
   5 - access("ID"=1)
       filter("ID"=1)


SQL_ID  bn11srsqjnc0u, child number 0
-------------------------------------
select /*+ TST2 index_ss(t1 i1) */ * from t1 where id IN( 1 , 2 )
 
Plan hash value: 319309540
 
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |      1 |        |  2582 (100)|      2 |00:00:00.43 |    2585 |      4 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1   |      1 |      2 |  2582   (1)|      2 |00:00:00.43 |    2585 |      4 |
|*  2 |   INDEX SKIP SCAN                   | I1   |      1 |      2 |  2581   (1)|      2 |00:00:00.43 |    2584 |      4 |
----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("ID"=1 OR "ID"=2))
 
25 окт 19, 18:37    [22002969]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 442
Ну да, считаю. Этот Index Full Scan предполагает изменить моё мнение как-то ?
25 окт 19, 18:38    [22002970]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 442
А, ну ок.
25 окт 19, 18:39    [22002971]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9632
Alexander Anokhin
Ну да, считаю. Этот Index Full Scan предполагает изменить моё мнение как-то ?


И где ты видишь INDEX FULL SCAN???

SQL_ID  bn11srsqjnc0u, child number 0
-------------------------------------
select /*+ TST2 index_ss(t1 i1) */ * from t1 where id IN( 1 , 2 )
 
Plan hash value: 319309540
 
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |      1 |        |  2582 (100)|      2 |00:00:00.43 |    2585 |      4 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1   |      1 |      2 |  2582   (1)|      2 |00:00:00.43 |    2585 |      4 |
|*  2 |   INDEX SKIP SCAN                   | I1   |      1 |      2 |  2581   (1)|      2 |00:00:00.43 |    2584 |      4 |
----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("ID"=1 OR "ID"=2))
 


SY.
25 окт 19, 19:10    [22002986]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 442
Вот всё возьми да расскажи! Тут тебе надо подумать.
25 окт 19, 19:17    [22002991]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
-2-
Member

Откуда:
Сообщений: 14992
SY
И где ты видишь INDEX FULL SCAN???
Видишь access? Что скипает такой skip scan??
25 окт 19, 19:41    [22003007]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9632
-2-
SY
И где ты видишь INDEX FULL SCAN???
Видишь access? Что скипает такой skip scan??


Теперь вижу.

SY.
25 окт 19, 20:33    [22003032]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
xtender
Member

Откуда: Мск
Сообщений: 5249
Alexander Anokhin
С 12.2 работает новый or expansion, новый хинт OR_EXPAND. Как его с этим примером подружить не нашёл.
хз что там за правила проверки - при нормальном id in (1,2) не работает (ORE: bypassed - No valid predicate for OR expansion.), но вот дурацкое добавление исправляет ситуацию:
SQL> explain plan for select * from t1 where id=1 or id=2 and id!=1;
SQL> @xplan +outline

P_FORMAT
------------------------
typical +outline

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1477483978

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |     2 |  1056 |     8   (0)| 00:00:01 |
|   1 |  VIEW                                 | VW_ORE_BA8ECEFB |     2 |  1056 |     8   (0)| 00:00:01 |
|   2 |   UNION-ALL                           |                 |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 |  1009 |     4   (0)| 00:00:01 |
|*  4 |     INDEX SKIP SCAN                   | I1              |     1 |       |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 |  1009 |     4   (0)| 00:00:01 |
|*  6 |     INDEX SKIP SCAN                   | I1              |     1 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$9162BF3C_2" "T1"@"SET$9162BF3C_2")
      INDEX_SS(@"SET$9162BF3C_2" "T1"@"SET$9162BF3C_2" ("T1"."GENDER" "T1"."ID"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$9162BF3C_1" "T1"@"SET$9162BF3C_1")
      INDEX_SS(@"SET$9162BF3C_1" "T1"@"SET$9162BF3C_1" ("T1"."GENDER" "T1"."ID"))
      NO_ACCESS(@"SEL$BA8ECEFB" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB")
      OUTLINE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$BA8ECEFB")
      OR_EXPAND(@"SEL$1" (1) (2))
      OUTLINE_LEAF(@"SET$9162BF3C")
      OUTLINE_LEAF(@"SET$9162BF3C_1")
      OUTLINE_LEAF(@"SET$9162BF3C_2")
      ALL_ROWS
      DB_VERSION('18.1.0')
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   4 - access("ID"=1)
       filter("ID"=1)
   6 - access("ID"=2)
       filter("ID"=2 AND LNNVL("ID"=1))


только не смог придумать как такое же провернуть с IN
26 окт 19, 03:03    [22003095]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 442
xtender
Alexander Anokhin
С 12.2 работает новый or expansion, новый хинт OR_EXPAND. Как его с этим примером подружить не нашёл.
хз что там за правила проверки - при нормальном id in (1,2) не работает (ORE: bypassed - No valid predicate for OR expansion.), но вот дурацкое добавление исправляет ситуацию:


Класс, теперь понятнее. Такая проверка нужна для переменных (bind или в случае соединений), когда значения зараннее не известны, чтобы одни и те же строки несколько раз не вытащить. И старый добрый or expansion (concatenation) умеет добавлять эти дополнительные условия, а для нового этот шаг преобразований забыли добавить, похоже. В результате для нового or expansion валидным считается только такой "or" где непересечение значений явно прописано и для литералов и для переменных.

var b1 number
var b2 number
var b3 number

select /*+ use_concat(OR_PREDICATES(1)) */ * from t1 where id in (:B1,:B2, :B3);

---------------------------------------------------------------------------
| Id  | Operation                            | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |        |     9 (100)|
|   1 |  CONCATENATION                       |      |        |            |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |      1 |     3   (0)|
|*  3 |    INDEX SKIP SCAN                   | I1   |      1 |     3   (0)|
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |      1 |     3   (0)|
|*  5 |    INDEX SKIP SCAN                   | I1   |      1 |     3   (0)|
|   6 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |      1 |     3   (0)|
|*  7 |    INDEX SKIP SCAN                   | I1   |      1 |     3   (0)|
---------------------------------------------------------------------------

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

   3 - access("ID"=:B3)
       filter("ID"=:B3)
   5 - access("ID"=:B2)
       filter(("ID"=:B2 AND LNNVL("ID"=:B3)))
   7 - access("ID"=:B1)
       filter(("ID"=:B1 AND LNNVL("ID"=:B2) AND LNNVL("ID"=:B3)))


select * from t1 where id in (:B1,:B2, :B3);

----------------------------------------------------------------
| Id  | Operation                 | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |        |  2324 (100)|
|*  1 |  TABLE ACCESS STORAGE FULL| T1   |      1 |  2324   (1)|
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - storage(("ID"=:B1 OR "ID"=:B2 OR "ID"=:B3))
       filter(("ID"=:B1 OR "ID"=:B2 OR "ID"=:B3))


select * from t1 where (id = :B1 and :B1 <> :B2 and :B1 <> :B3) or (id = :B2 and :B2 <> :B3) or (id = :B3);

----------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |        |     9 (100)|
|   1 |  VIEW                                  | VW_ORE_BA8ECEFB |      3 |     9   (0)|
|   2 |   UNION-ALL                            |                 |        |            |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | T1              |      1 |     3   (0)|
|*  4 |     INDEX SKIP SCAN                    | I1              |      1 |     3   (0)|
|*  5 |    FILTER                              |                 |        |            |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1              |      1 |     3   (0)|
|*  7 |      INDEX SKIP SCAN                   | I1              |      1 |     3   (0)|
|*  8 |    FILTER                              |                 |        |            |
|   9 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1              |      1 |     3   (0)|
|* 10 |      INDEX SKIP SCAN                   | I1              |      1 |     3   (0)|
----------------------------------------------------------------------------------------

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

   4 - access("ID"=:B3)
       filter("ID"=:B3)
   5 - filter(:B2<>:B3)
   7 - access("ID"=:B2)
       filter(("ID"=:B2 AND LNNVL("ID"=:B3)))
   8 - filter((:B1<>:B3 AND :B1<>:B2))
  10 - access("ID"=:B1)
       filter(("ID"=:B1 AND LNNVL("ID"=:B3) AND (LNNVL("ID"=:B2) OR
              LNNVL(:B2<>:B3))))
26 окт 19, 15:31    [22003207]     Ответить | Цитировать Сообщить модератору
 Re: INDEX SKIP SCAN и Concatenation  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 442
SY
... а затем CONCATENATION подразумевающий SORT/HASH лучше:

SY.

Нет там SORT/HASH. Шаг CONCATENATION ничего не делает, как UNION ALL, строки собирает снизу просто.
26 окт 19, 15:36    [22003208]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить