Re: Optimizing Query

From: Herman de Boer <h.de.boer_at_itcg.nl>
Date: 25 Oct 2001 00:06:29 -0700
Message-ID: <bde5777e.0110242306.3beae08f@posting.google.com>

Hello Colin,

personally, I would try the following changes: 1. Instead of processing 1000 rows a time, which is apparently repeated until 'ready': process all rows in a single pass, in one transaction. Use a large rollback segment, if necessary. 2. rewrite the statement so that full table scans are used. Join both tables with hash join (use_hash(c) use_hash(o) hints if necessary). 3. Set hash_area_size to at least 40 Mbyte for this session (alter session set hash_area_size = 40000000).

This way, total elapsed time might be decreased a lot. It is in general not a wise idea to split a select-statement which returns about 200000 rows in pieces of 1000.

Kind Regards,

Herman de Boer. Received on Thu Oct 25 2001 - 02:06:29 CDT