/+ precompute_subquery /子查询中的提示

最新推荐文章于 2021-11-04 10:37:19 发布

原创最新推荐文章于 2021-11-04 10:37:19 发布 · 175 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Access #SQL #Oracle

oracle SQL 专栏收录该内容

30 篇文章

订阅专栏

本文通过具体示例展示了如何使用Oracle SQL提示precompute_subquery优化含有IN子查询的SQL语句执行效率，对比了不同情况下的执行计划及成本。

QUOTE:
--------------------------------------------------------------------------------
select * from tab1 where tab1.object_name in(select /*+ precompute_subquery */ object_name from tab2 where owner='SYS');

/*+ precompute_subquery */子查询中的提示

就是把子查询中in的多个值改写成多个OR条件
--------------------------------------------------------------------------------

SCOTT@ncdb>select count(*) from dba_objects;

COUNT(*)
----------
62493

Elapsed: 00:00:00.84
SCOTT@ncdb>create table dba_objects_20090210 as select * from dba_objects;

Table created.

Elapsed: 00:00:01.56

SCOTT@ncdb>create table dba_objects2 as select * from dba_objects;

Table created.

Elapsed: 00:00:01.48

SCOTT@ncdb>select owner, count(*) from dba_objects2 group by owner;

OWNER        COUNT(*)
---------- ----------
PUBLIC          20084
SYSTEM            454
XDB               680
OLAPSYS           720
IUFOV31           366
YONGYOU          1573
SYS             23257
TSMSYS              3
MDSYS             936
SHUIBO           1562
JV                 50
NCTOCC              7
SYSMAN           1346
HAIFENG          1561
EXFSYS            282
SI_INFORMT          8
N_SCHEMA
BI_DATA            18
NCV31            4798
WMSYS             315
ORDSYS           1721
SCOTT              17
NCTOPSI           220
NFD               191
ORACLE_OCM          8
BI_REP            111
CTXSYS            313
ORDPLUGINS         10
OUTLN               9
DBSNMP             46
DMSYS             189
OULONG           1503
RMAN              128
OA                  8

33 rows selected.

Elapsed: 00:00:00.82
SCOTT@ncdb>create index idx_dba_objects_name on dba_objects_20090210(object_name);

Index created.

Elapsed: 00:00:00.43
SCOTT@ncdb>explain plan for
2 select * from dba_objects_20090210 where object_name in
3 (select object_name from dba_objects2 where owner='NCTOCC');

Explained.

Elapsed: 00:00:00.06
SCOTT@ncdb>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 2716592996

-----------------------------------------------------------------------------------------------------
| Id | Operation                                 | Name                                 | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                                           |    94 | 24440 |   114   (6)| 00:00:01 |
|   1 | NESTED LOOPS                           |                                           |    94 | 24440 |   114   (6)| 00:00:01 |
|   2 |   SORT UNIQUE                             |                                          |    10 |   830 |   103   (5)| 00:00:01 |
|* 3 |    TABLE ACCESS FULL                 | DBA_OBJECTS2                 |    10 |   830 |   103   (5)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DBA_OBJECTS_20090210 |    10 | 1770 |     2   (0)| 00:00:01 |
|* 5 |    INDEX RANGE SCAN                  | IDX_DBA_OBJECTS_NAME |    10 |         |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

3 - filter("OWNER"='NCTOCC')
5 - access("OBJECT_NAME"="OBJECT_NAME")

Note
-----
- dynamic sampling used for this statement

22 rows selected.

Elapsed: 00:00:00.06
SCOTT@ncdb>explain plan for
2 select * from dba_objects_20090210 where object_name in
3 (select object_name from dba_objects2 where owner='SYS');

Explained.

Elapsed: 00:00:00.06
SCOTT@ncdb>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 3391626064

-----------------------------------------------------------------------------------------------------
| Id | Operation            | Name                 | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                      | 68173 |    16M|       |   560   (5)| 00:00:04 |
|* 1 | HASH JOIN RIGHT SEMI|                      | 68173 |    16M| 2352K|   560   (5)| 00:00:04 |
|* 2 |   TABLE ACCESS FULL | DBA_OBJECTS2         | 25340 | 2053K|       |   104   (6)| 00:00:01 |
|   3 |   TABLE ACCESS FULL | DBA_OBJECTS_20090210 | 77278 |    13M|       |   108 (10)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

1 - access("OBJECT_NAME"="OBJECT_NAME")
2 - filter("OWNER"='SYS')

Note
-----
- dynamic sampling used for this statement

20 rows selected.

Elapsed: 00:00:00.04
SCOTT@ncdb>explain plan for
2 select * from dba_objects_20090210 where object_name in
3 (select /*+ precompute_subquery */object_name from dba_objects2 where owner='SYS');

Explained.

Elapsed: 00:00:00.34
SCOTT@ncdb>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 3391626064

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

1 - access("OBJECT_NAME"="OBJECT_NAME")
2 - filter("OWNER"='SYS')

Note
-----
- dynamic sampling used for this statement

20 rows selected.

Elapsed: 00:00:00.04
SCOTT@ncdb>explain plan for
2 select /*+ precompute_subquery */ * from dba_objects_20090210 where object_name in
3 (select object_name from dba_objects2 where owner='SYS');

Explained.

Elapsed: 00:00:00.04
SCOTT@ncdb>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 3391626064

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

1 - access("OBJECT_NAME"="OBJECT_NAME")
2 - filter("OWNER"='SYS')

Note
-----
- dynamic sampling used for this statement

20 rows selected.

Elapsed: 00:00:00.04
SCOTT@ncdb>

=======================================================================================================

SQL> create table test_1 as select * from dba_objects;

表已创建。

SQL> create table test2 as select rownum id from dual connect by rownum<10;

表已创建。

SQL> create index i_test_1 on test_1(object_id);

索引已创建。

SQL> set autot trace exp
SQL> select * from test_1 where object_id in (select /*+ precompute_subquery */ id from test2);

执行计划
----------------------------------------------------------
Plan hash value: 3907423376

-----------------------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     8 | 1416 |    12   (0)| 00:00:01 |
|   1 | INLIST ITERATOR             |          |       |       |            |        |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_1   |     8 | 1416 |    12   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN          | I_TEST_1 |   207 |       |     9   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

3 - access("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3 OR "OBJECT_ID"=4
OR "OBJECT_ID"=5 OR "OBJECT_ID"=6 OR "OBJECT_ID"=7 OR "OBJECT_ID"=
8 OR

"OBJECT_ID"=9)

Note
-----
- dynamic sampling used for this statement

==================================================================================================

SCOTT@ncdb>explain plan for
2 select * from dba_objects_20090210 where object_name in
3 (select /*+ precompute_subquery */ object_name from dba_objects2 where owner='NCTOCC');

Explained.

Elapsed: 00:00:00.18
SCOTT@ncdb>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2232840152

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

   3 - access("OBJECT_NAME"='V_COINFO' OR "OBJECT_NAME"='V_CUSTINFO' OR
              "OBJECT_NAME"='V_RETURNBILL_STATUS' OR "OBJECT_NAME"='V_SALEORDER_STATUS' OR
              "OBJECT_NAME"='V_SALEOUT_STATUS' OR "OBJECT_NAME"='V_SALERET_STATUS' OR
              "OBJECT_NAME"='V_USERINFO')

Note
-----
- dynamic sampling used for this statement

22 rows selected.