5 Replies Latest reply: Feb 7, 2013 6:22 PM by choti RSS

    distinct is taking lot of time

    choti
        
       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