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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/19423/viewspace-667934/