Не работает индекс при соединении union / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Не работает индекс при соединении union

13 сообщений из 13, страница 1 из 1

Не работает индекс при соединении union

#40120955

Loky1987

Гость

Подскажите пожалуйста
Есть таблицы
table1(id number, amount number);
table2(id number, amount number);
table3 (id number, table_id number);

Есть индексы для table1 и table2

create unique index pk_table1 on table1(id);
create unique index pk_table2 on table2(id);

Почему следующий запрос не использует индексы pk_table1 и pk_table2 а прогоняет все записи таблиц table1 и table2 TABLE_ACCESS_FULL

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.

select  t.amount
from 
(
SELECT * FROM table1 
union
SELECT * FROM table2
 ) t
where t.id = 
(select max(table_id) from table3 where id = 1)

При чем если вместо подзапроса (select max(table_id) from table3 where id = 1) указать конкретное значение, то индексы прекрасно используются. В чем подвох?

...

Рейтинг:

0 / 0

17.12.2021, 11:14

| Ответить | Цитировать | Написать

Не работает индекс при соединении union

#40120988

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

Loky1987,

если заменить на union ALL , тож индекс не используется?

.....
stax

...

Рейтинг:

0 / 0

17.12.2021, 12:34

| Ответить | Цитировать | Написать

Не работает индекс при соединении union

#40120991

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

Loky1987

В чем подвох?

В "проталкивании" предикатов.
Попробуйте

Код: plsql

1.
2.
3.
4.
5.
6.
7.

select  t.amount
from 
(
SELECT * FROM table1 where id = (select max(table_id) from table3 where id = 1)
union
SELECT * FROM table2 where id = (select max(table_id) from table3 where id = 1)
 ) t

...

Рейтинг:

0 / 0

17.12.2021, 12:43

| Ответить | Цитировать | Написать

Не работает индекс при соединении union

#40121024

Loky1987

Гость

Stax,

Пробовал не спасает

Нашел статью https://docs.oracle.com/cd/B10501_01/server.920/a96533/opt_ops.htm#1005867]

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

...

Рейтинг:

0 / 0

17.12.2021, 13:47

| Ответить | Цитировать | Написать

Не работает индекс при соединении union

#40121033

Loky1987

Гость

andrey_anonymous,

Да, так работает, но мне такой вариант не подходит, так выборка с юнионом это вьюха на самом деле

Код: plsql

1.
2.
3.
4.

CREATE OR REPLACE VIEW V_table_n AS 
SELECT * FROM table1 
union
SELECT * FROM table2;

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

...

Рейтинг:

0 / 0

17.12.2021, 14:02

| Ответить | Цитировать | Написать

Не работает индекс при соединении union

#40121035

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

Loky1987,

Нет, конечно, есть разные варианты: переписать через джойн или прохинтовать так чтобы преобразовалось в джойн, или переписать через in и прохинтовать через precompute_subquery

...

Рейтинг:

0 / 0

17.12.2021, 14:04

| Ответить | Цитировать | Написать

Не работает индекс при соединении union

#40121037

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

Loky1987

И эта вьюха много где используется,

union действительно нужен без ALL? ето ж дистинкт на "Таблицы огромные"

.....
stax

...

Рейтинг:

0 / 0

17.12.2021, 14:06

| Ответить | Цитировать | Написать

Не работает индекс при соединении union

#40121172

SeaGate

Участник

Откуда: Новосибирск

Сообщения: 1 744

Рейтинг: 2 / 0

Loky1987,

Если table3.id уникально, то:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.

SQL> create unique index pk_table3 on table3(id);

Index created.
SQL> explain plan for
  2  select     t.amount
  3  from
  4  (
  5  SELECT * FROM table1
  6  union
  7  SELECT * FROM table2
  8   ) t
  9  where t.id =
 10  (select table_id from table3 where id = 1);

Explained.

SQL>
SQL> select * from dbms_xplan.display(format=>'outline');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 84331562

---------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |           |     1 |    41 |     0   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                   |           |     1 |    41 |     0   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | TABLE3    |     1 |    26 |     0   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN            | PK_TABLE3 |     1 |       |     0   (0)| 00:00:01 |
|   4 |   VIEW                          |           |     1 |    15 |     0   (0)| 00:00:01 |
|   5 |    SORT UNIQUE                  |           |     2 |    52 |     0   (0)| 00:00:01 |
|   6 |     UNION ALL PUSHED PREDICATE  |           |       |       |            |          |
|   7 |      TABLE ACCESS BY INDEX ROWID| TABLE1    |     1 |    26 |     0   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN         | PK_TABLE1 |     1 |       |     0   (0)| 00:00:01 |
|   9 |      TABLE ACCESS BY INDEX ROWID| TABLE2    |     1 |    26 |     0   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN         | PK_TABLE2 |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$639F1A6F" "TABLE1"@"SEL$2" ("TABLE1"."ID"))
      INDEX_RS_ASC(@"SEL$B01C6807" "TABLE2"@"SEL$3" ("TABLE2"."ID"))
      USE_NL(@"SEL$8B13E788" "T"@"SEL$1")
      LEADING(@"SEL$8B13E788" "TABLE3"@"SEL$4" "T"@"SEL$1")
      NO_ACCESS(@"SEL$8B13E788" "T"@"SEL$1")
      INDEX_RS_ASC(@"SEL$8B13E788" "TABLE3"@"SEL$4" ("TABLE3"."ID"))
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$04A8DF8C")
      OUTLINE(@"SEL$8F9407EC")
      UNNEST(@"SEL$4")
      OUTLINE(@"SEL$2E82301F")
      OUTLINE(@"SET$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$8F9407EC" >"SEL$2E82301F")
      OUTLINE_LEAF(@"SEL$8B13E788")
      PUSH_PRED(@"SEL$8B13E788" "T"@"SEL$1" 1)
      OUTLINE_LEAF(@"SET$5715CE2E")
      OUTLINE_LEAF(@"SEL$B01C6807")
      OUTLINE_LEAF(@"SEL$639F1A6F")
      ALL_ROWS
      DB_VERSION('21.1.0')
      OPTIMIZER_FEATURES_ENABLE('21.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"=1)
   8 - access("TABLE1"."ID"="TABLE_ID")
  10 - access("TABLE2"."ID"="TABLE_ID")

Если table3.id не уникально, то:

Код: plsql

SQL> create materialized view mv3
  2  enable query rewrite
  3  as
  4  select id, max(table_id) tid
  5    from table3
  6   group by id;

Materialized view created.

SQL>
SQL> create unique index pk_mv3 on mv3(id);

Index created.

SQL>
SQL> explain plan for
  2  select     t.amount
  3  from
  4  (
  5  SELECT * FROM table1
  6  union
  7  SELECT * FROM table2
  8   ) t
  9  where t.id =
 10  (select max(table_id) from table3 where id = 1 group by id);

Explained.

SQL>
SQL> select * from dbms_xplan.display(format=>'outline');

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1980969663

-----------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |           |     1 |    41 |     0   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                           |           |     1 |    41 |     0   (0)| 00:00:01 |
|   2 |   MAT_VIEW REWRITE ACCESS BY INDEX ROWID| MV3       |     1 |    26 |     0   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                    | PK_MV3    |     1 |       |     0   (0)| 00:00:01 |
|   4 |   VIEW                                  |           |     1 |    15 |     0   (0)| 00:00:01 |
|   5 |    SORT UNIQUE                          |           |     2 |    52 |     0   (0)| 00:00:01 |
|   6 |     UNION ALL PUSHED PREDICATE          |           |       |       |            |          |
|   7 |      TABLE ACCESS BY INDEX ROWID        | TABLE1    |     1 |    26 |     0   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN                 | PK_TABLE1 |     1 |       |     0   (0)| 00:00:01 |
|   9 |      TABLE ACCESS BY INDEX ROWID        | TABLE2    |     1 |    26 |     0   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN                 | PK_TABLE2 |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$639F1A6F" "TABLE1"@"SEL$2" ("TABLE1"."ID"))
      INDEX_RS_ASC(@"SEL$B01C6807" "TABLE2"@"SEL$3" ("TABLE2"."ID"))
      USE_NL(@"SEL$A0FFABC2" "T"@"SEL$1")
      LEADING(@"SEL$A0FFABC2" "MV3"@"SEL$D0C90877" "T"@"SEL$1")
      NO_ACCESS(@"SEL$A0FFABC2" "T"@"SEL$1")
      INDEX_RS_ASC(@"SEL$A0FFABC2" "MV3"@"SEL$D0C90877" ("MV3"."ID"))
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$1")
      REWRITE(@"SEL$4" "MV3")
      OUTLINE(@"SEL$EE0CAC36")
      OUTLINE(@"SEL$50496432")
      OUTLINE(@"SEL$5E71963D")
      UNNEST(@"SEL$EE0CAC36")
      OUTLINE(@"SEL$C73070B2")
      OUTLINE(@"SET$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$5E71963D" >"SEL$C73070B2")
      OUTLINE_LEAF(@"SEL$A0FFABC2")
      PUSH_PRED(@"SEL$A0FFABC2" "T"@"SEL$1" 1)
      OUTLINE_LEAF(@"SET$5715CE2E")
      OUTLINE_LEAF(@"SEL$B01C6807")
      OUTLINE_LEAF(@"SEL$639F1A6F")
      ALL_ROWS
      DB_VERSION('21.1.0')
      OPTIMIZER_FEATURES_ENABLE('21.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("MV3"."ID"=1)
   8 - access("TABLE1"."ID"="MV3"."TID")
  10 - access("TABLE2"."ID"="MV3"."TID")

Понятно, что join тоже поможет.

...

Рейтинг:

0 / 0

17.12.2021, 23:12

| Ответить | Цитировать | Написать

Не работает индекс при соединении union

#40121191

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

Простенький трюк:
добавляем выделенное

Код: plsql

1.
2.

select * from V_table_n v
where id = (select max(table_id) abc from table3 t3 where t3.id=1 and table_id*0=v.id*0);

и все работает само без уникальностей и прочего:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

SQL> create table table1(id primary key, padding) as
  2    select level,rpad('x',100,'x') from dual connect by level<=1000;

Table created.

SQL> create table table2(id primary key, padding) as
  2    select 50+level,rpad('x',100,'x') from dual connect by level<=1000;

Table created.

SQL> create table table3(id, table_id) as
  2    select level,level from dual connect by level<=1000;

Table created.

SQL> CREATE OR REPLACE VIEW V_table_n AS
  2  SELECT * FROM table1
  3  union
  4  SELECT * FROM table2;

View created.

до

Код: plsql

SQL> explain plan for
  2  select * from V_table_n v
  3  where id = (select max(table_id) abc from table3 t3 where t3.id=1);

Explained.

SQL> select * from table(dbms_xplan.display('','','+outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 124048865

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |  2000 |   126K|    20  (15)| 00:00:01 |
|*  1 |  VIEW                | V_TABLE_N |  2000 |   126K|    17  (18)| 00:00:01 |
|   2 |   SORT UNIQUE        |           |  2000 |   205K|    17  (18)| 00:00:01 |
|   3 |    UNION-ALL         |           |       |       |            |          |
|   4 |     TABLE ACCESS FULL| TABLE1    |  1000 |   102K|     7   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| TABLE2    |  1000 |   102K|     7   (0)| 00:00:01 |
|   6 |   SORT AGGREGATE     |           |     1 |     8 |            |          |
|*  7 |    TABLE ACCESS FULL | TABLE3    |     1 |     8 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$4" "T3"@"SEL$4")
      FULL(@"SEL$2" "TABLE1"@"SEL$2")
      FULL(@"SEL$3" "TABLE2"@"SEL$3")
      PUSH_SUBQ(@"SEL$4")
      NO_ACCESS(@"SEL$1" "V"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$4")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"= (SELECT MAX("TABLE_ID") FROM "TABLE3" "T3" WHERE
              "T3"."ID"=1))
   7 - filter("T3"."ID"=1)

43 rows selected.

и после 16

Код: plsql

SQL> explain plan for
  2  select * from V_table_n v
  3  where id = (select max(table_id) abc from table3 t3 where t3.id=1 and table_id*0=v.id*0);

Explained.

SQL> select * from table(dbms_xplan.display('','','+outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 1921140133

-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |     1 |    91 |     8  (13)| 00:00:01 |
|   1 |  NESTED LOOPS                   |             |     1 |    91 |     8  (13)| 00:00:01 |
|   2 |   VIEW                          | VW_SQ_1     |     1 |    26 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY                |             |     1 |     8 |     4  (25)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL           | TABLE3      |     1 |     8 |     3   (0)| 00:00:01 |
|   5 |   VIEW                          | V_TABLE_N   |     1 |    65 |     4   (0)| 00:00:01 |
|   6 |    SORT UNIQUE                  |             |     2 |   210 |     4   (0)| 00:00:01 |
|   7 |     UNION ALL PUSHED PREDICATE  |             |       |       |            |          |
|   8 |      TABLE ACCESS BY INDEX ROWID| TABLE1      |     1 |   105 |     2   (0)| 00:00:01 |
|*  9 |       INDEX UNIQUE SCAN         | SYS_C009596 |     1 |       |     1   (0)| 00:00:01 |
|  10 |      TABLE ACCESS BY INDEX ROWID| TABLE2      |     1 |   105 |     2   (0)| 00:00:01 |
|* 11 |       INDEX UNIQUE SCAN         | SYS_C009597 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$639F1A6F" "TABLE1"@"SEL$2" ("TABLE1"."ID"))
      INDEX_RS_ASC(@"SEL$B01C6807" "TABLE2"@"SEL$3" ("TABLE2"."ID"))
      USE_HASH_AGGREGATION(@"SEL$8F9407EC")
      FULL(@"SEL$8F9407EC" "T3"@"SEL$4")
      USE_NL(@"SEL$2E82301F" "V"@"SEL$1")
      LEADING(@"SEL$2E82301F" "VW_SQ_1"@"SEL$04A8DF8C" "V"@"SEL$1")
      NO_ACCESS(@"SEL$2E82301F" "V"@"SEL$1")
      NO_ACCESS(@"SEL$2E82301F" "VW_SQ_1"@"SEL$04A8DF8C")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$04A8DF8C")
      OUTLINE(@"SET$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$4")
      UNNEST(@"SEL$4")
      OUTLINE_LEAF(@"SEL$2E82301F")
      PUSH_PRED(@"SEL$2E82301F" "V"@"SEL$1" 2 1)
      OUTLINE_LEAF(@"SET$5715CE2E")
      OUTLINE_LEAF(@"SEL$B01C6807")
      OUTLINE_LEAF(@"SEL$639F1A6F")
      OUTLINE_LEAF(@"SEL$8F9407EC")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T3"."ID"=1)
   9 - access("ID"="ABC")
       filter("ID"*0="ITEM_1")
  11 - access("ID"="ABC")
       filter("ID"*0="ITEM_1")

...

Рейтинг:

0 / 0

18.12.2021, 03:53

| Ответить | Цитировать | Написать

Не работает индекс при соединении union

#40121192

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

тестовый скрипт на попробовать: https://gist.github.com/xtender/213cc6f3ee724205d5fd061952661ffd

...

Рейтинг:

0 / 0

18.12.2021, 03:54

| Ответить | Цитировать | Написать

Не работает индекс при соединении union

#40121226

SeaGate

Участник

Откуда: Новосибирск

Сообщения: 1 744

Рейтинг: 2 / 0

Будем знать. Я пытался коррелировать, но делал не так, потому unnest не проходил.

...

Рейтинг:

0 / 0

18.12.2021, 14:58

| Ответить | Цитировать | Написать

Не работает индекс при соединении union

#40123525

Loky1987

Гость

Ох, спасибо большое за ответы. Сейчас буду переваривать.

...

Рейтинг:

0 / 0

28.12.2021, 09:58

| Ответить | Цитировать | Написать

Не работает индекс при соединении union

#40123653

Loky1987

Гость

Sayan Malakshinov,

Спасибо такой вариант работает, а можете пояснить такое поведение оракла, чтобы дальше максимально осознано использовать данный трюк :)

...

Рейтинг:

0 / 0

28.12.2021, 16:25

| Ответить | Цитировать | Написать

13 сообщений из 13, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Не работает индекс при соединении union

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&msg=40123525&tid=1879641]:	0ms
get settings:	24ms
get forum list:	12ms
check forum access:	3ms
check topic access:	3ms
track hit:	52ms
get topic data:	10ms
get forum data:	2ms
get page messages:	324ms
get tp. blocked users:	1ms
others:	309ms

total:	740ms