Oracle view performance with rownum

Question

I am using Oracle 10g, and I have a view that joins two large tables (millions of records). I am trying to select a limited "sample" of the data for the user like this:

select * from VIEW_NAME where ROWNUM < 5; 

It is very slow, and I think it should not be, because I just need a few rows of the result, so Oracle should not calculate the full join.

The requirement is that the user should be able to specify interactively the number of returned rows (it doesn't matter exactly which rows from the result). Is there any way to achieve this? (with rownum or with another method)

(I can change the view definition or the way the final SQL is built, but as far as I know, I cannot pass information about the desired number of rows dynamically to the view)

EDIT: The view definition is very simple, something like this:

CREATE OR REPLACE VIEW VIEW_NAME AS
(
    select
    e.id as ID,
    e.somefield as something,
    ... (some similar selects from e)
    c.field as anotherthing,
   ... (lots of other fields from c)
    from SCHEMA.TABLE1 e
    inner join SCHEMA.TABLE2 c on e.key = c.key
)

Explain plan mentions a full table access for both tables which is not surprising, because just returning the first few rows should not take a long time.

EDIT2: here's the full plan

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2644394598

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |     4 |  1252 |       | 43546   (1)| 00:08:43 |       |       |        |      |            |
|*  1 |  COUNT STOPKEY            |             |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR          |             |       |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10002    |   696K|   207M|       | 43546   (1)| 00:08:43 |       |       |  Q1,02 | P->S | QC (RAND)  |
|*  4 |     COUNT STOPKEY         |             |       |       |       |            |          |       |       |  Q1,02 | PCWC |            |
|*  5 |      HASH JOIN BUFFERED   |             |   696K|   207M|    49M| 43546   (1)| 00:08:43 |       |       |  Q1,02 | PCWP |            |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
|   6 |       BUFFER SORT         |             |       |       |       |            |          |       |       |  Q1,02 | PCWC |            |
|   7 |        PX RECEIVE         |             |   696K|    90M|       |  5137   (1)| 00:01:02 |       |       |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH      | :TQ10000    |   696K|    90M|       |  5137   (1)| 00:01:02 |       |       |        | S->P | HASH       |
|   9 |          TABLE ACCESS FULL| TABLE1      |   696K|    90M|       |  5137   (1)| 00:01:02 |       |       |        |      |            |
|  10 |       PX RECEIVE          |             |   892K|   149M|       |  5260   (1)| 00:01:04 |       |       |  Q1,02 | PCWP |            |
|  11 |        PX SEND HASH       | :TQ10001    |   892K|   149M|       |  5260   (1)| 00:01:04 |       |       |  Q1,01 | P->P | HASH       |
|  12 |         PX BLOCK ITERATOR |             |   892K|   149M|       |  5260   (1)| 00:01:04 |     1 |   140 |  Q1,01 | PCWC |            |
|  13 |          TABLE ACCESS FULL| TABLE2      |   892K|   149M|       |  5260   (1)| 00:01:04 |     1 |   140 |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(ROWNUM<5)
   4 - filter(ROWNUM<5)
   5 - access("E"."KEY"="C"."KEY")

27 rows selected.

Answer 1

I'd see what the /*+ NOPARALLEL */ hint does as per GuiGi's answer. Another thing to try is look at the plan generated for this:

select /*+ FIRST_ROWS(10)*/ * from VIEW_NAME where ROWNUM < 5;

Answer 2

You can try adding a NOPARALLEL hint to the query.

select /*+ NOPARALLEL */ * from VIEW_NAME where ROWNUM < 5; 

This is a situation where parallel execution is chosen but it might be bad for performance, as it would use more CPU and I/O.

Answer 3

Do you have join index (indexed nested loops should be the access path chosen by optimizer)? Try disabling hash_join (together with sort_merge_join) to see what is the cost of alternative plane, if you see ordinary nested loops, then optimizer ignored index for some reason.

When tuning queries with views inline the view definition, then try hinting the access path that you want. When you find magic hints (e.g.cardinality) sometimes they can be moved into outside query block (this is especially true for later oracle versions).

Answer 4

You can also try:

select * FROM 
(SELECT rownum ROW_NUMBER, YOUR_VIEW.* FROM  YOUR_VIEW) 
WHERE ROW_NUMBER> 2