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

Откуда:
Сообщений: 17
Доброе время суток!

Есть партиционированная таблица с локальным непрефиксным индексом:
CREATE TABLE TBL1
(
  PART_ID                NUMBER NOT NULL,
  DATETIME               DATE NOT NULL
)
PARTITION BY LIST (PART_ID)
(  
  PARTITION X1000 VALUES (1000),  
  PARTITION X1001 VALUES (1001),  
  PARTITION X1002 VALUES (1002),  
  PARTITION X1009 VALUES (1009),  
  PARTITION XDEFAULT VALUES (DEFAULT)
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

CREATE INDEX IDX_21 ON TBL1 (DATETIME) LOCAL;


Статистика по партиции 1002 посчитана:
      DBMS_STATS.GATHER_TABLE_STATS(
        OwnName          => user,
        TabName          => 'tbl1',
        PartName         => 'X1002',
        Granularity      => 'PARTITION',
        Estimate_Percent => 5,
        Cascade          => TRUE);


В данной партиции более двух миллионов строк и более 40 тысяч различных значений datetime.

Почему при выполнении запроса:
select max(datetime)
from tbl1
where part_id = 1002

не используется индекс IDX_21?

Вот план запроса:
Plan
SELECT STATEMENT  ALL_ROWSCost: 3,612  Bytes: 12              
    3 SORT AGGREGATE  Bytes: 12          
        2 PARTITION LIST SINGLE  Cost: 3,612  Bytes: 26,997,480  Partition #: 2  Partitions determined by Key Values    
            1 TABLE ACCESS FULL TABLE TBL1 Cost: 3,612  Bytes: 26,997,480  Partition #: 3  Partitions accessed #3


Казалось бы гораздо проще всего просмотреть одну ветку партиции индекса, но оптимизатор говорит, что проще пробежаться по всей партиции таблицы.

Если указать условие, на конкретное время:
select *
from tbl1 A
where part_id = 1002
  and datetime = :df


то судя по плану запроса просматривается конкретная партиция индекса, что верно:
Plan
SELECT STATEMENT  ALL_ROWSCost: 4  Bytes: 1,265  			
	3 PARTITION LIST SINGLE  Cost: 4  Bytes: 1,265  Partition #: 1  Partitions determined by Key Values		
		2 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED TABLE TBL1 Cost: 4  Bytes: 1,265  Partition #: 2  Partitions accessed #3	
			1 INDEX RANGE SCAN INDEX KSMFR_SC_DEVS.IDX_21 Access Predicates: "DATETIME"=:DF  Cost: 3  Partition #: 3  Partitions accessed #3


Я попробовал создать префиксный индекс, и тогда он уже подхватывается при вычислении max(tradetime).
Но почему не подхватывается НЕпрефиксный?
Я раньше не замечал подобного.
4 фев 19, 21:49    [21801593]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
Fogel
Member

Откуда:
Сообщений: 343
ха-хаха
сколько умных слов и разбора, а толку...

Потому что отбор идёт по полю, где нет индекса:
where part_id = 1002
4 фев 19, 23:40    [21801616]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
Андрей_7777
Member

Откуда:
Сообщений: 17
Fogel,

Индекс локально партиционированный, поэтому условием part_id = 1002 однозначно определяется требуемая партиция индекса, а дальше всего-лишь нужно пройтись по одной ветке B-дерева этой партиции, чтобы найти максимальное значение.

Ps: почитайте про партиционированные индексы, например, в книге Тома Кайта.
5 фев 19, 00:09    [21801622]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
кит северных морей
Member

Откуда: Красноярск
Сообщений: 589
Fogel
ха-хаха
сколько умных слов и разбора, а толку...

Потому что отбор идёт по полю, где нет индекса:
where part_id = 1002
и что? нужная секция известна, индекс локальный, то есть секция индекса тоже известна. максимум берется от индексированного поля. желание прочитать одну ветку секции индекса вместо секции таблицы вполне понятно.
5 фев 19, 00:13    [21801623]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
Андрей_7777
Но почему не подхватывается НЕпрефиксный?
Я раньше не замечал подобного.


explain plan for
select /*+ gather_plan_statistics  index(t)*/ max(datetime)
from tbl1 t
where part_id = 1002
;

Statement processed.

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

PLAN_TABLE_OUTPUT
Plan hash value: 519586747
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    22 |  3235   (1)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE              |        |     1 |    22 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE      |        |   102K|  2209K|  3235   (1)| 00:00:01 |   KEY |   KEY |
|   3 |    FIRST ROW                 |        |   102K|  2209K|  3235   (1)| 00:00:01 |       |       |
|   4 |     INDEX FULL SCAN (MIN/MAX)| IDX_21 |   102K|  2209K|  3235   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Download CSV
15 rows selected.


explain plan for
select /*+ gather_plan_statistics  full(t)*/ max(datetime)
from tbl1 t
where part_id = 1002
;

Statement processed.

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

PLAN_TABLE_OUTPUT
Plan hash value: 3517912980
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    22 |   124   (4)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE             |      |     1 |    22 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE     |      |   102K|  2209K|   124   (4)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS STORAGE FULL| TBL1 |   102K|  2209K|   124   (4)| 00:00:01 |     3 |     3 |
----------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Download CSV
14 rows selected.

explain plan for
select /*+ gather_plan_statistics */ max(datetime)
from tbl1 partition for(1002) t
;

Statement processed.

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

PLAN_TABLE_OUTPUT
Plan hash value: 2385088529
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE             |        |     1 |     9 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE     |        |     1 |     9 |     1   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    INDEX FULL SCAN (MIN/MAX)| IDX_21 |     1 |     9 |     1   (0)| 00:00:01 |     3 |     3 |
------------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Download CSV
14 rows selected.
5 фев 19, 01:12    [21801631]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
Ах да,
explain plan for
select /*+ gather_plan_statistics */ max(datetime)
from tbl1 partition for(1002) t
where part_id = 1002
;

Statement processed.

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

PLAN_TABLE_OUTPUT
Plan hash value: 3517912980
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    22 |   124   (4)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE             |      |     1 |    22 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE     |      |   102K|  2209K|   124   (4)| 00:00:01 |KEY(AP)|KEY(AP)|
|   3 |    TABLE ACCESS STORAGE FULL| TBL1 |   102K|  2209K|   124   (4)| 00:00:01 |     3 |     3 |
----------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Download CSV
14 rows selected.


Собственно, обратите внимание на projection (livesql что-то бастует, сделайте самостоятельно).
С LIST я не особо возился, но для RANGE и HASH помимо pruning безусловно потребовался бы фильтр на part_id = 1002.
Видимо, даже с LIST SINGLE действует общее правило - pruning не отменяет filtering.
5 фев 19, 01:20    [21801632]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
andrey_anonymous
Видимо, даже с LIST SINGLE действует общее правило - pruning не отменяет filtering.

Хотя по-хорошему это, конечно, баг оптимизатора.
Посмотрите на статистики сессии при выполнении
select /*+ index(t)*/ max(datetime)
from tbl1 t
where part_id = 1002

и обнаружите, что оптимизатор сильно перезаложился, выдав из-под "first row" все 102K :)
5 фев 19, 01:37    [21801633]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 5066
Андрей_7777,

нужно хинтануть:
select/*+ index(tbl1) */ max(datetime) from tbl1 where part_id = 1002


оптимизатор тут что-то глючит нехило: один из примеров, когда CBO выбирает план с более высоким Cost
+ 11.2.0.2
SQL> explain plan for select max(datetime) from tbl1 where part_id = 1002;

Explained.

SQL> @xplan advanced


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3517912980

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    22 |     2   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |    22 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |     1 |    22 |     2   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | TBL1 |     1 |    22 |     2   (0)| 00:00:01 |     3 |     3 |
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / TBL1@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "TBL1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.2')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) MAX("DATETIME")[7]
   2 - "DATETIME"[DATE,7]
   3 - "DATETIME"[DATE,7]
+ 11.2.0.2 hinted
SQL> explain plan for select/*+ index(tbl1) */ max(datetime) from tbl1 where part_id = 1002;

Explained.

SQL> @xplan advanced


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 519586747

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    22 |     0   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE              |        |     1 |    22 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE      |        |     1 |    22 |     0   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    FIRST ROW                 |        |     1 |    22 |     0   (0)| 00:00:01 |       |       |
|   4 |     INDEX FULL SCAN (MIN/MAX)| IDX_21 |     1 |    22 |     0   (0)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$1 / TBL1@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "TBL1"@"SEL$1" ("TBL1"."DATETIME"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.2')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) MAX("DATETIME")[7]
   2 - "DATETIME"[DATE,7]
   3 - "DATETIME"[DATE,7]
   4 - "DATETIME"[DATE,7]
+ 12.2.0.1
SQL> explain plan for select max(datetime) from tbl1 where part_id = 1002;

Explained.

SQL> @xplan advanced


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3517912980

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    22 |     2   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE             |      |     1 |    22 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE     |      |     1 |    22 |     2   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS STORAGE FULL| TBL1 |     1 |    22 |     2   (0)| 00:00:01 |     3 |     3 |
----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / TBL1@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "TBL1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPT_PARAM('_fix_control' '9550277:1')
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) MAX("DATETIME")[7]
   2 - (rowset=256) "DATETIME"[DATE,7]
   3 - (rowset=256) "DATETIME"[DATE,7]
+ 12.2.0.1 hinted
SQL> explain plan for select/*+ index(tbl1) */ max(datetime) from tbl1 where part_id = 1002;

Explained.

SQL> @xplan advanced


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 519586747

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    22 |     0   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE              |        |     1 |    22 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE      |        |     1 |    22 |     0   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    FIRST ROW                 |        |     1 |    22 |     0   (0)| 00:00:01 |       |       |
|   4 |     INDEX FULL SCAN (MIN/MAX)| IDX_21 |     1 |    22 |     0   (0)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$1 / TBL1@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "TBL1"@"SEL$1" ("TBL1"."DATETIME"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPT_PARAM('_fix_control' '9550277:1')
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) MAX("DATETIME")[7]
   2 - "DATETIME"[DATE,7]
   3 - "DATETIME"[DATE,7]
   4 - "DATETIME"[DATE,7]
+ 18.4
SQL> explain plan for select max(datetime) from tbl1 where part_id = 1002;

Explained.

SQL> @xplan advanced


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3517912980

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    22 |     2   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |    22 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |     1 |    22 |     2   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | TBL1 |     1 |    22 |     2   (0)| 00:00:01 |     3 |     3 |
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / TBL1@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "TBL1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('18.1.0')
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) MAX("DATETIME")[7]
   2 - (rowset=256) "DATETIME"[DATE,7]
   3 - (rowset=256) "DATETIME"[DATE,7]
+ 18.4 hinted
SQL> explain plan for select/*+ index(tbl1) */ max(datetime) from tbl1 where part_id = 1002;

Explained.

SQL> @xplan advanced

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 519586747

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    22 |     1   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE              |        |     1 |    22 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE      |        |     1 |    22 |     1   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    FIRST ROW                 |        |     1 |    22 |     1   (0)| 00:00:01 |       |       |
|   4 |     INDEX FULL SCAN (MIN/MAX)| IDX_21 |     1 |    22 |     1   (0)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$1 / TBL1@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "TBL1"@"SEL$1" ("TBL1"."DATETIME"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('18.1.0')
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) MAX("DATETIME")[7]
   2 - "DATETIME"[DATE,7]
   3 - "DATETIME"[DATE,7]
   4 - "DATETIME"[DATE,7]
5 фев 19, 01:42    [21801634]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
xtender
оптимизатор тут что-то глючит нехило: один из примеров, когда CBO выбирает план с более высоким Cost
|   3 |    TABLE ACCESS FULL   | TBL1 |     1 |    22 |     2   (0)| 00:00:01 |     3 |     3 |

Не маловато данных?
5 фев 19, 02:02    [21801635]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 5066
andrey_anonymous,

а зачем данные, чтобы увидеть, что выбран план с более высокой стоимостью?
5 фев 19, 02:05    [21801636]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
xtender
а зачем данные, чтобы увидеть, что выбран план с более высокой стоимостью?

Нууу... Оценка мощности FTS в одну строку как-то не навевает мысли о высокой стоимости... Скорее наоборот :)
5 фев 19, 02:14    [21801637]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 5066
andrey_anonymous,

0 больше 2?
5 фев 19, 02:15    [21801638]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
xtender
0 больше 2?

short table scan и прочая эмпирика?
5 фев 19, 02:38    [21801639]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 5066
andrey_anonymous
short table scan
это влияет на другое, уже во время выполнения, не на выбор плана, т.к. проверяется позже для конкретной row-source операции
5 фев 19, 02:42    [21801640]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
xtender
andrey_anonymous
short table scan
это влияет на другое

0 или 1 - это вообще ни о чем - только эмпирика, только хардкор.
Посмотри оценки с livesql на 100к записей, FTS дешевле IFS - и это было бы верно, если бы не ошибка в оценке мощности минимаксного IFS + first row
5 фев 19, 02:51    [21801642]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
andrey_anonymous
ошибка в оценке мощности минимаксного IFS + first row

...при наличии предиката на PART_ID, без которого оценка корректна (третий вариант)
5 фев 19, 02:57    [21801643]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 5066
andrey_anonymous
0 или 1 - это вообще ни о чем - только эмпирика, только хардкор.
блин, ну очевидно же из моих примеров, что не рассматривается индексный доступ и не будет выбран хоть какую стоимость ты сгенерируй.
Вот тебе для примера:
+
CREATE TABLE TBL1
(
  PART_ID                NUMBER NOT NULL,
  DATETIME               DATE
)
PARTITION BY LIST (PART_ID)
(  
  PARTITION X1000 VALUES (1000),  
  PARTITION X1001 VALUES (1001),  
  PARTITION X1002 VALUES (1002),  
  PARTITION X1009 VALUES (1009),  
  PARTITION XDEFAULT VALUES (DEFAULT)
);
insert into tbl1 select 1002, null from dual connect by level<=1e5;
insert into tbl1 select 1002, date'2019-01-01'+level from dual connect by level<=10;
CREATE INDEX IDX_21 ON TBL1 (DATETIME) LOCAL;
call dbms_stats.gather_table_stats(user,'TBL1');

и сравни стоимость
select max(datetime) from tbl1 where part_id = 1002 and datetime is not null
с
select/*+ index(tbl1) */ max(datetime) from tbl1 where part_id = 1002 and datetime is not null
5 фев 19, 02:59    [21801644]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
xtender
andrey_anonymous
0 или 1 - это вообще ни о чем - только эмпирика, только хардкор.
блин, ну очевидно же из моих примеров, что не рассматривается индексный доступ

Из твоих как раз не очевидно :)
Более того, ты прямо утверждаешь, что оптимизатор "выбирает план с большей стоимостью", что технически неверно.
Посмотри мои - там нагляднее имхо, в т.ч. видна эмпирика (будет или нет рассматриваться индексный доступ - зависит от наличия предиката на PART_ID).
5 фев 19, 03:10    [21801649]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
andrey_anonymous
xtender
блин, ну очевидно же из моих примеров, что не рассматривается индексный доступ

Ну кстати не факт в 18 (или кто там на livesql):
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    22 |  3235   (1)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE              |        |     1 |    22 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE      |        |   102K|  2209K|  3235   (1)| 00:00:01 |   KEY |   KEY |
|   3 |    FIRST ROW                 |        |   102K|  2209K|  3235   (1)| 00:00:01 |       |       |
|   4 |     INDEX FULL SCAN (MIN/MAX)| IDX_21 |   102K|  2209K|  3235   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------
 
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    22 |   124   (4)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE             |      |     1 |    22 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE     |      |   102K|  2209K|   124   (4)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS STORAGE FULL| TBL1 |   102K|  2209K|   124   (4)| 00:00:01 |     3 |     3 |
----------------------------------------------------------------------------------------------------
5 фев 19, 03:15    [21801650]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 5066
andrey_anonymous
Более того, ты прямо утверждаешь, что оптимизатор "выбирает план с большей стоимостью", что технически неверно.
читай внимательно:
xtender
один из примеров, когда CBO выбирает план с более высоким Cost
Ты будешь отрицать, что выбран план с большим Cost?
Тем более, что ты же был вроде, когда я выступал в РуОУГе с темой, когда и почему оракл не выбирает план с меньшей стоимостью. И это один из очевидных примеров.


andrey_anonymous
Посмотри мои - там нагляднее имхо
ты путаешь partition pruning с указанием конкретной секции через partition for. Это разные механизмы и, в общем, случае очевидно, что при указании секции через partition for и добавлении предиката, нужен будет отфильтровать записи по предикату. Попробуй, например, захинтовать индексный доступ в своем запросе:
+ partition for без хинта
SQL> explain plan for select max(datetime) from tbl1 partition for(1002) t where part_id = 1002;

Explained.

SQL> @xplan advanced


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3517912980

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    12 |   275   (1)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |    12 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |   100K|  1171K|   275   (1)| 00:00:01 |KEY(AP)|KEY(AP)|
|   3 |    TABLE ACCESS FULL   | TBL1 |   100K|  1171K|   275   (1)| 00:00:01 |     3 |     3 |
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "T"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) MAX("DATETIME")[7]
   2 - (rowset=256) "DATETIME"[DATE,7]
   3 - (rowset=256) "DATETIME"[DATE,7]

37 rows selected.
+ partition for с хинтом
SQL> explain plan for select/*+ index(tbl1) */ max(datetime) from tbl1 partition for(1002) t where part_id = 1002;

Explained.

SQL> @xplan advanced


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3517912980

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    12 |   275   (1)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |    12 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |   100K|  1171K|   275   (1)| 00:00:01 |KEY(AP)|KEY(AP)|
|   3 |    TABLE ACCESS FULL   | TBL1 |   100K|  1171K|   275   (1)| 00:00:01 |     3 |     3 |
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "T"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) MAX("DATETIME")[7]
   2 - (rowset=256) "DATETIME"[DATE,7]
   3 - (rowset=256) "DATETIME"[DATE,7]
5 фев 19, 03:46    [21801653]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 5066
andrey_anonymous
Ну кстати не факт в 18 (или кто там на livesql):
что ты имеешь ввиду? Я же показал, что без хинта IFS(min/max) выбран не будет
5 фев 19, 03:47    [21801654]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
xtender
andrey_anonymous
Более того, ты прямо утверждаешь, что оптимизатор "выбирает план с большей стоимостью", что технически неверно.
читай внимательно:
xtender
один из примеров, когда CBO выбирает план с более высоким Cost
Ты будешь отрицать, что выбран план с большим Cost?

1. Меня убедит только 10053, где будет рассмотрено два плана и выбран более дорогой.
Все остальное - от лукавого, ибо самого факта _выбора не было, если, к примеру, индексный доступ по каким-либо причинам не рассматривался.
2. Я же показал, что согласно оценке оптимизатора на непустой таблице FTS дешевле.

xtender
ты путаешь partition pruning с указанием конкретной секции через partition for. Это разные механизмы и, в общем, случае очевидно, что при указании секции через partition for и добавлении предиката, нужен будет отфильтровать записи по предикату.

Похоже, что путаешь ты.
Смотри внимательно.
Я показал, что, избавившись от предиката (заменив его partition for) в случае PARTITION LIST SINGLE (это важно) удалось привести CBO в состояние, в котором он смог дать адекватную оценку минимаксному скану индекса.
Ты же не будешь утверждать, что минимаксный поиск тождественен IFS/IFS?
При этом возвращение предиката вернуло и проблемы с оценкой (дальше).
Тут все немного сложно - CBO не всегда адекватно реагирует даже на два предиката с partition key, а именно, если поведение не изменилось со старинных времен, использует для partition pruning первый из встреченный предикатов и игнорит второй.
А в приведенном примере "условно конфликтуют" partition for и предикат - ХЗ как индусы разрулили такой пердимонокль.

xtender
Попробуй, например, захинтовать индексный доступ в своем запросе:

Да без проблем:
select /*+ index(t) */ max(datetime)
from tbl1 partition for(1002) t
where part_id = 1002

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    22 |  3235   (1)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE              |        |     1 |    22 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE      |        | 95771 |  2057K|  3235   (1)| 00:00:01 |KEY(AP)|KEY(AP)|
|   3 |    FIRST ROW                 |        | 95771 |  2057K|  3235   (1)| 00:00:01 |       |       |
|   4 |     INDEX FULL SCAN (MIN/MAX)| IDX_21 | 95771 |  2057K|  3235   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------


у тебя просто ошибка в хинте
xtender
SQL> explain plan for select/*+ index(tbl1) */ max(datetime) from tbl1 partition for(1002) t where part_id = 1002;
5 фев 19, 05:40    [21801661]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
xtender
andrey_anonymous
Ну кстати не факт в 18 (или кто там на livesql):
что ты имеешь ввиду? Я же показал, что без хинта IFS(min/max) выбран не будет

Я имею ввиду, что 3235 > 124 - следовательно, CBO выбирает более дешевый план и нет причин подозревать его в халатности, пока иное не будет доказано 10053.
Одновременно я имею ввиду, что оценка 3235 для IFS(MIN/MAX) - ложна.
В этом легко убедиться, посчитав логические чтения хинтованного запроса хотя бы через sessstat.
Естественно, при проведении подобного измерения следует исключить dynamic sampling :)
5 фев 19, 05:49    [21801662]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 5066
andrey_anonymous
1. Меня убедит только 10053, где будет рассмотрено два плана и выбран более дорогой.
Все остальное - от лукавого, ибо самого факта _выбора не было, если, к примеру, индексный доступ по каким-либо причинам не рассматривался.
я прямым текстом и сказал, что более дешевый индексный доступ не рассматривается, приведя пример изначально и отдельно еще один(с данными) тут: 21801644
xtender
andrey_anonymous
0 или 1 - это вообще ни о чем - только эмпирика, только хардкор.
блин, ну очевидно же из моих примеров, что не рассматривается индексный доступ и не будет выбран хоть какую стоимость ты сгенерируй.
Вот тебе для примера:
+
CREATE TABLE TBL1
(
  PART_ID                NUMBER NOT NULL,
  DATETIME               DATE
)
PARTITION BY LIST (PART_ID)
(  
  PARTITION X1000 VALUES (1000),  
  PARTITION X1001 VALUES (1001),  
  PARTITION X1002 VALUES (1002),  
  PARTITION X1009 VALUES (1009),  
  PARTITION XDEFAULT VALUES (DEFAULT)
);
insert into tbl1 select 1002, null from dual connect by level<=1e5;
insert into tbl1 select 1002, date'2019-01-01'+level from dual connect by level<=10;
CREATE INDEX IDX_21 ON TBL1 (DATETIME) LOCAL;
call dbms_stats.gather_table_stats(user,'TBL1');


и сравни стоимость
select max(datetime) from tbl1 where part_id = 1002 and datetime is not null

с
select/*+ index(tbl1) */ max(datetime) from tbl1 where part_id = 1002 and datetime is not null
5 фев 19, 05:52    [21801663]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 5066
andrey_anonymous
2. Я же показал, что согласно оценке оптимизатора на непустой таблице FTS дешевле.
да по барабану, что в твоем примере он дешевле. Я показал, что безотносительно стоимости, индексный доступ выбран не будет
5 фев 19, 05:53    [21801664]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 5066
andrey_anonymous
Я показал, что, избавившись от предиката (заменив его partition for) в случае PARTITION LIST SINGLE (это важно) удалось привести CBO в состояние, в котором он смог дать адекватную оценку минимаксному скану индекса.
Ты же не будешь утверждать, что минимаксный поиск тождественен IFS/IFS?
При этом возвращение предиката вернуло и проблемы с оценкой (дальше).
Еще раз тебе говорю, partition pruning и ручное указание секции - разные вещи, оцениваются и разбираются оптимизатором по разному. Что даже твой пример изначально и показывает. При ручном указании секции через partition for - может оказаться, что в секции много разных part_id, что очевидно совершенно не эквивалентно указанию part_id=1002
5 фев 19, 05:57    [21801665]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
кит северных морей
Member

Откуда: Красноярск
Сообщений: 589
xtender
При ручном указании секции через partition for - может оказаться, что в секции много разных part_id, что очевидно совершенно не эквивалентно указанию part_id=1002
как это возможно в случае с list?
5 фев 19, 06:04    [21801666]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 5066
Почему при ifs(min/max) возвращает высокую кардинальность уже после сна расскажу с трассой и примерами
5 фев 19, 06:06    [21801667]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 5066
кит северных морей,

Укажи несколько значений
5 фев 19, 06:07    [21801668]     Ответить | Цитировать Сообщить модератору
 Re: Не используется партиционированный индекс при выполнении запроса  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
xtender
При ручном указании секции через partition for - может оказаться, что в секции много разных part_id, что очевидно совершенно не эквивалентно указанию part_id=1002

Спасибо, КЭП

Ни в одном из тестов я не обнаружил фильтра на part_id => спец. случай.
С part_id = :X может повести себя иначе.

Однако суть примера с partition for - показать, что CBO способен выбрать IFS(MIN/MAX) в условиях ТС, если, конечно, сумеет правильно оценить. А на способность правильно ценить (или вообще рассматривать IFS - 10053 покажет) влияет наличие предиката по part_id.
5 фев 19, 11:52    [21801815]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Oracle Ответить