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

Откуда:
Сообщений: 144
Можно ли как-то подсказать оптимизатору, что после сделанного unnest подзапроса нужно сделать соединение хэшированием (или любым другим способом) с основной таблицей запроса.

Например для простого запроса

SELECT * FROM emp e WHERE e.deptno IN (SELECT d.deptno FROM dept d WHERE d.loc = 'CHICAGO')

мне хочется, чтобы emp и dept соединились посредством вложенных циклов... Как это сделать не меняя структуру запроса?

SQL> EXPLAIN PLAN FOR 
  2  SELECT * FROM emp e WHERE e.deptno IN (SELECT d.deptno FROM dept d WHERE d.loc = 'CHICAGO')
  3  /

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     4 |   192 |     5 |
|*  1 |  HASH JOIN           |             |     4 |   192 |     5 |
|*  2 |   TABLE ACCESS FULL  | DEPT        |     1 |    11 |     2 |
|   3 |   TABLE ACCESS FULL  | EMP         |    12 |   444 |     2 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("D"."LOC"='CHICAGO')

Без хинтов делается HASH JOIN. Если написать

SELECT /*+ USE_NL(e d) */ * FROM emp e WHERE e.deptno IN (SELECT /*+ UNNEST */ d.deptno FROM dept d WHERE d.loc = 'CHICAGO')

то оно, естетсвенно не подхватится, так как алиас d лежит вне области видимости... Можно как-то это все-таки сделать?

Вот в запросах, использующих представления можно используя global hints с минимальными ограничениями применять хинты к таблицам внутри этих представлений, неужели нельзя подобным образом "достучаться" до таблицы внутри подзапроса?
10 дек 07, 14:25    [5026955]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и подзапросы...  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
Попробуйте внимательно прочитать, как работет хинт use_nl и подумать какой смысл на самом деле имеет ваша запись USE_NL(e d).

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements006.htm#SQLRF50701
The USE_NL hint instructs the optimizer to join each specified table to another row source with a nested loops join, using the specified table as the inner table.

...
The optimizer uses those hints when the referenced table is forced to be the inner table of a join. The hints are ignored if the referenced table is the outer table.


/*+ ordered use_nl(e)*/

Andrei Kiselev
--
The views expressed are my own and not necessarily those of my hedgehog
10 дек 07, 14:53    [5027179]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и подзапросы...  [new]
Nike_K
Member

Откуда:
Сообщений: 144
Я и ёжик
Попробуйте внимательно прочитать, как работет хинт use_nl и подумать какой смысл на самом деле имеет ваша запись USE_NL(e d).

...

/*+ ordered use_nl(e)*/


Большое спасибо за ответ. Вы отталкиваетесь от конкретного примера, который я привел неудачно. Что если в запросе больше двух таблиц, причем желаемый порядок с помощью хинта ORDERED не установить? Например

SELECT * FROM table1 t1, table2 t2 WHERE t1.a = t2.a AND t1.b IN (SELECT t3.b FROM table3 t3)

Могу я с помощью хинтов задать такой план:

1. Соединение table1 и table3 вложенным циклом
2. Соединение результата предыдущего шага с table2 вложенным циклом?
10 дек 07, 15:10    [5027352]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и подзапросы...  [new]
_AndreyP
Member

Откуда: Krsk-->SPb
Сообщений: 429
Nike_K

такой план:

1. Соединение table1 и table3 вложенным циклом
2. Соединение результата предыдущего шага с table2 вложенным циклом?


хароший у Вас план :) надо попробовать:
SELECT /*+ leading(t3 t1 t2) use_nl(t1 t2) */
      *
  FROM table1 t1
      ,table2 t2
 WHERE t1.a = t2.a
   AND t1.b IN (SELECT t3.b FROM table3 t3)
10 дек 07, 15:33    [5027588]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и подзапросы...  [new]
Nike_K
Member

Откуда:
Сообщений: 144
_AndreyP

хароший у Вас план :) надо попробовать:
SELECT /*+ leading(t3 t1 t2) use_nl(t1 t2) */
      *
  FROM table1 t1
      ,table2 t2
 WHERE t1.a = t2.a
   AND t1.b IN (SELECT t3.b FROM table3 t3)


Вы сами пробовали? Во-первых, внутри хинта LEADING можно указать только одну таблицу. Во-вторых, в качестве параметра хинта table нельзя использовать алиас таблицы, которая находится внутри подзапроса, так как она вне зоны видимости. В этом мой вопрос и состоит.
10 дек 07, 16:04    [5027890]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и подзапросы...  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
Nike_K

Во-первых, внутри хинта LEADING можно указать только одну таблицу.

Еще раз намекаю, что стоит почитать документацию.

Nike_K

Во-вторых, в качестве параметра хинта table нельзя использовать алиас таблицы, которая находится внутри подзапроса, так как она вне зоны видимости. В этом мой вопрос и состоит.

Сложный вопрос, операция unnest как раз выносит подзапрос во внешний запрос и если обратите внимание на созданый предикат соединения, он вполне себе ссылается именно на T3 :
("T1"."B"="T3"."B")
с другой стороны в третьем примере внизу USE_NL(t3) пришлось указывать в подзапросе, хотя во втором leading( t3 ... ) вполне себе отработал.

Nike_K

Вы сами пробовали?



SQL> SELECT --+ leading(t1 t3 ) use_nl(t2 t1)
  2  * 
  3  FROM table1 t1, table2 t2 WHERE t1.a = t2.a AND t1.b IN (SELECT /*+ UNNEST */ t3.b FROM table3 t3  );

План выполнения
----------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |   571K|    11M| 50709   (2)|
|   1 |  NESTED LOOPS       |         |   571K|    11M| 50709   (2)|
|   2 |   NESTED LOOPS      |         | 57100 |   557K|    50  (38)|
|   3 |    TABLE ACCESS FULL| TABLE1  | 57100 |   446K|    34   (9)|
|*  4 |    INDEX UNIQUE SCAN| TEST_PK |     1 |     2 |     0   (0)|
|*  5 |   TABLE ACCESS FULL | TABLE2  |    10 |   120 |     1   (0)|
--------------------------------------------------------------------

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

   4 - access("T1"."B"="T3"."B")
   5 - filter("T1"."A"="T2"."A")

Note
-----
   - 'PLAN_TABLE' is old version

SQL> 
SQL> SELECT --+ leading(t3 t1) use_nl(t2 t1)
  2  * 
  3  FROM table1 t1, table2 t2 WHERE t1.a = t2.a AND t1.b IN (SELECT /*+ UNNEST */ t3.b FROM table3 t3  );

План выполнения
----------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |   571K|    11M| 51319   (2)|
|   1 |  NESTED LOOPS       |         |   571K|    11M| 51319   (2)|
|   2 |   NESTED LOOPS      |         | 57100 |   557K|   660  (10)|
|   3 |    INDEX FULL SCAN  | TEST_PK |    20 |    40 |     1   (0)|
|*  4 |    TABLE ACCESS FULL| TABLE1  |  2855 | 22840 |    33  (10)|
|*  5 |   TABLE ACCESS FULL | TABLE2  |    10 |   120 |     1   (0)|
--------------------------------------------------------------------

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

   4 - filter("T1"."B"="T3"."B")
   5 - filter("T1"."A"="T2"."A")

Note
-----
   - 'PLAN_TABLE' is old version

SQL> 
SQL> SELECT --+ leading(t1 t2 ) use_nl(t2 t1)
  2  * 
  3  FROM table1 t1, table2 t2 WHERE t1.a = t2.a AND t1.b IN (SELECT /*+ UNNEST USE_NL(t3) */ t3.b FROM table3 t3  );

План выполнения
----------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |   571K|    11M| 50853   (2)|
|   1 |  NESTED LOOPS       |         |   571K|    11M| 50853   (2)|
|   2 |   NESTED LOOPS      |         |   571K|    10M| 50693   (2)|
|   3 |    TABLE ACCESS FULL| TABLE1  | 57100 |   446K|    34   (9)|
|*  4 |    TABLE ACCESS FULL| TABLE2  |    10 |   120 |     1   (0)|
|*  5 |   INDEX UNIQUE SCAN | TEST_PK |     1 |     2 |     0   (0)|
--------------------------------------------------------------------

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

   4 - filter("T1"."A"="T2"."A")
   5 - access("T1"."B"="T3"."B")

Note
-----
   - 'PLAN_TABLE' is old version

P.S. table3.b был объвлен первичным ключом (TEST_PK), иначе дополнительнильно придеться играться с выбором способа соединения semi_join ( nl_sj, hash_sj ...)
10 дек 07, 16:57    [5028329]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и подзапросы...  [new]
Nike_K
Member

Откуда:
Сообщений: 144
Я и ёжик
Nike_K

Во-первых, внутри хинта LEADING можно указать только одну таблицу.

Еще раз намекаю, что стоит почитать документацию.


Странно, в документацию я, естественно, смотрел. Вот BNF для LEADING. Как видите, внутри одна таблица... В отличие от BNF для USE_NL, например.

Я и ёжик

Nike_K

Во-вторых, в качестве параметра хинта table нельзя использовать алиас таблицы, которая находится внутри подзапроса, так как она вне зоны видимости. В этом мой вопрос и состоит.

Сложный вопрос, операция unnest как раз выносит подзапрос во внешний запрос и если обратите внимание на созданый предикат соединения, он вполне себе ссылается именно на T3:


Спасибо, ваш пример доказывает, при применении хинта LEADING оптимизатор видит уже преобразованную с помощью UNNEST версию подзапроса и это можно использовать. Буду пробовать.
10 дек 07, 17:56    [5028740]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и подзапросы...  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
Nike_K
Странно, в документацию я, естественно, смотрел.


Well, LEADING hint changed. In 9.2 docs:

LEADING
The LEADING hint causes Oracle to use the specified table as the first table in the join order.

If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.


In 10g docs:

LEADING
The LEADING hint instructs the optimizer to use the specified set of tables as the prefix in the execution plan. This hint is more versatile than the ORDERED hint. For example:
.
.
.
The LEADING hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph. If you specify two or more conflicting LEADING hints, then all of them are ignored. If you specify the ORDERED hint, it overrides all LEADING hints.



SY.
P.S. I do not have 9.2 to test if the above is documentation bug.

Сообщение было отредактировано: 10 дек 07, 19:10
10 дек 07, 19:09    [5029043]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и подзапросы...  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
SY

P.S. I do not have 9.2 to test if the above is documentation bug.

9.2.0.7 в вышепреведенном тесте ведет себя так же как 10-ка, на первый взгляд, второй взгляд кинуть не успел, было пора на пьнку...
10 дек 07, 22:07    [5029533]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить