Here is my query:
SELECT a, b
FROM (
SELECT tab1.a, tab1.b,
RANK() OVER (PARTITION BY tab1.d
ORDER BY tab1.a DESC NULLS LAST) rnk
FROM tab1, tab2
WHERE tab2.x = xpto
AND tab1.d = xyz
AND tab1.c = tab2.a
) WHERE rnk < 7

I have a primary key on tab1, that includes column c, d and others (the order is c,d,e,f).
Have a primary key in tab2.
The response time is about 0.6s to 1.3s. The point is that I have to make this query for 40000 times, if
it takes 1s the procedure time is about 11hours!!!

Explain plan
SELECT STATEMENT Optimizer Mode=CHOOSE 1 96
VIEW 1 39 96
WINDOW SORT PUSHED RANK 1 85 96
TABLE ACCESS BY LOCAL INDEX ROWID TAB1 3 117 2
NESTED LOOPS 1 85 94
TABLE ACCESS FULL TAB2 1 46 92
PARTITION RANGE ITERATOR KEY KEY
INDEX RANGE SCAN PK_TAB1 3 1 KEY KEY


Table tab1 has 115200000 records and 70800.

I try to run statistics, but without success in performance.

What can I do?

Thanks in advance,
Carlos Dias