отсечение субпартиций

LVA
Дата: 18.11.2008 11:56:39
Помогите разобраться. Имеется таблица с партиционированием BY RANGE и субпартициями BY LIST.
При выборке оптимизатор использует полный перебор субпартиций (PARTITION LIST ALL), в то время как необходим перебор только нужных.
Ниже приведен тестовый скрипт и результаты выполнения.
автор

-- основная таблица
create table x_tab (
base_date date not null,
base_type number(2,0) not null,
amount number not null
)
partition by range (base_date)
subpartition by list (base_type)
subpartition template (
subpartition "01" values ( 1 ),
subpartition "02" values ( 2 ),
subpartition "03" values ( 3 ),
subpartition "04" values ( 4 )
)
(partition "X_TAB_200801" values less than (to_date(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "X_TAB_200802" values less than (to_date(' 2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "X_TAB_OTHERS" values less than (maxvalue)
)
/

-- таблица, по которой надо отсекать субпартиции
create table x_id(id number not null, use_yn char(1) not null)
/

-- наполнение данными
insert into x_id (id, use_yn)
select level, case when mod(level,2) = 1 then 'Y' else 'N' end
from dual connect by level < 5
/
commit
/
insert into x_tab(base_date, base_type, amount)
select to_date('2008-01-01','yyyy-mm-dd') + level/(24*60), mod(level,4)+1, mod(level,24*60)
from dual connect by level < (24*60)*30*3
/
commit
/
-- сбор статистики
begin
dbms_stats.gather_table_stats(ownname => NULL, tabname => 'X_TAB',
granularity => 'ALL', estimate_percent => 5, CASCADE => TRUE,
no_invalidate => FALSE);
dbms_stats.gather_table_stats(ownname => NULL, tabname => 'X_ID',
granularity => 'ALL', estimate_percent => 100, CASCADE => TRUE,
no_invalidate => FALSE);
end;
/

-- проверка данных
select * from x_id
/
-- проверяем заполненность партиций / субпартиций
select trunc(t.base_date,'MM') period, base_type, count(*)
from x_tab t
group by trunc(t.base_date,'MM'), base_type
/

-- делаем выборку и смотрим план
explain plan for
select * from x_tab t
where base_date between :d1 and :d2
and t.base_type in (select x.id from x_id x where x.use_yn ='Y')
/

select * from table(dbms_xplan.display)
/


результаты выполнения скрипта:
автор

SQL> -- основная таблица
SQL> create table x_tab (
2 base_date date not null,
3 base_type number(2,0) not null,
4 amount number not null
5 )
6 partition by range (base_date)
7 subpartition by list (base_type)
8 subpartition template (
9 subpartition "01" values ( 1 ),
10 subpartition "02" values ( 2 ),
11 subpartition "03" values ( 3 ),
12 subpartition "04" values ( 4 )
13 )
14 (partition "X_TAB_200801" values less than (to_date(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
15 partition "X_TAB_200802" values less than (to_date(' 2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
16 partition "X_TAB_OTHERS" values less than (maxvalue)
17 )
18 /

Table created
SQL> -- таблица, по которой надо отсекать субпартиции
SQL> create table x_id(id number not null, use_yn char(1) not null)
2 /

Table created
SQL> -- наполнение данными
SQL> insert into x_id (id, use_yn)
2 select level, case when mod(level,2) = 1 then 'Y' else 'N' end
3 from dual connect by level < 5
4 /

4 rows inserted
SQL> commit
2 /

Commit complete
SQL> insert into x_tab(base_date, base_type, amount)
2 select to_date('2008-01-01','yyyy-mm-dd') + level/(24*60), mod(level,4)+1, mod(level,24*60)
3 from dual connect by level < (24*60)*30*3
4 /

129599 rows inserted
SQL> commit
2 /

Commit complete
SQL> -- сбор статистики
SQL> begin
2 dbms_stats.gather_table_stats(ownname => NULL, tabname => 'X_TAB',
3 granularity => 'ALL', estimate_percent => 5, CASCADE => TRUE,
4 no_invalidate => FALSE);
5 dbms_stats.gather_table_stats(ownname => NULL, tabname => 'X_ID',
6 granularity => 'ALL', estimate_percent => 100, CASCADE => TRUE,
7 no_invalidate => FALSE);
8 end;
9 /

PL/SQL procedure successfully completed
SQL> -- проверка данных
SQL> select * from x_id
2 /

ID USE_YN
---------- ------
1 Y
2 N
3 Y
4 N
SQL> -- проверяем заполненность партиций / субпартиций
SQL> select trunc(t.base_date,'MM') period, base_type, count(*)
2 from x_tab t
3 group by trunc(t.base_date,'MM'), base_type
4 /

PERIOD BASE_TYPE COUNT(*)
----------- --------- ----------
01.01.2008 1 11159
01.02.2008 1 10440
01.03.2008 1 10800
01.01.2008 2 11160
01.03.2008 2 10800
01.02.2008 2 10440
01.01.2008 3 11160
01.02.2008 3 10440
01.03.2008 3 10800
01.03.2008 4 10800
01.01.2008 4 11160
01.02.2008 4 10440

12 rows selected
SQL> -- делаем выборку и смотрим план
SQL> explain plan for
2 select * from x_tab t
3 where base_date between :d1 and :d2
4 and t.base_type in (select x.id from x_id x where x.use_yn ='Y')
5 /

Explained
SQL> select * from table(dbms_xplan.display)
2 /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2301227329
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 164 | 3116 | 101 (4)| 00:00:
|* 1 | FILTER | | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 164 | 3116 | 101 (4)| 00:00:
|* 3 | TABLE ACCESS FULL | X_ID | 2 | 10 | 3 (0)| 00:00:
| 4 | PARTITION RANGE ITERATOR| | 327 | 4578 | 97 (4)| 00:00:
| 5 | PARTITION LIST ALL | | 327 | 4578 | 97 (4)| 00:00:
|* 6 | TABLE ACCESS FULL | X_TAB | 327 | 4578 | 97 (4)| 00:00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:D1)<=TO_DATE(:D2))
2 - access("T"."BASE_TYPE"="X"."ID")
3 - filter("X"."USE_YN"='Y')

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
6 - filter("BASE_DATE">=:D1 AND "BASE_DATE"<=:D2)

21 rows selected
.....
Дата: 18.11.2008 12:12:28
для данного плана сабж - нормальное явление. можете изменить на nested loop, тогда получите отечение subpartiton
Timm
Дата: 18.11.2008 12:20:58
А какая версия? в 10-ке появились PARTITION RANGE/LIST SUBQUERY, правда непонятно, будут ли они использоваться для отсечения subpartitions. 10053 наверное должен это показать.
LVA
Дата: 18.11.2008 12:21:32
да что же тут нормального то ???
это абсолютно ненормально.
LVA
Дата: 18.11.2008 12:23:08
Timm

А какая версия? в 10-ке появились PARTITION RANGE/LIST SUBQUERY, правда непонятно, будут ли они использоваться для отсечения subpartitions. 10053 наверное должен это показать.


данное поведение одинаково и на 9-ке, и на 10-ке
9.2.0.6.0
10.2.0.3.0

но вообще то надо плясать от 9-ки
.....
Дата: 18.11.2008 12:47:47
LVA
да что же тут нормального то ???
это абсолютно ненормально.


это ваши мысли или официальная позиция Oracle ?
LVA
Дата: 18.11.2008 14:17:12
для чего нужны тогда партиции+субпартиции, если по ним нет отсечения ?

Оракл ведет себя непредсказуемо. Вот более наглядный пример
автор

SQL> create table x_id2id (id_from number, id_to number, use_yn char(1))
2 /

Table created
SQL> insert into x_id2id (id_from, id_to, use_yn)
2 select level, level, case when mod(level,2) = 1 then 'Y' else 'N' end
3 from dual connect by level < 5
4 /

4 rows inserted
SQL> begin
2 dbms_stats.gather_table_stats(ownname => NULL, tabname => 'X_ID2ID',
3 granularity => 'ALL', estimate_percent => 100, CASCADE => TRUE,
4 no_invalidate => FALSE);
5 end;
6 /

PL/SQL procedure successfully completed


теперь смотрим планы для двух вариантов селекта
1)

select * from x_tab t
where base_date between :d1 and :d2
and t.base_type in (
select x.id_to from x_id2id x where x.id_from = :id
)
и
2)

select * from x_tab t
where base_date between :d1 and :d2
and t.base_type in (
select x.id_to from x_id2id x where x.id_from = :id
or :id is null -- добавлено, по сравнению с первым селектом
)

для 9.2.0.6.0
и 1ый и 2ой варинты дают отсечение по субпартициям, но при этом зачем то тратит ресурсы на HASH JOIN SEMI
автор

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstar
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 1600 | 45 |
|* 1 | FILTER | | | | |
|* 2 | HASH JOIN SEMI | | 80 | 1600 | 45 |
| 3 | PARTITION RANGE ITERATOR| | | | | KEY
| 4 | PARTITION LIST ITERATOR| | | | | KEY
|* 5 | TABLE ACCESS FULL | X_TAB | 321 | 4494 | 42 | KEY
|* 6 | TABLE ACCESS FULL | X_ID2ID | 1 | 6 | 2 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:Z)<=TO_DATE(:Z))
2 - access("T"."BASE_TYPE"="X"."ID_TO")
5 - filter("T"."BASE_DATE">=:Z AND "T"."BASE_DATE"<=:Z)
6 - filter("X"."ID_FROM"=TO_NUMBER(:Z))


для 10.2.0.3.0
1-ый дает отсечение, но уже применяет PARTITION LIST SUBQUERY, все с тем же HASH JOIN
автор

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 1640 | 101 (4)| 00:0
|* 1 | FILTER | | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 82 | 1640 | 101 (4)| 00:0
|* 3 | TABLE ACCESS FULL | X_ID2ID | 1 | 6 | 3 (0)| 00:0
| 4 | PARTITION RANGE ITERATOR| | 327 | 4578 | 97 (4)| 00:0
| 5 | PARTITION LIST SUBQUERY| | 327 | 4578 | 97 (4)| 00:0
|* 6 | TABLE ACCESS FULL | X_TAB | 327 | 4578 | 97 (4)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:D1)<=TO_DATE(:D2))
2 - access("T"."BASE_TYPE"="X"."ID_TO")
3 - filter("X"."ID_FROM"=TO_NUMBER(:ID))
6 - filter("BASE_DATE">=:D1 AND "BASE_DATE"<=:D2)


а вот второй приводит к
автор

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 164 | 3280 | 101 (4)| 00:0
|* 1 | FILTER | | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 164 | 3280 | 101 (4)| 00:0
|* 3 | TABLE ACCESS FULL | X_ID2ID | 1 | 6 | 3 (0)| 00:0
| 4 | PARTITION RANGE ITERATOR| | 327 | 4578 | 97 (4)| 00:0
| 5 | PARTITION LIST ALL | | 327 | 4578 | 97 (4)| 00:0
|* 6 | TABLE ACCESS FULL | X_TAB | 327 | 4578 | 97 (4)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:D1)<=TO_DATE(:D2))
2 - access("T"."BASE_TYPE"="X"."ID_TO")
3 - filter(:ID IS NULL OR "X"."ID_FROM"=TO_NUMBER(:ID))
6 - filter("BASE_DATE">=:D1 AND "BASE_DATE"<=:D2)


Необходимо сесть только на нужные субпартиции, вне зависимости от версии оракл. Есть какие нибудь мысли ?
LVA
Дата: 18.11.2008 14:42:53
Добавлю, что проблема для меня лично в том, что мне нужно выбирать либо по всем субпартициям, которые разрешены к использованию (то есть USE_YN='Y'), либо для тех id_to, которые соответствую заданному id_from.

Если не уточнять (where x.id_from = :id), то субпартиции не отсекаются ну в 9-ке, ну в 10-ке.
Если уточнять, то отсечение все таки происходит, хотя и коряво
А при объединении в одно условие (where x.id_from = :id OR : id is null) поведение для разных версия различается в принципе
_xcb
Дата: 18.11.2008 15:22:09
Да что же вы хотите?
При плане с хешем, на этапе построения плана значение не известно, поэтому и отсекать ничего нельзя,
вам же сказали используйте план с вложенными циклами. там после вычисления того что вернет подзапрос можно решать какие подсекции нужно читать.
Либо подставле условие с константами на поле субпартицирования.
LVA
Дата: 18.11.2008 15:48:22
_xcb
Да что же вы хотите?
При плане с хешем, на этапе построения плана значение не известно, поэтому и отсекать ничего нельзя,
вам же сказали используйте план с вложенными циклами. там после вычисления того что вернет подзапрос можно решать какие подсекции нужно читать.
Либо подставле условие с константами на поле субпартицирования.


я совсем не понимаю, почему нельзя отсекать - ясно, что перечень партиций определяется динамически, так на то и существуют PARTITION LIST ITERATOR и PARTITION LIST SUBQUERY.

И, тем более, я привел примеры, когда отсечение происходит - ввел таблицу x_id2id, при этом я уточняю bind-переменной значение id_from, которое отображается во множество значений id_to, которое заранее не известно. И при этом все нормуль!