Home » RDBMS Server » Performance Tuning » Used Left Join - Query goes for Full Table scan (Oracle 11.2)
Used Left Join - Query goes for Full Table scan [message #669956] Fri, 25 May 2018 11:57 Go to next message
saravanantgu
Messages: 3
Registered: May 2018
Junior Member
SELECT DISTINCT
TASK.RDB_POOLING_ID,TASK.FTN_NBR,TASK.M5_ORDR_ID,TASK.CUST_NME,TASK.HI_ID,TASK.H6_ID,TASK.NUA_ADR,TASK.SPA_ETH_CD,TASK.ORDR_TYPE_NME, TASK.PROD_TYPE_NME,TASK.ACTY_TYPE_NME,TASK.SCNRO_NME,
CASE WHEN TASK.TASK_CRET_DT IS NOT NULL THEN TO_CHAR(TASK.TASK_CRET_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END ORDR_RECV_DT,
CASE WHEN ORDR.CUST_CMMT_DT IS NOT NULL THEN TO_CHAR(ORDR.CUST_CMMT_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END CUST_WANT_DT,TASK.DVSTY_NME,TASK.VNDR_NME,TASK.QUEUE_NME,TASK.TASK_NME,TASK.TASK_ID,
CASE WHEN TASK.TASK_CRET_DT IS NOT NULL THEN TO_CHAR(TASK.TASK_CRET_DT+1/24,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END TASK_CRET_DT,
TASK.INITL_ASN_TO_NME,TASK.ASN_TO_NME,
CASE WHEN TASK.REASN_DT IS NOT NULL THEN TO_CHAR(TASK.REASN_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END REAS_DT,
CASE WHEN TASK.EST_CMPLT_DT IS NOT NULL THEN TO_CHAR(TASK.EST_CMPLT_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END EST_CMPLT_DT,
round((select new_time( sysdate, 'EST', 'GMT') from dual)- TASK_CRET_DT,0)SLA_NBR,
TASK.TASK_STUS_NME,TASK.TASK_CMPLT_DT,TASK.PORT_SPED_NME,TASK.SCA_NBR,TASK.REAS_CD,TASK.CMNT_TXT,TASK.REQ_TYPE_NME,TASK.ACCS_IMPLMTN_ GRP_NME,
CASE WHEN TASK.CREAT_DT IS NOT NULL THEN TO_CHAR(TASK.CREAT_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END CREAT_DT,
CASE WHEN TASK.MOD_DT IS NOT NULL THEN TO_CHAR(TASK.MOD_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END MOD_DT,TASK.L2_TAG_NME,TASK.IPV4_ADR,TASK.IPV6_ADR,TASK.CXR_NME,TASK.RSRC_NME,
TASK.FMS_CKT_ID,TASK.DD_NBR,TASK.NRM_SO_NME,TASK.NRM_SO_OBJ_ID,NVL(ORDR.PRE_QUAL_NBR,NRM_ORDR.PREQUAL_LINE_ITEM_ID)PRE_QUAL_NBR,
(select DSGN_CNTL_FOLDR_LOC_NME from DD_INTRO where DD_NBR = TASK.DD_NBR AND ROWNUM =1) DSGN_CNTL_FOLDR_LOC_NME,
TASK.TASK_CRET_DT RECV_DT,ORDR.CUST_CMMT_DT TASK_CUST_WANT_DT,TASK.TASK_CRET_DT TASK_CREAT_DT,TASK.REASN_DT TASK_REAS_DT,TASK.EST_CMPLT_DT TASK_EST_CMPLT_DT,TASK.MOD_DT TASK_MOD_DT,SITE.RGN_NME,SITE.LOC_NME,SITE.SITE_ID,
SITE.TOC_NME,TASK.VRF_NME VRF_NME,NULL VRF_ID,NVL(TASK.VLAN_ID,TASK.DLCI_ID)VLAN_ID,TASK.M5_ORDR_TYPE_CD,TASK.M5_ORDR_SUB_TYPE_CD,TASK.RELTD_M5_ORDR_ID,TASK.RELTD_M5_ORDR_N BR,TASK.ENT_TYPE_NME,TASK.TASK_PRCS_ID,TASK.EXP_CD,
TASK.EXP_DT,TASK.EXP_USER_NME,CUST_ACCT.CSG_LVL CSG_LVL_CD,
NVL((SELECT case when SCA_NBR IS NOT NULL then 'Yes' else 'No'end
FROM BPMF_NRM_ORDR WHERE FTN_NBR = TASK.FTN_NBR AND SRVC_CAT_NME = 'IP Services' and rownum =1)
,(SELECT CASE WHEN APRV_SCA_NME IS NOT NULL THEN 'Yes' else 'No' end
from DD_OPPORTUNITY where dd_nbr = TASK.dd_nbr and rownum =1))SCA_FLG_CD,
NRM_ORDR.SCA_CD,
CASE WHEN NRM_ORDR.PROD_ID like '%Ethernet%' THEN 'Yes' ELSE 'No' END SPA_CD,
CUST_ACCT.CITY CITY_NME,CUST_ACCT.STATE_PROV STATE_PRVN_CD,CUST_ACCT.CTRY_CD,NRM_ORDR.ETHRNT_ACCS_TYPE_NME PRICE_TYPE_NME,TASK.TASK_ASN_DT,
CASE WHEN TASK.TASK_ASN_DT IS NOT NULL THEN TO_CHAR(TASK.TASK_ASN_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END TASK_ASN_DT_TXT,
NRM_ORDR.ASYMET_CD,NRM_ORDR.ETHRNT_MONTR_CD,NRM_ORDR.SYMET_SPD_NME,NRM_ORDR.ASYMET_SPD_NME,NULL EIGRP_FLAG_CD,NRM_ORDR.EXP_CD M5_EXP_CD,TASK.RFS_ORDR_ID
FROM DD_RDB_POOLING_TASK_DETL TASK
LEFT JOIN BPMF_NRM_ORDR NRM_ORDR ON NRM_ORDR.FTN_NBR = TASK.FTN_NBR
LEFT JOIN M5_ORDR ORDR ON ORDR.ORDER_NBR = TASK.FTN_NBR
LEFT JOIN (SELECT PQ.PREQUAL_LINE_ITEM_ID,INTL.RGN_NME,INTL.LOC_NME,INTL.SITE_ID,INTL.TOC_NME
FROM PREQUAL_PRS_DATA PQ,INTL_SITE_LOC INTL
WHERE INTL.site_id = trim(substr(PQ.accs_city_site_nme,1,5))
)SITE on SITE.PREQUAL_LINE_ITEM_ID = ORDR.PRE_QUAL_NBR
LEFT JOIN M5_CUST_ACCT CUST_ACCT ON CUST_ACCT.CUST_ACCT_ID = ORDR.PRNT_ACCT_ID
WHERE (TASK.FTN_NBR IS NOT NULL OR TASK.NUA_ADR IS NOT NULL);

The query goes for FULL Table scan on those LEFT Join, I attached the Explain plan. Any suggestions are most welcome.
Re: Used Left Join - Query goes for Full Table scan [message #669957 is a reply to message #669956] Fri, 25 May 2018 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

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
Re: Used Left Join - Query goes for Full Table scan [message #669958 is a reply to message #669957] Fri, 25 May 2018 12:09 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
SELECT DISTINCT 
TASK.rdb_pooling_id, 
TASK.ftn_nbr, 
TASK.m5_ordr_id, 
TASK.cust_nme, 
TASK.hi_id, 
TASK.h6_id, 
TASK.nua_adr, 
TASK.spa_eth_cd, 
TASK.ordr_type_nme, 
TASK.prod_type_nme, 
TASK.acty_type_nme, 
TASK.scnro_nme, 
CASE 
  WHEN TASK.task_cret_dt IS NOT NULL THEN 
  To_char(TASK.task_cret_dt, 'mm/dd/yyyy hh24:mi:ss') 
  ||' EST' 
  ELSE '' 
END 
ORDR_RECV_DT, 
CASE 
  WHEN ORDR.cust_cmmt_dt IS NOT NULL THEN 
  To_char(ORDR.cust_cmmt_dt, 'mm/dd/yyyy hh24:mi:ss') 
  ||' EST' 
  ELSE '' 
END 
CUST_WANT_DT, 
TASK.dvsty_nme, 
TASK.vndr_nme, 
TASK.queue_nme, 
TASK.task_nme, 
TASK.task_id, 
CASE 
  WHEN TASK.task_cret_dt IS NOT NULL THEN 
  To_char(TASK.task_cret_dt + 1 / 24, 'mm/dd/yyyy hh24:mi:ss') 
  ||' EST' 
  ELSE '' 
END 
TASK_CRET_DT, 
TASK.initl_asn_to_nme, 
TASK.asn_to_nme, 
CASE 
  WHEN TASK.reasn_dt IS NOT NULL THEN 
  To_char(TASK.reasn_dt, 'mm/dd/yyyy hh24:mi:ss') 
  ||' EST' 
  ELSE '' 
END                                                  REAS_DT, 
CASE 
  WHEN TASK.est_cmplt_dt IS NOT NULL THEN 
  To_char(TASK.est_cmplt_dt, 'mm/dd/yyyy hh24:mi:ss') 
  ||' EST' 
  ELSE '' 
END 
EST_CMPLT_DT, 
Round((SELECT New_time(SYSDATE, 'EST', 'GMT') 
       FROM   dual) - task_cret_dt, 0)               SLA_NBR, 
TASK.task_stus_nme, 
TASK.task_cmplt_dt, 
TASK.port_sped_nme, 
TASK.sca_nbr, 
TASK.reas_cd, 
TASK.cmnt_txt, 
TASK.req_type_nme, 
TASK.accs_implmtn_                                   GRP_NME, 
CASE 
  WHEN TASK.creat_dt IS NOT NULL THEN 
  To_char(TASK.creat_dt, 'mm/dd/yyyy hh24:mi:ss') 
  ||' EST' 
  ELSE '' 
END                                                  CREAT_DT, 
CASE 
  WHEN TASK.mod_dt IS NOT NULL THEN 
  To_char(TASK.mod_dt, 'mm/dd/yyyy hh24:mi:ss') 
  ||' EST' 
  ELSE '' 
END                                                  MOD_DT, 
TASK.l2_tag_nme, 
TASK.ipv4_adr, 
TASK.ipv6_adr, 
TASK.cxr_nme, 
TASK.rsrc_nme, 
TASK.fms_ckt_id, 
TASK.dd_nbr, 
TASK.nrm_so_nme, 
TASK.nrm_so_obj_id, 
Nvl(ORDR.pre_qual_nbr, NRM_ORDR.prequal_line_item_id)PRE_QUAL_NBR, 
(SELECT dsgn_cntl_foldr_loc_nme 
 FROM   dd_intro 
 WHERE  dd_nbr = TASK.dd_nbr 
        AND ROWNUM = 1)                              DSGN_CNTL_FOLDR_LOC_NME, 
TASK.task_cret_dt                                    RECV_DT, 
ORDR.cust_cmmt_dt                                    TASK_CUST_WANT_DT, 
TASK.task_cret_dt                                    TASK_CREAT_DT, 
TASK.reasn_dt                                        TASK_REAS_DT, 
TASK.est_cmplt_dt                                    TASK_EST_CMPLT_DT, 
TASK.mod_dt                                          TASK_MOD_DT, 
SITE.rgn_nme, 
SITE.loc_nme, 
SITE.site_id, 
SITE.toc_nme, 
TASK.vrf_nme                                         VRF_NME, 
NULL                                                 VRF_ID, 
Nvl(TASK.vlan_id, TASK.dlci_id)                      VLAN_ID, 
TASK.m5_ordr_type_cd, 
TASK.m5_ordr_sub_type_cd, 
TASK.reltd_m5_ordr_id, 
TASK.reltd_m5_ordr_n                                 BR, 
TASK.ent_type_nme, 
TASK.task_prcs_id, 
TASK.exp_cd, 
TASK.exp_dt, 
TASK.exp_user_nme, 
CUST_ACCT.csg_lvl                                    CSG_LVL_CD, 
Nvl((SELECT CASE 
              WHEN sca_nbr IS NOT NULL THEN 'Yes' 
              ELSE 'No' 
            END 
     FROM   bpmf_nrm_ordr 
     WHERE  ftn_nbr = TASK.ftn_nbr 
            AND srvc_cat_nme = 'IP Services' 
            AND ROWNUM = 1), (SELECT CASE 
                                       WHEN aprv_sca_nme IS NOT NULL THEN 'Yes' 
                                       ELSE 'No' 
                                     END 
                              FROM   dd_opportunity 
                              WHERE  dd_nbr = TASK.dd_nbr 
                                     AND ROWNUM = 1))SCA_FLG_CD, 
NRM_ORDR.sca_cd, 
CASE 
  WHEN NRM_ORDR.prod_id LIKE '%Ethernet%' THEN 'Yes' 
  ELSE 'No' 
END                                                  SPA_CD, 
CUST_ACCT.city                                       CITY_NME, 
CUST_ACCT.state_prov                                 STATE_PRVN_CD, 
CUST_ACCT.ctry_cd, 
NRM_ORDR.ethrnt_accs_type_nme                        PRICE_TYPE_NME, 
TASK.task_asn_dt, 
CASE 
  WHEN TASK.task_asn_dt IS NOT NULL THEN 
  To_char(TASK.task_asn_dt, 'mm/dd/yyyy hh24:mi:ss') 
  ||' EST' 
  ELSE '' 
END                                                  TASK_ASN_DT_TXT, 
NRM_ORDR.asymet_cd, 
NRM_ORDR.ethrnt_montr_cd, 
NRM_ORDR.symet_spd_nme, 
NRM_ORDR.asymet_spd_nme, 
NULL                                                 EIGRP_FLAG_CD, 
NRM_ORDR.exp_cd                                      M5_EXP_CD, 
TASK.rfs_ordr_id 
FROM   dd_rdb_pooling_task_detl TASK 
       left join bpmf_nrm_ordr NRM_ORDR 
              ON NRM_ORDR.ftn_nbr = TASK.ftn_nbr 
       left join m5_ordr ORDR 
              ON ORDR.order_nbr = TASK.ftn_nbr 
       left join (SELECT PQ.prequal_line_item_id, 
                         INTL.rgn_nme, 
                         INTL.loc_nme, 
                         INTL.site_id, 
                         INTL.toc_nme 
                  FROM   prequal_prs_data PQ, 
                         intl_site_loc INTL 
                  WHERE  INTL.site_id = 
                         Trim(Substr(PQ.accs_city_site_nme, 1, 5)))SITE 
              ON SITE.prequal_line_item_id = ORDR.pre_qual_nbr 
       left join m5_cust_acct CUST_ACCT 
              ON CUST_ACCT.cust_acct_id = ORDR.prnt_acct_id 
WHERE  ( TASK.ftn_nbr IS NOT NULL 
          OR TASK.nua_adr IS NOT NULL ); 
Re: Used Left Join - Query goes for Full Table scan [message #669959 is a reply to message #669958] Fri, 25 May 2018 12:48 Go to previous messageGo to next message
saravanantgu
Messages: 3
Registered: May 2018
Junior Member
I have attached the explain plan in JPG form.

We ran the SQL Tune Advisor, no recommendation.

In BPMF_NRM_ORDR - We have a 'NONUNQUIE' index, but DD_RDB_POOLING_TASK_DETL doesn't have Index on 'TASK.FTN_NBR'. Same is applicable for remaining as well - it doesn't have the index on those columns we mapped.

Any more details required, let me know. Thanks in Advance.
  • Attachment: 2.JPG
    (Size: 92.79KB, Downloaded 941 times)
Re: Used Left Join - Query goes for Full Table scan [message #669961 is a reply to message #669959] Sat, 26 May 2018 02:08 Go to previous messageGo to next message
John Watson
Messages: 8077
Registered: January 2010
Location: Global Village
Senior Member
To display the plan is way that is complete and readable, please use SQL*Plus and copy/paste the result. Like this:
C:\Users\john>
C:\Users\john>sqlplus scott/tiger

SQL*Plus: Release 12.2.0.1.0 Production on Sat May 26 08:06:23 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sat May 26 2018 08:05:58 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

orclx> explain plan for
  2  select * from emp where deptno=10;

Explained.

orclx> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |   117 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     3 |   117 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=10)

13 rows selected.

orclx>

Be sure to enclose the the text in [code] tags, as described here How to use code tags and make your code easier to read
Re: Used Left Join - Query goes for Full Table scan [message #670011 is a reply to message #669961] Tue, 29 May 2018 09:14 Go to previous messageGo to next message
saravanantgu
Messages: 3
Registered: May 2018
Junior Member
Here is the explain plan...
SQL> select * from table(dbms_xplan.display);
Plan hash value: 3253446784

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                          | 88769 |    34M|       | 16792   (1)| 00:03:22 |
|   1 |  FAST DUAL                    |                          |     1 |       |       |     2   (0)| 00:00:01 |
|*  2 |  COUNT STOPKEY                |                          |       |       |       |            |          |
|   3 |   TABLE ACCESS BY INDEX ROWID | DD_INTRO                 |     1 |   118 |       |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN          | PK_DD_INTRO              |     1 |       |       |     1   (0)| 00:00:01 |
|*  5 |  COUNT STOPKEY                |                          |       |       |       |            |          |
|*  6 |   TABLE ACCESS BY INDEX ROWID | BPMF_NRM_ORDR            |     1 |    30 |       |     1   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN           | IX02_BPMF_NRM_ORDR       |     1 |       |       |     1   (0)| 00:00:01 |
|*  8 |   COUNT STOPKEY               |                          |       |       |       |            |          |
|   9 |    TABLE ACCESS BY INDEX ROWID| DD_OPPORTUNITY           |     1 |   138 |       |     1   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN         | PK_DD_OPPORTUNITY        |     1 |       |       |     1   (0)| 00:00:01 |
|  11 |  HASH UNIQUE                  |                          | 88769 |    34M|    36M| 16792   (1)| 00:03:22 |
|* 12 |   HASH JOIN RIGHT OUTER       |                          | 88769 |    34M|       |  9055   (1)| 00:01:49 |
|  13 |    VIEW                       |                          | 18156 |   673K|       |   182   (1)| 00:00:03 |
|* 14 |     HASH JOIN                 |                          | 18156 |   762K|       |   182   (1)| 00:00:03 |
|  15 |      TABLE ACCESS FULL        | INTL_SITE_LOC            |    88 |  2376 |       |     3   (0)| 00:00:01 |
|  16 |      TABLE ACCESS FULL        | PREQUAL_PRS_DATA         | 18156 |   283K|       |   179   (1)| 00:00:03 |
|* 17 |    HASH JOIN RIGHT OUTER      |                          | 88769 |    31M|  4288K|  8871   (1)| 00:01:47 |
|  18 |     TABLE ACCESS FULL         | BPMF_NRM_ORDR            | 64556 |  3530K|       |  2887   (1)| 00:00:35 |
|* 19 |     HASH JOIN RIGHT OUTER     |                          | 86339 |    26M|  2160K|  4436   (1)| 00:00:54 |
|  20 |      TABLE ACCESS FULL        | M5_CUST_ACCT             | 59745 |  1458K|       |   513   (1)| 00:00:07 |
|* 21 |      HASH JOIN RIGHT OUTER    |                          | 86339 |    23M|  2936K|  2579   (1)| 00:00:31 |
|  22 |       TABLE ACCESS FULL       | M5_ORDR                  | 69872 |  2115K|       |   395   (2)| 00:00:05 |
|* 23 |       TABLE ACCESS FULL       | DD_RDB_POOLING_TASK_DETL | 86339 |    21M|       |   929   (1)| 00:00:12 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1)
   4 - access("DD_NBR"=:B1)
   5 - filter(ROWNUM=1)
   6 - filter("SRVC_CAT_NME"='IP Services')
   7 - access("FTN_NBR"=:B1)
   8 - filter(ROWNUM=1)
  10 - access("DD_NBR"=:B1)
  12 - access("SITE"."PREQUAL_LINE_ITEM_ID"(+)="ORDR"."PRE_QUAL_NBR")
  14 - access("INTL"."SITE_ID"=TRIM(SUBSTR("PQ"."ACCS_CITY_SITE_NME",1,5)))
  17 - access("NRM_ORDR"."FTN_NBR"(+)="TASK"."FTN_NBR")
  19 - access("CUST_ACCT"."CUST_ACCT_ID"(+)="ORDR"."PRNT_ACCT_ID")
  21 - access("ORDR"."ORDER_NBR"(+)="TASK"."FTN_NBR")
  23 - filter("TASK"."FTN_NBR" IS NOT NULL OR "TASK"."NUA_ADR" IS NOT NULL)

47 rows selected.
*BlackSwan added{code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read

[Updated on: Tue, 29 May 2018 09:25] by Moderator

Report message to a moderator

Re: Used Left Join - Query goes for Full Table scan [message #670251 is a reply to message #670011] Wed, 20 June 2018 02:29 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Optimizer chooses the best possible plan:
1. The statement applies only "( TASK.ftn_nbr IS NOT NULL OR TASK.nua_adr IS NOT NULL )" conditions for the "base" table, so optimizer decides to perform FULL table scan on it. According to optimizer stats it estimates that 86339 rows will be retrieved.
2. Instead of using NESTED LOOP join and accessing each outer-joined table 86339 times using index it correctly decides to use HASH join and to scan the whole table once

Michael
Re: Used Left Join - Query goes for Full Table scan [message #670253 is a reply to message #670011] Wed, 20 June 2018 02:47 Go to previous message
John Watson
Messages: 8077
Registered: January 2010
Location: Global Village
Senior Member
Nearly half the cost and time estimate is operation 11, the HASH UNIQUE. This is because you are using DISTINCT. However, the optimizer does not think that the DISTINCT will remove any rows: the estimated row counts are the same before and after. Do you you actually need it? Does it remove any rows? If there are any duplicates, is there a cheaper way of removing them?
Previous Topic: Wrong plan chosen by optimizer
Next Topic: join is slow
Goto Forum:
  


Current Time: Wed Dec 11 00:04:35 CST 2019