Привет.
В теме фигурирует много всего, но оно все взаимосвязано, может кому-то будет полезно. Вопрос всего один и ближе к концу.
| + Есть такая таблица |
drop table fact;
create table fact
(
dt date not null,
created timestamp(6) not null,
expired timestamp(6) not null,
value number
)
partition by range (dt)
interval (numtodsinterval(1,'DAY'))
(partition empty values less than (date '2001-01-01'));
insert into fact
select t.*, dbms_random.value
from
(select trunc(sysdate)-level, systimestamp, date '9999-01-01' from dual connect by level <= 1000) t,
(select dummy from dual connect by level <= 1);
exec dbms_stats.gather_table_stats (user,'t_fact');
|
Необходимо сделать слебующее обновление:
update fact
set expired = created
where dt in
(select
to_date (substr(column_value,1,8), 'yyyymmdd')
from table (sys.odcivarchar2list ('20120101')));
В данном случае все ок.
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 37 | 3467 (1)| 00:00:42 | | |
| 1 | UPDATE | FACT | | | | | | |
| 2 | NESTED LOOPS | | 1 | 37 | 3467 (1)| 00:00:42 | | |
| 3 | SORT UNIQUE | | 8168 | 16336 | 29 (0)| 00:00:01 | | |
| 4 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 8168 | 16336 | 29 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 35 | 13 (0)| 00:00:01 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | FACT | 1 | 35 | 13 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------
Теперь задача усложняется и надо обновлять не всю секцию целиком, а строки, созданные в определенное время.
update fact
set expired = created
where (dt, created) in
(select
to_date (substr(column_value,1,8), 'yyyymmdd'),
-- to_timestamp(substr(column_value,instr(column_value,' ')),'yyyymmddhh24missff6')
to_timestamp(substr(column_value,10),'yyyymmddhh24missff6')
from table(sys.odcivarchar2list('20120101 20120101202020000000')) t);
План уже не такой как хотелось бы:
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 37 | 13523 (1)| 00:02:43 | | |
| 1 | UPDATE | FACT | | | | | | |
|* 2 | HASH JOIN SEMI | | 1 | 37 | 13523 (1)| 00:02:43 | | |
| 3 | PARTITION RANGE ALL | | 1 | 35 | 13493 (1)| 00:02:42 | 1 |1048575|
| 4 | TABLE ACCESS FULL | FACT | 1 | 35 | 13493 (1)| 00:02:42 | 1 |1048575|
| 5 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 8168 | 16336 | 29 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------
Это исправляется следующим образом:
update /*+ use_nl(fact) leading(kokbf$0 fact) */ fact
set expired = created
where (dt, created) in
(select
to_date (substr(column_value,1,8), 'yyyymmdd'),
-- to_timestamp(substr(column_value,instr(column_value,' ')),'yyyymmddhh24missff6')
to_timestamp(substr(column_value,10),'yyyymmddhh24missff6')
from table(sys.odcivarchar2list('20120101 20120101202020000000')) t);
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 37 | 55074 (1)| 00:11:01 | | |
| 1 | UPDATE | FACT | | | | | | |
| 2 | NESTED LOOPS | | 1 | 37 | 55074 (1)| 00:11:01 | | |
| 3 | SORT UNIQUE | | 8168 | 16336 | 29 (0)| 00:00:01 | | |
| 4 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 8168 | 16336 | 29 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 35 | 13 (0)| 00:00:01 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | FACT | 1 | 35 | 13 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------
Здесь можно обратить внимание на стоимость которая увеличилась более чем в 15 раз из-за чего Оракл и стал предпочитать PARTITION RANGE ALL без хинта.
| + Про хинт cardinality |
Интересный эффект наблюдается в зависимости от того каким образом получать timestamp из строки.
Таким образом хинт не подхватывается, но при указании алиаса все ок:
update fact
set expired = created
where (dt, created) in
(select --+ cardinality(10)
to_date (substr(column_value,1,8), 'yyyymmdd'),
-- to_timestamp(substr(column_value,instr(column_value,' ')),'yyyymmddhh24missff6')
to_timestamp(substr(column_value,10),'yyyymmddhh24missff6')
from table(sys.odcivarchar2list('20120101 20120101202020000000')) t);
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 37 | 13523 (1)| 00:02:43 | | |
| 1 | UPDATE | FACT | | | | | | |
|* 2 | HASH JOIN SEMI | | 1 | 37 | 13523 (1)| 00:02:43 | | |
| 3 | PARTITION RANGE ALL | | 1 | 35 | 13493 (1)| 00:02:42 | 1 |1048575|
| 4 | TABLE ACCESS FULL | FACT | 1 | 35 | 13493 (1)| 00:02:42 | 1 |1048575|
| 5 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 8168 | 16336 | 29 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------
update fact
set expired = created
where (dt, created) in
(select --+ cardinality(t 10)
to_date (substr(column_value,1,8), 'yyyymmdd'),
-- to_timestamp(substr(column_value,instr(column_value,' ')),'yyyymmddhh24missff6')
to_timestamp(substr(column_value,10),'yyyymmddhh24missff6')
from table(sys.odcivarchar2list('20120101 20120101202020000000')) t);
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 37 | 97 (2)| 00:00:02 | | |
| 1 | UPDATE | FACT | | | | | | |
| 2 | NESTED LOOPS | | 1 | 37 | 97 (2)| 00:00:02 | | |
| 3 | SORT UNIQUE | | 10 | 20 | 29 (0)| 00:00:01 | | |
| 4 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 10 | 20 | 29 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 35 | 13 (0)| 00:00:01 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | FACT | 1 | 35 | 13 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------
Если получать timestamp несколько иначе, то создается non-mergeable view (мне до конца неясно по каким причинам) и хинт подхватывается без алиаса (правда для этапа SORT UNIQUE, а не COLLECTION ITERATOR CONSTRUCTOR FETCH):
update fact
set expired = created
where (dt, created) in
(select --+ cardinality(10)
to_date (substr(column_value,1,8), 'yyyymmdd'),
to_timestamp(substr(column_value,instr(column_value,' ')),'yyyymmddhh24missff6')
-- to_timestamp(substr(column_value,10),'yyyymmddhh24missff6')
from table(sys.odcivarchar2list('20120101 20120101202020000000')) t);
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 53 | 165 (1)| 00:00:02 | | |
| 1 | UPDATE | FACT | | | | | | |
| 2 | NESTED LOOPS | | 1 | 53 | 165 (1)| 00:00:02 | | |
| 3 | VIEW | VW_NSO_1 | 10 | 180 | 30 (4)| 00:00:01 | | |
| 4 | SORT UNIQUE | | 10 | 20 | 30 (4)| 00:00:01 | | |
| 5 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 8168 | 16336 | 29 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ITERATOR | | 1 | 35 | 13 (0)| 00:00:01 | KEY | KEY |
|* 7 | TABLE ACCESS FULL | FACT | 1 | 35 | 13 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------
Без хинта план выглядит так:
update fact
set expired = created
where (dt, created) in
(select
to_date (substr(column_value,1,8), 'yyyymmdd'),
to_timestamp(substr(column_value,instr(column_value,' ')),'yyyymmddhh24missff6')
-- to_timestamp(substr(column_value,10),'yyyymmddhh24missff6')
from table(sys.odcivarchar2list('20120101 20120101202020000000')) t);
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 53 | 13524 (1)| 00:02:43 | | |
| 1 | UPDATE | FACT | | | | | | |
|* 2 | HASH JOIN | | 1 | 53 | 13524 (1)| 00:02:43 | | |
| 3 | PARTITION RANGE ALL | | 1 | 35 | 13493 (1)| 00:02:42 | 1 |1048575|
| 4 | TABLE ACCESS FULL | FACT | 1 | 35 | 13493 (1)| 00:02:42 | 1 |1048575|
| 5 | VIEW | VW_NSO_1 | 8168 | 143K| 30 (4)| 00:00:01 | | |
| 6 | SORT UNIQUE | | 8168 | 16336 | 30 (4)| 00:00:01 | | |
| 7 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 8168 | 16336 | 29 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------
|
Собственно главный вопрос в следующем.
На одном из экземпляров, кардинальность по умолчанию была 1 а не 8168, поэтому никаких проблем не возникало.
На всех экземплярах optimizer_dynamic_sampling = 2, associate statistics не используется.
По какой причине еще по умолчанию может быть 1?| + Альтернативы |
| Проблем с кардинальностью гарантированно можно избежать при merge (PARTITION RANGE SUBQUERY) или forall (несколько PARTITION RANGE SINGLE), но интересует именно update. |