Re: query "select * from table_name where rownum = 1" very slow

No version, no specifics about optimizer being used. Why do so many people assume everyone knows which version they are using, and/or expect we can look over their shoulder.

Rownum per se doesn't result in a full table scan. Rownum just limits the resultset to the first record. (The optimizer will perform a count stopkey operation on the resultset)
However in the statement above, if you leave the rownum=1 condition out, what do you get? Exactly: a full table scan. So, if you would have run dbms_xplan on the the statement, you would have typically seen count(stopkey)

    table_name (full table scan)
So yes, evidently, if you run this statement against a table with a large number of records that will take some time, and you will thrash the performance of the database.
But evidently: you ASK it to perform a full table scan, so that is what you get.

The rule of leading subset doesn't apply anymore to Oracle 9i and higher.
If you are using the CBO, you will get index skip scans.

I wouldn't trust on hearsay, but adhere to Tom Kyte's mantra: Test, test and test
(and of course use auto_trace and run explain plan)

-- 
Sybrand Bakker
Senior Oracle DBA