0

I have the following query

 SELECT * FROM A JOIN B ON A.a=B.b JOIN C ON A.a=C.c.

The above query return million rows and takes lot of time to return results. How can I see just first "N" records so that the query runs faster.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user1118468
  • 713
  • 4
  • 10
  • 19

3 Answers3

2

In Oracle, you would just add rownum to the where clause:

SELECT *
FROM A JOIN
     B
     ON A.a = B.b JOIN
     C
     ON A.a = C.c
WHERE rownum < 100;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You can use hint first_rows:

SELECT /*+ FIRST_ROWS(100) */ * FROM A JOIN B ON A.a=B.b JOIN C ON A.a=C.c

Also check if you have indices on A.a, B.b and C.c.

As an addition you should understand that any sorting(order by), or analytic function, or group by operation, or distinct (maybe something else as well which requests the whole data set, e.g. UNION or INTERSECT) will destroy this hint and anyway will collect the whole dataset. They simply cannot work without it: e.g. how to order the data set if there is not the whole list presented?

smnbbrv
  • 23,502
  • 9
  • 78
  • 109
  • The `FIRST_ROWS` *hint* doesn't limit the rows, although it may make returning the first rows faster. – Jeffrey Kemp Mar 10 '14 at 03:50
  • yes, but I thought rownum is quite clear and the problem is to make it faster... Anyway, the biggest part of answer is about another thing – smnbbrv Mar 10 '14 at 08:32
  • You're right, the OP's real problem is the performance of the query - but their most immediate question was how to limit the rows, and they don't appear to be familiar with the ROWNUM solution. But your answer is useful and I'm not downvoting it. – Jeffrey Kemp Mar 10 '14 at 08:33
-2

You can get desired number of record with TOP Clause. Here is an example:-

SELECT TOP n *
FROM A
JOIN B ON A.a=B.b 
JOIN C ON A.a=C.C
sunk
  • 320
  • 1
  • 3
  • 9