OraFAQ Forum: Performance Tuning » Query does not error, but does not come back with a result set (as if it's in an endless loop)

Home » RDBMS Server » Performance Tuning » Query does not error, but does not come back with a result set (as if it's in an endless loop) (Oracle 11G running on UNIX AIX 6.1 O/S)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query does not error, but does not come back with a result set (as if it's in an endless loop) [message #642508]

Sun, 13 September 2015 17:15

aft5425@anthem.com
Messages: 11
Registered: September 2015

Junior Member

Hi, I've searched for answers on this forum, as well as google and I've tried a few suggestions, but nothing has worked so far. I'm running what I think is a simple nested subquery.

Issue is that the query never stops. I let it run for more than 15 minutes, but it still doesn't come back. When I run everything except the outermost query, I get a result set in less than 2 seconds (I have a hardcoded grup in the innermost query to limit the data). But adding the outermost query causes it to never stop!

Starting with the innermost query...
The innermost query uses D_ESTABLISH which is the most recent established date, where the D_UPDT is greater than 8/31/13.
The 2nd subquery gives me the specific group where the D_RELEASE date is not null.
The 3rd subquery gives me the I_CUST with the lowest number for that I_GRUP (1 to many -- I_GRUP to I_CUST)
Up to here, it's fine and runs fast
The 4th outermost query simply takes the I_CUST from previous query and gives me

SELECT DISTINCT ACCT.I_CUST FROM SFDC_STAGING.STG_IND_SALI_PERSON_ACCNT ACCT
WHERE ACCT.I_CUST IN 
   (SELECT DISTINCT MIN(CUST.I_CUST)   
     FROM SFDC_STAGING.STG_IND_SALI_CUST CUST,
          SFDC_STAGING.STG_IND_SALI_ADDR ADDR,
          SFDC_STAGING.STG_IND_SALI_EMAIL EMAL,
          SFDC_STAGING.STG_IND_SALI_GRUP GRUP
     WHERE ACCT.I_CUST = CUST.I_CUST
       AND CUST.I_CURR_GRUP = GRUP.I_GRUP
       AND ADDR.I_GRUP = GRUP.I_GRUP
       AND CUST.I_CUST = EMAL.I_CUST(+)
       AND  CUST.C_STAT = 'A'                                                     --ONLY GET ACTIVE CUSTOMERS
       AND  ADDR.C_STAT = 'A'                                                     --WITH A VALID ADDRESS
       AND (EMAL.C_STAT = 'A' OR EMAL.C_STAT IS NULL)                             --AND ONLY IF THE EMAIL STATUS NOT INACTIVE
       AND  GRUP.I_GRUP IN
             (SELECT DISTINCT(GRAG.I_GRUP)
                FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG,
                     SFDC_STAGING.STG_IND_SALI_GRUP GRUP
               WHERE GRAG.I_GRUP = GRUP.I_GRUP 
                 AND GRAG.D_RELEASE IS NOT NULL                                   --ONLY GET RECS WITH NO OWNERSHIP
                 AND GRAG.D_ESTABLISH IN
                    (SELECT DISTINCT MAX(GRAG.D_ESTABLISH)                        --GET REC WITH MOST RECENT ESTABLISHED DT
                       FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG,                  
                            SFDC_STAGING.STG_IND_SALI_GRUP GRUP
                      WHERE GRAG.I_GRUP = GRUP.I_GRUP
                        AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'         --WHERE DATE GREATER THAN THIS DATE
                        AND GRAG.I_GRUP = 8722127                                 --FOR THIS SPECIFIC GROUP
                     GROUP BY GRAG.I_GRUP
                    )
                 AND GRAG.I_GRUP = GRUP.I_GRUP
                 AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831'                --USE DATE HERE TOO OR ELSE IT WON'T WORK
   )
 GROUP BY GRUP.I_GRUP
)


Table definitions are here:
SFDC_STAGING.STG_IND_SALI_CUST CUST
i_cust INTEGER 10
i_curr_grup INTEGER 10
i_ssn VARCHAR2(9)
d_crea TIMESTAMP(6)
d_updt TIMESTAMP(6)
c_stat VARCHAR2(1)
n_prefix VARCHAR2(4)
n_first VARCHAR2(15)
n_mi VARCHAR2(1)
n_last VARCHAR2(24)

SFDC_STAGING.STG_IND_SALI_ADDR ADDR
i_addr INTEGER 10
i_grup INTEGER 10
d_crea DATE
d_updt DATE
c_stat VARCHAR2(1)
t_type VARCHAR2(10)
t_addr_1 VARCHAR2(30)
t_addr_2 VARCHAR2(30)
t_city VARCHAR2(24)
t_state VARCHAR2(2)
i_zipc VARCHAR2(11)
c_county VARCHAR2(30)
c_county_code VARCHAR2(5)

SFDC_STAGING.STG_IND_SALI_EMAIL EMAL
i_email INTEGER 10
i_cust INTEGER 10
t_e_mail VARCHAR 120
c_stat VARCHAR2(1)
d_crea DATE
d_updt TIMESTAMP(6)

SFDC_STAGING.STG_IND_SALI_GRUP GRUP
i_grup INTEGER 10
d_crea DATE
d_updt TIMESTAMP(6)
c_stat VARCHAR2(1)
c_ownr_type VARCHAR2(1)
c_market_org VARCHAR2(12)
t_suppress VARCHAR2(10)
t_language VARCHAR2(2)

SFDC_STAGING.STG_IND_SALI_GRAG GRAG
i_grup INTEGER 10
i_agnt VARCHAR2(12)
d_crea DATE
d_updt DATE
d_establish DATE
d_release DATE
d_sked_release DATE

SFDC_STAGING.STG_IND_SALI_PERSON_ACCNT ACCT
I_CUST	INTEGER		
N_PREFIX VARCHAR2(10)
N_FIRST	VARCHAR2(20)
N_LAST	VARCHAR2(30)
T_EVENING_PHONE	VARCHAR2(20)
T_DAY_PHONE VARCHAR2(20)
D_BIRTH DATE
C_GEND	CHAR (1)
P_ADDR_1 VARCHAR2(30)
P_ADDR_2 VARCHAR2(30)
P_CITY VARCHAR2(30)
P_STATE	VARCHAR2(2)
P_ZIPC VARCHAR2 (15)

Please help. I think I have provided everything that is requested.

*BlackSwan added {code} tags. Please do so yourself in the future.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

[Updated on: Sun, 13 September 2015 17:23] by Moderator