Full table scan instead of Index range scan lead to peformance issue

We have a merge statements shown below, instead of index range scan it is making full table scan. It ran successfully till last until after the table was altered added 3-4 new columns when it started to do a full table scan.

We 开发者_如何学Chave 7 similar tables with same changes, i.e. added 3-4 columns but when we re-built the indexes it solved the problem, except for one table.

Could anyone throw some light on this?

-Nagu

Try gathering statistics on the involved table. The best way to do this is to use routines in the DBMS_STATS package. The easiest thing to do is to simply call DBMS_STATS.GATHER_DATABASE_STATS specifying no parameters (i.e. using default values for all parameters). This will take a while, however. To gather statistics for a single table you can use DBMS_STATS.GATHER_TABLE_STATS. You'll need to give values for the 'ownname' and 'tabname' parameters; thus, if the table you're interested is named "MY_SCHEMA.MY_TABLE" the call to DBMS_STATS.GATHER_TABLE_STATS would look like

DBMS_STATS.GATHER_TABLE_STATS('MY_SCHEMA', 'MY_TABLE');

There are other parameters to this routine but the default values will work fine.

If the database is still insisting on a full table scan of the table you're interested in it might mean that there isn't an index on your table which the database feels might be useful in satisfying the query. If you can post the code of your query and tell us what indexes you have on the table you're having problems with we might be able to make additional recommendations.

Share and enjoy.