Home » RDBMS Server » Performance Tuning » b-tree index of partitioned tabl (10g)
b-tree index of partitioned tabl [message #322643] Sun, 25 May 2008 12:04 Go to next message
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 #322644 is a reply to message #322643] Sun, 25 May 2008 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is the b-tree index on INV_DATE still required though the table is partitioned on that column?

Yes unless you have a partition per second.

Quote:
Should the other 3 b-tree indexes be changed to bitmap indexes?

what is the relation with the partitioning?

Regards
Michel
Re: b-tree index of partitioned tabl [message #322646 is a reply to message #322644] Sun, 25 May 2008 13:35 Go to previous messageGo to next message
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 #322647 is a reply to message #322643] Sun, 25 May 2008 13:39 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
What were the results from your benchmark tests evaluating both cases?
Re: b-tree index of partitioned tabl [message #322648 is a reply to message #322646] Sun, 25 May 2008 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bitmap indexes must be used only if there is a low level of concurrent database manipulation language (DML) transactions.
Is this your case?

Also do you have a partition per day?

Regards
Michel
Re: b-tree index of partitioned tabl [message #322903 is a reply to message #322648] Mon, 26 May 2008 15:26 Go to previous messageGo to next message
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 #322905 is a reply to message #322643] Mon, 26 May 2008 15:34 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
What were the results from your benchmark tests evaluating both cases?
Re: b-tree index of partitioned tabl [message #322948 is a reply to message #322903] Mon, 26 May 2008 22:57 Go to previous message
Michel Cadot
Messages: 64103
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

Previous Topic: optimizer path via DBLINKS
Next Topic: Network Load
Goto Forum:
  


Current Time: Sun Dec 04 12:41:16 CST 2016

Total time taken to generate the page: 0.24796 seconds