Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Can Oracle do a query plan like this?

Re: Can Oracle do a query plan like this?

From: Greg Stark <greg-spare-1_at_mit.edu>
Date: Sun, 06 Feb 2000 21:31:31 GMT
Message-ID: <87n1pe10ii.fsf@HSE-Montreal-ppp33976.qc.sympatico.ca>

michael_bialik_at_my-deja.com writes:

> Hi.
>
> Why don't you create an index ( b,a ) and aprtition the table by b
> instead of a?
> It looks to me that in your case you must define an additional
> index ( on b field ).
> I think that if your queries are selecting on b then partitioning by a
> is missing the point.

Because a is a kind of timestamp and we would want all the old records moved to read-only data and eventually moved offline. Actually it's a very coarse timestamp, essentially one value per day, so iterating over values would be totally feasible.

b is the userid and partitioning by it would buy us very little. It would mean manually monitoring the partitions and splitting them when they grow too large, and they would have to all be online and read-write forever.

Given that the standard example of partitioning is to partition by month for a financial transaction database, it seems unlikely that that application would require global indexes on userid and product type etc. Surely there are some circumstances where Oracle would know to iterate through each partition doing an index range scan?

--
greg Received on Sun Feb 06 2000 - 15:31:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US