Query Taking long time

Question

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.

You can use SELECT Top n * from A.... – sunk Mar 04 '14 at 15:57 — sunk, Mar 04 '14 at 15:57
What database are you using? – Gordon Linoff Mar 04 '14 at 16:03 — Gordon Linoff, Mar 04 '14 at 16:03
@GordonLinoff I am using Oracle – user1118468 Mar 04 '14 at 16:20 — user1118468, Mar 04 '14 at 16:20

score 2 · Answer 1 · answered Mar 04 '14 at 16:26

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;

answered Mar 04 '14 at 16:26

Gordon Linoff

1,242,037
58
646
786

smnbbrv · Answer 2 · 2014-03-04T16:43:04.737

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?

edited Mar 04 '14 at 16:43

answered Mar 04 '14 at 16:38

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

score -2 · Answer 3 · answered Mar 04 '14 at 15:58

-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

answered Mar 04 '14 at 15:58

sunk

320
1
3
9

Query Taking long time

3 Answers3