Re: SQL query Tuning

Your query is doing a full table scan not because of the <> but because no rows are eliminated right away (in fact it doesn't use the pk index at all).

That is the query doesn't know which product_range_id it should look for so it has to consider all of them. So without further limiting rows the query will have to do a full table or full index scan.

If you had an index on the active_flag you could use that to elminate the inactive ones, but I suspect that there aren't many of those.

You might be able to re-order IPAR_IND_1 (put osr_ from and to product_range_code first or put it in it's own index) and that might help. I would need to know more about the table layout to say more.

Cheers
BK

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.
Received on Tue Nov 06 2001 - 00:33:28 CET