SQL Tuning having MAX function

[siraj777]

SELECT quarter
FROM rcqtr
WHERE rcqtr_num = ( SELECT MAX(rcqtr_num) FROM rcprod11 )

rcprod11 table having 400,000 rows.
rcqtr table have around 1000 rows.

The above query is taking 30 seconds. Any ideas to tune it?

 

[sem]

Create index on rcprod11.rcqtr_num. Don't forget to gather statistics.

Regards, Dima

http://seminihin.narod.ru

 

[dbtoo2001]

Dima, Would that be a function based index? How about an additional index on rcqtr.quarter , rcqtr.rcqtr_num ?


Would something like
SELECT a.quarter, max(b.rcqtr_num) over (partition by b.rcqtr_num) FROM rcqtr A, rcprod11 B
WHERE a.rcqtr_num = b.rcqtr_num

 

[sem]

dbtoo2001, did you mean FFS? It may depends on, because the size of compound index sometimes may not fit into memory thus it would be faster to perform a set of "in memory" operations instead of one, even easier, but "in swap". As for windowing, yes, this is the best way IMHO, but in 8i it has some limitations (and workarounds though).
As for this specific case with 30 sec for 400K rows - the plan should be ugly indeed

Regards, Dima

http://seminihin.narod.ru