This discussion is archived
5 Replies Latest reply: Feb 7, 2013 4:22 PM by choti RSS

distinct is taking lot of time

choti Newbie
Currently Being Moderated
  
 SELECT DISTINCT Dealer_CODE ,
  EXT_CUST_ID ,
  FULL_NAME ,
  LAST_NAME ,
  FIRST_NAME ,
  MIDDLE_NAME ,
  OTHER_NAME ,
  STREET_ADDRESS_ONE ,
  STREET_ADDRESS_TWO ,
  CITY ,
  STATE ,
  POSTAL_CODE_STRING ,
  PHONE_NUMBER_HOME ,
  PHONE_NUMBER_WORK ,
  PHONE_NUMBER_CELL ,
  EMAIL ,
  CUSTOMER_ACTIVE ,
  MAX(migrate_time) AS migrate_time
FROM customer o
WHERE exists
  (SELECT 1
    from customer_fl i
    WHERE i.dealer_code=o.dealer_code
    AND i.ext_cust_id=o.ext_cust_id
    AND i.response_id=o.response_id  )
GROUP BY DEALER_CODE ,
  EXT_CUST_ID ,
  FULL_NAME ,
  LAST_NAME ,
  FIRST_NAME ,
  MIDDLE_NAME ,
  OTHER_NAME ,
  STREET_ADDRESS_ONE ,
  STREET_ADDRESS_TWO ,
  CITY ,
  STATE ,
  POSTAL_CODE_STRING ,
  PHONE_NUMBER_HOME ,
  PHONE_NUMBER_WORK ,
  PHONE_NUMBER_CELL ,
  EMAIL ,
  CUSTOMER_ACTIVE;
how can i rewirte the query. Distinct is taking lot of time

Customer has index on dealer_code,migrate_time.


Customer_fl has only 3 columns dealer_code,ext_cust_id, response_id



Explain plan is below
-------------------------
 OPERATION                                         OBJECT_NAME       OPTIONS        COST 

 SELECT STATEMENT                                                                                   1561656 
                
          
     HASH                                                                              GROUP BY       1561656 
         
                  
         HASH JOIN                                                                      RIGHT SEMI       1511717 
         
                           
           Access Predicates 
                                    
           AND 
                                             
                    I.DEALER_CODE=O.DEALER_CODE 
                                             
                 I.EXT_CUST_ID=O.EXT_CUST_ID 
                                             
                 I .RESPONSE_ID=O.RESPONSE_ID 
                           
 TABLE ACCESS                                     CUSTOMER_FL                        FULL       1031 

                           
 REMOTE                                                CUSTOMER                                            281571 
 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points