How to return first 500 records from a query having 3 or 4 joins - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: How to return first 500 records from a query having 3 or 4 joins

  1. #11
    Join Date
    May 2006
    Posts
    4
    Hi Thomas,

    I agree with you, but in above solution inner sub query will fatch all 60,000 records (WITH ORDER BY) and then will filter out only 500 records.

    Is there any way in which oracle work just for 500 records (in a particular order), and should not work (fatch) additional 59,500 records?

    Thanx in advance.
    Sukhveer Singh

  2. #12
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    woopss... How Oracle do an ORDER BY without READ ?????
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #13
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by sukhveer_ebix
    I agree with you, but in above solution inner sub query will fatch all 60,000 records (WITH ORDER BY) and then will filter out only 500 records.

    Is there any way in which oracle work just for 500 records (in a particular order), and should not work (fatch) additional 59,500 records?
    If Oracle can work out a way of reading the records in the required order by using an index then it can do it, as long as it knows that that is what you want. The first_rows hint will help with that. Probably though it will not, so the entire result set will have to be read and sorted, hence there will be little performance benefit to restricting the query to the first 500 rows.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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