well, if you are going to cross partitions - hitting 5 days worth of data - hopefully you would NOT be using an index at all. Hopefully you would be using a full scan of the five partitions since you are hitting every row.

If all of your queries include "startdate" in the predicate and you think you'll hit partitions at the most typically - it is likely you want to employ locally partitioned indexes for most all of your indexes.

And startdate doesn't need to be in all of these indexes (they do not need to be prefixed with startdate). Only when you are going after the previous N hours might you want an index that starts with startdate.


for example, suppose you have queries like:

select .... 
  from t
 where startdate between sysdate and sysdate-5
   and x > 100;

select .... 
  from t
 where startdate between sysdate and sysdate-2
   and x > 100;

it MIGHT make sense to have a locally partitioned index on X, just on X. If x > 100 returns a very small number of rows from those five partitions then an index on X and just on X would be appropriate. We will do five index range scans (which is acceptable) to find the rows.

For the second query we would just do two index range scans (again, acceptable).


You would want a globally partitioned index on X if you did queries like:

select .... 
  from t
 where startdate between sysdate and sysdate-50
   and x > 100;

select .... 
  from t
 where x > 100;

assuming again that x > 100 returns a small number of rows from the candidate set of data. We'd want a global index on X in this case because doing 50 index range scans is becoming unacceptable - too much work. And in the second case - we'd do N range scans where N was the number of partitions in the table.



If you just query:

select .... 
  from t
 where startdate between sysdate and sysdate-5;

select .... 
  from t
 where startdate between sysdate and sysdate-2;

I would want no indexes - just full scan the partitions and be done with it, an index is useful to retrieve a SMALL NUMBER of rows from a large set of data, if you are looking at every row in a set of data - indexes are something to be avoided.


If you query up for the last 5 hours of data - again, you probably DON'T want any indexes either. Early in the day - you'd be returning every row from the partition. In the middle of the day, you'd be returning almost 50% of the data. At the end of the day - you'd be returning about 20% of the data. That is too much of the data to be using an index - a nice full scan would be best.


You'd only want an index on startdate if you run a query that returns a small number of rows from a large number of rows (eg: if you asked for a 5 minute window of data - indexing startdate would make sense - otherwise probably not)