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

Asked

Viewed 4,128 times

0

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

My table has 4 million lines , and I’m having trouble trying to return only your first 100 lines, because every time I use ROWNUM, sgbd reads the whole table (I think, since it takes hours to complete the query).

Example:

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

Oracle doesn’t have TOP or Limit, so I don’t know how to use rownum to pick up lines without reading the entire table

oracle

  • Rownum works the same as TOP or LIMIT, the only difference is the syntax. But this behavior indicates that you do not have an Word in the TYPE column. If you have an Input in this column, you can try forcing it to be used via the Choose directive.

1 answer

2

1- Be specific in your search;

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

Using clause != you force the index to compare all 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 see oracle documentation: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm

Browser other questions tagged oracle

You are not signed in. Login or sign up in order to post.