it is due to the way you gathered statistics.
we have column stats on exactly one column - the column you happen to not use in the where clause.
by using "for all indexed columns size 254", you preclude any column level stats on ID, hence we "guess"
get rid of your method opt
get rid of your cascade (we already have stats on the index, when you created it we computed them)
get rid of your estimate percent (it is already going to be pretty much compute in 11g)
you don't want to use those parameters typically (except maybe to demonstrate a concept). you need statistics on columns you use in where clauses (not just indexed columns). you want to let things default especially in 11g (else you are using the old 10g slow algorithms to gather statistics).
ops$tkyte%ORA11GR2> create table t
2 partition by list(id)
3 ( partition p1 values (1),
4 partition p2 values (2),
5 partition p3 values (3),
6 partition p4 values (4),
7 partition p5 values (5),
8 partition pmax values (default)
9 ) nologging as
10 select a.*, mod(rownum,5)+1 as id
11 from all_objects a;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create index t_ind
2 on t(object_id)
3 nologging local;
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> begin
2 dbms_stats.gather_table_stats
3 (ownname=>user,
4 tabname=>'T' );
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select table_name,
2 partition_name,num_rows
3 from user_tab_statistics;
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
T 72826
T PMAX 0
T P5 14565
T P4 14565
T P3 14565
T P2 14566
T P1 14565
7 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select index_name, partition_name, num_rows
2 from user_ind_statistics;
INDEX_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
T_IND 72826
T_IND P1 14565
T_IND P2 14566
T_IND P3 14565
T_IND P4 14565
T_IND P5 14565
T_IND PMAX 0
7 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select column_name,count(*)
2 from user_tab_histograms
3 group by column_name;
COLUMN_N COUNT(*)
-------- ----------
SUBOBJEC 2
T_NAME
TIMESTAM 2
P
STATUS 2
OBJECT_I 2
D
CREATED 2
GENERATE 2
D
SECONDAR 2
Y
LAST_DDL 2
_TIME
DATA_OBJ 2
ECT_ID
OBJECT_T 2
YPE
NAMESPAC 2
E
ID 2
OWNER 2
OBJECT_N 2
AME
TEMPORAR 2
Y
15 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set autotrace traceonly explain statistics;
ops$tkyte%ORA11GR2> select count(*) from t
2 where id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2831600127
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 65 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION LIST SINGLE| | 14565 | 43695 | 65 (0)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS FULL | T | 14565 | 43695 | 65 (0)| 00:00:01 | 1 | 1 |
-----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
217 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select /*+ index(t,t_ind) */
2 count(*) from t
3 where id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2422430388
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 415 (1)| 00:00:05 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION LIST SINGLE| | 14565 | 43695 | 34 (0)| 00:00:01 | KEY | KEY |
| 3 | INDEX FULL SCAN | T_IND | 14565 | 43695 | 34 (0)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set autotrace off;
ops$tkyte%ORA11GR2> select count(*) from t where id = 1;
COUNT(*)
----------
14565
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production