Multiple data files
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Multiple data files

  1. #1
    Join Date
    Feb 2001
    Posts
    15

    Lightbulb

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The only way you can guarantee an order is to use an "ORDER BY" clause.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    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


  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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

  5. #5
    Join Date
    Jul 2000
    Posts
    243
    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...

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

  7. #7
    Join Date
    Feb 2001
    Posts
    15

    Cool multiple data files

    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.

  8. #8
    Join Date
    Aug 2000
    Posts
    462
    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.

  9. #9
    Join Date
    Feb 2001
    Posts
    15
    thanks guys.
    just for FYI
    I tested with one datafile and always get data in sequence(my luck).
    I agreed about order by clause.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width