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
-----------------------------------------------------------------------------------------------------
| 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 /*+ 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
-----------------------------------------------------------------------------------------------------
| 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>
=======================================================================================================
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
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 69 | 12213 | 59 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DBA_OBJECTS_20090210 | 69 | 12213 | 59 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_DBA_OBJECTS_NAME | 309 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
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.