| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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 | | '%' AND
NVL(T01.DIRECT_YN, 'N') = 'N' AND (T01.BIZ_KIND_CD LIKE : i_BizKind |
| '%' OR
(T01.BIZ_KIND_CD IS NULL AND : i_BizKind IS
NULL)) 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 START
WITH CARRIER_NO = : i_Carrier_No CONNECT BY PRIOR CARRIER_NO =
HIGH_CARRIER)
/*AND (T01.DPTR_PLAN_DT >= i_Search_SDate AND
T01.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 IS
NULL)) 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 /*AND
T04.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(+) AND
V01.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(+) AND
T20.CAR_TYPE = T25.CAR_TYPE(+) AND V01.OR_TRUCKER_CD =
T26.CARRIER_NO(+)
 |
 |