I have table of 8M records. The below query takes 2 min for 2 M record output . What should I look in below query to tune.
DECODE(SUBSTR(pol_num, 1, 3),
'00X', SUBSTR(pol_num, 3, 7),
'00T', SUBSTR(pol_num, 3, 7),
SUBSTR(pol_num, 5)) POL_NUM,
Last edited by srt; 01-18-2006 at 11:57 AM.
Are you sure that you need the DISTINCT?
Yes dave I need the distinct. Without distinct it returns rows right away, but the problem is comming with distinct. What would be the correct solution when using distinct.
See if you are getting disk-based sorts, using "set autotrace traceonly". If so we could look at modifying the memory management to sort in memory.
Also, you might like to look at this: http://oraclesponge.blogspot.com/200...le-faster.html
Click Here to Expand Forum to Full Width