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

Откуда:
Сообщений: 7712
+ DDL

create table TestTableMaxValues
(
   id number not null constraint pk_TestTableMaxValues primary key,
   val nvarchar2(255)
);

create table TestTableMaxPrimary
(
   id number not null constraint pk_TestTableMaxPrimary primary key,
   val nvarchar2(255)
);

create table TestTableMaxSecondary
(
   id number not null constraint pk_TestTableMaxSecondary primary key,
   parent_id number not null,
   dt date not null,
   value_id number not null,
   constraint fk_TestTableMaxPri_Sec foreign key (parent_id) references TestTableMaxPrimary (id),
   constraint fk_TestTableMaxVal_Sec foreign key (value_id) references TestTableMaxValues (id)
);

create index idx_TestTableMaxSecondary on TestTableMaxSecondary (parent_id, dt);

Родил
#1
select
  p.*,
  s.*,
  v.*
from
  TestTableMaxPrimary p
  join TestTableMaxSecondary s on (s.parent_id=p.id)
  join TestTableMaxValues v on (v.id=s.value_id)
where
  (s.parent_id, s.dt) in (select
                            sg.parent_id,
                            max(sg.dt)
                          from
                            TestTableMaxSecondary sg
                          where
                            (sg.parent_id=s.parent_id)
                          group by sg.parent_id
                         )
order by p.id;
#2
select
  p.*,
  s.*,
  v.*
from
  TestTableMaxPrimary p
  join TestTableMaxSecondary s on (s.parent_id=p.id)
  join TestTableMaxValues v on (v.id=s.value_id)
where
  s.dt >= all (select
                 sg.dt
               from
                 TestTableMaxSecondary sg
               where
                 (sg.parent_id=s.parent_id)
              )
order by p.id;
Мо, сие можно пооптимальнее организовать?

P.S. А как в PL/SQL Developer'е можно план запроса посмотреть?
_________________
"Helo, word!" - 17 errors 56 warnings
11 июн 10, 10:56    [8929172]     Ответить | Цитировать Сообщить модератору
 Re: join с учетом max()  [new]
env
Member

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

File->New->Explain plan window ни о чём не говорит?
11 июн 10, 11:07    [8929265]     Ответить | Цитировать Сообщить модератору
 Re: join с учетом max()  [new]
Добрый Э - Эх
Guest
Ex_Soft
P.S. А как в PL/SQL Developer'е можно план запроса посмотреть?
F5
11 июн 10, 11:07    [8929270]     Ответить | Цитировать Сообщить модератору
 Re: join с учетом max()  [new]
Ex_Soft
Member

Откуда:
Сообщений: 7712
ПыСы - удовлетворили Картинка с другого сайта. THNX

А что насчет запросов/их оптимизации?
11 июн 10, 11:12    [8929316]     Ответить | Цитировать Сообщить модератору
 Re: join с учетом max()  [new]
Добрый Э - Эх
Guest
Как варианты:
1) not exists или left join,
2) join таблицы с inline-view, содержащей нужный агрегированный запрос
11 июн 10, 11:20    [8929389]     Ответить | Цитировать Сообщить модератору
 Re: join с учетом max()  [new]
Добрый Э - Эх
Guest
На худой конец - ROW_NUMBER() с последующей фильтрацией на внешнем уровне вложенности запроса.
11 июн 10, 11:21    [8929402]     Ответить | Цитировать Сообщить модератору
 Re: join с учетом max()  [new]
Добрый Э - Эх
Guest
Собственно, наиболее часто используемые решения данной задачи представлены тут: тынц
11 июн 10, 11:40    [8929576]     Ответить | Цитировать Сообщить модератору
 Re: join с учетом max()  [new]
Ex_Soft
Member

Откуда:
Сообщений: 7712
Добрый Э - Эх
Собственно, наиболее часто используемые решения данной задачи представлены тут: тынц

Поскольку
#3
select
  p.*,
  s.*,
  v.*
from
  TestTableMaxPrimary p
  join TestTableMaxSecondary s on (s.parent_id=p.id)
  join TestTableMaxValues v on (v.id=s.value_id)
where
  (s.parent_id, s.dt) in (select
                            sg.parent_id,
                            sg.dt
                          from
                            TestTableMaxSecondary sg
                          where
                            (sg.parent_id=s.parent_id)
                            and (rownum<2)
                          order by sg.parent_id, sg.dt 
                         )
order by p.id;
не катит, я так понял:
https://www.sql.ru/forum/actualthread.aspx?tid=580229#5985168

1)
select *
  from (
         select t.*, row_number() 
                           over(partition by num 
                                    order by dt desc) as rn
           from <table_name> t
       )
 where rn = 1;

делает приблизительно аналогичное? Как бы скрестить #3 с Вашим №1?

BTW, а по планам:
+ #1

DescriptionObject owner Object name Cost Cardinality Bytes
SELECT STATEMENT GOAL = ALL_ROWS 19 4 1172
SORT ORDER BY 19 4 1172
FILTER
NESTED LOOPS 3 4 1172
NESTED LOOPS 2 1 23
TABLE ACCESS FULL ASPNETUSER TESTTABLEMAXSECONDARY 1 1 17
TABLE ACCESS BY INDEX ROWID ASPNETUSER TESTTABLEMAXPRIMARY 1 1 6
INDEX UNIQUE SCAN ASPNETUSER PK_TESTTABLEMAXPRIMARY 1 1
TABLE ACCESS BY INDEX ROWID ASPNETUSER TESTTABLEMAXVALUES 1 4 1080
INDEX UNIQUE SCAN ASPNETUSER PK_TESTTABLEMAXVALUES 1 1
FILTER
SORT GROUP BY NOSORT 1 1 11
TABLE ACCESS FULL ASPNETUSER TESTTABLEMAXSECONDARY 1 1 11


+ #2

DescriptionObject owner Object name Cost Cardinality Bytes
SELECT STATEMENT GOAL = ALL_ROWS 19 4 1172
SORT ORDER BY 19 4 1172
FILTER
NESTED LOOPS 3 4 1172
NESTED LOOPS 2 1 23
TABLE ACCESS FULL ASPNETUSER TESTTABLEMAXSECONDARY 1 1 17
TABLE ACCESS BY INDEX ROWID ASPNETUSER TESTTABLEMAXPRIMARY 1 1 6
INDEX UNIQUE SCAN ASPNETUSER PK_TESTTABLEMAXPRIMARY 1 1
TABLE ACCESS BY INDEX ROWID ASPNETUSER TESTTABLEMAXVALUES 1 4 1080
INDEX UNIQUE SCAN ASPNETUSER PK_TESTTABLEMAXVALUES 1 1
INDEX RANGE SCAN ASPNETUSER IDX_TESTTABLEMAXSECONDARY 1 1 11


IMHO, #2 получче (idx_TestTableMaxSecondary) будет?
11 июн 10, 12:53    [8930450]     Ответить | Цитировать Сообщить модератору
 Re: join с учетом max()  [new]
Добрый Э - Эх
Guest
Ex_Soft
Как бы скрестить #3 с Вашим №1?

select p.*, s.*, v.*
  from TestTableMaxPrimary p
  join (
         select t.*, 
                row_number() 
                      over(partition by parent_id 
                               order by dt desc) as rn 
           from TestTableMaxSecondary t
       ) s 
    on (s.parent_id=p.id)
   and s.rn = 1
  join TestTableMaxValues v
    on (v.id=s.value_id)
 order by p.id;
11 июн 10, 13:09    [8930639]     Ответить | Цитировать Сообщить модератору
 Re: join с учетом max()  [new]
Ex_Soft
Member

Откуда:
Сообщений: 7712
Добрый Э - Эх, THNX

+ Правда план у нее получился, IMHO, не того...

Description Object owner Object name Cost Cardinality Bytes
SELECT STATEMENT GOAL = CHOOSE 35 29 9483
NESTED LOOPS 35 29 9483
MERGE JOIN 33 7 399
TABLE ACCESS BY INDEX ROWID ASPNETUSER TESTTABLEMAXPRIMARY 1 5 30
INDEX FULL SCAN ASPNETUSER PK_TESTTABLEMAXPRIMARY 1 5
SORT JOIN 32 7 357
VIEW ASPNETUSER 17 7 357
WINDOW SORT PUSHED RANK 17 7 119
TABLE ACCESS FULL ASPNETUSER TESTTABLEMAXSECONDARY 1 7 119
TABLE ACCESS BY INDEX ROWID ASPNETUSER TESTTABLEMAXVALUES 1 4 1080
INDEX UNIQUE SCAN ASPNETUSER PK_TESTTABLEMAXVALUES 1 1

11 июн 10, 13:45    [8930957]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить