Thanks Tamil, for the prompt reply. I still have some doubt. Let me try to explain it with help of two queries.

First if I run below query with first 500 records then it seems ok as there is no fix order of the records

select cust_id , s.state , name
from sales s , customer c
where s.cust_id = c.id
and rownum <= 500

BUT if i want records to be always in a particular order then i will modify the query a bit

select cust_id , s.state , name
from sales s , customer c
where s.cust_id = c.id
and rownum <= 500
ORDER BY name, state

Will this query give me exact results every time in the same order?
I think oracle will get the result in defined order and then will return me first 500 records. But this is not the optimise way i believe.
Is there any way to do this.

Thanks for help in advance
Sukhveer Singh