SQL query Tuning

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 PlanSELECT  --+ 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 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 Mon Nov 05 2001 - 13:27:34 CET