Re: Index Ordered Table ordering

Kris Jenkins <krisajenkins_at_yahoo.co.uk> wrote in message news:<40210d61$0$21304$cc9e4d1f_at_news.dial.pipex.com>...
> Ed prochak wrote:
> > Kris Jenkins <krisajenkins_at_yahoo.co.uk> wrote in message news:<401e2c88$0$16460$cc9e4d1f_at_news.dial.pipex.com>...
> >
> >>Hi,
> >>
> >>Is there a way to choose the order of Index Ordered Tables?
> >>
> >>I've got a table like this:
> >>
> >>CREATE TABLE prices (
> >> price integer,
> >> id integer
> >> CONSTRAINT prices_pri PRIMARY KEY ( price, id )
> >>) ORGANIZATION INDEX;
> >>
> >>...and this gives me a table that is effectively 'ORDER BY price ASC, id
> >>ASC'. What I'd really like is to have it natively stored as 'ORDER BY
> >>price *DESC*, id ASC'.
> >>
> >>Is that possible?
> >>
> >>If not I'll store price as (-1 * price), but that's not ideal.
> >>
> >>Cheers,
> >>Kris
> >
> >
> >
> > you shouldn't care until you query the data. Stop thinking of ORACLE
> > as a file system and think in terms of tables (ie UNORDERED SETS of
> > data). Besides your initial assumption is wrong. The indices do not
> > have any special ordering.
>
> I thought that was the whole point of an Index-Ordered-Table (IOT) -
> isn't that what the ORGANIZATION INDEX keywords mean?
>
> > Fact is, that index would work just fine for a query like:
> >
> > SELECT * FROM prices_pri
> > WHERE price BETWEEN 0.01 AND 0.99
> > ORDER BY price DESC, id ;
>
> True, but I'm only ever interested in the highest or lowest price right
> now. That price will change as rows get inserted/deleted, but on a
> query, I only really care about the highest/lowest.
>
>
> Thanks for the reply though, ed :-)

I do not understand why you would use an IOT structure for data with these chararistics. As a general rule the PK value should not change often. The cost of maintaining the index structure may be fairly high for the data as described. And based on experience with pricing systems I would expect an effective_date value to also be part of the equation. For that matter doesn't price only have significance in relationship to an ID (item/part/product)? If so ID, Price seems a better PK.

Nervertheless, as worded max and min on the price will find the highest and lowest price for you. By using a group by clause you could easily produce a report by ID with the lowest and highest price.

If the change order of the price changes is significant then you might want to add a sequence number to the rows. This would allow you to find the current (max sequence) and the first (min sequence) price.

IMHO -- Mark D Powell -- Received on Thu Feb 05 2004 - 08:47:45 CST