关于添加rownum条件count stopkey执行计划对于cost成本估算的影响

监控系统发现一个sql语句的相应时间特别长，消耗的IO资源也非常高，但是cost成本却非常低

SQL> SELECT A.ATTRVALUE, B.TYPENAME
2 FROM tbcs.GROUP_SUBS_MEMBER_ATTR A, tbcs.RESOURCE_TYPE B
3 WHERE A.ATTRID = 'res_type'
4 AND A.REGION = 23
5 AND B.ITEMID = A.ATTRVALUE
6 AND ROWNUM = 1;

Elapsed: 00:03:18.62

Execution Plan
----------------------------------------------------------
Plan hash value: 1650466411

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 7 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| IM_RES_TYPE | 1 | 36 | 1 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 4 | 165 | 7 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE SINGLE | | 4 | 76 | 3 (0)| 00:00:01 | 4 | 4 |
|* 5 | TABLE ACCESS FULL | GROUP_SUBS_MEMBER_ATTR | 4 | 76 | 3 (0)| 00:00:01 | 4 | 4 |
|* 6 | INDEX UNIQUE SCAN | PK_IMRESTYPE | 1 | | 0 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
5 - filter("A"."ATTRID"='res_type' AND "A"."ATTRVALUE" IS NOT NULL AND "A"."REGION"=23)
6 - access("RES_TYPE_ID"="A"."ATTRVALUE")

Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
469240 consistent gets
469186 physical reads
0 redo size
619 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

那么问题是为什么cost的成本这么低，但是逻辑读确如此之高。

首先这里简单的分析下这个执行计划为什么会有如此高的逻辑读，先来看下面的查询：

SQL> select count(*)
2 from tbcs.GROUP_SUBS_MEMBER_ATTR A
3 where A.ATTRID = 'res_type'
4 AND A.REGION = 23
5 and "A"."ATTRVALUE" IS NOT NULL
6 ;

COUNT(*)
----------
14

而且这部分数据都在rownum 7千万以上的位置。

SQL> select cn from (select a.*, rownum cn from tbcs.GROUP_SUBS_MEMBER_ATTR A where region=23)b where b. ATTRID = 'res_type' and ATTRVALUE IS NOT NULL

CN
----------
72251394
72253121
72261116
72287094
72292151
72296915
72296922
72304758
72333694
72334266
72334281
72334924
72336096
72336103

14 rows selected.

虽然sql语句加上了rownum=1的限制，但是由于tbcs.GROUP_SUBS_MEMBER_ATTR A中(“A”.”ATTRID”=’res_type’ AND “A”.”ATTRVALUE” IS NOT NULL AND “A”.”REGION”=23)的满足这个条件的第一行数据在分区段的较后面的block中（全表扫描也是有顺序的）。此时这个单分区全表扫描 count stopeky的时候也要遍历这个分区的大部分block才能找到满足(“A”.”ATTRID”=’res_type’ AND “A”.”ATTRVALUE” IS NOT NULL AND “A”.”REGION”=23)的第一行数据，然后将这行数据同时与IM_RES_TYPE表做nested loop，当发现这行数据满足”RES_TYPE_ID”=”A”.”ATTRVALUE”即终止查询。如果不满足再去单分区全扫描找第二行满足谓词条件5的数据行，然后再去和IM_RES_TYPE表比对，一直到找到符合条件的数据行为止。

如果全表扫描前几次IO能够扫描的block刚好能够满足谓词条件，则加上rownum限制条件确实是能够减少大部分的IO消耗。

sys@CRMDB1>select attrid from tbcs.GROUP_SUBS_MEMBER_ATTR A where region=23 and ATTRVALUE IS NOT NULL rownum=1;

ATTRID
--------------------------------
Flag1

1 row selected.

sys@CRMDB1>select * from tbcs.GROUP_SUBS_MEMBER_ATTR A where region=23 and attrid='Flag1' and ATTRVALUE IS NOT NULL and rownum=1;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 484799315

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE SINGLE| | 2 | 82 | 2 (0)| 00:00:01 | 4 | 4 |
|* 3 | TABLE ACCESS FULL | GROUP_SUBS_MEMBER_ATTR | 2 | 82 | 2 (0)| 00:00:01 | 4 | 4 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
3 - filter("ATTRID"='Flag1' AND "A"."ATTRVALUE" IS NOT NULL AND "REGION"=23)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
968 bytes sent via SQL*Net to client
521 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

由于满足谓词5的数据都在分区段的后面的block中，所以这个sql会消耗特别多的IO资源。

下面我们来解释下为什么在单分区全表扫描然后count stopkey时cbo的评估的cost成本只有3，先来看下表的统计信息：

关于GROUP_SUBS_MEMBER_ATTR表的统计信息：

Table                                  Number                        Empty    Chain Average Global         Sample Date
Name                                  of Rows          Blocks       Blocks    Count Row Len Stats            Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
GROUP_SUBS_MEMBER_ATTR            344,752,080       22,395,19            0        0      41 YES        17,237,604 09-10-2014

Column                             Distinct              Number       Number         Sample Date
Name                                 Values     Density Buckets        Nulls           Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
REGION                                    4   .25000000       1            0     17,237,604 09-10-2014
ATTRID                                  166   .00602410       1            0     17,237,604 09-10-2014
ATTRVALUE                           189,682   .00000527       1   60,824,860     14,196,361 09-10-2014

Index                                      Leaf       Distinct         Number      AV      Av      Cluster Date
Name                           BLV         Blks           Keys        of Rows     LEA    Data       Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
PK_CM_GROUP_MEMBERATTR           3    3,462,820    341,230,660    341,230,660       1       1 108,506,400 09-10-2014

index                          Column                          Col Column
Name                           Name                            Pos Details
------------------------------ ------------------------------ ---- ------------------
PK_CM_GROUP_MEMBERATTR         GRPSUBSMEMOID                     1 NUMBER(18,0) NOT NULL
                               ATTRID                            2 VARCHAR2(32) NOT NULL
                               REGION                            3 NUMBER(5,0) NOT NULL

**********************************************************
Partition Level
**********************************************************

Partition              Number                        Empty Average    Chain Average Global Date
Name                  of Rows          Blocks       Blocks   Space    Count Row Len Stats MM-DD-YYYY
-------------- -------------- --------------- ------------ ------- -------- ------- ------ ----------
P_R_20            107,562,800        7,002,86            0       0        0      41 YES    10-10-2014
P_R_21             65,051,340        4,220,31            0       0        0      41 YES    07-07-2014
P_R_22             89,764,040        5,816,18            0       0        0      41 YES    08-07-2014
P_R_23             77,962,200        5,069,40            0       0        0      41 YES    07-26-2014
P_R_99                      0            0,00            0       0        0       0 YES    09-15-2013

对于attrid的num_distinct是166，num_nulls是0，attrvalue的num_distinct是189682，num_nulls是60824860，而且他们的number buckets都是1，也就是没有直方图，此时cbo要根据(“A”.”ATTRID”=’res_type’ AND “A”.”ATTRVALUE” IS NOT NULL AND “A”.”REGION”=23）谓词条件取出满足条件的数据，由于根据统计信息得出大部分的数据都是满足于这个条件的，cbo会认为只需要全表扫描的前几次IO都能够取出满足条件的数据。（关于选择率和cost成本的计算太过于复杂，不属于本篇blog讨论的范围）

单独分离出来这部分查询cbo估算的执行成本也很低，但是消耗的逻辑读还是特别高。

sys@CRMDB1>select * from tbcs.GROUP_SUBS_MEMBER_ATTR A where rownum=1 and region=23 and ATTRID = 'res_type' AND "A"."ATTRVALUE" IS NOT NULL

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 484799315

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE SINGLE| | 2 | 82 | 2 (0)| 00:00:01 | 4 | 4 |
|* 3 | TABLE ACCESS FULL | GROUP_SUBS_MEMBER_ATTR | 2 | 82 | 2 (0)| 00:00:01 | 4 | 4 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
3 - filter("ATTRID"='Flag1' AND "A"."ATTRVALUE" IS NOT NULL AND "REGION"=23)

Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
469266 consistent gets
468242 physical reads
0 redo size
984 bytes sent via SQL*Net to client
521 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

这里由于(“ATTRID”=’Flag1′ AND “A”.”ATTRVALUE” IS NOT NULL AND “REGION”=23)的数据具有较大的倾斜性且没有直方图，导致了cbo全表扫描count stopkey时错误的估算了成本。

下面xiaoyu新建一个表来测试有直方图和没直方图时估算类似rownum=1 and column1=A这类查询的成本区别。

SQL> select owner,count(*) from tab01 group by owner;

OWNER COUNT(*)
------------------------------ ----------
OWBSYS_AUDIT 24
MDSYS 4022
QWE 1
PUBLIC 67990
OUTLN 20
CTXSYS 778
OLAPSYS 1442
FLOWS_FILES 26
OWBSYS 4
SYSTEM 1236
ORACLE_OCM 16
EXFSYS 624
APEX_030200 5122
DBSNMP 114
ORDSYS 5026
ORDPLUGINS 20
SYSMAN 7108
APPQOSSYS 10
XDB 2336
ORDDATA 514
XIAOYU 154
SYS 75434
WMSYS 666

23 rows selected.

SQL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'TAB01',method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly;
SQL> select * from tab01 where rownum=1 and owner='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 4034257318

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE ALL| | 2 | 196 | 2 (0)| 00:00:01 | 1 | 40 |
|* 3 | TABLE ACCESS FULL | TAB01 | 2 | 196 | 2 (0)| 00:00:01 | 1 | 40 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
3 - filter("OWNER"='SYS')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
45 physical reads
0 redo size
1605 bytes sent via SQL*Net to client
523 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 tab01 where rownum=1 and owner='XIAOYU';

Execution Plan
----------------------------------------------------------
Plan hash value: 4034257318

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE ALL| | 2 | 196 | 2 (0)| 00:00:01 | 1 | 40 |
|* 3 | TABLE ACCESS FULL | TAB01 | 2 | 196 | 2 (0)| 00:00:01 | 1 | 40 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
3 - filter("OWNER"='XIAOYU')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2576 consistent gets
2673 physical reads
0 redo size
1615 bytes sent via SQL*Net to client
523 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 tab01 where rownum=1 and owner='IMP';

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 4034257318

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE ALL| | 2 | 196 | 2 (0)| 00:00:01 | 1 | 40 |
|* 3 | TABLE ACCESS FULL | TAB01 | 2 | 196 | 2 (0)| 00:00:01 | 1 | 40 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
3 - filter("OWNER"='IMP')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3359 consistent gets
3263 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

这里看出来在num bucket等于1时，全表扫描然后count stop key成本估算都是2，但是由于数据的分布问题实际的逻辑读是有量变的。

SQL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'TAB01',method_opt=>'for all columns size auto');

PL/SQL procedure successfully completed.

SQL> select * from tab01 where rownum=1 and owner='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 4034257318

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE ALL| | 2 | 196 | 2 (0)| 00:00:01 | 1 | 40 |
|* 3 | TABLE ACCESS FULL | TAB01 | 2 | 196 | 2 (0)| 00:00:01 | 1 | 40 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
3 - filter("OWNER"='SYS')

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
45 physical reads
0 redo size
1605 bytes sent via SQL*Net to client
523 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 tab01 where rownum=1 and owner='XIAOYU';

Execution Plan
----------------------------------------------------------
Plan hash value: 4034257318

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 25 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE ALL| | 2 | 196 | 25 (0)| 00:00:01 | 1 | 40 |
|* 3 | TABLE ACCESS FULL | TAB01 | 2 | 196 | 25 (0)| 00:00:01 | 1 | 40 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
3 - filter("OWNER"='XIAOYU')

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2576 consistent gets
2673 physical reads
0 redo size
1615 bytes sent via SQL*Net to client
523 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 tab01 where rownum=1 and owner='IMP';

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 4034257318

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 161 (0)| 00:00:02 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE ALL| | 2 | 196 | 161 (0)| 00:00:02 | 1 | 40 |
|* 3 | TABLE ACCESS FULL | TAB01 | 2 | 196 | 161 (0)| 00:00:02 | 1 | 40 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
3 - filter("OWNER"='IMP')

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3359 consistent gets
3263 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

如果收集该列的直方图，此时优化器清楚的知道谓词条件的数据分布，对于这类查询优化器就能够较准确的评估cost的成本。

优化这个sql并不难，有一个思路就是利用小表IM_RES_TYPE去做驱动表驱动大表GROUP_SUBS_MEMBER_ATTR，可以利用attrvalue和attrid的等值条件创建索引来避免对大表的单分区全扫描。

直接创建(ATTRVALUE+ATTRID)的索引。

explain plan for
SELECT A.ATTRVALUE, B.TYPENAME
FROM tbcs.GROUP_SUBS_MEMBER_ATTR A, tbcs.RESOURCE_TYPE B
WHERE A.ATTRID = 'res_type'
AND A.REGION = 23
AND B.ITEMID = A.ATTRVALUE
AND ROWNUM = 1;

Plan hash value: 1650466411

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 7 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| IM_RES_TYPE | 1 | 36 | 1 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 4 | 165 | 7 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE SINGLE | | 4 | 76 | 3 (0)| 00:00:01 | 4 | 4 |
|* 5 | TABLE ACCESS FULL | GROUP_SUBS_MEMBER_ATTR | 4 | 76 | 3 (0)| 00:00:01 | 4 | 4 |
|* 6 | INDEX UNIQUE SCAN | PK_IMRESTYPE | 1 | | 0 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
5 - filter("A"."ATTRID"='res_type' AND "A"."ATTRVALUE" IS NOT NULL AND "A"."REGION"=23)
6 - access("RES_TYPE_ID"="A"."ATTRVALUE")

这里默认情况下cbo根据cost成本估算并不会选择先用tbcs.RESOURCE_TYPE B去做驱动表做nested loop关联，这是因为两个sql的执行成本太接近，优化器既有可能选择全表扫描count stopkey，也有可能选择索引扫描count stopkey，但是消耗的IO资源是存在量变的。

SQL> SELECT /*+leading(B A)*/A.ATTRVALUE, B.TYPENAME
2 FROM tbcs.GROUP_SUBS_MEMBER_ATTR A, tbcs.RESOURCE_TYPE B
3 WHERE A.ATTRID = 'res_type'
4 AND A.REGION = 23
5 AND B.ITEMID = A.ATTRVALUE
6 AND ROWNUM = 1;

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 458037665

-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 14 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| GROUP_SUBS_MEMBER_ATTR | 1 | 19 | 12 (0)| 00:00:01 | 4 | 4 |
| 3 | NESTED LOOPS | | 10 | 55 | 14 (0)| 00:00:01 | | |
| 4 | TABLE ACCESS FULL | IM_RES_TYPE | 1 | 36 | 2 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE SINGLE | | 10 | | 3 (0)| 00:00:01 | 4 | 4 |
|* 6 | INDEX RANGE SCAN | IND_ATTRVALUE_ATTRID | 10 | | 3 (0)| 00:00:01 | 4 | 4 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
2 - filter("A"."REGION"=23)
6 - access("RES_TYPE_ID"="A"."ATTRVALUE" AND "A"."ATTRID"='res_type')
filter("A"."ATTRVALUE" IS NOT NULL)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
370 consistent gets
0 physical reads
0 redo size
619 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

这里需要强制加上leading(B A)让优化器先以tbcs.RESOURCE_TYPE B去做驱动表，然后驱动GROUP_SUBS_MEMBER_ATTR A表。

我们这里还可以有个想法就是，既然原sql消耗的成本主要在全表扫描取(“A”.”ATTRID”=’res_type’ AND “A”.”ATTRVALUE” IS NOT NULL AND “A”.”REGION”=23)的数据，那么我们能否用另外的一个取数据的办法就是通过index range scan的方式了，那我们能否让cbo走(ATTRVALUE+ATTRID)的索引来取数据。

SQL> SELECT /*+index(A ind_attrvalue_attrid)*/A.ATTRVALUE, B.TYPENAME
2 FROM tbcs.GROUP_SUBS_MEMBER_ATTR A, tbcs.RESOURCE_TYPE B
3 WHERE A.ATTRID = 'res_type'
4 AND A.REGION = 23
5 AND B.ITEMID = A.ATTRVALUE
6 AND ROWNUM = 1;

Elapsed: 00:02:21.41

Execution Plan
----------------------------------------------------------
Plan hash value: 485372855

--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 11 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | IM_RES_TYPE | 1 | 36 | 1 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 4 | 165 | 11 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE SINGLE | | 4 | 76 | 7 (0)| 00:00:01 | 4 | 4 |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID| GROUP_SUBS_MEMBER_ATTR | 4 | 76 | 7 (0)| 00:00:01 | 4 | 4 |
|* 6 | INDEX FULL SCAN | IND_ATTRVALUE_ATTRID | 98189 | | 6 (0)| 00:00:01 | 4 | 4 |
|* 7 | INDEX UNIQUE SCAN | PK_IMRESTYPE | 1 | | 0 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
5 - filter("A"."REGION"=23)
6 - access("A"."ATTRID"='res_type')
filter("A"."ATTRID"='res_type' AND "A"."ATTRVALUE" IS NOT NULL)
7 - access("RES_TYPE_ID"="A"."ATTRVALUE")

Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
227934 consistent gets
227981 physical reads
0 redo size
619 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

cob采取比较糟糕的index full scan然会回表的方式，并没有采取我们所认为的index range scan的方式来回表，那我们想想为什么优化器不能够采用index range scan的方式了，这里是因为我们创建索引是(ATTRVALUE+ATTRID)的组合索引，在这个索引取数据时由于”A”.”ATTRVALUE” IS NOT NULL并不是一个等值的条件，而这个attrvalue又是前导列，这个导致优化器如果想走index range scan需要走类似的index range scan然后 INLIST ITERATOR迭代的方式，由于attrvalue有很大一部分的null value，这将会导致这部分执行cbo估算时较高，而不选择这种执行计划，进而选择了更糟糕的index full scan回表的方式，由于attrid=’res_type’ 具有很大的倾斜性，刚好这部分数据又在索引的后面的几个leaf block中。

再来想想(ATTRID+ATTRVALUE)复合索引，通过attrid是索引前导列，(“A”.”ATTRID”=’res_type’ AND “A”.”ATTRVALUE” IS NOT NULL AND “A”.”REGION”=23)这个谓词条件利用attrid是索引前导列的复合索引可以很精准的从root到branch再到leaf block，从而在leaf block时index range scan。

SQL> SELECT A.ATTRVALUE, B.TYPENAME
2 FROM tbcs.GROUP_SUBS_MEMBER_ATTR A, tbcs.RESOURCE_TYPE B
3 WHERE A.ATTRID = 'res_type'
4 AND A.REGION = 23
5 AND B.ITEMID = A.ATTRVALUE
6 AND ROWNUM = 1;

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 2801988880

--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 9 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | IM_RES_TYPE | 1 | 36 | 1 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 4 | 165 | 9 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE SINGLE | | 4 | 76 | 5 (0)| 00:00:01 | 4 | 4 |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID| GROUP_SUBS_MEMBER_ATTR | 4 | 76 | 5 (0)| 00:00:01 | 4 | 4 |
|* 6 | INDEX RANGE SCAN | IND_ATTRID_ATTRVALUE | 98189 | | 4 (0)| 00:00:01 | 4 | 4 |
|* 7 | INDEX UNIQUE SCAN | PK_IMRESTYPE | 1 | | 0 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
5 - filter("A"."REGION"=23)
6 - access("A"."ATTRID"='res_type')
filter("A"."ATTRVALUE" IS NOT NULL)
7 - access("RES_TYPE_ID"="A"."ATTRVALUE")

Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
10 consistent gets
5 physical reads
0 redo size
619 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

这里我们需要注意的是在表中添加了rownum的限制又添加了部分谓词的条件时，刚好满足这个谓词条件对应的数据块在表段较后面的数据块中，且对应的列没有直方图又存在数据倾斜，此时cbo估算这个全表扫描 count stopkey成本往往是不准确的，当然上面这个例子确实比较特殊，没有直方图且数据有倾斜性，这里借助这个sql case分析只是为了让我们更好的理解nested loop的原理、以及rownum对于表扫描索引扫描时执行计划和成本估算的影响。