Index not being used

Hi
Database  1 is on 11.1.0.7.2
SQL> explain plan for
  2  SELECT *
  3    FROM pa_expenditure_items_all
  4  WHERE system_linkage_function = 'VI' AND cost_distributed_flag = 'N';
Explained.
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 997445706
--------------------------------------------------------------------------------
--------------
| Id  | Operation         | Name                     | Rows  | Bytes | Cost
(%CP
U)| Time     |
--------------------------------------------------------------------------------
--------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                          | 47861 |    12M|
679K  (
3)| 02:15:51 |
|*  1 |  TABLE ACCESS FULL| PA_EXPENDITURE_ITEMS_ALL | 47861 |    12M|
679K  (
3)| 02:15:51 |
--------------------------------------------------------------------------------
--------------

Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
   1 - filter("SYSTEM_LINKAGE_FUNCTION"='VI' AND
"COST_DISTRIBUTED_FLAG"='N')

=====
Database 2 is on 11.1.0.7.1 and
QL> explain plan
  2  for
  3  SELECT *
  4    FROM pa_expenditure_items_all
  5  WHERE system_linkage_function = 'VI' AND cost_distributed_flag = 'N';
Explained.
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1598013654
--------------------------------------------------------------------------------
------------------------
| Id  | Operation                   | Name                     | Rows  |
Bytes |
 Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                          |    13 |
3588 |
     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| PA_EXPENDITURE_ITEMS_ALL |    13 |
3588 |
     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PA_EXPENDITURE_ITEMS_N3  |    50
|       |
     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SYSTEM_LINKAGE_FUNCTION"='VI')
   2 - access("COST_DISTRIBUTED_FLAG"='N')
15 rows selected.

Before blaming it on PSU, I was thinking if there was anything else that I
can do
1. Stats are most recent and representative on the involved
2. Checked distinct_keys, clustering_factor, num_rows between both databases
and about the same.
3.  Checked this
http://jonathanlewis.wordpress.com/2007/02/15/index-not-used-10g/ and it may
be that I am hitting it b ut I am not able to validate it.
4. If I hint the 11.1.0.7.2 database to use the index hint, then the index
gets used

I am going to switch Oracle Homes and try but just want to make sure that I
there was anything else I could try

Thank you
Kumar

Other related posts: