DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

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

  1. #1
    Join Date
    May 2006
    Posts
    4

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

    Hi,

    Can someone help me to find a way to reterive first 500 records from a query having 3 or more then 3 joins. By default query is returing more then 60000 records,and taking enough time to execute.
    Is there any way in which oracle work to reterive first 500 records and should not work for rest of the records, so that query execution time can be much faster.

    Thanx in advance.
    Sukhveer Singh

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    where rownum <= 500
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

  4. #4
    Join Date
    May 2006
    Posts
    4
    Hi,

    Thanx for reply, rownum is a solution, but rownum will first make join for all the records (let say in my case more then 60,000) and then it will filter out first 500. so the query execution time will remain same. Pls correct me if i am wrong?

    Sukhveer Singh

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    how about putting in a first_rows hint as well?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by sukhveer_ebix
    Hi,

    Thanx for reply, rownum is a solution, but rownum will first make join for all the records (let say in my case more then 60,000) and then it will filter out first 500. so the query execution time will remain same. Pls correct me if i am wrong?

    Sukhveer Singh
    I don't think so.

    See below:
    Code:
    SQL> explain plan for
      2  select cust_id , s.state , name
      3  from sales s , customer c
      4  where s.cust_id = c.id
      5  and  rownum <= 500
      6  /
    
    Explained.
    
    SQL>
    SQL> select * from table(dbms_xplan.display)
      2  /
    
    -----------------------------------------------------------------------------
    | Id  | Operation                     |  Name       | Rows  | Bytes | Cost  |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |             |     1 |    38 | 11591 |
    |*  1 |  COUNT STOPKEY                |             |       |       |       |
    |   2 |   NESTED LOOPS                |             |     1 |    38 | 11591 |
    |   3 |    TABLE ACCESS FULL          | SALES       |    14M|   109M| 11591 |
    |   4 |    TABLE ACCESS BY INDEX ROWID| CUSTOMER    |     1 |    30 |       |
    |*  5 |     INDEX UNIQUE SCAN         | PK_CUST     |     1 |       |       |
    -----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM<=500)
       5 - access("S"."CUST_ID"="C"."ID")
    
    Note: cpu costing is off
    
    19 rows selected.
    COUNT STOPKEY will stop the process.

    OR USE PAGINATION concept

    tkprof output:
    Code:
    select /*+ FIRST_ROWS_100 index(s sales_idx_1) */
          cust_id , s.state , name
    from sales s , customer c
    where s.cust_id = c.id
    and  rownum <= 500
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch       35      0.01       0.01          0       1109          0         500
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       37      0.01       0.01          0       1109          0         500
    
    Misses in library cache during parse: 0
    Optimizer goal: CHOOSE
    Parsing user id: 31
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
        500  COUNT STOPKEY
        500   TABLE ACCESS BY INDEX ROWID SALES
        750    NESTED LOOPS
        250     TABLE ACCESS FULL CUSTOMER
        500     INDEX RANGE SCAN SALES_IDX_1 (object id 68538)
    
    ********************************************************************************
    Note: SALES table 14M rows and CUSTOMER has 95820 rows.

    Tamil
    Last edited by tamilselvan; 05-24-2006 at 02:38 PM.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How much work you save, and hence the performance improvement, would depend on the join mechanism of course. It wouldn't save you much on a hash join or a sort-merge, but on a nested loop it will. The first-rows hint could well change the join mechanism to one that will promote savings due to the rownum predicate.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  8. #8
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Tamil, that is a good one. Thanks.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  9. #9
    Join Date
    May 2006
    Posts
    4
    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

  10. #10
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Tamil showed you Oracle just read 500 (only) when you specify ROWNUM <=500 and how Oracle will do an ORDER BY on rows which was never red?

    Use subquery for this

    SELECT * FROM ( Your original query with ORDER BY) where rownum <=500

    But the plan will be different and will fetch all rows satisfying your condition.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

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