Re: SQL query Tuning

ramsunders_at_yahoo.com (R197509) wrote in message news:<1e562f83.0111050427.2965002d_at_posting.google.com>...
> Hi,
> I'm trying to tune the following SQL query.
> The requirement is to Select Overlapping ranges.
> Eg. Product range from 'A4' to 'A7', overlaps the Product range 'A1'
> to 'A5'.
>
> Product_range_id is the Primary key of the table and there is a
> non-unique index on combination of csc_code,
> Osr_from_range_product_code, Osr_to_range_product_code.
>
> Following is the query we are using and it's Explain Plan-
> SELECT --+ RULE
> to_number(IPAR1.product_range_id||lpad(IPAR2.product_range_id,11,0))
> Range_id,
> IPAR1.csc_code Csc_code,
> IPAR1.osr_from_range_product_code Range_from,
> IPAR1.osr_to_range_product_code Range_to,
> IPAR2.osr_from_range_product_code Overlapping_Range_from,
> IPAR2.osr_to_range_product_code Overlapping_Range_to
> FROM inv_product_allocation_ranges IPAR1,
> inv_product_allocation_ranges IPAR2
> WHERE IPAR1.product_range_id <> IPAR2.product_range_id
> AND IPAR1.csc_code = IPAR2.csc_code
> AND IPAR2.osr_from_range_product_code BETWEEN
> IPAR1.osr_from_range_product_code AND IPAR1.osr_to_range_product_code
> AND IPAR1.active_flag = 'Y'
> AND IPAR2.active_flag = 'Y';
>
> SELECT STATEMENT Optimizer=HINT: RULE
> NESTED LOOPS
> TABLE ACCESS (FULL) OF INV_PRODUCT_ALLOCATION_RANGES
> TABLE ACCESS (BY INDEX ROWID) OF INV_PRODUCT_ALLOCATION_RANGES
> INDEX (RANGE SCAN) OF IPAR_IND_1 (NON-UNIQUE)
>
> Above query is taking around 3 hrs in Production, because of the
> 'not-equal to' join.
> We need to reduce this time to less than 1 hr.
> We are using Oracle8i. Is there any feature by which we can avoid the
> necessity of join?
> Or can we rewrite the query in such a way that it will take less time?
>
>
> Regards,
> Ram.

I guess you are indeed right about the <> operator not being responsible for the Full Table scan, and a Full table scan is probably the right thing too in this case. Also you are right in saying that there would hardly be any inactive records as such and so an index on active_flag would not help.
I will try re-ordering the index as you have suggested or probably add a composite index on osr_from_range_product_code and osr_to_range_product_code.

Thanks. Received on Tue Nov 06 2001 - 06:17:07 CET