-
Should I index?
Hi,
I have a couple of tables with at least 5 million rows each from which ad-hoc reports generated. The reports can be sliced-and-diced using a combination of any of the 15+ columns on these tables. My goal is to improve the poor performance of these reports. Questions:
1- Do you advice that I create an index on each of the columns used in the where clause (taking into account all the various scenarios that the report can be sliced-and-diced)
2. What kind of index - bitmap or b-tree. The environment is a dss (data warehouse) and the average # of distinct rows is 3800 out of 5 million rows.
3. If the tables are partitioned (by range), is it recommended to also partition the indexes (using the same range as the table). Comments are also welcome.
Thanks,
L
leonard905
leonard905@yahoo.com
-
Re: Should I index?
1- Do you advice that I create an index on each of the columns used in the where clause (taking into account all the various scenarios that the report can be sliced-and-diced)
yes, and analyze columns also.
2. What kind of index - bitmap or b-tree. The environment is a dss (data warehouse) and the average # of distinct rows is 3800 out of 5 million rows.
3800 out of 5,000,000 sound ok for BM indexes. For columns at about 10,000 unique values you might like to test sizes of BM vs. btree.
3. If the tables are partitioned (by range), is it recommended to also partition the indexes (using the same range as the table). Comments are also welcome.
yes.
Consider physically ordering the data within each partition by the columns most commonly used as predicates in user reports also, to increase block selectivity of the indexes.
-
If this is the fact table for a DSS, the answer is almost always to bitmap all the keys. Why would you not?
- Chris
-
Also, ensure that FK's are in place to the dimension tables, even if the are disbable+novalidate+rely , and enable star_transformations.
-
This query is taking a minute ?!! And, the table has 2000 rows.
What kind of hardware are you running this database on ?
svk
-
Originally posted by svk
This query is taking a minute ?!! And, the table has 2000 rows.
I am lost here! What are you talking about, svk? Where did you get those informations from? And what relevance do they have on the original question. Enlighten me, please.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
thats a reply to a different thread, i think.
-
Ah, it makes much more sense now.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
I'm sorry !! It really should have been a reply to another question in the same forum.
svk
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|