alter session set _unnest_subquery=false;

转载

mb5fe55a9dbe9dd 2019-07-04 09:28:00

alter session set _unnest_subquery=false;

不让它进行子查询改写


select count(*) from test o where object_id>(select avg(object_id) from test i where i.object_type=o.object_type);

SQL> set linesize 200
SQL> set pagesize 200
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;

会话已更改。

SQL> select count(*) from test o where object_id>(select avg(object_id) from test i where i.object_type=o.object_type);

  COUNT(*)
----------
     43307

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  anxr90rp27yn9, child number 0
-------------------------------------
select count(*) from test o where object_id>(select avg(object_id) from
test i where i.object_type=o.object_type)

Plan hash value: 731730653

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation       | Name  | Starts | E-Rows | A-Rows |   A-Time | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |   |      1 |    |  1 |00:00:00.43 |    2488 |   2484 |     |     |        |
|   1 |  SORT AGGREGATE       |   |      1 |  1 |  1 |00:00:00.43 |    2488 |   2484 |     |     |        |
|*  2 |   HASH JOIN       |   |      1 |  53949 |  43307 |00:00:00.42 |    2488 |   2484 |  1229K|  1229K| 1566K (0)|
|   3 |    VIEW         | VW_SQ_1 |      1 |     45 | 45 |00:00:00.38 |    1244 |   1242 |     |     |        |
|   4 |     HASH GROUP BY     |   |      1 |     45 | 45 |00:00:00.38 |    1244 |   1242 |  9057K|  2661K| 2508K (0)|
|   5 |      TABLE ACCESS FULL| TEST  |      1 |  86974 |  86974 |00:00:00.35 |    1244 |   1242 |     |     |        |
|   6 |    TABLE ACCESS FULL  | TEST  |      1 |  86974 |  86974 |00:00:00.01 |    1244 |   1242 |     |     |        |
-------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("ITEM_1"="O"."OBJECT_TYPE")
       filter("OBJECT_ID">"AVG(OBJECT_ID)")


已选择25行。




关闭子查询改写：


SQL> set linesize 200
SQL> set pagesize 200
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;

会话已更改。

SQL> alter session set _unnest_subquery=false;                                                                         
alter session set _unnest_subquery=false
                  *
第 1 行出现错误:
ORA-00911: 无效字符


SQL> alter system set "_unnest_subquery"=false
  2  ;

系统已更改。

SQL> select count(*) from test o where object_id>(select avg(object_id) from test i where i.object_type=o.object_type);

  COUNT(*)
----------
     43307

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  anxr90rp27yn9, child number 2
-------------------------------------
select count(*) from test o where object_id>(select avg(object_id) from
test i where i.object_type=o.object_type)

Plan hash value: 2139949240

------------------------------------------------------------------------------------------------
| Id  | Operation      | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    1 |        |      1 |00:00:00.37 | 69664 |  69552 |
|   1 |  SORT AGGREGATE      |      |    1 |      1 |      1 |00:00:00.37 | 69664 |  69552 |
|*  2 |   FILTER       |      |    1 |        |  43307 |00:00:00.37 | 69664 |  69552 |
|   3 |    TABLE ACCESS FULL | TEST |    1 |  86974 |  86974 |00:00:00.02 |  1244 |   1242 |
|   4 |    SORT AGGREGATE    |      |   55 |      1 |     55 |00:00:00.33 | 68420 |  68310 |
|*  5 |     TABLE ACCESS FULL| TEST |   55 |   1933 |  87204 |00:00:00.32 | 68420 |  68310 |
------------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID">)
   5 - filter("I"."OBJECT_TYPE"=:B1)


已选择24行。