Не используется партиционированный индекс при выполнении запроса / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Не используется партиционированный индекс при выполнении запроса

25 сообщений из 30, страница 1 из 2

все

Не используется партиционированный индекс при выполнении запроса

#39769413

Андрей_7777

Гость

Доброе время суток!

Есть партиционированная таблица с локальным непрефиксным индексом:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

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 посчитана:

Код: plsql

1.
2.
3.
4.
5.
6.
7.

      DBMS_STATS.GATHER_TABLE_STATS(
        OwnName          => user,
        TabName          => 'tbl1',
        PartName         => 'X1002',
        Granularity      => 'PARTITION',
        Estimate_Percent => 5,
        Cascade          => TRUE);

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

Почему при выполнении запроса:

Код: plsql

1.
2.
3.

select max(datetime)
from tbl1
where part_id = 1002

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

Вот план запроса:

Код: plsql

1.
2.
3.
4.
5.

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

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

Если указать условие, на конкретное время:

Код: plsql

1.
2.
3.
4.

select *
from tbl1 A
where part_id = 1002
  and datetime = :df

то судя по плану запроса просматривается конкретная партиция индекса, что верно:

Код: plsql

1.
2.
3.
4.
5.

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).
Но почему не подхватывается НЕпрефиксный?
Я раньше не замечал подобного.

...

Рейтинг:

0 / 0

04.02.2019, 21:49

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769426

Fogel

Участник

Сообщения: 400

Рейтинг: 0 / 0

ха-хаха
сколько умных слов и разбора, а толку...

Потому что отбор идёт по полю, где нет индекса:

Код: plsql

where part_id = 1002

...

Рейтинг:

0 / 0

04.02.2019, 23:40

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769428

Андрей_7777

Гость

Fogel,

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

Ps: почитайте про партиционированные индексы, например, в книге Тома Кайта.

...

Рейтинг:

0 / 0

05.02.2019, 00:09

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769429

кит северных морей

Участник

Откуда: krsk / nyc / krsk

Сообщения: 808

Рейтинг: 0 / 0

Fogelха-хаха
сколько умных слов и разбора, а толку...

Потому что отбор идёт по полю, где нет индекса:

Код: plsql

where part_id = 1002

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

...

Рейтинг:

0 / 0

05.02.2019, 00:13

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769433

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

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

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.

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.

...

Рейтинг:

0 / 0

05.02.2019, 01:12

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769434

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

Ах да,

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

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.

...

Рейтинг:

0 / 0

05.02.2019, 01:20

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769435

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

andrey_anonymousВидимо, даже с LIST SINGLE действует общее правило - pruning не отменяет filtering.
Хотя по-хорошему это, конечно, баг оптимизатора.
Посмотрите на статистики сессии при выполнении

Код: plsql

1.
2.
3.

select /*+ index(t)*/ max(datetime)
from tbl1 t
where part_id = 1002

и обнаружите, что оптимизатор сильно перезаложился, выдав из-под "first row" все 102K :)

...

Рейтинг:

0 / 0

05.02.2019, 01:37

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769436

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

Андрей_7777,

нужно хинтануть:

Код: plsql

select/*+ index(tbl1) */ max(datetime) from tbl1 where part_id = 1002

оптимизатор тут что-то глючит нехило: один из примеров, когда CBO выбирает план с более высоким Cost

11.2.0.2

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.

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

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.

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

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.

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

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.

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

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.

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

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.

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]

...

Рейтинг:

0 / 0

05.02.2019, 01:42

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769437

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

xtenderоптимизатор тут что-то глючит нехило: один из примеров, когда CBO выбирает план с более высоким Cost

Код: plsql

|   3 |    TABLE ACCESS FULL   | TBL1 |     1 |    22 |     2   (0)| 00:00:01 |     3 |     3 |

Не маловато данных?

...

Рейтинг:

0 / 0

05.02.2019, 02:02

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769438

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

andrey_anonymous,

а зачем данные, чтобы увидеть, что выбран план с более высокой стоимостью?

...

Рейтинг:

0 / 0

05.02.2019, 02:05

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769439

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

xtenderа зачем данные, чтобы увидеть, что выбран план с более высокой стоимостью?
Нууу... Оценка мощности FTS в одну строку как-то не навевает мысли о высокой стоимости... Скорее наоборот :)

...

Рейтинг:

0 / 0

05.02.2019, 02:14

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769440

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

andrey_anonymous,

0 больше 2?

...

Рейтинг:

0 / 0

05.02.2019, 02:15

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769441

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

xtender0 больше 2?
short table scan и прочая эмпирика?

...

Рейтинг:

0 / 0

05.02.2019, 02:38

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769442

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

andrey_anonymousshort table scanэто влияет на другое, уже во время выполнения, не на выбор плана, т.к. проверяется позже для конкретной row-source операции

...

Рейтинг:

0 / 0

05.02.2019, 02:42

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769444

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

xtenderandrey_anonymousshort table scanэто влияет на другое
0 или 1 - это вообще ни о чем - только эмпирика, только хардкор.
Посмотри оценки с livesql на 100к записей, FTS дешевле IFS - и это было бы верно, если бы не ошибка в оценке мощности минимаксного IFS + first row

...

Рейтинг:

0 / 0

05.02.2019, 02:51

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769445

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

andrey_anonymousошибка в оценке мощности минимаксного IFS + first row
...при наличии предиката на PART_ID, без которого оценка корректна (третий вариант)

...

Рейтинг:

0 / 0

05.02.2019, 02:57

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769446

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

andrey_anonymous0 или 1 - это вообще ни о чем - только эмпирика, только хардкор.блин, ну очевидно же из моих примеров, что не рассматривается индексный доступ и не будет выбран хоть какую стоимость ты сгенерируй.
Вот тебе для примера:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

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');

и сравни стоимость

Код: plsql

select max(datetime) from tbl1 where part_id = 1002 and datetime is not null

Код: plsql

select/*+ index(tbl1) */ max(datetime) from tbl1 where part_id = 1002 and datetime is not null

...

Рейтинг:

0 / 0

05.02.2019, 02:59

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769447

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

xtenderandrey_anonymous0 или 1 - это вообще ни о чем - только эмпирика, только хардкор.блин, ну очевидно же из моих примеров, что не рассматривается индексный доступ
Из твоих как раз не очевидно :)
Более того, ты прямо утверждаешь, что оптимизатор "выбирает план с большей стоимостью", что технически неверно.
Посмотри мои - там нагляднее имхо, в т.ч. видна эмпирика (будет или нет рассматриваться индексный доступ - зависит от наличия предиката на PART_ID).

...

Рейтинг:

0 / 0

05.02.2019, 03:10

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769448

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

andrey_anonymousxtenderблин, ну очевидно же из моих примеров, что не рассматривается индексный доступ
Ну кстати не факт в 18 (или кто там на livesql):

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

-------------------------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------------------

...

Рейтинг:

0 / 0

05.02.2019, 03:15

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769449

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

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

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

partition for без хинта

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.

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 с хинтом

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.

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]

...

Рейтинг:

0 / 0

05.02.2019, 03:46

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769450

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

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

...

Рейтинг:

0 / 0

05.02.2019, 03:47

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769456

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

xtenderandrey_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Попробуй, например, захинтовать индексный доступ в своем запросе:

Да без проблем:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

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

Код: plsql

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

...

Рейтинг:

0 / 0

05.02.2019, 05:40

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769457

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

xtenderandrey_anonymousНу кстати не факт в 18 (или кто там на livesql):что ты имеешь ввиду? Я же показал, что без хинта IFS(min/max) выбран не будет
Я имею ввиду, что 3235 > 124 - следовательно, CBO выбирает более дешевый план и нет причин подозревать его в халатности, пока иное не будет доказано 10053.
Одновременно я имею ввиду, что оценка 3235 для IFS(MIN/MAX) - ложна.
В этом легко убедиться, посчитав логические чтения хинтованного запроса хотя бы через sessstat.
Естественно, при проведении подобного измерения следует исключить dynamic sampling :)

...

Рейтинг:

0 / 0

05.02.2019, 05:49

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769458

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

andrey_anonymous1. Меня убедит только 10053, где будет рассмотрено два плана и выбран более дорогой.
Все остальное - от лукавого, ибо самого факта _выбора не было, если, к примеру, индексный доступ по каким-либо причинам не рассматривался .я прямым текстом и сказал, что более дешевый индексный доступ не рассматривается, приведя пример изначально и отдельно еще один(с данными) тут: 21801644
xtenderandrey_anonymous0 или 1 - это вообще ни о чем - только эмпирика, только хардкор.блин, ну очевидно же из моих примеров, что не рассматривается индексный доступ и не будет выбран хоть какую стоимость ты сгенерируй.
Вот тебе для примера:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

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');

и сравни стоимость

Код: plsql

select max(datetime) from tbl1 where part_id = 1002 and datetime is not null

Код: plsql

select/*+ index(tbl1) */ max(datetime) from tbl1 where part_id = 1002 and datetime is not null

...

Рейтинг:

0 / 0

05.02.2019, 05:52

| Ответить | Цитировать | Написать

Не используется партиционированный индекс при выполнении запроса

#39769459

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

andrey_anonymous2. Я же показал, что согласно оценке оптимизатора на непустой таблице FTS дешевле.да по барабану, что в твоем примере он дешевле. Я показал, что безотносительно стоимости, индексный доступ выбран не будет

...

Рейтинг:

0 / 0

05.02.2019, 05:53

| Ответить | Цитировать | Написать

25 сообщений из 30, страница 1 из 2

все

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&fpage=85&tid=1882832]:	0ms
get settings:	11ms
get forum list:	16ms
check forum access:	4ms
check topic access:	4ms
track hit:	33ms
get topic data:	14ms
get forum data:	3ms
get page messages:	73ms
get tp. blocked users:	2ms
others:	340ms

total:	500ms