Объединение исключений не работает в Oracle с подзапросами
Я могу заставить исключение объединений работать для простых случаев, таких как отношения один-к-одному, но не для немного более сложных сценариев. В конечном счете, я хочу попробовать якорное моделирование, но сначала мне нужно найти способ обойти эту проблему. Я использую Oracle 12c Enterprise Edition Release 12.1.0.2.0.
DDL для моего теста:
drop view product_5nf;
drop table product_color cascade constraints;
drop table product_price cascade constraints;
drop table product cascade constraints;
create table product(
product_id number not null
,constraint product_pk primary key(product_id)
);
create table product_color(
product_id number not null references product
,color varchar2(10) not null
,constraint product_color_pk primary key(product_id)
);
create table product_price(
product_id number not null references product
,from_date date not null
,price number not null
,constraint product_price_pk primary key(product_id, from_date)
);
Некоторые примеры данных:
insert into product values(1);
insert into product values(2);
insert into product values(3);
insert into product values(4);
insert into product_color values(1, 'Red');
insert into product_color values(2, 'Green');
insert into product_price values(1, date '2016-01-01', 10);
insert into product_price values(1, date '2016-02-01', 8);
insert into product_price values(1, date '2016-05-01', 5);
insert into product_price values(2, date '2016-02-01', 5);
insert into product_price values(4, date '2016-01-01', 10);
commit;
5NF вид
Это первое представление не компилируется - оно завершается с ORA-01799: столбец не может быть внешне присоединен к подзапросу. К сожалению, именно так определяется большинство исторических представлений, когда я смотрю на онлайн примеры якорного моделирования...
create view product_5nf as
select p.product_id
,pc.color
,pp.price
from product p
left join product_color pc on(
pc.product_id = p.product_id
)
left join product_price pp on(
pp.product_id = p.product_id
and pp.from_date = (select max(pp2.from_date)
from product_price pp2
where pp2.product_id = pp.product_id)
);
Ниже моя попытка исправить это. При использовании этого вида с простым выбором product_id Oracle удаляет product_color, но не product_price.
create view product_5nf as
select product_id
,pc.color
,pp.price
from product p
left join product_color pc using(product_id)
left join (select pp1.product_id, pp1.price
from product_price pp1
where pp1.from_date = (select max(pp2.from_date)
from product_price pp2
where pp2.product_id = pp1.product_id)
)pp using(product_id);
select product_id
from product_5nf;
----------------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 |
|* 1 | HASH JOIN OUTER | | 4 |
| 2 | INDEX FAST FULL SCAN| PRODUCT_PK | 4 |
| 3 | VIEW | | 3 |
| 4 | NESTED LOOPS | | 3 |
| 5 | VIEW | VW_SQ_1 | 5 |
| 6 | HASH GROUP BY | | 5 |
| 7 | INDEX FULL SCAN | PRODUCT_PRICE_PK | 5 |
|* 8 | INDEX UNIQUE SCAN | PRODUCT_PRICE_PK | 1 |
----------------------------------------------------------
Единственное решение, которое я нашел, - использовать вместо этого скалярные подзапросы, например:
create or replace view product_5nf as
select p.product_id
,pc.color
,(select pp.price
from product_price pp
where pp.product_id = p.product_id
and pp.from_date = (select max(from_date)
from product_price pp2
where pp2.product_id = pp.product_id)) as price
from product p
left join product_color pc on(
pc.product_id = p.product_id
)
select product_id
from product_5nf;
---------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | 4 |
| 1 | INDEX FAST FULL SCAN| PRODUCT_PK | 4 |
---------------------------------------------------
Теперь Oracle успешно удаляет таблицу product_price. Однако скалярные подзапросы реализованы не так, как объединения, и способ их выполнения просто не позволяет мне получить приемлемую производительность в реальном сценарии.
TL; DR Как мне переписать вид product_5nf чтобы Oracle успешно удалил обе зависимые таблицы?
5 ответов
Я думаю, что у вас есть две проблемы здесь.
Во-первых, устранение объединения работает только для определенных, конкретных ситуаций (PK-PK, PK-FK и т. Д.). Это не общая вещь, где вы можете LEFT JOIN к любому набору строк, который будет возвращать одну строку для каждого значения ключа соединения, и Oracle будет исключать соединение.
Во-вторых, даже если бы Oracle был достаточно продвинут, чтобы сделать исключение в ЛЮБОЙ LEFT JOIN если он знал, что получит только одну строку для каждого значения ключа соединения, Oracle пока не поддерживает исключение соединений в LEFT JOINS основанные на комбинированном ключе (в документе поддержки Oracle 887553.1 говорится, что это входит в R12.2).
Одним из обходных путей, который вы могли бы рассмотреть, является материализация представления с последней строкой для каждого product_id, затем LEFT JOIN на материализованный взгляд. Как это:
create table product(
product_id number not null
,constraint product_pk primary key(product_id)
);
create table product_color(
product_id number not null references product
,color varchar2(10) not null
,constraint product_color_pk primary key(product_id)
);
create table product_price(
product_id number not null references product
,from_date date not null
,price number not null
,constraint product_price_pk primary key (product_id, from_date )
);
-- Add a VIRTUAL column to PRODUCT_PRICE so that we can get all the data for
-- the latest row by taking the MAX() of this column.
alter table product_price add ( sortable_row varchar2(80) generated always as ( lpad(product_id,10,'0') || to_char(from_date,'YYYYMMDDHH24MISS') || lpad(price,10,'0')) virtual not null );
-- Create a MV snapshot so we can materialize a view having only the latest
-- row for each product_id and can refresh that MV fast on commit.
create materialized view log on product_price with sequence, primary key, rowid ( price ) including new values;
-- Create the MV
create materialized view product_price_latest refresh fast on commit enable query rewrite as
SELECT product_id, max( lpad(product_id,10,'0') || to_char(from_date,'YYYYMMDDHH24MISS') || lpad(price,10,'0')) sortable_row
FROM product_price
GROUP BY product_id;
-- Create a primary key on the MV, so we can do join elimination
alter table product_price_latest add constraint ppl_pk primary key ( product_id );
-- Insert the OP's test data
insert into product values(1);
insert into product values(2);
insert into product values(3);
insert into product values(4);
insert into product_color values(1, 'Red');
insert into product_color values(2, 'Green');
insert into product_price ( product_id, from_date, price ) values(1, date '2016-01-01', 10 );
insert into product_price ( product_id, from_date, price) values(1, date '2016-02-01', 8);
insert into product_price ( product_id, from_date, price) values(1, date '2016-05-01', 5);
insert into product_price ( product_id, from_date, price) values(2, date '2016-02-01', 5);
insert into product_price ( product_id, from_date, price) values(4, date '2016-01-01', 10);
commit;
-- Create the 5NF view using the materialized view
create or replace view product_5nf as
select p.product_id
,pc.color
,to_date(substr(ppl.sortable_row,11,14),'YYYYMMDDHH24MISS') from_date
,to_number(substr(ppl.sortable_row,25)) price
from product p
left join product_color pc on pc.product_id = p.product_id
left join product_price_latest ppl on ppl.product_id = p.product_id
;
-- The plan for this should not include any of the unnecessary tables.
select product_id from product_5nf;
-- Check the plan
SELECT *
FROM TABLE (DBMS_XPLAN.display_cursor (null, null,
'ALLSTATS LAST'));
------------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | INDEX FULL SCAN | PRODUCT_PK | 1 |
------------------------------------------------
Я не могу исключить объединение цен, но если вы сделаете следующее, это может по крайней мере уменьшить доступ к одному индексу для проверки цены:
CREATE OR REPLACE view product_5nf as
select p.product_id
,pc.color
,pp.price
from product p
left join product_color pc ON p.product_id = pc.product_id
left join (select pp1.product_id, pp1.price
from (SELECT product_id,
price,
from_date,
max(from_date) OVER (PARTITION BY product_id) max_from_date
FROM product_price) pp1
where pp1.from_date = max_from_date) pp ON p.product_id = pp.product_id;
Теперь Oracle успешно удаляет таблицу product_price. Однако скалярные подзапросы реализованы не так, как объединения, и способ их выполнения просто не позволяет мне получить приемлемую производительность в реальном сценарии.
Оптимизатор на основе затрат в Oracle 12.1 может выполнять преобразование запросов для удаления вложенных скалярных подзапросов. Таким образом, производительность может быть столь же хорошей, как и LEFT JOIN вы после в своем вопросе.
Хитрость в том, что вам нужно немного поиграть.
Сначала убедитесь, что скалярный подзапрос возвращает max() без group byТаким образом, CBO знает, что нет шансов получить более одного ряда. (В противном случае это не будет гнездом).
Во-вторых, вам нужно объединить все поля из product_price в один скалярный подзапрос, иначе CBO будет разложен и присоединится product_price многократно.
Вот тестовый пример для Oracle 12.1, который иллюстрирует эту работу.
drop view product_5nf;
drop table product_color cascade constraints;
drop table product_price cascade constraints;
drop table product cascade constraints;
create table product(
product_id number not null
,constraint product_pk primary key(product_id)
);
create table product_color(
product_id number not null references product
,color varchar2(10) not null
,constraint product_color_pk primary key(product_id)
);
create table product_price(
product_id number not null references product
,from_date date not null
,price number not null
,constraint product_price_pk primary key (product_id, from_date )
);
insert into product ( product_id ) SELECT rownum FROM dual connect by rownum <= 100000;
insert into product_color ( product_id, color ) SELECT rownum, dbms_random.string('a',8) color FROM DUAL connect by rownum <= 100000;
--delete from product_price;
insert into product_price ( product_id, from_date, price ) SELECT product_id, trunc(sysdate) + dbms_random.value(-3,3) from_date, floor(dbms_random.value(50,120)/10)*10 price from product cross join lateral ( SELECT rownum x FROM dual connect by rownum <= mod(product_id,5));
commit;
begin dbms_stats.gather_table_stats ( ownname => USER, tabname => 'PRODUCT' ); end;
begin dbms_stats.gather_table_stats ( ownname => USER, tabname => 'PRODUCT_COLOR' ); end;
begin dbms_stats.gather_table_stats ( ownname => USER, tabname => 'PRODUCT_PRICE' ); end;
commit;
alter table product_price add ( composite_column varchar2(80) generated always as ( to_char(from_date,'YYYYMMDDHH24MISS') || lpad(price,10,0)) virtual );
create or replace view product_5nf as
select d.product_id, d.color, to_date(substr(d.product_date_price,1,14),'YYYYMMDDHH24MISS') from_date, to_number(substr(d.product_date_price,-10)) price
from
( select p.product_id
,pc.color
,( SELECT max(composite_column) FROM product_price pp WHERE pp.product_id = p.product_id AND pp.from_date = ( SELECT max(pp2.from_date) FROM product_price pp2 WHERE pp2.product_id = pp.product_id ) ) product_date_price
from product p
left join product_color pc on pc.product_id = p.product_id ) d
;
select product_id from product_5nf;
----------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS FULL| PRODUCT | 100K|
----------------------------------------------
select * from product_5nf;
SELECT *
FROM TABLE (DBMS_XPLAN.display_cursor (null, null,
'ALLSTATS LAST'));
--------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN RIGHT OUTER | | 100K| 8387K| 3159K| 8835K (0)|
| 2 | VIEW | VW_SSQ_2 | 2 | | | |
| 3 | HASH GROUP BY | | 2 | 13M| 2332K| 12M (0)|
| 4 | VIEW | VM_NWVW_3 | 2 | | | |
|* 5 | FILTER | | | | | |
| 6 | HASH GROUP BY | | 2 | 23M| 5055K| 20M (0)|
|* 7 | HASH JOIN | | 480K| 12M| 4262K| 17M (0)|
| 8 | TABLE ACCESS FULL| PRODUCT_PRICE | 220K| | | |
| 9 | TABLE ACCESS FULL| PRODUCT_PRICE | 220K| | | |
|* 10 | HASH JOIN OUTER | | 100K| 5918K| 3056K| 5847K (0)|
| 11 | TABLE ACCESS FULL | PRODUCT | 100K| | | |
| 12 | TABLE ACCESS FULL | PRODUCT_COLOR | 100K| | | |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_2"="P"."PRODUCT_ID")
5 - filter("PP"."FROM_DATE"=MAX("PP2"."FROM_DATE"))
7 - access("PP2"."PRODUCT_ID"="PP"."PRODUCT_ID")
10 - access("PC"."PRODUCT_ID"="P"."PRODUCT_ID")
Хорошо, я отвечаю на свой вопрос. Информация в этом ответе действительна для Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64-битная версия, но, вероятно, не для более поздних версий. Не голосуйте за этот ответ, так как он не отвечает на вопрос.
Из-за конкретного ограничения в текущей версии (как заметил Мэтью Макпик) просто невозможно заставить Oracle полностью исключить ненужные объединения в нижележащем представлении 5NF. Ограничение состоит в том, что исключение объединений невозможно для левых объединений, основанных на составном ключе.
Любая попытка обойти это ограничение, по-видимому, приводит к дублированию или обновлению аномалий. Принятый ответ демонстрирует, как преодолеть это ограничение в оптимизаторе, используя материализованные представления и, таким образом, дублируя данные. Этот ответ показывает, как решить проблему с меньшим дублированием, но с аномалиями обновления.
Этот обходной путь основан на том факте, что вы можете использовать пустые столбцы в уникальных индексах. Мы положим null для всех исторических версий и фактических product_id для самой последней версии ссылка на таблицу продуктов с внешним ключом.
alter table product_price add(
latest_id number
,constraint product_price_uk unique(latest_id)
,constraint product_price_fk2 foreign key(latest_id) references product(product_id)
,constraint product_price_chk check(latest_id = product_id)
);
-- One-time update of existing data
update product_price a
set a.latest_id = a.product_id
where from_date = (select max(from_date)
from product_price b
where a.product_id = b.product_id);
PRODUCT_ID FROM_DATE PRICE LATEST_ID
---------- ---------- ---------- ----------
1 2016-01-01 10 null
1 2016-02-01 8 null
1 2016-05-01 5 1
2 2016-02-01 5 2
4 2016-01-01 10 4
-- New view definition
create or replace view product_5nf as
select p.product_id
,pc.color
,pp.price
from product p
left join product_color pc on(pc.product_id = p.product_id)
left join product_price pp on(pp.latest_id = p.product_id);
Конечно сейчас latest_id должен поддерживаться вручную... всякий раз, когда вставляется новая запись, старая запись должна сначала быть обновлена с нулем.
У этого подхода есть два преимущества. Во-первых, Oracle может полностью удалить ненужные объединения. И, во-вторых, объединения не выполняются как скалярные подзапросы.
SQL> select count(*) from product_5nf;
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX FULL SCAN| PRODUCT_PK |
---------------------------------------
Oracle признает, что счет может быть решен, даже не касаясь базовой таблицы. И никаких ненужных соединений не видно...
SQL> select product_id, price from product_5nf;
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN OUTER | |
| 2 | INDEX FULL SCAN | PRODUCT_PK |
| 3 | TABLE ACCESS BY INDEX ROWID| PRODUCT_PRICE |
|* 4 | INDEX FULL SCAN | PRODUCT_PRICE_UK |
---------------------------------------------------------
Oracle признает, что мы должны присоединиться к product_price чтобы получить ценовой столбец. А также product_color нигде не видно...
SQL> select * from product_5nf;
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN OUTER | |
| 2 | NESTED LOOPS OUTER | |
| 3 | INDEX FULL SCAN | PRODUCT_PK |
| 4 | TABLE ACCESS BY INDEX ROWID| PRODUCT_COLOR |
|* 5 | INDEX UNIQUE SCAN | PRODUCT_COLOR_PK |
| 6 | TABLE ACCESS BY INDEX ROWID | PRODUCT_PRICE |
|* 7 | INDEX FULL SCAN | PRODUCT_PRICE_UK |
----------------------------------------------------------
Здесь Oracle должен материализовать все объединения, поскольку на все столбцы есть ссылки.
[Я не знаю, считается ли ANTI-JOIN подзапросом в Oracle], но not exists хитрость часто является способом избежать агрегирующего подзапроса:
CREATE VIEW product_5nfa as
SELECT p.product_id
,pc.color
,pp.price
FROM product p
LEFT JOIN product_color pc
ON pc.product_id = p.product_id
LEFT join product_price pp
ON pp.product_id = p.product_id
AND NOT EXISTS ( SELECT * FROM product_price pp2
WHERE pp2.product_id = pp.product_id
AND pp2.from_date > pp.from_date
)
;
Комментарий от OP: представление создано, но Oracle по-прежнему не может удалить объединение. Вот план выполнения.
select count(*) from product_5nfa;
-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS OUTER | |
| 3 | INDEX FULL SCAN | PRODUCT_PK |
| 4 | VIEW | |
| 5 | NESTED LOOPS ANTI| |
|* 6 | INDEX RANGE SCAN| PRODUCT_PRICE_PK |
|* 7 | INDEX RANGE SCAN| PRODUCT_PRICE_PK |
-------------------------------------------------