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

[tamilselvan]

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:

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:

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