PRECOMPUTE_SUBQUERY应用的一个例子（转）

最新推荐文章于 2021-04-02 22:59:19 发布

congtudi4111

最新推荐文章于 2021-04-02 22:59:19 发布

阅读量147

点赞数

PRECOMPUTE_SUBQUERY应用的一个例子

http://space.itpub.net/19423/viewspace-667934

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
***************************************