PRECOMPUTE_SUBQUERY應用的一個例子
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux IA64: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table t (id varchar2(10),text varchar2(4000));
表已建立。
SQL> insert into t select '1',object_name from dba_objects;
已建立110613行。
SQL> /
已建立110613行。
SQL> /
已建立110613行。
SQL> insert into t values ('2','aaa');
已建立 1 行。
SQL> create index t_idx on t(id);
索引已建立。
SQL> create index t_idx1 on t(text);
索引已建立。
SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all indexed co
lumns',cascade=>true);
PL/SQL 過程已成功完成。
SQL> select id,count(*) from t group by id;
ID COUNT(*)
---------- ----------
1 331839
2 1
SQL> set autot trace exp stat
SQL> select * from t
2 where t.id in (select x.id from t x where x.text='aaa');
執行計劃
----------------------------------------------------------
Plan hash value: 2990025524
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 332K| 13M| 315 (3)| 00:00:04 |
|* 1 | HASH JOIN RIGHT SEMI | | 332K| 13M| 315 (3)| 00:00:04 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 7 | 154 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX1 | 7 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T | 332K| 7142K| 308 (2)| 00:00:04 |
由於Oracle不知道傳遞過來的ID列內容，因此，無法得到準確的資訊，10053顯示，Oracle估算的選擇度是1
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."ID"="X"."ID")
3 - access("X"."TEXT"='aaa')
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
1342 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
346 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from t
2 where t.id in (select /*+ precompute_subquery */ x.id from t x where x.text='aaa')
3 ;
執行計劃
----------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 22 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."ID"='2') 子查詢值預先計算，因此此處直接把值傳遞過來，Oracle可以得到準確的選擇度，因此走了索引掃描
統計資訊
----------------------------------------------------------
4 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
346 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
原始SQL，10053：
** Performing dynamic sampling initial checks. **
Column (#2): TEXT(VARCHAR2)
AvgLen: 21.00 NDV: 44554 Nulls: 0 Density: 2.2445e-05
Histogram: HtBal #Bkts: 75 UncompBkts: 75 EndPtVals: 76
Using density: 2.2445e-05 of col #2 as selectivity of unpopular value pred
** Dynamic sampling initial checks returning FALSE.
Using density: 2.2445e-05 of col #2 as selectivity of unpopular value pred
Table: T Alias: X
Card: Original: 332432 Rounded: 7 Computed: 7.46 Non Adjusted: 7.46
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 309.06 Resp: 309.06 Degree: 0
Cost_io: 303.00 Cost_cpu: 82948384
Resp_io: 303.00 Resp_cpu: 82948384
Using density: 2.2445e-05 of col #2 as selectivity of unpopular value pred
Access Path: index (AllEqRange)
Index: T_IDX1
resc_io: 10.00 resc_cpu: 74174
ix_sel: 2.2485e-05 ix_sel_with_filters: 2.2485e-05
Cost: 4.00 Resp: 4.00 Degree: 1
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Using density: 2.2445e-05 of col #2 as selectivity of unpopular value pred
Using density: 2.2445e-05 of col #2 as selectivity of unpopular value pred
Access Path: index (AllEqRange)
Index: T_IDX1
resc_io: 3.00 resc_cpu: 22964
ix_sel: 2.2485e-05 ix_sel_with_filters: 2.2485e-05
Cost: 1.20 Resp: 1.20 Degree: 0
****** finished trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: T_IDX
resc_io: 604.00 resc_cpu: 70669350
ix_sel: 1 ix_sel_with_filters: 1
Cost: 243.67 Resp: 243.67 Degree: 0
******** Cost index join ********
Index join: Considering index join to index T_IDX1
Index join: Joining index T_IDX
Ix HA Join
Outer table:
resc: 1.20 card 7.46 bytes: 31 deg: 1 resp: 1.20
Inner table:
resc: 304.58 card: 332432.00 bytes: 12 deg: 1 resp: 304.58
using dmeth: 2 #groups: 1
Cost per ptn: 2.93 #ptns: 1
hash_area: 2 (max=256) Hash join: Resc: 308.71 Resp: 308.71 [multiMatchCost=0.00]
******** Index join cost ********
Cost: 308.71
******** End index join costing ********
Best:: AccessPath: IndexRange Index: T_IDX1
Cost: 4.00 Degree: 1 Resp: 4.00 Card: 7.46 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
*** 2010-07-13 13:24:48.518
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
Table: T Alias: T
Card: Original: 332432 Rounded: 332432 Computed: 332432.00 Non Adjusted: 332432.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 307.85 Resp: 307.85 Degree: 0
Cost_io: 303.00 Cost_cpu: 66326784
Resp_io: 303.00 Resp_cpu: 66326784
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: T_IDX
resc_io: 604.00 resc_cpu: 70669350
ix_sel: 1 ix_sel_with_filters: 1
Cost: 243.67 Resp: 243.67 Degree: 0
Access Path: index (FullScan)
Index: T_IDX
resc_io: 604.00 resc_cpu: 70669350
ix_sel: 1 ix_sel_with_filters: 1
Cost: 243.67 Resp: 243.67 Degree: 0
****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: TableScan
Cost: 307.85 Degree: 1 Resp: 307.85 Card: 332432.00 Bytes: 0
***************************************
[ 本帖最後由 sundog315 於 2010-7-13 16:35 編輯 ]
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux IA64: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table t (id varchar2(10),text varchar2(4000));
表已建立。
SQL> insert into t select '1',object_name from dba_objects;
已建立110613行。
SQL> /
已建立110613行。
SQL> /
已建立110613行。
SQL> insert into t values ('2','aaa');
已建立 1 行。
SQL> create index t_idx on t(id);
索引已建立。
SQL> create index t_idx1 on t(text);
索引已建立。
SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all indexed co
lumns',cascade=>true);
PL/SQL 過程已成功完成。
SQL> select id,count(*) from t group by id;
ID COUNT(*)
---------- ----------
1 331839
2 1
SQL> set autot trace exp stat
SQL> select * from t
2 where t.id in (select x.id from t x where x.text='aaa');
執行計劃
----------------------------------------------------------
Plan hash value: 2990025524
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 332K| 13M| 315 (3)| 00:00:04 |
|* 1 | HASH JOIN RIGHT SEMI | | 332K| 13M| 315 (3)| 00:00:04 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 7 | 154 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX1 | 7 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T | 332K| 7142K| 308 (2)| 00:00:04 |
由於Oracle不知道傳遞過來的ID列內容，因此，無法得到準確的資訊，10053顯示，Oracle估算的選擇度是1
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."ID"="X"."ID")
3 - access("X"."TEXT"='aaa')
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
1342 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
346 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from t
2 where t.id in (select /*+ precompute_subquery */ x.id from t x where x.text='aaa')
3 ;
執行計劃
----------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 22 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."ID"='2') 子查詢值預先計算，因此此處直接把值傳遞過來，Oracle可以得到準確的選擇度，因此走了索引掃描
統計資訊
----------------------------------------------------------
4 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
346 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
原始SQL，10053：
** Performing dynamic sampling initial checks. **
Column (#2): TEXT(VARCHAR2)
AvgLen: 21.00 NDV: 44554 Nulls: 0 Density: 2.2445e-05
Histogram: HtBal #Bkts: 75 UncompBkts: 75 EndPtVals: 76
Using density: 2.2445e-05 of col #2 as selectivity of unpopular value pred
** Dynamic sampling initial checks returning FALSE.
Using density: 2.2445e-05 of col #2 as selectivity of unpopular value pred
Table: T Alias: X
Card: Original: 332432 Rounded: 7 Computed: 7.46 Non Adjusted: 7.46
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 309.06 Resp: 309.06 Degree: 0
Cost_io: 303.00 Cost_cpu: 82948384
Resp_io: 303.00 Resp_cpu: 82948384
Using density: 2.2445e-05 of col #2 as selectivity of unpopular value pred
Access Path: index (AllEqRange)
Index: T_IDX1
resc_io: 10.00 resc_cpu: 74174
ix_sel: 2.2485e-05 ix_sel_with_filters: 2.2485e-05
Cost: 4.00 Resp: 4.00 Degree: 1
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Using density: 2.2445e-05 of col #2 as selectivity of unpopular value pred
Using density: 2.2445e-05 of col #2 as selectivity of unpopular value pred
Access Path: index (AllEqRange)
Index: T_IDX1
resc_io: 3.00 resc_cpu: 22964
ix_sel: 2.2485e-05 ix_sel_with_filters: 2.2485e-05
Cost: 1.20 Resp: 1.20 Degree: 0
****** finished trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: T_IDX
resc_io: 604.00 resc_cpu: 70669350
ix_sel: 1 ix_sel_with_filters: 1
Cost: 243.67 Resp: 243.67 Degree: 0
******** Cost index join ********
Index join: Considering index join to index T_IDX1
Index join: Joining index T_IDX
Ix HA Join
Outer table:
resc: 1.20 card 7.46 bytes: 31 deg: 1 resp: 1.20
Inner table:
resc: 304.58 card: 332432.00 bytes: 12 deg: 1 resp: 304.58
using dmeth: 2 #groups: 1
Cost per ptn: 2.93 #ptns: 1
hash_area: 2 (max=256) Hash join: Resc: 308.71 Resp: 308.71 [multiMatchCost=0.00]
******** Index join cost ********
Cost: 308.71
******** End index join costing ********
Best:: AccessPath: IndexRange Index: T_IDX1
Cost: 4.00 Degree: 1 Resp: 4.00 Card: 7.46 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
*** 2010-07-13 13:24:48.518
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
Table: T Alias: T
Card: Original: 332432 Rounded: 332432 Computed: 332432.00 Non Adjusted: 332432.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 307.85 Resp: 307.85 Degree: 0
Cost_io: 303.00 Cost_cpu: 66326784
Resp_io: 303.00 Resp_cpu: 66326784
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: T_IDX
resc_io: 604.00 resc_cpu: 70669350
ix_sel: 1 ix_sel_with_filters: 1
Cost: 243.67 Resp: 243.67 Degree: 0
Access Path: index (FullScan)
Index: T_IDX
resc_io: 604.00 resc_cpu: 70669350
ix_sel: 1 ix_sel_with_filters: 1
Cost: 243.67 Resp: 243.67 Degree: 0
****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: TableScan
Cost: 307.85 Degree: 1 Resp: 307.85 Card: 332432.00 Bytes: 0
***************************************
[ 本帖最後由 sundog315 於 2010-7-13 16:35 編輯 ]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19423/viewspace-667934/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Shell指令碼應用兩個例子指令碼
- 用一個通俗的例子講清楚APIAPI
- 物件導向的方法在遊戲中的應用的一個例子（上）(轉)物件遊戲
- 物件導向的方法在遊戲中的應用的一個例子（下）(轉)物件遊戲
- TinyXML應用例子XML
- 使用 ViroReact 開發增強實現應用的一個具體例子React
- 【設計模式】Java服務開發應用策略模式的一個例子設計模式Java
- OC RunLoop應用例子OOP
- TabStrip應用例子 (轉)
- 一個jquery的小例子jQuery
- 一個實際的例子學習 SAP BTP Java 應用的 @Before 註解使用方式Java
- 行列轉換的一個例子
- 一個可逆加密的例子 (轉)加密
- Angular Ngrx Store 應用程式狀態的一些典型例子Angular
- 用一個實際例子理解Dockervolume工作原理Docker
- 用java實現一個簡單的序列化的例子(轉)Java
- 用java實現一個簡單的序列化的例子 (轉)Java
- 一個複雜的json例子JSON
- 一個小例子搞懂redux的套路Redux
- 擼一個簡單的MVVM例子MVVM
- 第一個JavaScript的例子學習JavaScript
- 繫結變數的一個例子變數
- 分散式死鎖的一個例子分散式
- 面向切片程式設計(AOP)應用的一些實際例子程式設計
- 一個sql的行列轉置的例子SQL
- 用一個實際例子理解Docker volume工作原理Docker
- 用一個小例子教你入門機器學習框架TensorFlow機器學習框架
- 一個例子上手 SVG 動畫SVG動畫
- 一個行轉列的應用
- 一個jboss的應用問題
- 用一個通俗易懂的例子徹底說清楚單例模式單例模式
- 幾個行列轉換的實用小例子
- 一個簡單的iptable的應用
- 一個學習 Koa 原始碼的例子原始碼
- 從一個例子看Go的逃逸分析Go
- 一個最簡單的 Github workflow 例子Github
- 一個查詢不走索引的例子索引
- [Java]一個DeadLock(死鎖)的例子Java