Should creating an index instantly update Oracle's query plan?

If you have an inefficient query, and you add an index to help out performance, should the query "instantly" start using th开发者_开发百科e index?

Or do you need to clear out the Oracle "cache" (v$sql I believe) by running alter system flush shared_pool;?

As the DBA loves to answer, "it depends."

It depends on if Oracle thinks the index will help performance. If Oracle thinks the index isn't the best choice for the query, Oracle's not using it anyway.

It depends on whether you're using prepared statements. A prepared statement isn't reparsed during its lifetime, so if a running app uses a prepared statement you're trying to fix, you'lll need to restart the app.

Flushing the shared pool will force Oracle to reparse and reoptimize all statements (a hard parse), so if Oracle thinks the index will help performance, flushing the shared pool will do trick. However, it can also have far reaching consequences in a live production system -- causing a "parse storm", as every statement in use must be reparsed and reoptimized -- and should only be undertaken as a last resort.

You should regather statistics on the table. You can compute or estimate statistics. Example usage

Compute

BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
      OwnName        => 'ENROLLMENT'
     ,TabName        => 'STUDENTS'
    ,Estimate_Percent  => 0
    ,Degree            => 4
    ,Cascade           => TRUE
    ,No_Invalidate     => FALSE);
END;
/

Note the cascade argument is telling oracle to also gather stats on any indexes on the table as well.

Estimate

BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
      OwnName        => 'ENROLLMENT'
     ,TabName        => 'STUDENTS'
    ,Estimate_Percent  => DBMS_STATS.AUTO_SAMPLE_SIZE
    ,Degree            => 4
    ,Cascade           => TRUE
    ,No_Invalidate     => FALSE);
END;
/

GATHER_TABLE_STATS docs

Shared pool is not used to cache data.

Oracle Server has two performance measurement, logical read and physical read. Physical read is a measurement of disk read performance. Logical read is a measurement of read data from memory.

In any read method (index, full table scan or something), rows in blocks must be retrieved into buffer cache. It's the action of physical read.

Logical read is return result from cache if hit, if you use index to improve SQL performance, it's the improvement of logical read.

So in short, it's not necessary.