как можно оптимизировать запрос?

Есть три таблицы:

t1

a 1 2 3 4 5 6 7

t2

a b 2 1 2 2 2 3 3 4 3 5 4 3 4 5 7 2 7 4 7 5 8 6

t3

b c d e 1 6 10 first 2 8 5 second 3 8 3 third 4 2 4 fourth 5 2 6 fifth 6 8 5 sixth

Таблица t2 - развязка между t1 и t3
Запрос должен выдать все записи из t1 и, если есть, привязанные к ним записи из t3, но если к строке из t1 привязано несколько записей в t3, то из них должна отбираться одна запись по двум условиям:
1) сначала выбирается та запись, у которой значение "с" максимальное,
2) если и таких записей несколько, то выбирается та, у которой значение "d" максимальное.
Т.е. результат запроса должен быть таким:

a b c d e 1 2 2 8 5 second 3 5 2 6 fifth 4 3 8 3 third 5 6 7 2 8 5 second

Я получил необходимый результат, но уж очень мне не нравится, каким путем (что-то меня заклинило под вечер на LEFT JOIN-е).

WITH t1 AS
    (SELECT 1 a FROM DUAL
      UNION ALL SELECT 2 FROM DUAL
      UNION ALL SELECT 3 FROM DUAL
      UNION ALL SELECT 4 FROM DUAL
      UNION ALL SELECT 5 FROM DUAL
      UNION ALL SELECT 6 FROM DUAL
      UNION ALL SELECT 7 FROM DUAL),
     t2 AS
     (SELECT 2 a, 1 b FROM DUAL
       UNION ALL SELECT 2, 2 FROM DUAL
       UNION ALL SELECT 2, 3 FROM DUAL
       UNION ALL SELECT 3, 4 FROM DUAL
       UNION ALL SELECT 3, 5 FROM DUAL
       UNION ALL SELECT 4, 3 FROM DUAL
       UNION ALL SELECT 4, 5 FROM DUAL
       UNION ALL SELECT 7, 2 FROM DUAL
       UNION ALL SELECT 7, 4 FROM DUAL
       UNION ALL SELECT 7, 5 FROM DUAL
       UNION ALL SELECT 8, 6 FROM DUAL),
     t3 AS
     (
       SELECT 1 b, 6 c, 10 d, 'first' e FROM DUAL
       UNION ALL SELECT 2, 8, 5, 'second' FROM DUAL
       UNION ALL SELECT 3, 8, 3, 'third' FROM DUAL
       UNION ALL SELECT 4, 2, 4, 'fourth' FROM DUAL
       UNION ALL SELECT 5, 2, 6, 'fifth' FROM DUAL
       UNION ALL SELECT 6, 8, 5, 'sixth' FROM DUAL)

SELECT   *
    FROM (SELECT t1.a, t2.b, t6.c, t6.d, t3.e
            FROM (t1
                 LEFT JOIN
                 (t2 JOIN t3 ON (t2.b = t3.b))
                 ON (t1.a = t2.a))
                 LEFT JOIN
                 (SELECT t5.a, t5.f c, t5.g d
                    FROM (SELECT   t1.a, t4.f, MAX (t3.d) g
                              FROM (t1
                                   LEFT JOIN
                                   (t2 JOIN t3 ON (t2.b = t3.b))
                                   ON (t1.a = t2.a))
                                   LEFT JOIN
                                   (SELECT   t1.a, MAX (t3.c) AS f
                                        FROM t1
                                             LEFT JOIN
                                             (t2 JOIN t3 ON (t2.b = t3.b))
                                             ON (t1.a = t2.a)
                                    GROUP BY t1.a) t4 ON (t4.a = t1.a AND t4.f = t3.c)
                          GROUP BY t1.a, t4.f) t5
                   WHERE (t5.f IS NULL AND t5.g IS NULL) OR (t5.f IS NOT NULL AND t5.g IS NOT NULL)) t6
                 ON (t6.a = t1.a AND t6.c = t3.c AND t6.d = t3.d)
                 ) t7
   WHERE (t7.d IS NULL AND t7.e IS NULL) OR (t7.d IS NOT NULL AND t7.e IS NOT NULL)
ORDER BY 1

Как можно упростить мой запрос?

Если есть несколько путей решения, то было бы восхитительно заодно учесть, что в реальности табличка t1 - это запрос к большой табличке t0 (~500 млн. записей), и t1 на 4-5 порядков меньше, чем t2 и t3 ( t1 ~1 тыс. записей, t2 ~100 млн. записей, t3 ~30 млн. записей)

Не знаю, усложнит, ли решение задачи тот факт, что я не предусмотрел, что из t1 должны выбираться и другие столбцы (думаю, это ни на что не повлияет, но на всякий случай приведу табличку более подходящую табличку t1 и правильный результат запроса)
t1

a aa 1 one 2 two 3 three 4 four 5 five 6 six 7 seven

искомый результат:

a b c d e aa 1 one 2 2 8 5 second two 3 5 2 6 fifth three 4 3 8 3 third four 5 five 6 six 7 2 8 5 second seven

ну и соответствующий скрипт для получения всей этой гадости

WITH t1 AS
    (SELECT 1 a, 'one' aa FROM DUAL
      UNION ALL SELECT 2, 'two' FROM DUAL
      UNION ALL SELECT 3, 'three' FROM DUAL
      UNION ALL SELECT 4, 'four' FROM DUAL
      UNION ALL SELECT 5, 'five' FROM DUAL
      UNION ALL SELECT 6, 'six' FROM DUAL
      UNION ALL SELECT 7, 'seven' FROM DUAL),
     t2 AS
     (SELECT 2 a, 1 b FROM DUAL
       UNION ALL SELECT 2, 2 FROM DUAL
       UNION ALL SELECT 2, 3 FROM DUAL
       UNION ALL SELECT 3, 4 FROM DUAL
       UNION ALL SELECT 3, 5 FROM DUAL
       UNION ALL SELECT 4, 3 FROM DUAL
       UNION ALL SELECT 4, 5 FROM DUAL
       UNION ALL SELECT 7, 2 FROM DUAL
       UNION ALL SELECT 7, 4 FROM DUAL
       UNION ALL SELECT 7, 5 FROM DUAL
       UNION ALL SELECT 8, 6 FROM DUAL),
     t3 AS
     (
       SELECT 1 b, 6 c, 10 d, 'first' e FROM DUAL
       UNION ALL SELECT 2, 8, 5, 'second' FROM DUAL
       UNION ALL SELECT 3, 8, 3, 'third' FROM DUAL
       UNION ALL SELECT 4, 2, 4, 'fourth' FROM DUAL
       UNION ALL SELECT 5, 2, 6, 'fifth' FROM DUAL
       UNION ALL SELECT 6, 8, 5, 'sixth' FROM DUAL)

SELECT   *
    FROM (SELECT t1.a, t2.b, t6.c, t6.d, t3.e, t1.aa
            FROM (t1
                 LEFT JOIN
                 (t2 JOIN t3 ON (t2.b = t3.b))
                 ON (t1.a = t2.a))
                 LEFT JOIN
                 (SELECT t5.a, t5.f c, t5.g d
                    FROM (SELECT   t1.a, t4.f, MAX (t3.d) g
                              FROM (t1
                                   LEFT JOIN
                                   (t2 JOIN t3 ON (t2.b = t3.b))
                                   ON (t1.a = t2.a))
                                   LEFT JOIN
                                   (SELECT   t1.a, MAX (t3.c) AS f
                                        FROM t1
                                             LEFT JOIN
                                             (t2 JOIN t3 ON (t2.b = t3.b))
                                             ON (t1.a = t2.a)
                                    GROUP BY t1.a) t4 ON (t4.a = t1.a AND t4.f = t3.c)
                          GROUP BY t1.a, t4.f) t5
                   WHERE (t5.f IS NULL AND t5.g IS NULL) OR (t5.f IS NOT NULL AND t5.g IS NOT NULL)) t6
                 ON (t6.a = t1.a AND t6.c = t3.c AND t6.d = t3.d)
                 ) t7
   WHERE (t7.d IS NULL AND t7.e IS NULL) OR (t7.d IS NOT NULL AND t7.e IS NOT NULL)
ORDER BY 1

Посоветуйте, как быть?

Способ 1: работает если t3(b,c,d)-PK тоесть уникальны. Если нет, то использовать можно второй вариант, который возьемт первую попавшуюся запись

WITH t1 AS
    (SELECT 1 a FROM DUAL
      UNION ALL SELECT 2 FROM DUAL
      UNION ALL SELECT 3 FROM DUAL
      UNION ALL SELECT 4 FROM DUAL
      UNION ALL SELECT 5 FROM DUAL
      UNION ALL SELECT 6 FROM DUAL
      UNION ALL SELECT 7 FROM DUAL), 
     t2 AS
     (SELECT 2 a, 1 b FROM DUAL
       UNION ALL SELECT 2, 2 FROM DUAL
       UNION ALL SELECT 2, 3 FROM DUAL
       UNION ALL SELECT 3, 4 FROM DUAL
       UNION ALL SELECT 3, 5 FROM DUAL
       UNION ALL SELECT 4, 3 FROM DUAL
       UNION ALL SELECT 4, 5 FROM DUAL
       UNION ALL SELECT 7, 2 FROM DUAL
       UNION ALL SELECT 7, 4 FROM DUAL
       UNION ALL SELECT 7, 5 FROM DUAL
       UNION ALL SELECT 8, 6 FROM DUAL),
     t3 AS
     (
       SELECT 1 b, 6 c, 10 d, 'first' e FROM DUAL
       UNION ALL SELECT 2, 8, 5, 'second' FROM DUAL
       UNION ALL SELECT 3, 8, 3, 'third' FROM DUAL
       UNION ALL SELECT 4, 2, 4, 'fourth' FROM DUAL
       UNION ALL SELECT 5, 2, 6, 'fifth' FROM DUAL
       UNION ALL SELECT 6, 8, 5, 'sixth' FROM DUAL)
SELECT t1.a,
       MAX(t.b) KEEP (DENSE_RANK FIRST ORDER BY t.c desc, t.d desc) b,
       MAX(t.c) KEEP (DENSE_RANK FIRST ORDER BY t.c desc, t.d desc) c,
       MAX(t.d) KEEP (DENSE_RANK FIRST ORDER BY t.c desc, t.d desc) d,
       MAX(t.e) KEEP (DENSE_RANK FIRST ORDER BY t.c desc, t.d desc) e
  FROM t1,
       (SELECT t2.a, t3.b, t3.c, t3.d, t3.e
          FROM t2, t3
         WHERE t2.b = t3.b) t
         WHERE t1.a = t.a(+)
GROUP BY t1.a         
ORDER BY t1.a

Способ 2: если t3(b,c,d) не уникальны

WITH t1 AS
    (SELECT 1 a FROM DUAL
      UNION ALL SELECT 2 FROM DUAL
      UNION ALL SELECT 3 FROM DUAL
      UNION ALL SELECT 4 FROM DUAL
      UNION ALL SELECT 5 FROM DUAL
      UNION ALL SELECT 6 FROM DUAL
      UNION ALL SELECT 7 FROM DUAL), 
     t2 AS
     (SELECT 2 a, 1 b FROM DUAL
       UNION ALL SELECT 2, 2 FROM DUAL
       UNION ALL SELECT 2, 3 FROM DUAL
       UNION ALL SELECT 3, 4 FROM DUAL
       UNION ALL SELECT 3, 5 FROM DUAL
       UNION ALL SELECT 4, 3 FROM DUAL
       UNION ALL SELECT 4, 5 FROM DUAL
       UNION ALL SELECT 7, 2 FROM DUAL
       UNION ALL SELECT 7, 4 FROM DUAL
       UNION ALL SELECT 7, 5 FROM DUAL
       UNION ALL SELECT 8, 6 FROM DUAL),
     t3 AS
     (
       SELECT 1 b, 6 c, 10 d, 'first' e FROM DUAL
       UNION ALL SELECT 2, 8, 5, 'second' FROM DUAL
       UNION ALL SELECT 3, 8, 3, 'third' FROM DUAL
       UNION ALL SELECT 4, 2, 4, 'fourth' FROM DUAL
       UNION ALL SELECT 5, 2, 6, 'fifth' FROM DUAL
       UNION ALL SELECT 6, 8, 5, 'sixth' FROM DUAL)
SELECT a, b, c, d, e 
  FROM ( SELECT t1.a,
                t.b,
                t.c,
                t.d,
                t.e, 
                ROW_NUMBER() OVER (PARTITION BY t1.a ORDER BY t.c desc, t.d desc) rn 
          FROM t1,
               (SELECT t2.a, t3.b, t3.c, t3.d, t3.e
                  FROM t2, t3
                 WHERE t2.b = t3.b) t
                 WHERE t1.a = t.a(+))
 WHERE rn = 1
ORDER BY a    

вот какой-то набросок.
формально он проще.
на реальных данных может оказаться хуже.
результат более-менее похож.

WITH  t1 AS
    (SELECT 1 a, 'one' aa FROM DUAL
      UNION ALL SELECT 2, 'two' FROM DUAL
      UNION ALL SELECT 3, 'three' FROM DUAL
      UNION ALL SELECT 4, 'four' FROM DUAL
      UNION ALL SELECT 5, 'five' FROM DUAL
      UNION ALL SELECT 6, 'six' FROM DUAL
      UNION ALL SELECT 7, 'seven' FROM DUAL), 
     t2 AS
     (SELECT 2 a, 1 b FROM DUAL
       UNION ALL SELECT 2, 2 FROM DUAL
       UNION ALL SELECT 2, 3 FROM DUAL
       UNION ALL SELECT 3, 4 FROM DUAL
       UNION ALL SELECT 3, 5 FROM DUAL
       UNION ALL SELECT 4, 3 FROM DUAL
       UNION ALL SELECT 4, 5 FROM DUAL
       UNION ALL SELECT 7, 2 FROM DUAL
       UNION ALL SELECT 7, 4 FROM DUAL
       UNION ALL SELECT 7, 5 FROM DUAL
       UNION ALL SELECT 8, 6 FROM DUAL),
     t3 AS
     (
       SELECT 1 b, 6 c, 10 d, 'first' e FROM DUAL
       UNION ALL SELECT 2, 8, 5, 'second' FROM DUAL
       UNION ALL SELECT 3, 8, 3, 'third' FROM DUAL
       UNION ALL SELECT 4, 2, 4, 'fourth' FROM DUAL
       UNION ALL SELECT 5, 2, 6, 'fifth' FROM DUAL
       UNION ALL SELECT 6, 8, 5, 'sixth' FROM DUAL)
     ,t4 as (       
Select t2.a,t2.b , t3.c,t3.d, row_number() over(partition by t2.a order by  t2.a,t3.c desc,t3.d desc) rn  From t2 inner join t3 on t3.b = t2.b
)

Select t1.a, t2.b,t3.c,t3.d,t3.e
From t1 left join  t2 on t2.a = t1.a
      left join t3 on t3.b = t2.b
Where 
((t2.a, t3.c,t3.d) in (Select t4.a,t4.c,t4.d from t4 where t4.rn <=1) or t2.a is null)
order by t1.a

destructor_Петя, если Вам только текст упростить, то тут вариантов штук несколько есть, вот первый пришедший в голову:

select t1.a
     , max(t23.b) keep (dense_rank first order by t23.c desc nulls last, t23.d desc nulls last) b
     , max(t23.c) keep (dense_rank first order by t23.c desc nulls last, t23.d desc nulls last) c
     , max(t23.d) keep (dense_rank first order by t23.c desc nulls last, t23.d desc nulls last) d
     , max(t23.e) keep (dense_rank first order by t23.c desc nulls last, t23.d desc nulls last) e
     , max(t1.aa) keep (dense_rank first order by t23.c desc nulls last, t23.d desc nulls last) aa
from t1 t1
   , (select t2.a, t2.b, t3.c, t3.d, t3.e
        from t2,t3 
       where t2.b = t3.b
     ) t23
where t23.a(+) = t1.a
group by t1.a
order by 1
;

а если по-взрослому думать, то недостаточно информации.
- описания таблиц с ограничениями целостности (к примеру, null/not null по колонкам иногда играет существенную роль, uniq/pk - еще чаще)
- существующие индексы
- схема секционирования
- распределение данных
- частота и характер изменений данных, требования к актуальности (на предмет matview)
- конкуренция (один на базе/OLTP 24x7)
- возможность применения PL-кода (в Вашем случае перспективная тема для отбора записей t3)
...и т.д. и т.п.

вот еще упрощение на тему row_number
кажется, - он полностью экивалентен 2му варианту _Alex_SMIRNOV_

WITH  t1 AS
    (SELECT 1 a, 'one' aa FROM DUAL
      UNION ALL SELECT 2, 'two' FROM DUAL
      UNION ALL SELECT 3, 'three' FROM DUAL
      UNION ALL SELECT 4, 'four' FROM DUAL
      UNION ALL SELECT 5, 'five' FROM DUAL
      UNION ALL SELECT 6, 'six' FROM DUAL
      UNION ALL SELECT 7, 'seven' FROM DUAL), 
     t2 AS
     (SELECT 2 a, 1 b FROM DUAL
       UNION ALL SELECT 2, 2 FROM DUAL
       UNION ALL SELECT 2, 3 FROM DUAL
       UNION ALL SELECT 3, 4 FROM DUAL
       UNION ALL SELECT 3, 5 FROM DUAL
       UNION ALL SELECT 4, 3 FROM DUAL
       UNION ALL SELECT 4, 5 FROM DUAL
       UNION ALL SELECT 7, 2 FROM DUAL
       UNION ALL SELECT 7, 4 FROM DUAL
       UNION ALL SELECT 7, 5 FROM DUAL
       UNION ALL SELECT 8, 6 FROM DUAL),
     t3 AS
     (
       SELECT 1 b, 6 c, 10 d, 'first' e FROM DUAL
       UNION ALL SELECT 2, 8, 5, 'second' FROM DUAL
       UNION ALL SELECT 3, 8, 3, 'third' FROM DUAL
       UNION ALL SELECT 4, 2, 4, 'fourth' FROM DUAL
       UNION ALL SELECT 5, 2, 6, 'fifth' FROM DUAL
       UNION ALL SELECT 6, 8, 5, 'sixth' FROM DUAL)   

Select t1.a, t4.b,t4.c,t4.d,t4.e
From t1 left join ( Select t2.a,t2.b, t3.c,t3.d, t3.e,
                           row_number() over(partition by t2.a order by  t3.c desc ,t3.d desc ) rn  
                        From t2 Inner Join t3 on t3.b = t2.b) t4 on t4.a = t1.a
Where 
  nvl( t4.rn,1) = 1

хреновый в.,

зачем

Where 
  nvl( t4.rn,1) = 1

логично рассматривать rn=1 как условие outer join'а - тогда ему самое место в ON и nvl не нужен.

Не ожидал, что так быстро откликнетесь, спасибо всем, приятно удивлен!

Мне понравились решения _Alex_SMIRNOV_, andrey_anonymous, и хреновый в. (планы выполнения показывают в полтора раза меньшую стоимость, чем в моем варианте и, самое главное, жирный запрос t1 выполняется однократно). Наверное, попробую копать в сторону DENSE_RANK.

andrey_anonymous, осознаю, мне еще надо поучиться задавать вопросы правильно...
как и большинству новичков, задачу мне не удалось описать полностью с первых попыток, забыл/опустил существенные детали. (Например, забыл в постановке сказать, что данные в t1 не уникальны, это несколько усложняет задачку.)

Вообще, заморочек в этом зоопарке хватает..
Итак, в расширяющихся рамках задачи, в порядке поступивших замечаний:
1) ограничения целостности:
t1: все столбцы not null, есть еще столбец x
t2: все столбцы not null, тоже есть x not null (по нему идет partition, об этом далее)
t3: pk(b), тоже есть x not null.
2) существующие индексы:
t1: в табличке t0, на которой построен запрос, есть unique not partitioned index on a (в результате запроса t1.a уже не уникально)
t2: nonunique partitioned index on (a, b)
t3: unique not partitioned index on b,
nonunique partitioned index on c,
nonunique partitioned index on d.
3) схема секционирования:
все таблички (t0,t2,t3) - секционированы по значениям х. Этот х присутствует и в результате запроса t1.
4) данные во всех табличках по секциям распределены равномерно, запрос t1 выполняется либо по всем секциям, либо по одной выбранной секции.
5) данные во все таблички добавляются постоянно в режиме 24Х7, изменяться могут только в t3, все, что добавилось/изменилось надо видеть, т.е. matview, наверное, проблематично.
6) конкуренция - OLTP 24х7, большинство пользователей - писатели (залез, пару строк добавил, ушел).
7) pl код вполне возможен, просто с базовых курсов отложилось чье-то утверждение "все, что можно решить на sql, надо решать на sql, остальное - на pl/sql" и чаще всего именно sql решения в моей практике работали быстрее. Но вот в данной ситуации мое SQL решение мне очень не понравилось и хотел переходить к pl/sql. Этот топик, так сказать, попытка дать шанс решению на SQL :-).

8) и т.д и т.п.:

Чтобы не слишком заморачиваться, для упрощения можно считать, что запрос t1 выполнен по одному значению х. Тогда новые исходные данные:
t1

x a aa 1 1 one 1 2 two 1 2 another_two 1 3 three 1 4 four 1 4 four 1 5 five 1 5 another_five 1 6 six 1 7 seven

t2

x a b 1 9 3 1 2 1 1 2 2 1 2 3 1 3 4 1 3 5 1 4 3 1 4 5 1 7 2 1 7 4 1 7 5 3 8 7

t3

x b c d e 1 1 6 10 first 1 2 8 5 second 1 3 8 3 third 1 4 2 4 fourth 1 5 2 6 fifth 1 6 8 5 sixth 3 7 3 8 seventh

искомый результат

x a b c d e aa 1 1 one 1 2 2 8 5 second two 1 2 2 8 5 second another_two 1 3 5 2 6 fifth three 1 4 3 8 3 third four 1 4 3 8 3 third four 1 5 five 1 5 another_five 1 6 six 1 7 2 8 5 second seven

скрипт этих исходных данных

WITH t1 AS
    (SELECT 1 x, 1 a, 'one' aa FROM DUAL
      UNION ALL SELECT 1, 2, 'two' FROM DUAL
      UNION ALL SELECT 1, 2, 'another_two' FROM DUAL
      UNION ALL SELECT 1, 3, 'three' FROM DUAL
      UNION ALL SELECT 1, 4, 'four' FROM DUAL
      UNION ALL SELECT 1, 4, 'four' FROM DUAL
      UNION ALL SELECT 1, 5, 'five' FROM DUAL
      UNION ALL SELECT 1, 5, 'another_five' FROM DUAL
      UNION ALL SELECT 1, 6, 'six' FROM DUAL
      UNION ALL SELECT 1, 7, 'seven' FROM DUAL), 
     t2 AS
     (SELECT 1 x, 9 a, 3 b FROM DUAL
       UNION ALL SELECT 1, 2, 1 FROM DUAL
       UNION ALL SELECT 1, 2, 2 FROM DUAL
       UNION ALL SELECT 1, 2, 3 FROM DUAL
       UNION ALL SELECT 1, 3, 4 FROM DUAL
       UNION ALL SELECT 1, 3, 5 FROM DUAL
       UNION ALL SELECT 1, 4, 3 FROM DUAL
       UNION ALL SELECT 1, 4, 5 FROM DUAL
       UNION ALL SELECT 1, 7, 2 FROM DUAL
       UNION ALL SELECT 1, 7, 4 FROM DUAL
       UNION ALL SELECT 1, 7, 5 FROM DUAL
       UNION ALL SELECT 3, 8, 7 FROM DUAL),
     t3 AS
     (
       SELECT 1 x, 1 b, 6 c, 10 d, 'first' e FROM DUAL
       UNION ALL SELECT 1, 2, 8, 5, 'second' FROM DUAL
       UNION ALL SELECT 1, 3, 8, 3, 'third' FROM DUAL
       UNION ALL SELECT 1, 4, 2, 4, 'fourth' FROM DUAL
       UNION ALL SELECT 1, 5, 2, 6, 'fifth' FROM DUAL
       UNION ALL SELECT 1, 6, 8, 5, 'sixth' FROM DUAL
       UNION ALL SELECT 3, 7, 3, 8, 'seventh' FROM DUAL)