Home » RDBMS Server » Performance Tuning » INDEX column combination
INDEX column combination [message #204169] Sat, 18 November 2006 09:05 Go to next message
AbuShreek
Messages: 22
Registered: May 2006
Junior Member
Hello,

I have a table with three dimension columns (StreetId, RegionId and Time). I have many queries against this table having these columns in the WHERE statement. Sometimes the statment includes one, two or three of them.

The question is, which is better..to create one index that contains the three columns only or create indexes with combinations of the columns (for example: StreetId and RegionId, StreetId and Time, RegionId and Time, Time, RegionId, ...etc)?

I mostly care about reading speed not writing speed.

Another thing, If i take the first option and create only one index for all the dimension columns, shall I make it UNIQUE?

Thanks in advance.
Re: INDEX column combination [message #204201 is a reply to message #204169] Sat, 18 November 2006 18:07 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How many rows in the table?
What is the average number of rows per StreetId?
What is the average number of rows per RegionId?
What is the average number of rows per Time?

Ross Leishman
Re: INDEX column combination [message #204209 is a reply to message #204169] Sun, 19 November 2006 00:27 Go to previous messageGo to next message
AbuShreek
Messages: 22
Registered: May 2006
Junior Member
I have several fact tables that use the aforementioned dimension columns. The row count ranges from 1 to 11 million records.

I have like 100 region...
I have like 7000 streets...
and for each street for each day I have one time record ... (the busy hour for that street)...

thanks in advance,
Re: INDEX column combination [message #204254 is a reply to message #204209] Sun, 19 November 2006 21:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
One last question: are the tables updated by multiple users (ie. by a screen in an OLTP application), or are they updated exclusively by either batch processes or a single user?


Ross Leishman
Re: INDEX column combination [message #204902 is a reply to message #204254] Wed, 22 November 2006 09:46 Go to previous messageGo to next message
AbuShreek
Messages: 22
Registered: May 2006
Junior Member
There is only one update (in fact INSERT) by a JOB every morning. It is a data warehouse. no transactions, no multiple users and no updates.
Re: INDEX column combination [message #204952 is a reply to message #204902] Wed, 22 November 2006 19:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Benchmark it with separate BITMAP indexes on region and street, and a regular (B-Tree) index on Time.

When you run a query using 2 or more of the columns, check the Explain Plan to make sure it is using all of the indexes (not just one). If it will not use multiple indexes, add the /*+ INDEX_COMBINE*/ hint.

If you think the number of streets will grow to more than 10000, make it a b-tree index instead of bitmap.

Ross Leishman
Re: INDEX column combination [message #206108 is a reply to message #204952] Tue, 28 November 2006 16:29 Go to previous messageGo to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi Ross,
Extremely sorry for interrupting in between.
However have a query here.

There were two inputs
1) Its Data warehouse system
2) Approx distinct count for street, region(100 regions, 7000 streets out of millions of records)

And you have given the Suggestion
2 bitmap indexes(each for region and street) and and 1 b-tree on Time.

Now my query is
1) Since we use Bitmap where there is low cardinality,
what percetange you have considered for Bitmap Index?Or what percentage (cardinality in fact) shpuld be considered for Bitmap Index?
2) What difference it would have made having a composite index as compared to having 3 different indexes.
3)what would have been your suggestion if this were a OLTP System?

I know i should not have asked these questions in this thread, however i am simulating this with the problems i faced

Thanks in Advance,
Saket
Re: INDEX column combination [message #206127 is a reply to message #206108] Tue, 28 November 2006 20:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I do not hesitate to use a bitmap index with fewer than 2500 distinct values where there are 1M+ rows, or values of interest representing more than 1% or the table. Up to 10000 can be OK on a really big table, but I benchmark it and see how it performs.

A concatenated index will always perform better when you perform = queries on all indexed columns. It is just less versatile when you want to cover all possible access paths.

Bitmap indexes are not suitable for OLTP tables because of sharing problems during DML. You would need to use either separate or concatenated b-tree indexes .

Ross Leishman
Re: INDEX column combination [message #206370 is a reply to message #206127] Wed, 29 November 2006 15:01 Go to previous message
orasaket
Messages: 70
Registered: November 2006
Member
Hi Ross,

Many Thanks for the guidelines !!

Regards,
Pratap
Previous Topic: Hierarchical queries with rownum pseudocolumn in 10g vs. 9i
Next Topic: SQL vs. PLSQL
Goto Forum:
  


Current Time: Wed Dec 07 12:18:59 CST 2016

Total time taken to generate the page: 0.11714 seconds