| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: will the Return Order of rows change with time ?
Ratnesh,
Oracle will return the rows in order of physical access, which is determined by the access path. This can be a full table scan, index read or the result set from another operation. If parallelism is used, the return order can be altered. Updates/Deletes, export/import, rebuilds will almost certainly alter the physical v. logical order of the rows.
The only way to guarantee that the order returned is the order inserted is to add a sequential indicator, this could be a derived sequence number or a timestamp of sufficient granularity (dependent on # of rows inserted per second) and then add an 'ORDER BY' clause.
-----Original Message-----
Sent: Tuesday, October 22, 2002 4:39 AM
To: Multiple recipients of list ORACLE-L
Hi
I have a very large DW table in which there are only inserts and NO
updates/deletes.
The table grows by around 2-5 % every week due to new inserts.
I need to return the rows for each customer in the same order as inserted to
table.
Due to design/delivery constraints , i cannot modify the table.
ques 1 : if i do a 'select * from table' with where clause but no order by clause,
will the Order of rows returned be the same whenever this query
is
executed ?
Is this gauranteed by Oracle ?
ques 2 : if i export/import this table , then execute the same query,
will the Order of rows returned be the same as before the
export/import
?
ques 3 : if i use the 'move' cmd to rebuild this table , then execute my query,
will the Order of rows returned be the same as before the rebuild ?
any explanations are most welcome....
many thanks
ratnesh singh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ratnesh Kumar Singh
INET: ratnesh.singh_at_patni.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Oct 22 2002 - 11:34:11 CDT
 |
 |