表数据量和字段空值统计信息导致的执行计划错误

column: Art_id null number  --distinct values:214
column: User_id  not null number --distinct values: 83w
 
Index:
Idx_userid: user_id,status
Idx_artid: art_id,gmt_create,status,prod_type
 
[cc lang='sql']-----SQL---
SELECT /*+ordered use_nl(t1,t2)*/
T2.*
FROM (SELECT RID
FROM (SELECT R.RID, ROWNUM LINENUM
FROM (SELECT ROWID RID
FROM TABLE_CC T
WHERE PROD_TYPE = 1
AND T.USER_ID = :1
AND T.STATUS = :2
AND T.GMT_CREATE >=
TO_DATE(:3, 'yyyy-mm-ddhh24:mi:ss')
AND T.ART_ID = :4
ORDER BY T.GMT_CREATE DESC) R
WHERE ROWNUM <= :6)
WHERE LINENUM >= :7) T1,
TABLE_CC T2
WHERE T1.RID = T2.ROWID;
[/cc]

正常情况，该sql应该走上user_id的索引，每个user_id的记录平均应该是100行，而每个art_id应该会有几十上百万的记录。
 
但这个sql非常喜欢走art_id开头之索引，因为user_id并不在索引走，导致回表，逻辑读高达90w，如果强制走user_id索引只需要几十的逻辑读。
 
尝试创建了索引

1.      user_id, art_id,gmt_create,status,prod_type
2.      user_id,status,art_id
3.      user_id,art_id,gmt_crate,status
 
但上面的sql依然我行我素，摆着效率更高的索引不走，继续走art_id的索引。
甚至于建上art_id,gmt_create,status,prod_type,user_id的高冗余度索引，依然如此。
 

在分析了抽样分析表的统计信息和art_id和user_id的统计信息之后，执行计划依旧是错误的。
Why？？？
 

最后俊达同学找到了问题的关键。
 
从explain看来，走art_id的索引返回的rows 为1

走user_id的索引返回值也是1
 
[cc lang='sql']
---------------------------------------------------------------------------------------------------------
| Id | Operation                       | Name | Rows  | Bytes | Cost(%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0| SELECT STATEMENT                 ||     1 |   198 |    3   (0)| 00:00:01 |
|   1|  NESTED LOOPS                    | |     1 |  198 |     3   (0)| 00:00:01 |
|*  2|   VIEW                           | |     1 |   25 |     2   (0)| 00:00:01 |
|*  3|    COUNT STOPKEY                 | |       |      |            |          |
|   4|     VIEW                         | |     1 |   12 |     2   (0)| 00:00:01 |
|*  5|      TABLE ACCESS BY INDEX ROWID |TABLE_CC        |     1 |   36 |     2   (0)| 00:00:01 |
|*  6|       INDEX RANGE SCAN DESCENDING|INX_USER_ID |     1 |       |    2   (0)| 00:00:01 |
|   7 |  TABLE ACCESS BY USER ROWID     |TABLE_CC        |     1 |  173 |     1   (0)| 00:00:01 |
[/cc]
是统计信息惹的祸，当时虽然有了字段和索引的正确的统计信息，但是表的总数据量是不对的，表的row是800w，block是20w。

Art_id 因为是后加的字段，字段允许控制，其number nulls值 5000w

User_id是非空字段，number nulls为0
这样在判断执行计划统计rows时，
表数据量 800w减art_id的空值数为负数，这样返回的行数就算作1，这样分析器就认为走改索引为优
而走user_id的索引优势就没有了。
这就是导致执行计划错误的原因。
 

既然知道了问题所有，也就没必要在分析整个表了，直接修改数据字典，将表的number rows置成8kw
 
exec dbms_stats.set_table_stats(user,'TABLE_CC',numrows=>80000000,numblks=>2000000);

手动将统计信息置成某个值是非常有效的手段，但需要注意手动设置的值可能在表进行迁移或者重新统计后带来的问题。建议在做表的移动操作是对表统计信息进行备份

dbms_stats包的使用可参考http://psoug.org/reference/dbms_stats.html
 
--创建统计信息备份表
begin
dbms_stats.create_stat_table(ownname => 'USER',stattab =>'STAT_FEEDBACKS');
end;
 
--备份统计数据
begin
dbms_stats.EXPORT_TABLE_STATS(OWNNAME=>'USER',TABNAME=>'USER_FEEDBACKS',stattab=>'STAT_FEEDBACKS');
end;
 
--导入统计信息
execdbms_stats.import_table_stats(OWNNAME=>'USER',TABNAME=>'USER_FEEDBACKS',stattab=>'FEEDBACKS_090521');