slimdave

Thanks for your reply.

Autotrace did seem to suggest that I was reducing the number of LIOs e.g. consistent gets. The query was performing 18 consistent gets pre-reorg and 6 post. It has always been my understanding that ordering the data does reduce LIOs because the same blocks do not need to be read into the buffer cache over and over again when the table access by rowid occurrs. Am I missing something here?

With regard to parallel query, given this is OLTP we don't use it. The query currently runs quickly (on average 0.17 seconds per exec) but re-ordering the table got this down to 0.02 seconds. I realise these difference sound totally academic, but the query is executed so many time during the batch job that this reduced the runtime by 3 hours! Given the current runtime do you think parallel query would help here? Can table access by rowid take advantage of parallelism?

I've done a little reading on hash clusters and notice that in 8i at any rate range scans aren't supported. Would it be possible to for you to explain a bit more about this option?

Any help would be appreciated.

Thanks

Austin