Index selection

Database
Enthusiast

Index selection

Case-1
I have a fact table "fact1" which contains data for around 3 yrs.
PI of the fact table is store number and reporting date column.
Case-2
Another fact table which contains data for 3 yrs and PI is on store number only.

Most of my database queries join these fact table with dimension table
to find out the aggregated data for different timeframe like
daily,weekly,monthly,quarterly,yeraly for
last 3 yrs(current year,previous year,previous tp previous year).

For these two cases whether i will
Create a value ordered NUSI on reporting date column
or is it wise to have partitioning(PPI) on a interval of DAY on reporting date column.
Which will give the best performance and why?

Thanks

4 REPLIES
Senior Apprentice

Re: Index selection

Of course it access will be faster when you partition the tables instead of a VOSI.

But you must be carefull when accessing the 2nd table without the date column, this will be much slower than before (unless you create a NUSI on the PI).

You should always test a lot before you start partitioning :-)

Dieter
Enthusiast

Re: Index selection

Hi Dieter,

So do u mean to say for case-1 it is PPI and for
case-2 it is VOSI.

Or is it like create a VOSI on reporting date column for case-1 and case-2.

Lets say there is a billing table. As i think most queries will use to see data according to reporting date or for a particualr timeframe like weekly,monthly etc data and reporting date column is in UPI along with some other columns, So is it good
to use PPI or VOSI. I have chosen VOSI and feel it is fine to go with VOSI.
Please suggest.

Thanks
Senior Apprentice

Re: Index selection

In your case PPI access is always faster than VOSI.

But performance might degrade for joins and where conditions without the partitioning column if that column is not also part of the PI.

You'll find all the details in the Database Design manual.

In a nutshell:
Chapter 10: Primary Indexes
Summary of the Potential Advantages and Disadvantages of Partitioned Primary Indexes

Dieter
Enthusiast

Re: Index selection

Thanks. Got it..