Re: INSERT INTO with SELECT

David Small wrote in message <01bd2a61$ea492b60$3b81170a_at_smalld>...
> If I use an INSERT INTO clause, followed be a SELECT clause, followed by
>an ORDER BY clause I receive an error message ("SQL command not properly
>ended"). If I remove the ORDER BY clause, the statement works fine. Is
>there a way to include the ORDER BY clause in Oracle? Most other RDBMS's
>permit it in the INSERT INTO/SELECT statements.

ORDER BY is not allowed with DML statements like INSERT INTO/SELECT and CREATE TABLE AS SELECT. AFAIK you can not physically order data in an Oracle table by creating that table with an ORDER BY clause.

The major reason (IMO) for physically ordering data is to make sure that with one physical i/o, other "revelant" rows are read into the buffer, e.g. if most of the access on a persons table is alphabetically via surname then physically storing data alphabetically via surname gives an excellent buffer hit ratio.

This was true with the older RDBMS technology of the 80's, but I think that this is no longer a critical factor in getting high buffer cache hit ratios or good performance. New techniques such as "read-ahead" processes, new caching techniques and even the new hard disk technology with onboard caches have made this method of physically oredring data obselete IMHO.

A simple example. We have a table with close on to a 140 million rows. We have a couple of indexes on this table. One such index is about 4GB. Using this index I can retrieve all the records for an index value in a couple of seconds. Physically ordering this table will not only be an administrative and database nightmare, but will not make any significant difference in the speed of retrieval - thus what advantage will there be to physically order tables even 1% the size of the big table we're using?

regards,
Billy Received on Wed Jan 28 1998 - 00:00:00 CET