Re: How to insert records by descending order of date ...

edward_at_igate1.hac.com (Ed Bruce) wrote:

>In article <ALAW.95Sep5114237_at_ap283sun.us.oracle.com>, alaw_at_us.oracle.com wrote:
>>
>> Instead of inserting the rows in your desired order, you should write
>> your SQL statement to retrieve the rows in your desired order. In
>> your example, you can create an descending order index on the date
>> column. Inserting rows in a fixed order doesn't have a whole lot of
>> meaning in a relational database; if you use an index to retrieve the
>> rows, they will be retrieved in the order within the index anyway.
>>
 

>Inserting rows in a fixed order into a relational database can have a
>whole lot of meaning. Mainly because relational databases are notoriously
>slow.

I think what he was referring to is that it won't effect the order in which the data is retrieved which is absolutely true. You have no control over what order the RDBMS is going to store the data in. If you insert rows 1, 2, and 3, there's no guarantee that they won't be stored as 3, 1, and 2.

>I had a table that was update infrequently but data was retrieved based on
>a range of timestamps and another field. By using what Sybase called a
>cluster index I was able to guarantee that related data was in at most two
>pages and could be read into RAM with one disk read. With an non-clustered
>index it could take two disk reads. One to get the index and another to
>get the data.
 

>I have been working on a new project using Oracle and I see a need for
>this again. I thought this was possible based on cursory reading of the
>various index methods provided in Oracle. But I see from your email
>address you are probably an Oracle employee. Are you saying this isn't
>supported or that it isn't implemented well?

Oracle also has clusters. I'm not exactly sure what a Sybase cluster index is, but I suspect it's similar to Oracle's.

In Oracle a cluster is an oject that allows related records from 2 or more tables to be stored in the same physical space thus speeding up applications that access related records significantly. For example, if order_header and order_detail were clustered, you could get all of the order data from both tables in 1 physical i/o.

>Anyway your answer didn't answer the poster's question. The topic was:
>"How to insert records by descending order of date..." But from my
>dealings with Oracle that is to be expected.

As stated before, in an RDBMS it's irrelevant. The only time it might be relevant is to speed up the loading of data if you're simultaneously creating indexes. If that's the case, just sort the input data by date in descending order.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Incoming fire has the right of way!