select c1, c2,c3 from tab1
where c1 = 'abc';
its using below plan
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.27 | 26816 |
|* 1 | TABLE ACCESS FULL| TAB1 | 1 | 1171K| 0 |00:00:00.27 | 26816 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TAB1"."c1"='abc')
now my concern is , even if the rows it returns is 0, how the expected cardinality calculated to be 1171K. This perhaps tilting the optimizer decision to go for FTS of table, even if ther exist an index id1 on TAB1(C1) and resulting less record sometimes.Now below are the stats for the table and column c1
select num_rows from dba_tables where table_name='TAB1'
NUM_ROWS
-------
1171095
select density,num_distinct from dba_tab_col_statistics where table_name='TAB1' and column_name='C1'
density num_distinct
-------- ---------
4.26950845149198E-7 1171095
As ther existing frequency histogram on this column, so the expected cardinality estimation should be density*num_distinct = .5.
select endpoint_number, endpoint_value from dba_tab_histograms where table_name='TAB1' and column_name='C1';
ENDPOINT_NUMBER ENDPOINT_VALUE
-------------- ----------------
234219 3.80421485912222E35
when i force the index , the plan becaomes as below
Elapsed: 00:00:01.50
Execution Plan
----------------------------------------------------------
Plan hash value: 1013434088
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1171K| 93M| 870K (1)| 01:13:50 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1171K| 93M| 870K (1)| 01:13:50 |
|* 2 | INDEX RANGE SCAN | ID1 | 1171K| | 21124 (1)| 00:01:48 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TAB1"."C1"='abc')