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

Home -> Community -> Usenet -> c.d.o.server -> sql query slow... help me!

sql query slow... help me!

From: Kevin <procsharper_at_gmail.com>
Date: Thu, 14 Jun 2007 15:24:27 -0000
Message-ID: <1181834667.800737.105050@a26g2000pre.googlegroups.com>


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(+)
GROUP BY T01.ALLOC_NO, T01.DIV_NO) V01, TMS_PAN_AREA_TB T11, TMS_PAN_AREA_TB T12, TMS_PAN_ETCODE_TB T13, TMS_CUST_TB T15, TMS_CUST_TB T16, TMS_CARRIER_TB T17, TMS_CARRIER_TB T18, TMS_CAR_TB T20, TMS_USER_TB T21, TMS_PAN_ETCODE_TB T23, TMS_CARCAPACITY_TB T24, TMS_CARTYPE_TB T25, TMS_CARRIER_TB T26,    (SELECT ALLOC_NO, DIV_NO, SUM(FAMT) TRANS_FAMT, SUM(VAT_FAMT) TRANS_VAT_FAMT
     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(+)
ORDER BY T13.SORT_ORDER, V01.TRUCKER_ALLOC_DT, V01.TRUCKER_ALLOC_TM, V01.ALLOC_NO Received on Thu Jun 14 2007 - 10:24:27 CDT

Original text of this message

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