Хинты и подзапросы...

Nike_K

Дата: 10.12.2007 14:25:17

Можно ли как-то подсказать оптимизатору, что после сделанного 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.12.2007 14:53:09

Попробуйте внимательно прочитать, как работет хинт 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

Nike_K

Дата: 10.12.2007 15:10:20

Я и ёжик

Попробуйте внимательно прочитать, как работет хинт 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 вложенным циклом?

_AndreyP

Дата: 10.12.2007 15:33:26

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)

Nike_K

Дата: 10.12.2007 16:04:03

_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.12.2007 16:57:32

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 ...)

Nike_K

Дата: 10.12.2007 17:56:21

Я и ёжик

Nike_K

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

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

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

Я и ёжик

Nike_K

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

Спасибо, ваш пример доказывает, при применении хинта LEADING оптимизатор видит уже преобразованную с помощью UNNEST версию подзапроса и это можно использовать. Буду пробовать.

Дата: 10.12.2007 19:09:03

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.12.2007 22:07:12

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

9.2.0.7 в вышепреведенном тесте ведет себя так же как 10-ка, на первый взгляд, второй взгляд кинуть не успел, было пора на пьнку...