How to limit results in Oracle without reading the entire table?

Question

How to limit results in Oracle without reading the entire table?

Navigation

#1 by (2 votes)

0

How can I return the first 100 rows of a table without having to read all of its records?

My table has 4 rows of rows, and I'm having trouble trying to return only its first 100 rows, because every time I use ROWNUM, sgbd reads the entire table (I think, since it takes hours to finish routing). query).

Example:

SELECT ACCT FROM ACCT_CUST AC WHERE AC.TYPE != "DT" AND ROWNUM < 100;

Oracle does not have TOP or Limit, so I do not know how to use rownum to get the rows without reading the entire table

oracle

asked by anonymous 01.09.2015 / 23:16

1 answer

Ruby installing OpenCode GEM (tray) Is there anything wrong with this code that sends a String to the server?

score 2 · Answer 1

1- Be specific in your search;

SELECT ACCT FROM ACCT_CUST AC WHERE AC.TYPE IN ("AA","BB")

Using != clause you compel index to compare all the different results of your result.

2- To improve the performance of your result;

SELECT * FROM (
SELECT AC.ACCT, RANK () OVER ( ORDER BY AC.TYPE ) LINHA 
FROM ACCT_CUST AC 
WHERE AC.TYPE IN ("AA","BB","CC")
 )aux WHERE aux.LINHA BETWEEN 1 AND 100

For more in the Oracle documentation: link