执行计划的问题

aaaaaaaa2000

1. SQL> drop table t2 purge;
   
2. 
   
3. Table dropped.
   
4. 
   
5. SQL> create table t2 as select * from all_objects
   
6.   2  ;
   
7. 
   
8. Table created.
   
9. 
   
10. SQL> create index idn_t2 on t2(status);
    
11. 
    
12. Index created.
    
13. 
    
14. SQL> exec   dbms_stats.gather_table_stats(ownname=>'CLM',tabname=>'T2',cascade=>true,estimate_percent=>100,method_opt=>'for all columns size 1');  
    
15. 
    
16. PL/SQL procedure successfully completed.
    
17. 
    
18. SQL> select status,count(1) from t2 group by t2;
    
19. select status,count(1) from t2 group by t2
    
20.                                         *
    
21. ERROR at line 1:
    
22. ORA-00904: "T2": invalid identifier
    
23. 
    
24. 
    
25. SQL> select status,count(1) from t2 group by status;
    
26. 
    
27. STATUS    COUNT(1)
    
28. ------- ----------
    
29. VALID        71038
    
30. INVALID          2
    
31. 
    
32. SQL> set autotrace on
    
33. SQL> select object_id ,status from t2 where status='INVALID';
    
34. 
    
35. OBJECT_ID STATUS
    
36. ---------- -------
    
37.      73871 INVALID
    
38.      73846 INVALID
    
39. 
    
40. 
    
41. Execution Plan
    
42. ----------------------------------------------------------
    
43. Plan hash value: 1513984157
    
44. 
    
45. --------------------------------------------------------------------------
    
46. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
47. --------------------------------------------------------------------------
    
48. |   0 | SELECT STATEMENT  |      | 35520 |   416K|   284   (1)| 00:00:04 |
    
49. |*  1 |  TABLE ACCESS FULL| T2   | 35520 |   416K|   284   (1)| 00:00:04 |
    
50. --------------------------------------------------------------------------
    
51. 
    
52. Predicate Information (identified by operation id):
    
53. ---------------------------------------------------
    
54. 
    
55.    1 - filter("STATUS"='INVALID')
    
56. 
    
57. 
    
58. Statistics
    
59. ----------------------------------------------------------
    
60.           1  recursive calls
    
61.           0  db block gets
    
62.        1018  consistent gets
    
63.           0  physical reads
    
64.           0  redo size
    
65.         669  bytes sent via SQL*Net to client
    
66.         523  bytes received via SQL*Net from client
    
67.           2  SQL*Net roundtrips to/from client
    
68.           0  sorts (memory)
    
69.           0  sorts (disk)
    
70.           2  rows processed
    
71. 
    
72. SQL>

复制代码

为什么走的是全表扫描？这个invalid只有2个值应该有索引才对啊，忘刘总解答

aaaaaaaa2000

是因为没有收集直方图信息导致oracle不知道值分布走了全表扫描

那在收集表信息的时候是否收集直方图信息啊，实际环境中表很多很多，改如何进行判断？刘总有什么好的建议

Maclean Liu(刘相兵

请参考 CBO为什么不走索引？
  http://www.oracledatabase12g.com ... BC%95%EF%BC%9F.html

这篇正好讨论了lz这个问题

aaaaaaaa2000

多谢刘总解答啊！！！