Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Query takes more time when number of IN List value increases

Query takes more time when number of IN List value increases

From: Krishnaswamy, Ranganath <Ranganath.Krishnaswamy_at_blr.hpsglobal.com>
Date: Mon, 03 Feb 2003 01:58:59 -0800
Message-ID: <F001.00541D58.20030203015859@fatcity.com> 





Hi Listers,



I have the below query which takes 36 sec. to execute:



SELECT /*+ordered*/


   S.HOUSEBILL,


   OH.ORDER_REF_NO,
   V.CONTAINER_NUM,
   S.AEI_REFERENCE,
   CE1.SEQUENCE,




   TL1.COMPONENT_VALUE,



   TL2.COMPONENT_VALUE,(


      SELECT
         to_char(SCT1.TIMESTAMP,


         'YYYYMMDDHH24MISS')

      FROM
         SHIPMENT_CONTAINER_TIMESTAMP SCT1
      WHERE
         SCT1.EVENT_ID = XM1.EVENT_ID
         AND SCT1.SHIPMENT_ID = S.SHIPMENT_ID
         AND SCT1.CONTAINER_ID = v.CONTAINER_ID
         AND XM1.TABLE_NAME = 'SHIPMENT_CONTAINER_TIMESTAMP'
      UNION
      SELECT
         to_char(ST1.TIMESTAMP,


         'YYYYMMDDHH24MISS')

      FROM
         SHIPMENT_TIMESTAMP ST1
      WHERE
         ST1.EVENT_ID = XM1.ALT_EVENT_ID
         AND ST1.SHIPMENT_ID = S.SHIPMENT_ID
         AND XM1.ALT_EVENT_ID IS NOT NULL
         AND XM1.TABLE_NAME ='SHIPMENT_CONTAINER_TIMESTAMP'
         AND NOT EXISTS (
            SELECT
               SCT1.TIMESTAMP
            FROM
               SHIPMENT_CONTAINER_TIMESTAMP SCT1
            WHERE
               SCT1.EVENT_ID = XM1.EVENT_ID
               AND SCT1.SHIPMENT_ID = S.SHIPMENT_ID
               AND SCT1.CONTAINER_ID = v.CONTAINER_ID
               AND XM1.TABLE_NAME ='SHIPMENT_CONTAINER_TIMESTAMP'
         )
      UNION
      SELECT
         to_char(ST1.TIMESTAMP,


         'YYYYMMDDHH24MISS')

      FROM
         SHIPMENT_TIMESTAMP ST1
      WHERE
         XM1.TABLE_NAME = 'SHIPMENT_TIMESTAMP'
         AND ST1.SHIPMENT_ID = S.SHIPMENT_ID
         AND ST1.EVENT_ID = XM1.EVENT_ID
      UNION
      SELECT
         to_char(OT1.TIMESTAMP,


         'YYYYMMDDHH24MISS')

      FROM
         ORDER_TIMESTAMP OT1
      WHERE
         OT1.EVENT_ID = XM1.EVENT_ID
         AND OT1.SHIPMENT_ORDER_ID = SO.SHIPMENT_ORDER_ID
         AND XM1.TABLE_NAME = 'ORDER_TIMESTAMP'


   ) AS FROM_DATE,



   S.SHIPMENT_ID,



   OH.ORDER_ID,



   V.CONTAINER_ID



FROM



   SHIPMENT S,



   V_SHIP V,



   SHIPMENT_CUSTOMER SC,



   ORDER_HEADER OH,



   SHIPMENT_ORDER SO,



   TRAFFIC_LANE TL1,



   TRAFFIC_LANE TL2,



   CUSTOMER_EVENT CE1,



   XM_FIELD XM1



WHERE 



   (SC.CUSTOMER_MOT_ID = 1040130842248


   AND SC.SHIPMENT_ID = S.SHIPMENT_ID
   AND trunc(S.MSG_CREATION_DATE) >= trunc(sysdate - 90)
   AND trunc(S.MSG_CREATION_DATE) <= trunc(sysdate)


   AND S.SHIPMENT_ID = V.SHIPMENT_ID



   AND V.SHIPMENT_ID = SO.SHIPMENT_ID(+)

   AND SO.ORDER_ID = OH.ORDER_ID(+))



   AND (TL1.TRAFFIC_LANE_NO = TL2.TRAFFIC_LANE_NO


   AND TL1.LANE_ID = 10413530577392 
   and TL2.LANE_ID = 10413530577393
   AND TL2.COMPONENT_VALUE = S.ORG_STATION_CODE
   AND TL1.COMPONENT_VALUE = S.DEST_STATION_CODE)
   AND CE1.CUSTOMER_MOT_ID = 1040130842248
   and XM1.FIELD_ID = CE1.FIELD_ID
   AND XM1.table_name in ('SHIPMENT_TIMESTAMP')


ORDER BY



   S.SHIPMENT_ID,



   ORDER_ID,



   CONTAINER_ID,



   CE1.SEQUENCE


Here is the explain plan for the query:


Operation	Object Name	Rows	Bytes	Cost	TQ	In/Out
PStart	PStop

SELECT STATEMENT Hint=CHOOSE		349  	 	23589

  SORT ORDER BY		349  	103 K	23589

    HASH JOIN		349  	103 K	23561

      TABLE ACCESS BY INDEX ROWID	XM_FIELD	6  	348  	2

        INDEX RANGE SCAN	XM_FIELD_TABLE_NAME	6  	 	1

      MERGE JOIN CARTESIAN		5 K	1 M	23537

        NESTED LOOPS		182  	41 K	23355

          HASH JOIN		364 K	67 M	23355

            TABLE ACCESS BY INDEX ROWID	TRAFFIC_LANE	5  	190  	1

              INDEX RANGE SCAN	TL_LANE_ID_VALUE	5  	 	2

            HASH JOIN OUTER		7 M	1G	6630

              HASH JOIN OUTER		197 K	27 M	2918

                HASH JOIN		197 K	20 M	1096

                  TABLE ACCESS BY INDEX ROWID	SHIPMENT_CUSTOMER	39
1014  	2  	 	      	             	 
                    INDEX RANGE SCAN	SHIP_CUS_CMOT_ID	39
1  	 	      	             	 
                  HASH JOIN		506 K	39 M	450

                    TABLE ACCESS BY INDEX ROWID	SHIPMENT	102  	4 K
6  	 	      	             	 
                      INDEX RANGE SCAN	SHIP_MSG_CREATION_DT	102
2  	 	      	             	 
                    VIEW	V_SHIP	496 K	16 M	124

                      HASH JOIN OUTER		496 K	22 M	124

                        INDEX FAST FULL SCAN	SHP_SHP_ID_DEST_COUNTRY	40 K
514 K	4  	 	      	             	 
                        TABLE ACCESS FULL	SHIPMENT_CONTAINER	1 K
41 K	3  	 	      	             	 
                TABLE ACCESS FULL	SHIPMENT_ORDER	82  	3 K	1

              TABLE ACCESS FULL	ORDER_HEADER	3 K	36 K	7

          INDEX UNIQUE SCAN	XPKTRAFFIC_LANE	5  	190

        SORT JOIN		32  	416  	23537

          TABLE ACCESS BY INDEX ROWID	CUSTOMER_EVENT	32  	416  	1

            INDEX RANGE SCAN	CUST_EVENT_CMOT_ID	32






But when the query is changed to below query it takes 20 min. to execute:



SELECT /*+ordered*/


   S.HOUSEBILL,


   OH.ORDER_REF_NO,
   V.CONTAINER_NUM,
   S.AEI_REFERENCE,
   CE1.SEQUENCE,




   TL1.COMPONENT_VALUE,



   TL2.COMPONENT_VALUE,(


      SELECT
         to_char(SCT1.TIMESTAMP,


         'YYYYMMDDHH24MISS')

      FROM
         SHIPMENT_CONTAINER_TIMESTAMP SCT1
      WHERE
         SCT1.EVENT_ID = XM1.EVENT_ID
         AND SCT1.SHIPMENT_ID = S.SHIPMENT_ID
         AND SCT1.CONTAINER_ID = v.CONTAINER_ID
         AND XM1.TABLE_NAME = 'SHIPMENT_CONTAINER_TIMESTAMP'
      UNION
      SELECT
         to_char(ST1.TIMESTAMP,


         'YYYYMMDDHH24MISS')

      FROM
         SHIPMENT_TIMESTAMP ST1
      WHERE
         ST1.EVENT_ID = XM1.ALT_EVENT_ID
         AND ST1.SHIPMENT_ID = S.SHIPMENT_ID
         AND XM1.ALT_EVENT_ID IS NOT NULL
         AND XM1.TABLE_NAME ='SHIPMENT_CONTAINER_TIMESTAMP'
         AND NOT EXISTS (
            SELECT
               SCT1.TIMESTAMP
            FROM
               SHIPMENT_CONTAINER_TIMESTAMP SCT1
            WHERE
               SCT1.EVENT_ID = XM1.EVENT_ID
               AND SCT1.SHIPMENT_ID = S.SHIPMENT_ID
               AND SCT1.CONTAINER_ID = v.CONTAINER_ID
               AND XM1.TABLE_NAME ='SHIPMENT_CONTAINER_TIMESTAMP'
         )
      UNION
      SELECT
         to_char(ST1.TIMESTAMP,


         'YYYYMMDDHH24MISS')

      FROM
         SHIPMENT_TIMESTAMP ST1
      WHERE
         XM1.TABLE_NAME = 'SHIPMENT_TIMESTAMP'
         AND ST1.SHIPMENT_ID = S.SHIPMENT_ID
         AND ST1.EVENT_ID = XM1.EVENT_ID
      UNION
      SELECT
         to_char(OT1.TIMESTAMP,


         'YYYYMMDDHH24MISS')

      FROM
         ORDER_TIMESTAMP OT1
      WHERE
         OT1.EVENT_ID = XM1.EVENT_ID
         AND OT1.SHIPMENT_ORDER_ID = SO.SHIPMENT_ORDER_ID
         AND XM1.TABLE_NAME = 'ORDER_TIMESTAMP'


   ) AS FROM_DATE,



   S.SHIPMENT_ID,



   OH.ORDER_ID,



   V.CONTAINER_ID



FROM



   SHIPMENT S,



   V_SHIP V,



   SHIPMENT_CUSTOMER SC,



   ORDER_HEADER OH,



   SHIPMENT_ORDER SO,



   TRAFFIC_LANE TL1,



   TRAFFIC_LANE TL2,



   CUSTOMER_EVENT CE1,



   XM_FIELD XM1



WHERE 



   (SC.CUSTOMER_MOT_ID = 1040130842248


   AND SC.SHIPMENT_ID = S.SHIPMENT_ID
   AND trunc(S.MSG_CREATION_DATE) >= trunc(sysdate - 90)
   AND trunc(S.MSG_CREATION_DATE) <= trunc(sysdate)


   AND S.SHIPMENT_ID = V.SHIPMENT_ID



   AND V.SHIPMENT_ID = SO.SHIPMENT_ID(+)

   AND SO.ORDER_ID = OH.ORDER_ID(+))



   AND (TL1.TRAFFIC_LANE_NO = TL2.TRAFFIC_LANE_NO


   AND TL1.LANE_ID = 10413530577392 
   and TL2.LANE_ID = 10413530577393
   AND TL2.COMPONENT_VALUE = S.ORG_STATION_CODE
   AND TL1.COMPONENT_VALUE = S.DEST_STATION_CODE)
   AND CE1.CUSTOMER_MOT_ID = 1040130842248
   and XM1.FIELD_ID = CE1.FIELD_ID
   AND XM1.table_name in ('SHIPMENT_TIMESTAMP', 'ORDER_TIMESTAMP,


'SHIPMENT_CONTAINER_TIMESTAMP')



ORDER BY



   S.SHIPMENT_ID,



   ORDER_ID,



   CONTAINER_ID,



   CE1.SEQUENCE


Here is the explain plan for the query:


Operation	Object Name	Rows	Bytes	Cost	TQ	In/Out
PStart	PStop

SELECT STATEMENT Hint=CHOOSE		349  	 	23589

  SORT ORDER BY		349  	103 K	23589

    HASH JOIN		349  	103 K	23561

      INLIST ITERATOR

        TABLE ACCESS BY INDEX ROWID	XM_FIELD	6  	348  	2

          INDEX RANGE SCAN	XM_FIELD_TABLE_NAME	6  	 	2

      MERGE JOIN CARTESIAN		5 K	1 M	23537

        NESTED LOOPS		182  	41 K	23355

          HASH JOIN		364 K	67 M	23355

            TABLE ACCESS BY INDEX ROWID	TRAFFIC_LANE	5  	190  	1

              INDEX RANGE SCAN	TL_LANE_ID_VALUE	5  	 	2

            HASH JOIN OUTER		7 M	1G	6630

              HASH JOIN OUTER		197 K	27 M	2918

                HASH JOIN		197 K	20 M	1096

                  TABLE ACCESS BY INDEX ROWID	SHIPMENT_CUSTOMER	39
1014  	2  	 	      	             	 
                    INDEX RANGE SCAN	SHIP_CUS_CMOT_ID	39
1  	 	      	             	 
                  HASH JOIN		506 K	39 M	450

                    TABLE ACCESS BY INDEX ROWID	SHIPMENT	102  	4 K
6  	 	      	             	 
                      INDEX RANGE SCAN	SHIP_MSG_CREATION_DT	102
2  	 	      	             	 
                    VIEW	V_SHIP	496 K	16 M	124

                      HASH JOIN OUTER		496 K	22 M	124

                        INDEX FAST FULL SCAN	SHP_SHP_ID_DEST_COUNTRY	40 K
514 K	4  	 	      	             	 
                        TABLE ACCESS FULL	SHIPMENT_CONTAINER	1 K
41 K	3  	 	      	             	 
                TABLE ACCESS FULL	SHIPMENT_ORDER	82  	3 K	1

              TABLE ACCESS FULL	ORDER_HEADER	3 K	36 K	7

          INDEX UNIQUE SCAN	XPKTRAFFIC_LANE	5  	190

        SORT JOIN		32  	416  	23537

          TABLE ACCESS BY INDEX ROWID	CUSTOMER_EVENT	32  	416  	1

            INDEX RANGE SCAN	CUST_EVENT_CMOT_ID	32






Can anybody let me know as to how do I bring down the response time of the
query if the IN List is hardcoded with more than 1 value?  Any help in this
regard is very much appreciated.



Thanks and Regards,



Ranganath


WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee.  Access to this message
by anyone else is unauthorised.  If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Krishnaswamy, Ranganath
  INET: Ranganath.Krishnaswamy_at_blr.hpsglobal.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Mon Feb 03 2003 - 03:58:59 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US