Resp: 5.5074 Resp_io: 5.0000 Resc_cpu: 7617598
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "T1"."X" "X","T1"."NAME" "NAME" FROM "SYS"."T" "T","SYS"."T1" "T1" WHERE "T"."X"<100 AND "T1"."X"<100 AND "T"."X"="T1"."X"
kkoqbc-subheap (delete addr=0x2b4f6501c000, in-use=24512, alloc=26368)
集中关联方式的代价如下：
T关联T1 代价
ENSTED LOOPS JOIN(NL Join) 100.24
SORT MERGE JOIN(SM Join) 6.01
HASH JOIN(HA Join) 5.51
T1关联T
ENSTED LOOPS JOIN(NL Join) 102.06
SORT MERGE JOIN(SM Join) 6.01
HASH JOIN(HA Join) 5.51
尽管两种关联顺序里面的Hash Join的代价都是一样的5.51，但是第一个表关联的顺序的resc_cpu的代价要小，索引CBO最终选择了第一个表关联顺序的下的HASH JOIN
方式作为最终的执行计划，即T关联T1通过Hash Join的方式来做关联的代价最小。
下面这个部分是通过CBO给出的一个执行计划报告，其中ROWS例对应于上面每个操作的cardinality rounded cost 对应于每一个操作的cost值。
============
Plan Table
============
------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | HASH JOIN | | 81 | 810 | 6 | 00:00:01 |
| 2 | INDEX RANGE SCAN | IND_T | 82 | 328 | 2 | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | T1 | 99 | 594 | 3 | 00:00:01 |
| 4 | INDEX RANGE SCAN | IND_T1 | 99 | | 2 | 00:00:01 |
------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T"."X"="T1"."X")
2 - access("T"."X"<100)
4 - access("T1"."X"<100)
Content of other_xml column
===========================
db_version : 10.2.0.4
plan_hash : 2145127414
Outline Data:
通过www.2cto.com一系列的计算和比较，CBO最终选择了上面的执行计划作为SQL的最终执行计划。trace文件最后，这里不做讨论。
下面是帮一个网友看的一个问题
这个是测试环境，走索引的
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for CLAIMS[CLAIMS]
Table: CLAIMS Alias: CLAIMS
Card: Original: 100817.000000 Rounded: 6470 Computed: 6469.52 Non Adjusted: 6469.52
Access Path: TableScan
Cost: 2845.00 Resp: 2845.00 Degree: 0
Cost_io: 2805.00 Cost_cpu: 662177710
Resp_io: 2805.00 Resp_cpu: 662177710
Access Path: index (RangeScan)
Index: CLAIMS_IDX16
resc_io: 6904.60 resc_cpu: 275804847
ix_sel: 0.333333 ix_sel_with_filters: 0.333333
Cost: 6921.26 Resp: 6921.26 Degree: 1
Access Path: index (RangeScan)
Index: CLAIMS_IDX30
resc_io: 3346.60 resc_cpu: 171975781
ix_sel: 0.500000 ix_sel_with_filters: 0.500000
Cost: 3356.98 Resp: 3356.98 Degree: 1
****** trying bitmap/domain indexes ******
Access Path: index (AllEqRange)
Index: CLAIMS_IDX16
resc_io: 46.00 resc_cpu: 3689036
ix_sel: 0.166667 ix_sel_with_filters: 0.166667
Cost: 46.22 Resp: 46.22 Degree: 0
Access Path: index (AllEqRange)
Index: CLAIMS_IDX16
resc_io: 46.00 resc_cpu: 3689036
ix_sel: 0.166667 ix_sel_with_filters: 0.166667
Cost: 46.22 Resp: 46.22 Degree: 0
Access Path: index (AllEqRange)
Index: CLAIMS_IDX30
resc_io: 33.00 resc_cpu: 2589658
ix_sel: