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

Откуда:
Сообщений: 1686
есть таблица t:
id
,org_id
,(куча полей)

PK в этой таблице (id, org_id)
таблица большая

и есть таблица org

org_id
,(куча полей)

PK в этой таблице org_id
таблица маленькая

***

в таблице t могут быть не все комбинации (id, org_id)
надо как раз вывести недостающие комбинации

как это лучше сделать ?
23 фев 11, 14:44    [10278481]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
grok
Member

Откуда:
Сообщений: 1686
можно наверное сделать cartesian minus join
но мне кажется это будет долго
есть альтернативы ?
23 фев 11, 14:52    [10278525]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
Elic
Member

Откуда:
Сообщений: 29980
partitioned outer join:
with org as (select level as id from dual connect by level <= 4)
     , t as (select level as id, level as org_id, 'x' as x from dual connect by level <= 3)
select * from org left join t partition by (t.id) on (t.org_id = org.id)
--  where t.org_id is null
;

       ID        ID        ORG_ID X
--------- --------- ------------- -
        1         1             1 x
        1         2
        1         3
        1         4
        2         1
        2         2             2 x
        2         3
        2         4
        3         1
        3         2
        3         3             3 x
        3         4

12 rows selected.
23 фев 11, 16:53    [10279080]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
grok
Member

Откуда:
Сообщений: 1686
Elic
partitioned outer join:
with org as (select level as id from dual connect by level <= 4)
     , t as (select level as id, level as org_id, 'x' as x from dual connect by level <= 3)
select * from org left join t partition by (t.id) on (t.org_id = org.id)
--  where t.org_id is null
;

       ID        ID        ORG_ID X
--------- --------- ------------- -
        1         1             1 x
        1         2
        1         3
        1         4
        2         1
        2         2             2 x
        2         3
        2         4
        3         1
        3         2
        3         3             3 x
        3         4

12 rows selected.


странное дело

если написать

select * from org left join t partition by (t.id) on (t.org_id = org.id)

то всё работает как надо
и видно четко где надо - t.org_id null и org.org_id не null

но

select * from org left join t partition by (t.id) on (t.org_id = org.id)
where t.org_id is null

не возвращает данных

не помогают никакие средства
ни подзапрос, ни подзапрос с nvl - ничего
25 фев 11, 14:36    [10290010]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
grok
Member

Откуда:
Сообщений: 1686
т.е. например

select*
from
(
select t.id, nvl(t.org_id,-1) t_org_id, org.org_id from org left join t partition by (t.id) on (t.org_id = org.id)
)
where t_org_id = -1

т.е. вот такой запрос данных не возвращает
при том что внутренний запрос отдельно данные возвращает и -1 там есть!
25 фев 11, 14:47    [10290091]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
grok,

план runtime посмотри
25 фев 11, 14:49    [10290111]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
grok,

select id, t_org_id, org_id
from
(
 select rownum r,
        t.id, 
        nvl(t.org_id,-1) t_org_id, 
        org.org_id 
 from org left join t partition by (t.id) on (t.org_id = org.id)
)
where t_org_id = -1

такой же эффект?
25 фев 11, 14:57    [10290185]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
Elic
Member

Откуда:
Сообщений: 29980
grok
не помогают никакие средства
Версия х.y.z.1.0?
25 фев 11, 15:04    [10290233]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
grok
Member

Откуда:
Сообщений: 1686
env
grok,

select id, t_org_id, org_id
from
(
 select rownum r,
        t.id, 
        nvl(t.org_id,-1) t_org_id, 
        org.org_id 
 from org left join t partition by (t.id) on (t.org_id = org.id)
)
where t_org_id = -1

такой же эффект?


так прокатило
25 фев 11, 15:04    [10290243]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
grok,

10289041
читать по ссылкам
25 фев 11, 15:08    [10290279]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
grok
Member

Откуда:
Сообщений: 1686
Elic
grok
не помогают никакие средства
Версия х.y.z.1.0?


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
25 фев 11, 15:10    [10290302]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
grok
Member

Откуда:
Сообщений: 1686
grok
env
grok,

select id, t_org_id, org_id
from
(
 select rownum r,
        t.id, 
        nvl(t.org_id,-1) t_org_id, 
        org.org_id 
 from org left join t partition by (t.id) on (t.org_id = org.id)
)
where t_org_id = -1

такой же эффект?


так прокатило


всё равно не ясно

select * from org left join t partition by (t.id) on (t.org_id = org.id)
where t.org_id is null

вот в этом запросе переписывать практически нечего
неужели он мешает on c where ???
25 фев 11, 15:14    [10290337]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
grok,

в sqlplus

set autotrace on 
select * 
from org left join t partition by (t.id) on (t.org_id = org.id)
 where t.org_id is null;

результат сюда.
25 фев 11, 15:17    [10290368]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
grok
Member

Откуда:
Сообщений: 1686
env
grok,

в sqlplus

set autotrace on 
select * 
from org left join t partition by (t.id) on (t.org_id = org.id)
 where t.org_id is null;

результат сюда.


автор
no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=91185 Card=1 Bytes=52)
1 0 VIEW (Cost=91185 Card=1 Bytes=52)
2 1 FILTER
3 2 MERGE JOIN (PARTITION OUTER) (Cost=91185 Card=1 Bytes=13)
4 3 SORT (JOIN) (Cost=2 Card=154 Bytes=616)
5 4 INDEX (FULL SCAN) OF 'ORG_U1' (INDEX (UNIQUE)) (Cost=1 Card=154 Bytes=616)
6 3 SORT (PARTITION JOIN) (Cost=2711 Card=363388 Bytes=3270492)
7 6 INDEX (FULL SCAN) OF 'T_U1' (INDEX (UNIQUE)) (Cost=1314 Card=363388 Bytes=3270492)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1330 consistent gets
1062 physical reads
0 redo size
236 bytes sent via SQL*Net to client
233 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed

имена объектов чуток изменены
25 фев 11, 15:25    [10290447]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
grok
Member

Откуда:
Сообщений: 1686
env
grok,

в sqlplus

set autotrace on 
select * 
from org left join t partition by (t.id) on (t.org_id = org.id)
 where t.org_id is null;

результат сюда.


сорри
я тока вместо *
написал t.id, t.org_id, org.org_id

оттого и индекс фуллскан
25 фев 11, 15:30    [10290495]     Ответить | Цитировать Сообщить модератору
 Re: сконструировать отсутствующие данные  [new]
grok
Member

Откуда:
Сообщений: 1686
о как!

если переписать запрос вот так


select /*+ FULL(t)*/ * 
from org left join t partition by (t.id) on (t.org_id = org.id)
 where t.org_id is null;

то теперь данные возвращаются
а план - точно такой же, тока TABLE FULL SCAN
25 фев 11, 17:16    [10291339]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить