Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> sql query slow... help me!
Hi, I'm a software developer and I'm making a new software with
Oracle. I made a some sql querys and it's very very slow so I get
complain from the users. Is there any way that get it faster result ?
please help me.
here's the sql query
SELECT '', '0' SELECT_YN, V01.ALLOC_NO, V01.CUSTREQUEST_NO, V01.DIV_NO, V01.CONSOL_NO, DECODE(V01.TR_DT, NULL, '', TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.TR_DT, '', '')) TR_DATE, V01.STATUS, T13.CODE_NAME STATUS_NAME, BL_NO,
V01.CNTR_NO, V01.CARRIER_TRANS_NO,V01.TRUCKER_CD, T17.CARRIER_NAME, V01.SUB_CARRIER, T18.CARRIER_NAME, V01.CAR_CODE,
T20.CAR_NO, V01.PLATE_NO, V01.GPS_MOBILE_NUM GPS_MOBILE_NUM, V01.DRIVER, T21.USER_NAME, V01.MOBILE_NUM CELLULAR, V01.DPTR_AREA_CD, T11.AREA_NAME, V01.COLL_FR,V01.COLL_FR_NM, V01.COLL_FR_DESC, DECODE(V01.LOAD_BOOKING_DT, NULL,
TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.LOAD_BOOKING_DT, V01.LOAD_BOOKING_TM, '')) LOAD_BOOKING_DATETIME,
DECODE(V01.LOAD_ETA_DATE, NULL, '',TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.LOAD_ETA_DATE, V01.LOAD_ETA_TIME, ''))
LOAD_ETA_DATETIME, DECODE(V01.LOAD_ATA_DATE, NULL, '', TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.LOAD_ATA_DATE, V01.LOAD_ATA_TIME, '')) LOAD_ATA_DATETIME,DECODE(V01.LOAD_ATD_DATE, NULL, '', TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.LOAD_ATD_DATE, V01.LOAD_ATD_TIME, '')) LOAD_ATD_DATETIME,
V01.ATD_STOP_REASON, V01.ARR_AREA_CD, T12.AREA_NAME, V01.DELV_TO, V01.DELV_TO_NM, V01.DELV_TO_DESC, DECODE(V01.UNLOAD_BOOKING_DT, NULL, '', TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.UNLOAD_BOOKING_DT, V01.UNLOAD_BOOKING_TM, ''))UNLOAD_BOOKING_DATETIME, DECODE(V01.UNLOAD_ETA_DATE, NULL, '', TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.UNLOAD_ETA_DATE, V01.UNLOAD_ETA_TIME, '')) UNLOAD_ETA_DATETIME,
DECODE(V01.UNLOAD_ATA_DATE, NULL, '',TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.UNLOAD_ATA_DATE, V01.UNLOAD_ATA_TIME,
'')) UNLOAD_ATA_DATETIME,DECODE(V01.UNLOAD_ATU_DATE, NULL, '', TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.UNLOAD_ATU_DATE, V01.UNLOAD_ATU_TIME, '')) UNLOAD_ATD_DATETIME,
V01.ATU_STOP_REASON, PU_ORD_NO, PU_ORD_SEQ, MBL_NO, PO_NO, V01.ISSUE_NO, V01.SEAL_NO, V01.CUST_CD, T15.CUST_NAME, REAL_CUST_NAME, PACK_QTY, UNALLOC_PACK_QTY, UNALLOC_PACK_QTY/* , ALLOC_PACK_QTY*/ , CARGO_WT, UNALLOC_CARGO_WT, UNALLOC_CARGO_WT/* , ALLOC_CARGO_WT*/ , CARGO_CBM, UNALLOC_CARGO_CBM, UNALLOC_CARGO_CBM/* , ALLOC_CARGO_CBM*/ , CARGO_VOL_WT, UNALLOC_CARGO_VOL_WT, UNALLOC_CARGO_VOL_WT/* ,ALLOC_CARGO_VOL_WT*/ , LINE_CD, T16.CUST_NAME LINE_NM, V01.VSL_NM, V01.VOYAGE, V01.POL_CD,
V01.POD_CD, V04.ALLOC_FAMT + V04.ALLOC_VAT_FAMT, V03.TRANS_FAMT + V03.TRANS_VAT_FAMT, V01.TRANS_TON, V01.REMARK, V01.DESCRIPTION, V01.TR_TYPE, V01.PACK_UNIT,V01.ACCOUNT_GB, T23.CODE_NAME/*żżżżż*/ , V01.CAR_TON, T24.TON_DESC/ *żż*/ ,
T24.CAPACITY_CODE, T20.CAR_TYPE, T25.TYPE_NAME,V01.COSTCONFIRM_YN, V01.TRANS_NO, V01.FC_NO, V01.FC_REQUESTED_CARRIER,
V01.FC_CAR_SEQ, TRANS_CNT, ORDER_RANK,V01.MOBILE_NUM2, V01.BOOK_TYPE, DECODE(V01.TRUCKER_ALLOC_DT, NULL,
TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.TRUCKER_ALLOC_DT, V01.TRUCKER_ALLOC_TM, '')) TRUCKER_ALLOC_DATETIME,
V01.ENG_NM, V01.TEL1, V01.EMAIL FROM (SELECT MIN(T01.ALLOC_NO) ALLOC_NO, MIN(T01.DIV_NO)DIV_NO, MIN(T01.TR_TYPE) TR_TYPE, MIN(T01.DPTR_PLAN_DT) LOAD_BOOKING_DT,
MIN(T01.DPTR_PLAN_TM)LOAD_BOOKING_TM, MIN(T01.ARR_PLAN_DT) UNLOAD_BOOKING_DT, MIN(T01.ARR_PLAN_TM)
UNLOAD_BOOKING_TM,MIN(T01.DPTR_AREA_CD) DPTR_AREA_CD, MIN(T01.ARR_AREA_CD) ARR_AREA_CD, MAX(T01.PACK_QTY)
PACK_QTY,MAX(T01.UNALLOC_PACK_QTY) UNALLOC_PACK_QTY, SUM(T01.ALLOC_PACK_QTY) ALLOC_PACK_QTY, SUM(T04.QTY)
LOAD_QTY,MAX(T01.CARGO_WT) CARGO_WT, MAX(T01.UNALLOC_CARGO_WT) UNALLOC_CARGO_WT, SUM(T01.ALLOC_CARGO_WT)
ALLOC_CARGO_WT,MAX(T01.CARGO_CBM) CARGO_CBM, MAX(T01.UNALLOC_CARGO_CBM) UNALLOC_CARGO_CBM,
SUM(T01.ALLOC_CARGO_CBM)ALLOC_CARGO_CBM, MAX(T01.CARGO_VOL_WT) CARGO_VOL_WT, MAX(T01.UNALLOC_CARGO_VOL_WT)
UNALLOC_CARGO_VOL_WT,SUM(T01.ALLOC_CARGO_VOL_WT) ALLOC_CARGO_VOL_WT, MIN(T01.PACK_UNIT) PACK_UNIT,
MIN(T01.PU_REF_NO)PU_REF_NO, MIN(T01.STATUS) STATUS, MIN(T05.CAR_CAPACITY) CAR_TON, MIN(T01.CAR_QTY) CAR_QTY,
MIN(T01.CUST_CD)CUST_CD, MIN(T01.TRUCKER_CD) OR_TRUCKER_CD, MIN(T06.ORDERED_CARRIER) TRUCKER_CD, MIN(T01.TRUCKER_CANCEL_YN) TRUCKER_CANCEL_YN, MIN(T01.TRUCKER_CANCEL_RSN) TRUCKER_CANCEL_RSN,
MIN(T01.CARGO_TYPE)CARGO_TYPE, MIN(T01.DECL_NO) DECL_NO, MIN(T01.BL_NO) BL_NO, MIN(T01.ARR_DT) ARR_DT,
MIN(T01.OB_DT) OB_DT,MIN(T01.TR_DT) TR_DT, MIN(T01.DPTR_BND_AREA) DPTR_BND_AREA, MIN(T01.DPTR_DESC) DPTR_DESC,
MIN(T01.ARR_BND_AREA)ARR_BND_AREA, MIN(T01.ARR_DESC) ARR_DESC, MIN(T01.COLL_FR) COLL_FR, MIN(T01.COLL_FR_NM)
COLL_FR_NM,MIN(T01.COLL_FR_DESC) COLL_FR_DESC, MIN(T01.DELV_TO) DELV_TO, MIN(T01.DELV_TO_NM) DELV_TO_NM,
MIN(T01.DELV_TO_DESC)DELV_TO_DESC, MIN(T01.PU_ORD_NO) PU_ORD_NO, MIN(T01.PU_ORD_SEQ) PU_ORD_SEQ,
MIN(T01.AIR_TR_YN)AIR_TR_YN, MIN(T01.LINE_ORD_NO) LINE_ORD_NO, MIN(T01.BOOKING_NO) BOOKING_NO, MIN(T01.CY_CD)
CY_CD, MIN(T01.LINE_CD)LINE_CD, MIN(T01.FILE_NO) FILE_NO, MIN(T01.PO_NO) PO_NO, MIN(T01.DPTR_PORT_CD) DPTR_PORT_CD,
MIN(T01.CUST_PIC_NM)CUST_PIC_NM, MIN(T01.CUST_PIC_TEL) CUST_PIC_TEL, MIN(T01.FT20_QTY) FT20_QTY, MIN(T01.FT40_QTY)
FT40_QTY,MIN(T01.HBL_DEST) HBL_DEST, MIN(T01.POL_CD) POL_CD, MIN(T01.POD_CD) POD_CD, MIN(T01.MBL_NO) MBL_NO,
MAX(T01.VSL_NM) VSL_NM,MIN(T01.VOYAGE) VOYAGE, MAX(T01.READ_YN) READ_YN, MAX(T01.ARR_CHG_YN) ARR_CHG_YN,
MIN(T01.TOT_CHRG_WT)TOT_CHRG_WT, MIN(T01.ALLOC_PIC_NM) ALLOC_PIC_NM, MIN(T01.ALLOC_PIC_TEL) ALLOC_PIC_TEL, MIN(T01.TRUCKER_ALLOC_DT) TRUCKER_ALLOC_DT, MIN(T01.TRUCKER_ALLOC_TM) TRUCKER_ALLOC_TM, MIN(T01.REAL_CUST_NAME)
REAL_CUST_NAME,MIN(T01.REMARK) REMARK, MIN(T06.CNTR_TYPE) CNTR_TYPE, MIN(T06.CNTR_SIZE) CNTR_SIZE,
MIN(T06.LOAD_ETA_DATE)LOAD_ETA_DATE, MIN(T06.LOAD_ETA_TIME) LOAD_ETA_TIME, MIN(T03.REQUEST_NO) REQUEST_NO,
MIN(T03.SEQ) SEQ,MIN(T03.UNLOAD_ETA_DATE) UNLOAD_ETA_DATE, MIN(T03.UNLOAD_ETA_TIME) UNLOAD_ETA_TIME,
MIN(T05.TRANS_NO)TRANS_NO, MIN(T05.ACCOUNT_GB) ACCOUNT_GB, MIN(T05.TRANS_TON) TRANS_TON, MIN(T05.CARRIER_NO)
SUB_CARRIER,MIN(T05.CARCODE) CAR_CODE, MIN(T05.USER_NO) DRIVER, MIN(T05.MOBILE_NUM) MOBILE_NUM,
MIN(T05.MOBILE_NUM2) MOBILE_NUM2, MIN(T05.PLATE_NO) PLATE_NO/* , MIN(T05.MOBILE_NUM2) MOBILE_NUM2*/ , MIN(T05.GPS_MOBILE_NUM)GPS_MOBILE_NUM, MIN(T05.SEAL_NO) SEAL_NO, MIN(T05.ISSUE_NO) ISSUE_NO, MIN(T05.DESCRIPTION)
DESCRIPTION,MIN(T05.COSTCONFIRM_YN) COSTCONFIRM_YN, MIN(T04.LOAD_PLACE) LOAD_PLACE, MIN(T04.LOAD_DATE)
LOAD_ATA_DATE,MIN(T04.LOAD_TIME) LOAD_ATA_TIME, MIN(T04.LOAD_ATD_DATE) LOAD_ATD_DATE, MIN(T04.LOAD_ATD_TIME)
LOAD_ATD_TIME,MIN(T04.ATD_STOP_REASON) ATD_STOP_REASON, MIN(T04.UNLOAD_PLACE) UNLOAD_PLACE,
MIN(T04.UNLOAD_DATE)UNLOAD_ATA_DATE, MIN(T04.UNLOAD_TIME) UNLOAD_ATA_TIME, MIN(T04.UNLOAD_ATU_DATE)
UNLOAD_ATU_DATE,MIN(T04.UNLOAD_ATU_TIME) UNLOAD_ATU_TIME, MIN(T04.ATU_STOP_REASON) ATU_STOP_REASON,
MIN(T07.FC_NO) FC_NO,MIN(T07.REQUESTED_CARRIER) FC_REQUESTED_CARRIER, MIN(T07.CAR_SEQ) FC_CAR_SEQ, MAX(T05.CARRIER_TRANS_NO) CARRIER_TRANS_NO, RANK() OVER (PARTITION BY MIN(T05.TRANS_NO)
ORDER BY T01.ALLOC_NO) ORDER_RANK, COUNT(NVL(T05.TRANS_NO, '-1')) TRANS_CNT, MIN(T08.CNTR_NO) CNTR_NO, MIN(T06.CUSTREQUEST_NO) CUSTREQUEST_NO, MIN(T01.BOOK_TYPE) BOOK_TYPE, NVL(MAX(T01.DIRECT_YN), 'N') DIRECT_YN, MIN(T06.CONSOL_NO) CONSOL_NO, MIN(T01.ENG_NM) ENG_NM, MIN(T01.TEL1) TEL1, MIN(T01.EMAIL) EMAIL FROM TMS_PAN_ORDER_TB T01, TMS_ORDERREQUEST_TB T02, TMS_REQUESTGOODS_TB T03, TMS_TRANSGOODS_TB T04, TMS_TRANS_TB T05, TMS_REQUEST_TB T06, TMS_FCREQUESTARRANGE_TB T07, TMS_ORDER_CNTRDESC_TB T08 WHERE T01.TR_TYPE LIKE : i_Trans_Type | | '%' ANDNVL(T01.DIRECT_YN, 'N') = 'N' AND (T01.BIZ_KIND_CD LIKE : i_BizKind | | '%' OR
(T01.BIZ_KIND_CD IS NULL AND : i_BizKind ISNULL)) AND T01.ALLOC_NO LIKE NVL(: i_AllocNo, '') | | '%' AND T01.ALLOC_NO = T02.ALLOC_NO(+) AND
T01.DIV_NO = T02.DIV_NO(+) /*AND T01.TRUCKER_CD IN*/ AND T06.ORDERED_CARRIER IN (SELECT CARRIER_NO FROM TMS_CARRIERRELATION_TB STARTWITH CARRIER_NO = : i_Carrier_No CONNECT BY PRIOR CARRIER_NO = HIGH_CARRIER)
/*AND (T01.DPTR_PLAN_DT >= i_Search_SDate ANDT01.DPTR_PLAN_DT <= i_Search_EDate)*/ AND T06.LOAD_DATE BETWEEN : i_Search_SDate AND
: i_Search_EDate AND (T01.DPTR_AREA_CD LIKE : i_Dept_Area | | '%' OR (: i_Dept_Area IS NULL AND T01.DPTR_AREA_CD IS NULL)) AND (T01.ARR_AREA_CD LIKE : i_Arr_Area | | '%' OR (: i_Arr_Area IS NULL AND T01.ARR_AREA_CD IS NULL)) AND (T01.BL_NO LIKE : i_HBLNo | | '%' OR (: i_HBLNo IS NULL AND T01.BL_NO IS NULL)) AND (T06.CNTR_NO LIKE : i_CNTRNo | | '%' OR (: i_CNTRNo IS NULL AND T06.CNTR_NO IS NULL)) /* AND (T01.STATUS IN (SELECT CAR_CODE FROM TABLE (:L_CARS)) OR T01.STATUS IS NULL)*/ AND (T01.ENG_NM LIKE : i_BookingUser | | '%' OR (: i_BookingUser IS NULL AND T01.ENG_NM ISNULL)) AND T02.REQUEST_NO = T03.REQUEST_NO(+) AND T02.SEQ = T03.SEQ(+) AND
T03.REQUEST_NO = T04.REQUEST_NO(+) AND T03.SEQ = T04.SEQ(+) AND T04.TRANS_NO = T05.TRANS_NO(+) AND t03.request_no = t06.request_no /*ANDT04.REQUEST_NO = T06.REQUEST_NO(+)*/ AND T05.TRANS_NO = T07.TRANS_NO(+) AND
T01.ALLOC_NO = T08.ALLOC_NO(+) AND T01.DIV_NO =T08.DIV_NO(+)
FROM TMS_TRANSORDERCOST_TB GROUP BY ALLOC_NO, DIV_NO) V03, (SELECT ALLOC_NO, DIV_NO, SUM(FAMT) ALLOC_FAMT, SUM(VAT_FAMT) ALLOC_VAT_FAMT FROM TMS_ORDERCOST_TB GROUP BY ALLOC_NO, DIV_NO) V04 WHERE V01.DPTR_AREA_CD = T11.AREA_CODE(+) AND V01.ARR_AREA_CD = T12.AREA_CODE(+) AND T13.CODE_CLASS(+) = '213' AND V01.STATUS = T13.CODE_VALUE(+) AND V01.CUST_CD = T15.CUST_NO(+) AND V01.LINE_CD = T16.CUST_NO(+) AND V01.TRUCKER_CD = T17.CARRIER_NO(+) ANDV01.SUB_CARRIER = T18.CARRIER_NO(+) AND V01.ALLOC_NO = V04.ALLOC_NO(+) AND
V01.DIV_NO = V04.DIV_NO(+) AND V01.ALLOC_NO =V03.ALLOC_NO(+) AND V01.DIV_NO = V03.DIV_NO(+) AND V01.CAR_CODE = T20.CAR_CODE(+) AND
V01.DRIVER = T21.USER_NO(+) AND T23.CODE_CLASS(+) = '209' AND V01.ACCOUNT_GB = T23.CODE_VALUE(+) AND V01.CAR_TON = T24.CAR_CAPACITY(+) ANDT20.CAR_TYPE = T25.CAR_TYPE(+) AND V01.OR_TRUCKER_CD = T26.CARRIER_NO(+)
![]() |
![]() |