Как сделать двойной SELECT из одной и той же таблицы?

Question

[max4107575]

В СУБД ORACLE нужно выбрать в таблице MY_TABLE все записи у которых COL_3 = 4107575, а затем в ней же выбрать все остальные записи у которых COL_1,COL_2 соответствуют тем, которые были выбраны.
Индексы в таблице созданы COL_1,COL_2 и COL_3.

делаю

SELECT * FROM MY_TABLE WHERE (COL_1,COL_2) IN
(SELECT COL_1 ,COL_2 FROM MY_TABLE WHERE COL_3 = 4107575)

И поиск происходит очень долго
При том что SELECT COL_1 AS COL_1_N,COL_2 AS COL_2_N FROM MY_TABLE WHERE COL_3 = 4107575 дает всего несколько строк.
Если выбрать одну строку

SELECT * FROM MY_TABLE WHERE (COL_1,COL_2) =
(SELECT COL_1,COL_2 FROM MY_TABLE WHERE COL_3 = 4107575 FETCH NEXT 1 ROWS ONLY)

, то поиск происходит мгновенно.
Пробовал

SELECT * FROM MY_TABLE,(SELECT COL_1 AS COL_1_N,COL_2 AS COL_2_N FROM MY_TABLE WHERE COL_3 = 4107575) WHERE COL_1=COL_1_N, COL_2=COL_2_N

Также бесконечно долго происходит выборка.

Comments

[Rsa97]

Попробуйте добавить составной индекс (COL1, COL2)

[max4107575]

Rsa97, он есть

[Rsa97]

max4107575, Именно составной индекс, а не два отдельных?

[max4107575]

Rsa97, да, именно составной, а не два отдельных. По ним мгновенно ищет.

[Rsa97]

max4107575, Тогда надо EXPLAIN смотреть.

[max4107575]

Rsa97, Что именно смотреть там? Я, кстати, с помощью FOR в скрипте достаточно быстро этот вопрос решаю, но надо именно SELECT. Думаю должно быть решение

[Rsa97]

max4107575, Смотреть план запроса, какие именно индексы используются, какие объёмы выбираемых данных и т.д.
Покажите результат запроса

EXPLAIN PLAN FOR
  SELECT * FROM MY_TABLE WHERE (COL_1,COL_2) IN
    (SELECT COL_1 ,COL_2 FROM MY_TABLE WHERE COL_3 = 4107575)

[max4107575]

Rsa97, не делал никогда такого. Пишет Explained. Больше ничего. Пользуюсь Oracle SQL Developer. Нашел о чем Вы говорите. Буду разбираться

Answer 1

[Виктор]

SELECT a.* FROM MY_TABLE a
INNER JOIN MY_TABLE b
ON a.COL_1 = b.COL_1 AND a.COL_2 = b.COL_2
WHERE b.COL_3 = 4107575

Comments

[max4107575]

Тоже бесконечно долго ищет. Таблица реально огромная, но COL_3 = 4107575 выполняется мгновенно, также как COL_1='значение' AND COL_2='значение'. Индексы так созданы.

[Виктор]

max4107575, почему бы тогда не доставать по частям, с пагинацией? Маркетплейсы же не отдают весь каталог сразу, например, и это не просто так.

[max4107575]

Виктор, нужно всё достать и подсчитать количество потом. Скриптом с помощью FOR очень быстро находит, так что дело не в объеме, а в логике.

[Виктор]

max4107575, зачем все доставать, если нужно количество? Тогда доставайте количество.

Скриптом с помощью FOR очень быстро находит

тогда может с памятью проблема, с объемом данных? Если скрипт не накапливает значения. Можно так же скриптом и количество получить.

[max4107575]

Виктор,

FOR a_row IN (SELECT COL_1 ,COL_2 FROM MY_TABLE WHERE COL_3 = 4107575) LOOP
    FOR b_row IN (SELECT * FROM MY_TABLE WHERE COL_1=a_row.COL_1,COL_2=a_row.COL_2) LOOP
        ....
    END LOOP;
END LOOP;

Вот такой скрипт быстро отрабатывает.
Скриптом можно, но надо запросом, думаю что решение есть.

[Виктор]

max4107575,

....

это что? Итоговый результат скрипта какой?

[max4107575]

Виктор, результат то что нужно. Но нужно тот же результат получить запросом.

[Виктор]

max4107575, вводных данных явно не достаточно.

[max4107575]

Виктор, Ну мне надо понять почему очень медленно работает

SELECT * FROM MY_TABLE WHERE (COL_1,COL_2) IN
(SELECT COL_1 ,COL_2 FROM MY_TABLE WHERE COL_3 = 4107575)

Учитывая что

FOR a_row IN (SELECT COL_1 ,COL_2 FROM MY_TABLE WHERE COL_3 = 4107575) LOOP
    FOR b_row IN (SELECT * FROM MY_TABLE WHERE COL_1=a_row.COL_1,COL_2=a_row.COL_2) LOOP

работает быстро.
Как переделать запрос чтобы он работал быстро?

[Виктор]

max4107575,

FOR b_row IN (SELECT * FROM MY_TABLE

ничего не выводит, с этими данными СУБД дальше работает как-то, соответственно запрос по-другому строится

Answer 2

[Константин Цветков]

ВЫБРАТЬ * ИЗ ТАБЛИЦА КАК Т1
  СВЯЗАТЬ ТАБЛИЦА КАК Т2 ПО Т2.К1 = Т1.К1 И Т2.К2 = Т1.К2
  ГДЕ Т2.К3 = 4107575

Comments

[max4107575]

С JOIN тоже ищет бесконечно долго.
Если скриптом делать в два подхода - тогда быстро, но нужно именно SELECT.

Answer 3

[AKimovd]

With q as (Select /*+ materialize */ COL_1 ,COL_2 FROM MY_TABLE WHERE COL_3 = 4107575) 
SELECT * FROM MY_TABLE WHERE (COL_1,COL_2) IN
(select * FROM q)