CBO chooses different indexes
Hi,
on 11.2.0.4 SE on Win 2008
I have PRODDB . I duplicated it from backup files into DEVDB.
Table PS_GP_GL_DATA has two indexes : PSBGP_GL_DATA and PSAGP_GL_DATA.
For a query on that table :
On production CBO uses PSAGP_GL_DATA index (as I can see in Explain Plan).
------------------------------------------------------------------------------------------------------------
|* 32 | TABLE ACCESS BY INDEX ROWID | PS_GP_GL_DATA | 9 | 513 | 657 (0)| 00:00:08 |
|* 33 | INDEX RANGE SCAN | PSAGP_GL_DATA | 4715 | | 33 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | PS_GP_PIN | 1 | | 0 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | PS_GP_PIN | 1 | 21 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
On DEVDB, CBO uses PSBGP_GL_DATA index (as I can see in Explain Plan). It is faster in exécution.