| b-tree index of partitioned tabl [message #322643] |
Sun, 25 May 2008 12:04  |
beetel
Messages: 96
Registered: April 2007
|
Member |
|
|
I have a table which is range-partitioned on a date, say, inv_date. Now, I see that a b-tree index is also made on this same column. Apart from it, 3 other b-tree indexes are also present. My doubts are:
- Is the b-tree index on INV_DATE still required though the table is partitioned on that column?
- Should the other 3 b-tree indexes be changed to bitmap indexes?
|
|
|
|
|
|
| Re: b-tree index of partitioned tabl [message #322646 is a reply to message #322644] |
Sun, 25 May 2008 13:35  |
beetel
Messages: 96
Registered: April 2007
|
Member |
|
|
The INV_DATE does not have a timestamp component. So do I still need to use its b-tree index?
I'm thinking that if the table is partitioned by INV_DATE then what it does is the search first goes to the correct partition and then uses the local index (bitmap) in that partition to further filter. So if I have the table as:
- range-partitioned on INV_DATE
- has bitmap indexes on INV_REQT and INV_PCT (instead of b-tree)
Then a query that has 3 conditions on the following columns: INV_DATE, INV_REQT, INV_PCT
will first go to the correct partition, then will use the local bitmap indexes to further filter the records.
Let me know if this is correct or if not.. thanks.
[Updated on: Sun, 25 May 2008 13:41] Report message to a moderator |
|
|
|
|
|
|
|
| Re: b-tree index of partitioned tabl [message #322903 is a reply to message #322648] |
Mon, 26 May 2008 15:26 |
beetel
Messages: 96
Registered: April 2007
|
Member |
|
|
| Quote: | Bitmap indexes must be used only if there is a low level of concurrent database manipulation language (DML) transactions.
Is this your case?
|
Yes, this is for datawarehouse ETL load.
| Quote: | Also do you have a partition per day?
|
nope, it is quarterly.
|
|
|
|
|
|
| Re: b-tree index of partitioned tabl [message #322948 is a reply to message #322903] |
Mon, 26 May 2008 22:57 |
 |
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | | Quote: | Also do you have a partition per day?
|
nope, it is quarterly.
|
So it is not equivalent to have partitions and an index.
Now it depends on your queries.
And, as Ana said, you have to benchmark the different cases.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
