| INDEX column combination [message #204169] |
Sat, 18 November 2006 09:05  |
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  |
rleishman
Messages: 3728
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 |
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 |
rleishman
Messages: 3728
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 #204952 is a reply to message #204902] |
Wed, 22 November 2006 19:59 |
rleishman
Messages: 3728
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 |
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 |
rleishman
Messages: 3728
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
