Oracle Database Discussions

Hi Gurus

My one sql query taking one index in testing enviornment and different index on production .
Pls find screen shot
Oracle 10g 10.2.0.3
Testing envioronment
SQL> SELECT dm_aq_total, ndm_aq_total, mrf_code, org_id,
2 euc_number, spt_code,
3 ldz_identifier, exz_identifier, cnf_shipper_ref, ndm_soq,
4 transco_meter_read, wholly_dm_ind, num_dataloggers, num_intrptbl_days,
5 cnf_reference_num
6 FROM ai_sp_history
7 WHERE spo_identifier = 1181336
8 AND end_date <= '24-APR-07'
9 AND end_date >= '01-OCT-06'
10 ORDER BY start_date DESC;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1113860183

--------------------------------------------------------------------------------
--------------------------------------

| Id | Operation | Name | Rows | Bytes | C
ost (%CPU)| Time | Pstart| Pstop |

--------------------------------------------------------------------------------
--------------------------------------

| 0 | SELECT STATEMENT | | 1 | 114 |
3 (34)| 00:00:01 | | |

| 1 | SORT ORDER BY | | 1 | 114 |
3 (34)| 00:00:01 | | |

|* 2 | FILTER | | | |
| | | |

| 3 | PARTITION RANGE ITERATOR | | 1 | 114 |
2 (0)| 00:00:01 | KEY | KEY |

|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| AI_SP_HISTORY | 1 | 114 |
2 (0)| 00:00:01 | KEY | KEY |

|* 5 | INDEX RANGE SCAN | AIH002 | 1 | |
2 (0)| 00:00:01 | KEY | KEY |

--------------------------------------------------------------------------------
--------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(TO_DATE('24-APR-07')>=TO_DATE('01-OCT-06'))
4 - filter("SPO_IDENTIFIER"=1181336)
5 - access("END_DATE">='01-OCT-06' AND "END_DATE"<='24-APR-07')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
960118 consistent gets
0 physical reads
0 redo size
1314 bytes sent via SQL*Net to client
477 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

In production
SQL> SELECT dm_aq_total, ndm_aq_total, mrf_code, org_id,
2 euc_number, spt_code,
3 ldz_identifier, exz_identifier, cnf_shipper_ref, ndm_soq,
4 transco_meter_read, wholly_dm_ind, num_dataloggers, num_intrptbl_days,
5 cnf_reference_num
6 FROM ai_sp_history
7 WHERE spo_identifier = 1181336
8 AND end_date <= '24-APR-07'
9 AND end_date >= '01-OCT-06'
10 ORDER BY start_date DESC;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 779455578

--------------------------------------------------------------------------------
-------------------------------------

| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time | Pstart| Pstop |

--------------------------------------------------------------------------------
-------------------------------------

| 0 | SELECT STATEMENT | | 1 | 114 |
26 (0)| 00:00:01 | | |

|* 1 | FILTER | | | |
| | | |

|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| AI_SP_HISTORY | 1 | 114 |
26 (0)| 00:00:01 | ROWID | ROWID |

|* 3 | INDEX RANGE SCAN DESCENDING | AIH001 | 23 | |
3 (0)| 00:00:01 | | |

--------------------------------------------------------------------------------
-------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_DATE('24-APR-07')>=TO_DATE('01-OCT-06'))
2 - filter("END_DATE">='01-OCT-06' AND "END_DATE"<='24-APR-07')
3 - access("SPO_IDENTIFIER"=1181336)
filter("SPO_IDENTIFIER"=1181336)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1314 bytes sent via SQL*Net to client
477 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


How oracle chooses differnt index on same query on different servers?

thanks