Issue with rownum on view

There is a basic view which has complex join on some big tables. Since there are necessary indices for the query the query cost is small and runs very fast as below

CREATE OR REPLACE VIEW myview
SELECT col1,
Col2, …..
FROM bigtable1, bigtable2,…
WHERE bigtable1.col1=bigtable2.col1…..

SELECT *
FROM myview;

Runs fast in less than a second. Once the query includes a where clause constraint on rownum as below.

SELECT *
FROM myview
WHERE rownum<:n;

The query runs for ever and its cost increases tremendously. The plan shows full table scans on these table. It seems like optimizer assumes that since it needs to fetch :n number of rows why not do a full table scan.

The trick I found which fixed this query is using an order by clause as suggested by tom at this link http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

By using
CREATE OR REPLACE VIEW myview
SELECT col1,
Col2, …..
FROM bigtable1, bigtable2,…
WHERE bigtable1.col1=bigtable2.col1…..
Order by col1;

And


SELECT *
FROM myview
WHERE rownum<:n;

Fixed the problem. By using order by with rownum on outer query optimizer does sort on only first n number of rows. Once the rownum > n satisfies it returns the result set. That way optimizer keeps the plan intact as estimated and avoids full table scan.