CBO优化器的内部有两个重要的标准：selectivity and cardinality，cardinality 就CBO在处理完后返回的行数，selectivity是返回数据范围百分比，cardinality = (number of input rows)* selectivity,所以selectivity 的直接影响了CBO表访问路径，在like操作时比如绑定变量或join 表 column like column这种非常量的比较时CBO目前还没有那么伟大只是一个理想值5%，但有时big table 本来返回不足3%或更少时5%的大的Card就有可能在与第三张表关连时产生错误的评估，比如少量数据hash join +full table scan
下面做个实验分别掩饰绑定变量和 join column like column
sys@GGS>select org_name from icme.icme_org where org_code='000200180006001000010103';
Execution Plan
----------------------------------------------------------
Plan hash value: 3331258105
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 1 | 35 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_ICME_ORG_CODE | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
sys@GGS>select org_name from icme.icme_org where org_code like '000200180006001000010103';
Execution Plan
----------------------------------------------------------
Plan hash value: 3331258105
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 1 | 35 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_ICME_ORG_CODE | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
sys@GGS>var b varchar2(200);
sys@GGS>execute :b:='000200180006001000010103'
PL/SQL procedure successfully completed.
sys@GGS>select org_name from icme.icme_org where org_code like :b;
Execution Plan
----------------------------------------------------------
Plan hash value: 2212388312
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9357 | 319K| 505 (1)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 9357 | 319K| 505 (1)| 00:00:07 |
|* 2 | INDEX RANGE SCAN | PK_ICME_ORG_CODE | 1684 | | 11 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
sys@GGS>select 9357/num_rows from dba_tables where table_name='ICME_ORG' and OWNER='ICME';
9357/NUM_ROWS
-------------------------
.049998129812393466098842
–可以看到产生了Card 9357,刚好约为5% 。设置隐藏参数_like_with_bind_as_equality=true,默认false
sys@GGS>alter session set "_like_with_bind_as_equality"=true;
sys@GGS>select COLUMN_NAME,density from dba_tab_col_statistics where owner='ICME' and table_name='ICME_ORG' ;
COLUMN_NAME DENSITY
------------------------------ -------------------------
ORG_YUAN_CONTACT_PHONE 0
ORG_SECOND_NAME .5
ORG_RESP_CONTACT_PHONE 0
ORG_CONTACT_ADDRESS 0
ORG_ID .00000539738226959924
PARENT_ORG_ID .000137570504883753
ORG_CODE .00000539738226959924
ORG_SEQ .000284333238555587
ORG_NAME .000275482093663912
ORG_BUINESS_CODE .00000539738226959924
ORG_PASSWORD .2
ORG_TYPE_ID .00000269868631991499
ADMIN_ID .000721500721500722
CHECK_STUDENT .5
sys@GGS>select org_name from icme.icme_org where org_code like :b;
Execution Plan
----------------------------------------------------------
Plan hash value: 2212388312
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 70 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 2 | 70 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_ICME_ORG_CODE | 2 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORG_CODE" LIKE :B)
filter("ORG_CODE" LIKE :B)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
533 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sys@GGS>exec :b:='00020023';
sys@GGS>select org_name from icme.icme_org where org_code like :b||'%';
19996 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2212388312
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 70 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 2 | 70 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_ICME_ORG_CODE | 2 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORG_CODE" LIKE :B||'%')
filter("ORG_CODE" LIKE :B||'%')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9493 consistent gets
0 physical reads
0 redo size
476847 bytes sent via SQL*Net to client
15155 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19996 rows processed
references
“
In situations in which the LIKE pattern is expected to match very few rows, it is possible to set the hidden parameter _like_with_bind_as_equality to TRUE. This makes the
optimizer treat the predicate as though it were COLUMN = :BIND. In other words, the optimizer uses the column’s density as the selectivity instead of a fixed five percent.”
_like_with_bind_as_equality=true可以使用绑定变量的LIKE 转换为=:BIND的操作，使用column的density做为selectivity
Casd=2 是怎么来的呢，做个10053
########################################
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
Bind#0
oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=852 siz=32 off=0
kxsbbbfp=2b0736ad1d48 bln=32 avl=08 flg=05
value="00020023"
PARAMETERS WITH ALTERED VALUES
******************************
_pga_max_size = 1024000 KB
_smm_max_size = 378880 KB
optimizer_index_caching = 90
_like_with_bind_as_equality = true
Table Stats::
Table: ICME_ORG Alias: ICME_ORG
#Rows: 187147 #Blks: 2304 AvgRowLen: 81.00
Index: PK_ICME_ORG_CODE Col#: 3
LVLS: 2 #LB: 908 #DK: 188038 LB/K: 1.00 DB/K: 1.00 CLUF: 82996.00
Access Path: index (IndexOnly)
Index: PK_ICME_ORG_CODE
resc_io: 3.00 resc_cpu: 21964
ix_sel: 1.0741e-05 ix_sel_with_filters: 1.0741e-05
Cost: 3.00 Resp: 3.00 Degree: 0
SORT resource Sort statistics
Sort width: 2218 Area size: 1048576 Max Area size: 387973120
Degree: 1
Blocks to Sort: 1 Row size: 21 Total Rows: 2
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 8566955
Total Temp space used: 0
###########################################
Note
1.0741e-05=0.000010741
Card = index DK * ix_sel_with_filters = 188038*0.000010741 = 2.019716158 (Just my guess)
接着实验 5% selectivity 与第三张表关连的问题
sys@GGS>SELECT
2 t2.org_name
3 FROM icme.icme_org t1, icme.icme_org t2
4 WHERE t1.org_id = 100019697
5 AND t2.org_code LIKE t1.org_code||'%';
Execution Plan
----------------------------------------------------------
Plan hash value: 2241655122
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9357 | 612K| 507 (1)| 00:00:07 |
| 1 | NESTED LOOPS | | 9357 | 612K| 507 (1)| 00:00:07 |
| 2 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 1 | 32 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_ICME_ORG | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 9357 | 319K| 505 (1)| 00:00:07 |
|* 5 | INDEX RANGE SCAN | PK_ICME_ORG_CODE | 1684 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."ORG_ID"=100019697)
5 - access("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
filter("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
533 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
tip:
关于就返回1条数据，Cardinality 确是9357，Cardinality =187147* 5% ，做10053 event
****** finished trying bitmap/domain indexes ******
Best NL cost: 506.71
resc: 506.71 resc_io: 506.00 resc_cpu: 6070310
resp: 506.71 resp_io: 506.00 resp_cpu: 6070310
Join Card: 9357.35 = outer (1.00) * inner (187147.00) * sel (0.05)
Join cardinality for HJ/SMJ (no post filters): 187147.00, outer: 1.00, inner: 187147.00, sel: 1
Join Card - Rounded: 9357 Computed: 9357.35
Best:: JoinMethod: NestedLoop
Cost: 506.71 Degree: 1 Resp: 506.71 Card: 9357.35 Bytes: 67
***********************
sys@GGS>SELECT /*+gather_plan_statistics anbob*/
2 t3.ic_code,
3 t3.name,
4 t3.sex,
5 t2.org_name
6 FROM icme.icme_org t1, icme.icme_org t2, icme.icme_student t3
7 WHERE t1.org_id = 100019697
8 AND t2.org_code LIKE t1.org_code || '%'
9 AND t3.org_id = t2.org_id;
Plan hash value: 1682151602
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.01 | 54500 |
|* 1 | HASH JOIN | | 1 | 74694 | 20 |00:00:00.01 | 54500 |
| 2 | NESTED LOOPS | | 1 | 9357 | 1 |00:00:00.01 | 8 |
| 3 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 4 | INDEX UNIQUE SCAN | PK_ICME_ORG | 1 | 1 | 1 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 1 | 9357 | 1 |00:00:00.01 | 5 |
|* 6 | INDEX RANGE SCAN | PK_ICME_ORG_CODE | 1 | 1684 | 1 |00:00:00.01 | 4 |
| 7 | TABLE ACCESS FULL | ICME_STUDENT | 1 | 1478K| 1490K|00:00:01.49 | 54492 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."ORG_ID"="T2"."ORG_ID")
4 - access("T1"."ORG_ID"=100019697)
6 - access("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
filter("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
54499 consistent gets
0 physical reads
0 redo size
1421 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
tip：
如果1条数据的结果集与另一张1478K记录的表关连选把用hash_join and TFS ICME_STUDENT的执行计划很不不好，业务上是返回这个机构的人员只有20人，说明这是一个特例通常不可能是1条记录还自
关连。org_code是个树code，child是parent code向后延伸4位+1. e.g. parent code=001,child code=0010001.
用cardinality hint提示CBO后
SELECT /*+ gather_plan_statistics anbob cardinality(t2 1) */
t3.ic_code,
t3.name,
t3.sex,
t2.org_name
FROM icme.icme_org t1, icme.icme_org t2, icme.icme_student t3
WHERE t1.org_id = 100019697
AND t2.org_code LIKE t1.org_code || '%'
AND t3.org_id = t2.org_id
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 |
| 1 | TABLE ACCESS BY INDEX ROWID | ICME_STUDENT | 1 | 14 | 20 |00:00:00.01 | 24 |
| 2 | NESTED LOOPS | | 1 | 1 | 22 |00:00:00.01 | 13 |
| 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 8 |
| 4 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 5 | INDEX UNIQUE SCAN | PK_ICME_ORG | 1 | 1 | 1 |00:00:00.01 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 1 | 1 | 1 |00:00:00.01 | 5 |
|* 7 | INDEX RANGE SCAN | PK_ICME_ORG_CODE | 1 | 1684 | 1 |00:00:00.01 | 4 |
|* 8 | INDEX RANGE SCAN | IDX_STUDENT_ORGID | 1 | 14 | 20 |00:00:00.01 | 5 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ORG_ID"=100019697)
7 - access("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
filter("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
8 - access("T3"."ORG_ID"="T2"."ORG_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
1421 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
sys@GGS>SELECT /*+ cardinality(t2 1)*/
2 t3.ic_code,
3 t3.name,
4 t3.sex,
5 t2.org_name
6 FROM icme.icme_org t1, icme.icme_org t2, icme.icme_student t3
7 WHERE t1.org_id = 37000000
8 AND t2.org_code LIKE t1.org_code || '%'
9 AND t3.org_id = t2.org_id;
241593 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4009788969
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 516 (1)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID | ICME_STUDENT | 14 | 378 | 9 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 101 | 516 (1)| 00:00:07 |
| 3 | NESTED LOOPS | | 1 | 74 | 507 (1)| 00:00:07 |
| 4 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 1 | 32 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_ICME_ORG | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 1 | 42 | 505 (1)| 00:00:07 |
|* 7 | INDEX RANGE SCAN | PK_ICME_ORG_CODE | 1684 | | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_STUDENT_ORGID | 14 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ORG_ID"=37000000)
7 - access("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
filter("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
8 - access("T3"."ORG_ID"="T2"."ORG_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
270480 consistent gets
0 physical reads
0 redo size
8458866 bytes sent via SQL*Net to client
177658 bytes received via SQL*Net from client
16108 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
241593 rows processed
sys@GGS>SELECT
2 t3.ic_code,
3 t3.name,
4 t3.sex,
5 t2.org_name
6 FROM icme.icme_org t1, icme.icme_org t2, icme.icme_student t3
7 WHERE t1.org_id = 37000000
8 AND t2.org_code LIKE t1.org_code || '%'
9 AND t3.org_id = t2.org_id;
241593 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1682151602
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74694 | 7367K| 12527 (2)| 00:02:31 |
|* 1 | HASH JOIN | | 74694 | 7367K| 12527 (2)| 00:02:31 |
| 2 | NESTED LOOPS | | 9357 | 676K| 507 (1)| 00:00:07 |
| 3 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 1 | 32 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_ICME_ORG | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 9357 | 383K| 505 (1)| 00:00:07 |
|* 6 | INDEX RANGE SCAN | PK_ICME_ORG_CODE | 1684 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | ICME_STUDENT | 1478K| 38M| 12003 (2)| 00:02:25 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."ORG_ID"="T2"."ORG_ID")
4 - access("T1"."ORG_ID"=37000000)
6 - access("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
filter("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
77329 consistent gets
0 physical reads
0 redo size
10225534 bytes sent via SQL*Net to client
177658 bytes received via SQL*Net from client
16108 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
241593 rows processed
Note:
cardinality hint 后发现icme_org在记录少与另一张表关连时走了正常的索引，CR block 有原来的54499 变成了加hint后的24，但是当ICME_ORG表如果返回一个大的结果集时原来的HINT就通用了
如org_id=37000000时，有hint cr block是270480而没hint走全表是77329.所以我改用重写sql
sys@GGS>SELECT
2 t2.org_name
3 FROM icme.icme_org t1, icme.icme_org t2
4 WHERE t1.org_id = 37000000
5 AND t2.org_code LIKE t1.org_code||'%';
19996 rows selected.
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9357 | 612K| 507 (1)| 00:00:07 |
| 1 | NESTED LOOPS | | 9357 | 612K| 507 (1)| 00:00:07 |
| 2 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 1 | 32 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_ICME_ORG | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| ICME_ORG | 9357 | 319K| 505 (1)| 00:00:07 |
|* 5 | INDEX RANGE SCAN | PK_ICME_ORG_CODE | 1684 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9496 consistent gets
0 physical reads
0 redo size
476847 bytes sent via SQL*Net to client
15155 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19996 rows processed
sys@GGS>select org_id,org_name from icme.icme_org
start with org_id=37000000 connect by prior org_id=parent_org_id ;
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 187K| 4386K| 198 (2)| 00:00:03 |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 2 | INDEX FAST FULL SCAN | IDX_ICME_ORG_ID_PID_NAME | 187K| 4386K| 198 (2)| 00:00:03 |
--------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
896 consistent gets
0 physical reads
0 redo size
602504 bytes sent via SQL*Net to client
15155 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
19996 rows processed
--先改写第一部份，改用start with后cr blocks有原来的 9496降到896，再结合实际的业务人员是挂在组构的叶子上的,so 改写上面的与第三个表关连后的sql为
sys@GGS>WITH c
2 AS ( SELECT org_id, org_name
3 FROM icme.icme_org
4 WHERE CONNECT_BY_ISLEAF = 1
5 START WITH org_id = 37000000
6 CONNECT BY PRIOR org_id = parent_org_id)
7 SELECT t3.ic_code,
8 t3.name,
9 t3.sex,
10 c.org_name
11 FROM c, icme.icme_student t3
12 WHERE c.org_id = t3.org_id;
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1493K| 131M| | 15652 (2)| 00:03:08 |
|* 1 | HASH JOIN | | 1493K| 131M| 13M| 15652 (2)| 00:03:08 |
| 2 | VIEW | | 187K| 11M| | 198 (2)| 00:00:03 |
|* 3 | FILTER | | | | | | |
|* 4 | CONNECT BY NO FILTERING WITH START-WITH| | | | | | |
| 5 | INDEX FAST FULL SCAN | IDX_ICME_ORG_ID_PID_NAME | 187K| 4386K| | 198 (2)| 00:00:03 |
| 6 | TABLE ACCESS FULL | ICME_STUDENT | 1478K| 38M| | 12003 (2)| 00:02:25 |
-------------------------------------------------------------------------------------------------------------------------------
Tip:
到这里剩下的工作就是调ICME_STUDENT 的访问表结构了，如分区，索引..
加hint的sql时一定要考虑通用性，上篇也提到过优化的顺序改写sql优先与使用hint
Summary:
1,where 条件 like 绑定变量时CBO默认会取5% 的selectivity，so，执行计划中erows>arows
2,当多表之间以like 方式关连时，CBO默认也是会以5% 的selectivity计算card，当card大于实际的时再与其它表关连有可能会产生TFS替换INDEX SCAN的低效执行计划。
小建议：在summary加上like容易导致selectivity计算错误，summary写的文不对题啊，看半天才明白，用的时候要注意
感谢指正
以上测试在10205版本
在11G中的Extended Statistics 也许可以解决