Selecting data effectively sql

Question

I have a very large table with over 1000 records and 200 columns. When I try to retreive records matching some criteria in the WHERE clause using SELECT statement it takes a lot of time. But most of the time I just want to select a single record that matches the criteria in the WHERE clause rather than all the records.

I guess there should be a way to select just a single record and exit which would minimize the retrieval time. I tried ROWNUM=1 in the WHERE clause but it didn't really work because I guess the engine still checks all the records even after finding the first record matching the WHERE criteria. Is there a way to optimize in case if I want to select just a few records?
Thanks in advance.

Edit:

I am using oracle 10g. The Query looks like,

Select * 
from Really_Big_table 
where column1 is NOT NULL 
and column2 is NOT NULL 
and rownum=1;

This seems to work slower than the version without rownum=1;

Answer 1

rownum is what you want, but you need to perform your main query as a subquery.

For example, if your original query is:

  SELECT co1, col2
    FROM table
    WHERE condition

then you should try

  SELECT *
  FROM (
    SELECT col1, col2
      FROM table
      WHERE condition
  ) WHERE rownum <= 1

See http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html for details on how rownum works in Oracle.

Answer 2

1,000 records isn't a lot of data in a table. 200 columns is a reasonably wide table. For this reason, I'd suggest you aren't dealing with a really big table - I've performed queries against millions of rows with no problems.

Here is a little experiment... how long does it take to run this compared to the "SELECT *" query?

SELECT
    Really_Big_table.Id
FROM
    Really_Big_table
WHERE 
    column1 IS NOT NULL
AND
    column2 IS NOT NULL
AND
    rownum=1;