что-то не пойму. в 11g сломали _optimizer_cost_based_transformation?

Андрей Панфилов
Дата: 05.03.2011 13:14:29
схемы несколько разные, но думаю смысл понятен

+ 10.2.0.4
SQL> EXPLAIN PLAN
  2     FOR
  3        SELECT r_object_id
  4          FROM dm_folder_sp dm_folder
  5         WHERE     r_object_id IN (SELECT r_object_id
  6                                     FROM dm_folder_r
  7                                    WHERE r_folder_path = :1)
  8               AND object_name = :2
  9               AND i_has_folder = 1
 10               AND i_is_deleted = 0;

Explained.

SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 1672116385

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |     1 |    78 |     3   (0)| 00:00:01 |
|*  1 |  FILTER                       |                    |       |       |            |          |
|   2 |   NESTED LOOPS                |                    |     1 |    78 |     2   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S     |     1 |    62 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | D_1F0000508000000F |     3 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | D_1F00005080000143 |     1 |    16 |     1   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | DM_FOLDER_R        |     1 |    30 |     1   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN           | D_1F00005080000015 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "DM_FOLDER_R" "DM_FOLDER_R" WHERE
              "R_FOLDER_PATH"=:1 AND "R_OBJECT_ID"=:B1))
   3 - filter("GJ_"."I_HAS_FOLDER"=1 AND "GJ_"."I_IS_DELETED"=0)
   4 - access("GJ_"."OBJECT_NAME"=:2)

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

   5 - access("GJ_"."R_OBJECT_ID"="TGB_"."R_OBJECT_ID")
   6 - filter("R_OBJECT_ID"=:B1)
   7 - access("R_FOLDER_PATH"=:1)

25 rows selected.
SQL> EXPLAIN PLAN
  2     FOR
  3        SELECT    /*+ opt_param('_optimizer_cost_based_transformation','off') */
  4              r_object_id
  5          FROM dm_folder_sp dm_folder
  6         WHERE     r_object_id IN (SELECT r_object_id
  7                                     FROM dm_folder_r
  8                                    WHERE r_folder_path = :1)
  9               AND object_name = :2
 10               AND i_has_folder = 1
 11               AND i_is_deleted = 0;

Explained.

SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 3204416784

------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |     1 |    10 |     4  (25)| 00:00:01 |
|   1 |  VIEW                           |                    |     1 |    10 |     4  (25)| 00:00:01 |
|   2 |   HASH UNIQUE                   |                    |     1 |   124 |     4  (25)| 00:00:01 |
|   3 |    NESTED LOOPS                 |                    |     1 |   124 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                    |     1 |   104 |     2   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DM_FOLDER_R        |     1 |    30 |     1   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | D_1F00005080000015 |     1 |       |     1   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S     |     1 |    74 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN         | D_1F00005080000109 |     1 |       |     1   (0)| 00:00:01 |
|*  9 |     INDEX UNIQUE SCAN           | D_1F00005080000143 |     1 |    20 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   6 - access("R_FOLDER_PATH"=:1)
   7 - filter("GJ_"."OBJECT_NAME"=:2 AND "GJ_"."I_HAS_FOLDER"=1 AND "GJ_"."I_IS_DELETED"=0)

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

   8 - access("GJ_"."R_OBJECT_ID"="R_OBJECT_ID")
   9 - access("GJ_"."R_OBJECT_ID"="TGB_"."R_OBJECT_ID")

24 rows selected.
+ 11.2.0.2
SQL> EXPLAIN PLAN
  2     FOR
  3        SELECT r_object_id
  4          FROM dm_folder_sp dm_folder
  5         WHERE     r_object_id IN (SELECT r_object_id
  6                                     FROM dm_folder_r
  7                                    WHERE r_folder_path = :1)
  8               AND object_name = :2
  9               AND i_has_folder = 1
 10               AND i_is_deleted = 0;

Explained.

SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 543577744

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |     1 |    98 |     4   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS SEMI                   |                    |     1 |    98 |     4   (0)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                       |                    |     1 |    68 |     2   (0)| 00:00:01 |       |       |
|   3 |    PARTITION RANGE SINGLE            |                    |     1 |    49 |     2   (0)| 00:00:01 |     1 |     1 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| DM_SYSOBJECT_S     |     1 |    49 |     2   (0)| 00:00:01 |     1 |     1 |
|*  5 |      INDEX RANGE SCAN                | D_1F0027118000000F |     2 |       |     1   (0)| 00:00:01 |     1 |     1 |
|   6 |    PARTITION RANGE SINGLE            |                    |     1 |    19 |     0   (0)| 00:00:01 |   KEY |   KEY |
|*  7 |     INDEX UNIQUE SCAN                | D_1F00271180000145 |     1 |    19 |     0   (0)| 00:00:01 |   KEY |   KEY |
|   8 |   PARTITION RANGE SINGLE             |                    |     3 |    90 |     2   (0)| 00:00:01 |     1 |     1 |
|*  9 |    TABLE ACCESS BY LOCAL INDEX ROWID | DM_FOLDER_R        |     3 |    90 |     2   (0)| 00:00:01 |     1 |     1 |
|* 10 |     INDEX RANGE SCAN                 | D_1F00271180000015 |     3 |       |     1   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------------------------

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

   4 - filter("UJ_"."I_HAS_FOLDER"=1 AND "UJ_"."I_IS_DELETED"=0)

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

   5 - access("UJ_"."OBJECT_NAME"=:2)
   7 - access("UJ_"."R_OBJECT_ID"="YIB_"."R_OBJECT_ID" AND "UJ_"."I_PARTITION"="YIB_"."I_PARTITION")
   9 - filter("UJ_"."R_OBJECT_ID"="R_OBJECT_ID")
  10 - access("R_FOLDER_PATH"=:1)

26 rows selected.
SQL> EXPLAIN PLAN
  2     FOR
  3        SELECT    /*+ opt_param('_optimizer_cost_based_transformation','off') */
  4              r_object_id
  5          FROM dm_folder_sp dm_folder
  6         WHERE     r_object_id IN (SELECT r_object_id
  7                                     FROM dm_folder_r
  8                                    WHERE r_folder_path = :1)
  9               AND object_name = :2
 10               AND i_has_folder = 1
 11               AND i_is_deleted = 0;

Explained.

SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 543577744

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |     1 |    98 |     4   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS SEMI                   |                    |     1 |    98 |     4   (0)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                       |                    |     1 |    68 |     2   (0)| 00:00:01 |       |       |
|   3 |    PARTITION RANGE SINGLE            |                    |     1 |    49 |     2   (0)| 00:00:01 |     1 |     1 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| DM_SYSOBJECT_S     |     1 |    49 |     2   (0)| 00:00:01 |     1 |     1 |
|*  5 |      INDEX RANGE SCAN                | D_1F0027118000000F |     2 |       |     1   (0)| 00:00:01 |     1 |     1 |
|   6 |    PARTITION RANGE SINGLE            |                    |     1 |    19 |     0   (0)| 00:00:01 |   KEY |   KEY |
|*  7 |     INDEX UNIQUE SCAN                | D_1F00271180000145 |     1 |    19 |     0   (0)| 00:00:01 |   KEY |   KEY |
|   8 |   PARTITION RANGE SINGLE             |                    |     3 |    90 |     2   (0)| 00:00:01 |     1 |     1 |
|*  9 |    TABLE ACCESS BY LOCAL INDEX ROWID | DM_FOLDER_R        |     3 |    90 |     2   (0)| 00:00:01 |     1 |     1 |
|* 10 |     INDEX RANGE SCAN                 | D_1F00271180000015 |     3 |       |     1   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------------------------

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

   4 - filter("UJ_"."I_HAS_FOLDER"=1 AND "UJ_"."I_IS_DELETED"=0)

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

   5 - access("UJ_"."OBJECT_NAME"=:2)
   7 - access("UJ_"."R_OBJECT_ID"="YIB_"."R_OBJECT_ID" AND "UJ_"."I_PARTITION"="YIB_"."I_PARTITION")
   9 - filter("UJ_"."R_OBJECT_ID"="R_OBJECT_ID")
  10 - access("R_FOLDER_PATH"=:1)

26 rows selected.
SQL> EXPLAIN PLAN
  2     FOR
  3        SELECT    /*+ opt_param('_optimizer_cost_based_transformation','off') */
  4              r_object_id
  5          FROM dm_folder_sp dm_folder
  6         WHERE     r_object_id IN (SELECT /*+ NO_UNNEST */ r_object_id
  7                                     FROM dm_folder_r
  8                                    WHERE r_folder_path = :1)
  9               AND object_name = :2
 10               AND i_has_folder = 1
 11               AND i_is_deleted = 0;

Explained.

SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 4098382955

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |     1 |    68 |     4   (0)| 00:00:01 |       |       |
|*  1 |  FILTER                              |                    |       |       |            |       |          |       |
|   2 |   NESTED LOOPS                       |                    |     1 |    68 |     2   (0)| 00:00:01 |       |       |
|   3 |    PARTITION RANGE SINGLE            |                    |     1 |    49 |     2   (0)| 00:00:01 |     1 |     1 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| DM_SYSOBJECT_S     |     1 |    49 |     2   (0)| 00:00:01 |     1 |     1 |
|*  5 |      INDEX RANGE SCAN                | D_1F0027118000000F |     2 |       |     1   (0)| 00:00:01 |     1 |     1 |
|   6 |    PARTITION RANGE SINGLE            |                    |     1 |    19 |     0   (0)| 00:00:01 |   KEY |   KEY |
|*  7 |     INDEX UNIQUE SCAN                | D_1F00271180000145 |     1 |    19 |     0   (0)| 00:00:01 |   KEY |   KEY |
|   8 |   PARTITION RANGE SINGLE             |                    |     1 |    30 |     2   (0)| 00:00:01 |     1 |     1 |
|*  9 |    TABLE ACCESS BY LOCAL INDEX ROWID | DM_FOLDER_R        |     1 |    30 |     2   (0)| 00:00:01 |     1 |     1 |
|* 10 |     INDEX RANGE SCAN                 | D_1F00271180000015 |     3 |       |     1   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DM_FOLDER_R" "DM_FOLDER_R" WHERE "R_FOLDER_PATH"=:1 AND

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

              "R_OBJECT_ID"=:B1))
   4 - filter("UJ_"."I_HAS_FOLDER"=1 AND "UJ_"."I_IS_DELETED"=0)
   5 - access("UJ_"."OBJECT_NAME"=:2)
   7 - access("UJ_"."R_OBJECT_ID"="YIB_"."R_OBJECT_ID" AND "UJ_"."I_PARTITION"="YIB_"."I_PARTITION")
   9 - filter("R_OBJECT_ID"=:B1)
  10 - access("R_FOLDER_PATH"=:1)

28 rows selected.
Splain
Дата: 05.03.2011 15:44:18
В трассировке 10053 в конце файла после плана выполнения есть упоминание этого хинта?


/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('_optimizer_cost_based_transformation' 'off')
ALL_ROWS
...
END_OUTLINE_DATA
*/
Андрей Панфилов
Дата: 05.03.2011 17:38:11
Splain,

есть
wurdu
Дата: 08.03.2011 08:52:35
Андрей Панфилов, optimizer_cost_based_transformation=off отключает CBQT, но не transformation engine. Transformation engine вообще существует отдельно от CBO и работает даже с RBO (тот же unnest происходит и с /*+ rule */ ). Таким образом, unnest может происходить с отключенным CBQT, с включенным CBQT, но все равно не используя его (в 10053 появляется что-то типа Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing), и используя CBQT. Отключает трансформацию /*+ NO_QUERY_TRANSFORMATION */.
Андрей Панфилов
Дата: 08.03.2011 13:27:30
wurdu
Таким образом, unnest может происходить с отключенным CBQT, с включенным CBQT, но все равно не используя его (в 10053 появляется что-то типа Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing), и используя CBQT.
Я специально для 11g третий запрос с /*+ NO_UNNEST */ привел, джойн уходит, но все равно IN в EXISTS превращается
wurdu
Отключает трансформацию /*+ NO_QUERY_TRANSFORMATION */.
на моем примере этот хинт даже на 10g к положительному результату не приводит:

+
SQL> EXPLAIN PLAN
  2     FOR
  3        SELECT                                    /*+ NO_QUERY_TRANSFORMATION */
  4              r_object_id
  5          FROM dm_folder_sp dm_folder
  6         WHERE     r_object_id IN (SELECT r_object_id
  7                                     FROM dm_folder_r
  8                                    WHERE r_folder_path = :1)
  9               AND object_name = :2
 10               AND i_has_folder = 1
 11               AND i_is_deleted = 0;

Explained.

SQL> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 119265807

-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     1 |    20 |     3   (0)| 00:00:01 |
|*  1 |  FILTER                        |                    |       |       |            |          |
|   2 |   VIEW                         | DM_FOLDER_SP       |     1 |    20 |     2   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |                    |     1 |    82 |     2   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S     |     1 |    66 |     1   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | D_1F0000508000000F |     3 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | D_1F00005080000143 |     1 |    16 |     1   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS BY INDEX ROWID  | DM_FOLDER_R        |     1 |    30 |     1   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN            | D_1F00005080000015 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "DM_FOLDER_R" "DM_FOLDER_R" WHERE
              "R_FOLDER_PATH"=:1 AND "R_OBJECT_ID"=:B1))
   4 - filter("GJ_"."I_HAS_FOLDER"=1 AND "GJ_"."I_IS_DELETED"=0)
   5 - access("GJ_"."OBJECT_NAME"=:2)
   6 - access("GJ_"."R_OBJECT_ID"="TGB_"."R_OBJECT_ID")
   7 - filter("R_OBJECT_ID"=:B1)
   8 - access("R_FOLDER_PATH"=:1)

26 rows selected.
на 11g план точно такой же, как и с /*+ NO_UNNEST */
wurdu
Дата: 08.03.2011 14:21:16
Значит я не правильно понял цель. Я думал, что хочется избавиться от unnest, соответственно получить filter вместо join. Если не происходит unnest, то имеем filter с not exists. Это преобразование, насколько я знаю, происходит всегда, как in (..) заменяется на or, а ANSI синтаксис приводится к Ораклиному. Если происходит unnest, то имеем обычный join, собственно как в первом плане в 11.2 и во 2-м плане в 10g. Соответственно, с /*+ NO_QUERY_TRANSFORMATION */ результат тот же, что и с UNNEST, т.к. он отменяет эту трансформацию. А что хочется получить? Ведь есть вариант с unnest, т.е. без filter с not exists, и есть вариант без unnest - с фильтром.
Андрей Панфилов
Дата: 08.03.2011 14:50:12
wurdu,

хочется всегда получать второй план от 10.2.0.4, либо такой (получается при ALTER TABLE dm_folder_r ADD UNIQUE(r_folder_path)):
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |     1 |   108 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                    |     1 |   108 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                    |     1 |    92 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DM_FOLDER_R        |     1 |    30 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | D_1F00005080000015 |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S     |     1 |    62 |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | D_1F00005080000109 |     1 |       |     1   (0)| 00:00:01 |
|*  7 |   INDEX UNIQUE SCAN           | D_1F00005080000143 |     1 |    16 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   4 - access("R_FOLDER_PATH"=:1)
   5 - filter("GJ_"."OBJECT_NAME"=:2 AND "GJ_"."I_HAS_FOLDER"=1 AND "GJ_"."I_IS_DELETED"=0)
   6 - access("GJ_"."R_OBJECT_ID"="R_OBJECT_ID")
   7 - access("GJ_"."R_OBJECT_ID"="TGB_"."R_OBJECT_ID")

22 rows selected.
Проблема заключается в том, что в схеме имеет место быть перекос данных и доступ по r_folder_path всегда более оптимальный нежели все остальные (оптимизатору получается это объяснить либо подсовыванием левой статистики, либо "_optimizer_cost_based_transformation"=OFF в 10g (вендор не обременяет себя добавлением unique constraint в схеме)), зафиксировать план по сути возможности нет - приведенный SQL генерируется только если пользователь приложения является администратором, в других случаях там еще страшные куски по проверке прав доступа приклеиваются. Вообщем если вменяемых (т.е. левая статистика не подходит) решений нет, то, думаю, нужно заводить дефект у вендора приложения.
Андрей Панфилов
Дата: 08.03.2011 15:27:19
wurdu
Если происходит unnest, то имеем обычный join, собственно как в первом плане в 11.2 и во 2-м плане в 10g.
Второй план от 10.2.0.4 отличается от того, который получается при явном указании хинта /*+ UNNEST */ - я окончательно запутался :

------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |     1 |    88 |     4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS                   |                    |     1 |    88 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS                  |                    |     1 |    72 |     3  (34)| 00:00:01 |
|   3 |    VIEW                         | VW_NSO_1           |     1 |    10 |     2  (50)| 00:00:01 |
|   4 |     HASH UNIQUE                 |                    |     1 |    30 |     2  (50)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DM_FOLDER_R        |     1 |    30 |     1   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | D_1F00005080000015 |     1 |       |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID  | DM_SYSOBJECT_S     |     1 |    62 |     1   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN           | D_1F00005080000109 |     1 |       |     1   (0)| 00:00:01 |
|*  9 |   INDEX UNIQUE SCAN             | D_1F00005080000143 |     1 |    16 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   6 - access("R_FOLDER_PATH"=:1)
   7 - filter("GJ_"."OBJECT_NAME"=:2 AND "GJ_"."I_HAS_FOLDER"=1 AND "GJ_"."I_IS_DELETED"=0)
   8 - access("GJ_"."R_OBJECT_ID"="$nso_col_1")
   9 - access("GJ_"."R_OBJECT_ID"="TGB_"."R_OBJECT_ID")

24 rows selected.
wurdu
Дата: 08.03.2011 15:47:01
В 10g _optimizer_cost_based_transformation = off как раз позволяет получить трансформацию unnest, т.к. без хинта оптимизатор предпочитает не использовать трансформацию. В 11.2 оптимизатор использует unnest без хинта и проблема в неверном порядке соединения таблиц, вызванным неверным определением cardinality. Если в 10g access("R_FOLDER_PATH"=:1) приводит к rows = 1, То в 11.2 уже rows = 3. Поэтому уникальный индекс гарантированно позволяет получить rows = 1. Возможно статистика разная или оптимизатор в 11.2 стал как-то по-другому считать. Могу предположить, что проблема также в определении кардинальности для filter("GJ_"."OBJECT_NAME"=:2 AND "GJ_"."I_HAS_FOLDER"=1 AND "GJ_"."I_IS_DELETED"=0). Возможно создание extended statistics , появившейся в 11g, по полям OBJECT_NAME, I_HAS_FOLDER, I_IS_DELETED (а возможно только по I_HAS_FOLDER, I_IS_DELETED) решит проблему.
Андрей Панфилов
Дата: 08.03.2011 17:11:02
wurdu,

в 11g и 10g оно еще unnest по-разному делает:

11g:
Subquery Unnest (SU)
********************
SU:   Checking validity of unnesting subquery SEL$2 (#0)
SU:   Transforming ANY subquery to a join.
Registered qb: SEL$5DA710D3 0xb49eb0e0 (SUBQUERY UNNEST SEL$1; SEL$2)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$5DA710D3 nbfros=2 flg=0
    fro(0): flg=0 objn=74359 hint_alias="DM_FOLDER"@"SEL$1"
    fro(1): flg=0 objn=74689 hint_alias="DM_FOLDER_R"@"SEL$2"
10g:
Subquery Unnest
***************
SU: Considering subquery unnesting in query block SEL$F5BB74E1 (#0)
SU:   Checking validity of unnesting subquery SEL$3 (#0)
SU:   Transform an ANY subquery to semi-join or distinct.
Registered qb: SEL$291F8F59 0x256a32b8 (SUBQ INTO VIEW FOR COMPLEX UNNEST SEL$3)
  signature (): qb_name=SEL$291F8F59 nbfros=1 flg=0
    fro(0): flg=0 objn=305580 hint_alias="DM_FOLDER_R"@"SEL$3"
Registered qb: SEL$1F949E82 0x256a3988 (SUBQUERY UNNEST SEL$F5BB74E1; SEL$3)
  signature (): qb_name=SEL$1F949E82 nbfros=3 flg=0
    fro(0): flg=5 objn=0 hint_alias="VW_NSO_1"@"SEL$1F949E82"
    fro(1): flg=0 objn=305391 hint_alias="GJ_"@"SEL$2"
    fro(2): flg=0 objn=305577 hint_alias="TGB_"@"SEL$2"