Hint PRECOMPUTE_SUBQUERY 知多少
SQL> set autotrace traceonly;
SQL>
SQL> select a
2 from t1
3 where a in (select b from t2);
no rows selected
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 234 | 5 (20)|
| 1 | HASH JOIN SEMI | | 9 | 234 | 5 (20)|
| 2 | TABLE ACCESS FULL| T1 | 9 | 117 | 2 (0)|
| 3 | TABLE ACCESS FULL| T2 | 9 | 117 | 2 (0)|
----------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
318 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> drop table PLAN_TABLE;
Table dropped.
SQL> @?/rdbms/admin/utlxplan
Table created.
SQL> select a
2 from t1
3 where a in (select b from t2);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 234 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 9 | 234 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 9 | 117 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 9 | 117 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"="B")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
318 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> select a
2 from t1
3 where a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=11 OR "A"=12 OR "A"=13 OR "A"=14 OR "A"=15 OR "A"=16
OR "A"=17 OR "A"=18 OR "A"=19)把table2里的数据 直接返回给table A做过滤。我估计等于在SQL里写OR。
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
318 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Alter session set sql_trace=true;
PARSING IN CURSOR #1 len=97 dep=1 ……
SELECT /*+ BYPASS_RECURSIVE_CHECK */ DISTINCT * FROM (select /*+ PRECOMPUTE_SUBQUERY */b from t2)
PARSING IN CURSOR #2 len=75 dep=0 ……select afrom t1where a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2)
根据depth 是0 1 可以看出 这是一个递归SQL 先执行1 再执行0. 实际上就是分成了2条不同的SQL。
如果是这样的话 那么如果我在T2中有很多记录呢？
delete t2;insert into t2 select rownum from dual connect by level <= 1000; SQL> select a from t1 where a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2); 9 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 3617692013 --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 117 | 2 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T1 | 9 | 117 | 2 (0)| 00:00:01 |-------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("A"=1 OR "A"=2 OR "A"=3 OR "A"=4 OR "A"=5 OR "A"=6 OR "A"=7 OR "A"=8 OR "A"=9 OR "A"=10 OR "A"=11 OR "A"=12 OR "A"=13 OR "A"=14 OR "A"=15 OR "A"=16 OR "A"=17 OR "A"=18 OR "A"=19 OR "A"=20 OR "A"=21 OR "A"=22 OR "A"=23 OR "A"=24 OR "A"=25 OR "A"=26 OR "A"=27 OR "A"=28 OR "A"=29 OR "A"=30 OR "A"=31 OR "A"=32 OR "A"=33 OR "A"=34 OR "A"=35 OR "A"=36 OR "A"=37 OR "A"=38 OR "A"=39 OR "A"=40 OR "A"=41 OR "A"=42 OR "A"=43 OR "A"=44 OR "A"=45 OR "A"=46 OR "A"=47 OR "A"=48 OR……
就拼接到1000……
SQL> select sharable_mem,last_load_time from v$sql WHERE sql_text LIKE '%PRECOMPUTE_SUBQUERY%'
2 ;
SHARABLE_MEM LAST_LOAD_TIME
------------ ---------------------------------------------------------
19151 2011-04-26/10:55:41
14042 2011-04-26/10:52:50
32151 2011-04-26/10:55:41
2112412 2011-04-26/11:00:38
2253940 2011-04-26/11:00:36
可以看到在后面测试的情况下 同一条SQL 对于共享内存的消耗极大。
后面直接补充一条 如果T2 里面超过10000万。这个hint 会失效，但是共享内存首先是尝试load一次 失败后 重走标准执行计划，可是内存是被占用了的。实验就不补了。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21818314/viewspace-693725/ ，如需转载，请注明出处，否则将追究法律责任。
