Multiple data files

[sidshu]

We had one data file, there was no sequence problem
for retrieving the data without order by clause.It was in sequence.

but
When we added another datafile and index file.
Now when program retrieves the data(no order by
clause)
it does not come in sequence. e.g if order numbers are
123456, then it retrieves as 123654.Even I run a
simple select query without order by clause, it does
the same way.

Is it possible that data may be in two different
datafiles and retrieving without order by class
resulted that(no sequency)?
It retrieves data in sequency some time ,but most of
the time it does not.

[marist89]

The only way you can guarantee an order is to use an "ORDER BY" clause.

[rohitsn]

Hi,
Adding datafiles to tablespaces doesnot have any effect on the retrival of data.In order to retrieve the data in order u want use ORDER BY clause in ur select statement.
If u want any help regarding ur doubts please write to me at
rohitsn@orasearch.net


Regards,
Rohit Nirkhe,Oracle DBA,OCP

[SANJAY_G]

Oracle need not keep the data in sequnce even if there is only one datafile. So data retrieval has nothing to do with datafiles. You must use order by caluse to sort your data.

Sanjay

[shawish_sababa]

Hi sidshu

you are mixing 2 thigs that have no connection: the output of a select statment and the way your data is stord on your machin.

a datafile is a file in a directory, it is where you stor the data in your tables. when you create a table you create it a table space. when you create a tablespace you connct it to a datefile. you can read a lot about it in the oracle documantation (under concepts).

when you ex. a select the system is takeing the data from the datafile in a specific way that has it's own logic, no connection to datafile, but to data in the table, indexes and so on...

[tamilselvan]

The output of SELECT statement is in the order of rows stored in the data block, if ORDER BY clause is not included.

As others said, there is relationship between data file and output order.

[sidshu]

Thanks for suggetions.
When we had one data file , it was in sequency without using
order by clause.
The user used to see in sequence (form 4.5). When we added, then it retrieves in sequence some time but most of the time not.
I understand that order by clause will put in sequence.
but, why it was doing before adding second data file?

thanks again.

[kmesser]

Oracle will in many cases return the data in the order inserted, particularly if no rows have been deleted from the table with subsequent inserts. However, it was essentially luck. Control everything you can - default behavior has a way of creeping through revisions and system changes. Don't count on it if you can help it.

[sidshu]

thanks guys.
just for FYI
I tested with one datafile and always get data in sequence(my luck).
I agreed about order by clause.