5

I have a query in the form:

select a, b, c, d, e, f, g, h,
    row_number () over (partition by a, b order by c, d, e, f) as order
from table;

And it's performance is kind of terrible.

We have tried creating 2 indexes, the first for the partition by columns (a and b) and the second for the order by columns (c, d, e, f).

Using the explain plan we have found that indexes are not being used. Altough they are usefeul for other querys with a group by clause.

Any idea on how to refactor the query, or re-create the indexes so that they help with performance on this query?

Thanks in advance

rsinuhe
  • 177
  • 1
  • 2
  • 9

1 Answers1

1

To quote from a previous answer of mine:

The two most common reasons for a query not using indexes are:

  1. It's quicker to do a full table scan.
  2. Poor statistics.

In your case, you're selecting everything from the table so I'm guessing 1. is the main reason, in which case you're hosed.

You could try the index a, b, c, d, e, f, and I'm not saying that gathering statistics won't help. If you haven't I'd recommend doing it anyway:

begin

   dbms_stats.gather_table_stats (
   ownname => user, 
   tabname => 'MY_TABLE',
   estimate_percent => 25,
   method_opt => 'FOR ALL INDEXED COLUMNS',
   cascade => TRUE );

end;

There's also the remote possibility that your table is massively fragmented. If so alter table my_table move and then gather statistics.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • 1
    Even with an index over `a,b,c,d,e,f` it probably won't use it - a fully covering index (to remove the random table accesses) over `a,b,c,d,e,f,g,h` might be used. But then the index's size will dwarf the original table, and still might not be used :) – Jeffrey Kemp Mar 06 '12 at 03:57
  • I would add, something so drastic as rebuilding the table is a little bit premature - I'd first look at the query plan to determine why Oracle is deciding not to use the index, first. – Jeffrey Kemp Mar 06 '12 at 03:58
  • Looking at the query, it's returning every row as is - there's no filtering. – Adam Musch Mar 06 '12 at 06:18
  • Sorry for the delay, i didn't include where clause to keep it simple to explain, but point 1 was true, we were returning the full table, so the full scan was the best option anyway. – rsinuhe Sep 26 '12 at 16:33