对分区表的记录数为0的分区键的查询将导致成本估算为0

最近走错执行计划的SQL语句真是频繁出现，又遇到了SQL语句执行计划走错的问题，估算的成本竟然为0。
问题重现如下：
首先创建一个10个分区的分区表

SQL> DROP TABLE TEST PURGE;

Table dropped.

SQL> CREATE TABLE TEST(ID CHAR(8),COLNO VARCHAR2(30),NAME VARCHAR2(8),CONTENTS CHAR(500))
  2  PARTITION BY LIST(ID)
  3  (
  4  PARTITION P1  VALUES('00000001'),
  5  PARTITION P2  VALUES('00000002'),
  6  PARTITION P3  VALUES('00000003'),
  7  PARTITION P4  VALUES('00000004'),
  8  PARTITION P5  VALUES('00000005'),
  9  PARTITION P6  VALUES('00000006'),
 10  PARTITION P7  VALUES('00000007'),
 11  PARTITION P8  VALUES('00000008'),
 12  PARTITION P9  VALUES('00000009'),
 13  PARTITION P10 VALUES('00000010')
 14  )
 15  /

Table created.

向分区表中插入记录，其中P8,P9,P10中并没有记录。
SQL> INSERT INTO TEST SELECT LPAD(TRUNC(DBMS_RANDOM.VALUE(1,8)),8,'0'),RPAD(REVERSE(OBJECT_NAME),30,'X'),SUBSTR

(OBJECT_NAME,1,4),OBJECT_NAME||'***'||OBJECT_TYPE FROM ALL_OBJECTS ;

50633 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT DISTINCT ID FROM  TEST;

ID
----------------
00000001
00000002
00000003
00000004
00000005
00000006
00000007

7 rows selected.

SQL> SELECT COUNT(1) FROM TEST PARTITION (P8);

  COUNT(1)
----------
         0

SQL> SELECT COUNT(1) FROM TEST PARTITION (P9);

  COUNT(1)
----------
         0

SQL> SELECT COUNT(1) FROM TEST PARTITION (P10);

  COUNT(1)
----------
         0

分别创建如下2个本地索引：

SQL> CREATE INDEX TEST_IDX_01 ON TEST(ID,NAME) LOCAL;

Index created.

SQL> CREATE INDEX TEST_IDX_02 ON TEST(ID,COLNO) LOCAL;

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST',estimate_percent=>100,method_opt=>'for all columns size auto',cascade=>true);

PL/SQL procedure successfully completed.

下面看看SQL语句的执行计划：

SQL> SELECT * FROM TEST WHERE ID='00000001' AND COLNO='SNOITADNEMMOCER_WEIVMXXXXXXXXX';

Execution Plan
----------------------------------------------------------
Plan hash value: 2172372150

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |  2069 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE             |             |     1 |  2069 |     2   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST        |     1 |  2069 |     2   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                | TEST_IDX_02 |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------------------

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

   3 - access("ID"='00000001' AND "COLNO"='SNOITADNEMMOCER_WEIVMXXXXXXXXX')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1246  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

SQL> SELECT * FROM TEST WHERE ID='00000005' and COLNO='tnatsnoCdknL_03db5001/XXXXXXXX';

Execution Plan
----------------------------------------------------------
Plan hash value: 2172372150

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |  2069 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE             |             |     1 |  2069 |     2   (0)| 00:00:01 |     5 |     5 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST        |     1 |  2069 |     2   (0)| 00:00:01 |     5 |     5 |
|*  3 |    INDEX RANGE SCAN                | TEST_IDX_02 |     1 |       |     1   (0)| 00:00:01 |     5 |     5 |
------------------------------------------------------------------------------------------------------------------

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

   3 - access("ID"='00000005' AND "COLNO"='tnatsnoCdknL_03db5001/XXXXXXXX')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1246  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

此时ORACLE的执行计划是正确的。

看看ID=00000008 00000009 00000010的情况：
SQL> SET AUTOT TRACEONLY EXP

SQL> SELECT * FROM TEST WHERE ID='00000008' and COLNO='tnatsnoCdknL_03db5001/XXXXXXXX';

Execution Plan
----------------------------------------------------------
Plan hash value: 1143802304

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |  2116 |     0   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE             |             |     1 |  2116 |     0   (0)| 00:00:01 |     8 |     8 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST        |     1 |  2116 |     0   (0)| 00:00:01 |     8 |     8 |
|*  3 |    INDEX RANGE SCAN                | TEST_IDX_01 |     1 |       |     0   (0)| 00:00:01 |     8 |     8 |
------------------------------------------------------------------------------------------------------------------

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

   2 - filter("COLNO"='tnatsnoCdknL_03db5001/XXXXXXXX')
   3 - access("ID"='00000008')

SQL>  SELECT * FROM TEST WHERE ID='00000009' and COLNO='tnatsnoCdknL_03db5001/XXXXXXXX';

Execution Plan
----------------------------------------------------------
Plan hash value: 1143802304

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |  2116 |     0   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE             |             |     1 |  2116 |     0   (0)| 00:00:01 |     9 |     9 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST        |     1 |  2116 |     0   (0)| 00:00:01 |     9 |     9 |
|*  3 |    INDEX RANGE SCAN                | TEST_IDX_01 |     1 |       |     0   (0)| 00:00:01 |     9 |     9 |
------------------------------------------------------------------------------------------------------------------

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

   2 - filter("COLNO"='tnatsnoCdknL_03db5001/XXXXXXXX')
   3 - access("ID"='00000009')

SQL> SELECT * FROM TEST WHERE ID='00000006' and COLNO='tnatsnoCdknL_03db5001/XXXXXXXX';

Execution Plan
----------------------------------------------------------
Plan hash value: 2172372150

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |  2069 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE             |             |     1 |  2069 |     2   (0)| 00:00:01 |     6 |     6 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST        |     1 |  2069 |     2   (0)| 00:00:01 |     6 |     6 |
|*  3 |    INDEX RANGE SCAN                | TEST_IDX_02 |     1 |       |     1   (0)| 00:00:01 |     6 |     6 |
------------------------------------------------------------------------------------------------------------------

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

   3 - access("ID"='00000006' AND "COLNO"='tnatsnoCdknL_03db5001/XXXXXXXX')

可以看到，对于00000008 00000009的查询ORACLE估算出来的成本是0，而导致了使用错误的索引TEST_IDX_01。

如果我们使用了绑定变量的话，而且第一次查询使用了00000008 00000009，那么对于以后的查询，

将一直使用TEST_IDX_01索引。之所以造成这样的原因：是因为本地索引的P8,P9,P10分区没有数据，造成

BLEVEL和LEAF_BLOCKS为0了。导致评估出的COST为0。

SQL> select index_name,partition_name,blevel,leaf_blocks
  2  from user_ind_partitions 
  3  where index_name like 'TEST_IDX_0%' ORDER BY 1,2;

INDEX_NAME           PARTITION_     BLEVEL LEAF_BLOCKS
-------------------- ---------- ---------- -----------
TEST_IDX_01          P1                  1          26
TEST_IDX_01          P10                 0           0
TEST_IDX_01          P2                  1          26
TEST_IDX_01          P3                  1          26
TEST_IDX_01          P4                  1          25
TEST_IDX_01          P5                  1          25
TEST_IDX_01          P6                  1          26
TEST_IDX_01          P7                  1          26
TEST_IDX_01          P8                  0           0
TEST_IDX_01          P9                  0           0
TEST_IDX_02          P1                  1          52
TEST_IDX_02          P10                 0           0
TEST_IDX_02          P2                  1          53
TEST_IDX_02          P3                  1          53
TEST_IDX_02          P4                  1          52
TEST_IDX_02          P5                  1          52
TEST_IDX_02          P6                  1          52
TEST_IDX_02          P7                  1          52
TEST_IDX_02          P8                  0           0
TEST_IDX_02          P9                  0           0

20 rows selected.
由于分区键的值并不是很多，而且分区键的不同的值对应的记录数往往差别很多（HASH分区一般例外），因此，对于带分区键的查询一般最好别使用绑定变量。

解决方法：

1）、不使用绑定变量。

如果不使用绑定变量，就不会造成以后执行计划的共享了，当然也就不会有以后SQL性能突然变差了，当然这要付出加大共享池的代价的。

2）、造假的统计信息。

可以根据别的分区的统计信息，给没有数据的分区造假的统计信息。
如下所示：

SQL> exec dbms_stats.set_index_stats(user,'test_idx_01','p9',NUMROWS=>7000,NUMLBLKS=>26,INDLEVEL=>1,CLSTFCT=>4480);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.set_index_stats(user,'test_idx_01','p8',NUMROWS=>7000,NUMLBLKS=>26,INDLEVEL=>1,CLSTFCT=>4480);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.set_index_stats(user,'test_idx_01','p9',NUMROWS=>7000,NUMLBLKS=>26,INDLEVEL=>1,CLSTFCT=>4480);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.set_index_stats(user,'test_idx_01','p10',NUMROWS=>7000,NUMLBLKS=>26,INDLEVEL=>1,CLSTFCT=>4480);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.set_index_stats(user,'test_idx_02','p8',NUMROWS=>7000,NUMLBLKS=>52,INDLEVEL=>1,CLSTFCT=>6200);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.set_index_stats(user,'test_idx_02','p9',NUMROWS=>7000,NUMLBLKS=>52,INDLEVEL=>1,CLSTFCT=>6200);

PL/SQL procedure successfully completed.

SQL>  exec dbms_stats.set_index_stats(user,'test_idx_02','p10',NUMROWS=>7000,NUMLBLKS=>52,INDLEVEL=>1,CLSTFCT=>6200);

PL/SQL procedure successfully completed.

SQL>  select index_name,partition_name,blevel,leaf_blocks,num_rows,clustering_factor
  2   from user_ind_partitions
  3  where index_name like 'TEST_IDX_0%' ORDER BY 1,2;

INDEX_NAME           PARTITION_     BLEVEL LEAF_BLOCKS   NUM_ROWS CLUSTERING_FACTOR
-------------------- ---------- ---------- ----------- ---------- -----------------
TEST_IDX_01          P1                  1          26       7193              4480
TEST_IDX_01          P10                 1          26       7000              4480
TEST_IDX_01          P2                  1          26       7357              4490
TEST_IDX_01          P3                  1          26       7311              4466
TEST_IDX_01          P4                  1          25       7174              4383
TEST_IDX_01          P5                  1          25       7161              4419
TEST_IDX_01          P6                  1          26       7206              4421
TEST_IDX_01          P7                  1          26       7231              4465
TEST_IDX_01          P8                  1          26       7000              4480
TEST_IDX_01          P9                  1          26       7000              4480
TEST_IDX_02          P1                  1          52       7193              6181
TEST_IDX_02          P10                 1          52       7000              6200
TEST_IDX_02          P2                  1          53       7357              6343
TEST_IDX_02          P3                  1          53       7311              6316
TEST_IDX_02          P4                  1          52       7174              6197
TEST_IDX_02          P5                  1          52       7161              6188
TEST_IDX_02          P6                  1          52       7206              6205
TEST_IDX_02          P7                  1          52       7231              6240
TEST_IDX_02          P8                  1          52       7000              6200
TEST_IDX_02          P9                  1          52       7000              6200

20 rows selected.
SQL> SELECT * FROM TEST WHERE ID='00000008' and COLNO='tnatsnoCdknL_03db5001/XXXXXXXX';

Execution Plan
----------------------------------------------------------
Plan hash value: 2172372150

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |  2116 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE             |             |     1 |  2116 |     1   (0)| 00:00:01 |     8 |     8 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST        |     1 |  2116 |     1   (0)| 00:00:01 |     8 |     8 |
|*  3 |    INDEX RANGE SCAN                | TEST_IDX_02 |     1 |       |     1   (0)| 00:00:01 |     8 |     8 |
------------------------------------------------------------------------------------------------------------------

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

   3 - access("ID"='00000008' AND "COLNO"='tnatsnoCdknL_03db5001/XXXXXXXX')

3）、删除索引的统计信息

SQL> exec dbms_stats.delete_index_stats(user,'test_idx_01');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats(user,'test_idx_02');

PL/SQL procedure successfully completed.

SQL> select index_name,partition_name,blevel,leaf_blocks,num_rows,clustering_factor
  2   from user_ind_partitions
  3  where index_name like 'TEST_IDX_0%' ORDER BY 1,2;

INDEX_NAME           PARTITION_     BLEVEL LEAF_BLOCKS   NUM_ROWS CLUSTERING_FACTOR
-------------------- ---------- ---------- ----------- ---------- -----------------
TEST_IDX_01          P1
TEST_IDX_01          P10
TEST_IDX_01          P2
TEST_IDX_01          P3
TEST_IDX_01          P4
TEST_IDX_01          P5
TEST_IDX_01          P6
TEST_IDX_01          P7
TEST_IDX_01          P8
TEST_IDX_01          P9
TEST_IDX_02          P1
TEST_IDX_02          P10
TEST_IDX_02          P2
TEST_IDX_02          P3
TEST_IDX_02          P4
TEST_IDX_02          P5
TEST_IDX_02          P6
TEST_IDX_02          P7
TEST_IDX_02          P8
TEST_IDX_02          P9

20 rows selected.

SQL> set autot traceonly exp
SQL> SELECT * FROM TEST WHERE ID='00000008' and COLNO='tnatsnoCdknL_03db5001/XXXXXXXX';

Execution Plan
----------------------------------------------------------
Plan hash value: 2172372150

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |  2116 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE             |             |     1 |  2116 |     1   (0)| 00:00:01 |     8 |     8 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST        |     1 |  2116 |     1   (0)| 00:00:01 |     8 |     8 |
|*  3 |    INDEX RANGE SCAN                | TEST_IDX_02 |     1 |       |     1   (0)| 00:00:01 |     8 |     8 |
------------------------------------------------------------------------------------------------------------------

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

   3 - access("ID"='00000008' AND "COLNO"='tnatsnoCdknL_03db5001/XXXXXXXX')

不过这个解决方法有点不太实际。