Choosing data efficiently sql

Question

I have a very large table with more than 1000 records and 200 columns. When I try to restore records that meet some criteria in a sentence WHEREusing an instruction SELECT, it takes a long time. But most of the time I just want to select one record that meets the criteria in the sentence WHERE, and not in all the records.

I suppose there should be a way to select only one entry and exit, which will minimize the search time. I tried ROWNUM=1in the sentence WHERE, but actually it didn’t work, because I think that the engine still checks all records even after searching for the first record matching the criteria WHERE. Is there a way to optimize if I want to select only a few records?
Thanks in advance.

Edit:

I am using oracle 10g. The request looks like this:

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

This is slower than the version without rownum = 1;

Answer 1

rownum - this is what you want, but you need to execute the main query as a subquery.

For example, if your original request:

  SELECT co1, col2
    FROM table
    WHERE condition

then you should try

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

, rownum Oracle, . http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html.

Answer 2

1000 . 200 - . , - .

... "SELECT *"?

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

Answer 3

SQL ( , WHERE ORDER BY, ).

, SQL , .

- Oracle.

.

- *.

Answer 4

:

SELECT ename, sal 
FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) sal_rank
              FROM emp ) 
WHERE sal_rank <= 1;

WHERE

Answer 5

1000 . , , :

1.

. (HWM) - ( ), FULL TABLE SCAN , , , .

(dbms_stats.gather_table_stats('<owner>','<table>')) , ( ), (), :

SELECT t.avg_row_len * t.num_rows data_bytes, 
       (t.blocks - t.empty_blocks) * ts.block_size bytes_used
  FROM user_tables t
  JOIN user_tablespaces ts ON t.tablespace_name = ts.tablespace_name
 WHERE t.table_name = '<your_table>';

, , (PCT_FREE). , , ( 30%, YMMV), reset HWM:

• ALTER TABLE <your_table> MOVE;, INDEX (ALTER INDEX <index> REBUILD), .
• DBMS_REDEFINITION

2.

, LOB, CLOB, LONG (irk) .. 4000 ( ), , , .

, SELECT *. , SELECT rowid, : SELECT * WHERE rowid = <rowid>.