SQL Performance (MOSC)

MOSC Banner

CBO chooses different indexes

edited Feb 6, 2015 2:26AM in SQL Performance (MOSC) 12 commentsAnswered ✓

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center