Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Default order of data retrival in oracle

jvavadiyajvavadiya Member Posts: 1
We are using oracle 10g database for our application.In our application we have one table with arround 3,00,000 record.We need to query this table 1000 times per minute.We have one field called trd_seqno which gets increameted after each insertion of record in that table.We have index on this field.There is no updation or deletion in table.Now problem is that when we apply oredr by cause in query it takes arrount 1500 milisecond to query data and without order by it takes arround 15 miliseconds.So While quering data we need it in asending order of trd_seqno ( means in order in which we are inserting ) .One of my friend who is Oracle certified profetional said that if did not specify order by than default order of retrival is order in which we have inserted data (this is what we want for better performance),but our DBA team said that this not gaurnteed.
They said oracle specifies that is we want to to receive data in any perticular order we need to specify it in order by cause.

Can anyone tell me that what is default order of data retrival in oracle 10g? or anyother way to improve performance of query.

Thanks in advance.

Regards,
Jignesh


Comments

  • infidelinfidel Member Posts: 2,900
    : We are using oracle 10g database for our application.In our application we have one table with arround 3,00,000 record.We need to query this table 1000 times per minute.We have one field called trd_seqno which gets increameted after each insertion of record in that table.We have index on this field.There is no updation or deletion in table.Now problem is that when we apply oredr by cause in query it takes arrount 1500 milisecond to query data and without order by it takes arround 15 miliseconds.So While quering data we need it in asending order of trd_seqno ( means in order in which we are inserting ) .One of my friend who is Oracle certified profetional said that if did not specify order by than default order of retrival is order in which we have inserted data (this is what we want for better performance),but our DBA team said that this not gaurnteed.
    : They said oracle specifies that is we want to to receive data in any perticular order we need to specify it in order by cause.
    :
    : Can anyone tell me that what is default order of data retrival in oracle 10g? or anyother way to improve performance of query.

    SQL result sets are *by definition* unordered. Depending on indexes and individual DBMS implementations, the results you observe may always appear to be ordered in a particular fashion, but the point is the only way to *guarantee* an order is with the ORDER BY clause.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

Sign In or Register to comment.