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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Can't insert into partition

RE: Can't insert into partition

From: DENNIS WILLIAMS <DWILLIAMS_at_lifetouch.com>
Date: Thu, 07 Aug 2003 06:44:31 -0800
Message-ID: <F001.005C975C.20030807064431@fatcity.com>


Wolfgang - Yes, you are correct, it is using bind variables. To run the SQL standalone, we manually change these to literal variables.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, August 07, 2003 12:19 AM To: Multiple recipients of list ORACLE-L

Is the sql you posted the exact sql as it is executed in the PLSQL procedure, i.e. is the procedure using literals such as 2004 in the predicates for sourcefiscalyear, or is it really using a bindvariable?

At 02:29 PM 8/6/2003 -0800, you wrote:
>Henry - Thanks. I feel like I'm getting an education today on the Oracle
>Wait Interface today. Nothing like a live problem for everything to make
>sense.
> Thanks for pointing out that I could find the table. It is our
>WKLYJOBFACT table. Not one we suspected.
> We have been doing an EXPLAIN PLAN by extracting the SQL from the
stored
>procedure. I posted that. But when the SQL is extracted from the stored
>procedure, it runs just fine.
> Does anyone know how to get the explain plan that the PL/SQL procedure
is
>seeing?
> Thanks to everyone for helping narrow the problem down this far. It has
>kept me from rebuilding the table which probably would have accomplished
>nothing.

[...]

> > SQL> explain plan for
> > 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING
> > 3 ( bunch of columns )
> > 21 SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */
> > 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID,
> > 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE,
> > 24
> > CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE,
> > 25 CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR,
> > 26 CJS.RETAKEIND,
> > 27
> >

TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRSTUV
> > ,
> > 28 DECODE(TO_DATE(CJS.SHIPDATE,'YYYY/MM/DD'), NULL ,'N','Y'),
> > 29 DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0,
> > 30 DECODE(CJS.PAYSTATUSIND,'F','Y','N'),'Y') PAIDJOBIND,
> > 31 CJS.PAYSTATUSIND,PS.PAIDSHIPPEDJOBIND,
> > 32 DECODE(SUBSTR(CJS.JOBNBR,9,1),'I','Y','N') PREJOBIND,CJS.PLANTCODE,
> > 33
> > CJS.SHOTQTY,PS.MTDSHIPPEDPKGQTY,PS.MTDCHARGEBACKAMT,PS.MTDTERRCMSNAMT,
> > 34 NVL(PS.MTDTERRCMSNAMT,0) - NVL(PS.MTDCHARGEBACKAMT,0)
> > ,PS.MTDCASHRECEIVEDA,
> > 35 PS.MTDCASHRETAINEDAMT,PS.MTDESTACCTCMSNAMT,PS.MTDACCTCMSNPAIDAMT,
> > 36
> >

PS.MTDGROSSCASHAMT,PS.MTDSALESTAXAMT,CJS.YTDSHIPPEDPKGQTY,CJS.YTDCHARGEBAC
> > ,
> > 37 CJS.YTDTERRCMSNAMT,CJS.YTDTERREARNINGSAMT,CJS.YTDCASHRECEIVEDAMT,
> > 38

CJS.YTDCASHRETAINEDAMT,CJS.YTDESTACCTCMSNAMT,CJS.YTDACCTCMSNPAIDAMT,
> > 39 CJS.YTDCASHRECEIVEDAMT +YTDESTACCTCMSNAMT + YTDACCTCMSNPAIDAMT ,
> > 40 CJS.YTDSALESTAXAMT,CJS.YTDPERFECTSALEAMT,PS.PRELIMYTDSHIPPEDPKGQTY,
> > 41 PS.PRELIMYTDCHARGEBACKAMT,PS.PRELIMYTDTERRCMSNAMT,
> > 42 PS.PRELIMYTDTERRCMSNAMT - PS.PRELIMYTDCHARGEBACKAMT
> > ,PS.PRELIMYTDCASHRECEI,
> > 43 PS.PRELIMYTDCASHRETAINEDAMT,PS.PRELIMYTDESTACCTCMSNAMT,
> > 44
> >

PS.PRELIMYTDACCTCMSNPAIDAMT,PS.PRELIMYTDGROSSCASHAMT,PS.PRELIMYTDSALESTAXA
> > ,
> > 45 PS.PRELIMYTDPERFECTSALEAMT,CJS.YTDJTEPAIDPKGQTY,CJS.YTDPAIDPKGQTY,
> > 46 CJS.YTDUNPAIDPKGQTY,CJS.YTDXNOPURCHASEQTY,PS.YTDPAIDPKGSHIPQTY,
> > 47
> >

PS.YTDUNPAIDPKGSHIPQTY,CJS.PROOFPOSEQTY,CJS.PROOFCOUNTQTY,CJS.EXTRACTDATE,
> > 48

CJS.ORIGINALRECEIVEDDATE,PS.CMSNSTATUSCODE,WV1.FIRSTPOSITIVECASHDATE,
> > 49 DECODE(C.LIFETOUCHID, NULL ,'New','Retained') RENEWALSTATUSCODE,
> > 50 NVL(WV.PRELIMYTDESTACCTCMSNAMT,
> > 51 DECODE(NVL(PS.PRELIMYTDESTACCTCMSNAMT,0),0, NULL
> > ,PS.PRELIMYTDESTACCTCMSNA,
> > 52 APC.AVGPKGPRICE
> > 53 FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD,
> > 54 (SELECT A1.JOBNBR,DECODE(SUM(PAIDOFFERQTY),0,0,
> > 55 SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) )
> > AVGPKGPR
> > 56 FROM (SELECT DISTINCT
> > A.JOBNBR,A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAT
> > 57 FROM OFFERLOAD_STAGE A
> > 58 WHERE A.OFFERNAME IN (
> > 'A','B','C','D','E','F','G','H','I','J','K
> > 59 GROUP BY A1.JOBNBR ) APC,
> > 60 (SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID,MD.MARKETINGCODE
> > 61 FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR < 2004
> > 62 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C,
> > 63 (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE
> > 64 FROM CASHTXNFACT
> > 65 WHERE SOURCEFISCALYEAR = 2004 GROUP BY JOBNBR ) WV1,
> > 66 (SELECT X.JOBNBR,X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT
> > 67 FROM WKLYJOBFACT X,
> > 68 (SELECT JOBNBR,MAX(WEEKENDDATE) MAXWEEKENDDATE
> > 69 FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = 2004
> > 70 AND NVL(PRELIMYTDESTACCTCMSNAMT,0) > 0
> > 71 GROUP BY JOBNBR ) W1
> > 72 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE
)
> > WV
> > 73 WHERE CJS.JOBNBR = PS.JOBNBR (+)
> > 74 AND CJS.JOBNBR = APC.JOBNBR (+)
> > 75 AND CJS.MARKETINGCODE = MD.MARKETINGCODE
> > 76 AND CJS.LIFETOUCHID = C.LIFETOUCHID (+)
> > 77 AND CJS.MARKETINGCODE = C.MARKETINGCODE (+)
> > 78 AND CJS.JOBNBR = WV1.JOBNBR (+)
> > 79 AND CJS.JOBNBR = WV.JOBNBR (+);
> >
> > Explained.
> >
> > SQL> @explain
> >
> > Query Plan
> >



> > ------
> > INSERT STATEMENT Cost = 181253
> > LOAD AS SELECT
> > HASH JOIN OUTER
> > HASH JOIN OUTER
> > HASH JOIN OUTER
> > HASH JOIN OUTER
> > HASH JOIN OUTER
> > HASH JOIN
> > TABLE ACCESS FULL MARKETINGDIM
> > TABLE ACCESS FULL CURRJOB_STAGE
> > VIEW
> >
> > Query Plan
> >


> > ------
> > SORT GROUP BY
> > VIEW
> > SORT UNIQUE
> > TABLE ACCESS FULL OFFERLOAD_STAGE
> > VIEW
> > SORT GROUP BY
> > TABLE ACCESS FULL CASHTXNFACT
> > VIEW
> > SORT UNIQUE
> > NESTED LOOPS
> > PARTITION RANGE ITERATOR
> >
> > Query Plan
> >


> > ------
> > TABLE ACCESS FULL CURRJOBFACT
> > INDEX UNIQUE SCAN SYS_C00889
> > TABLE ACCESS FULL PERIOD_STAGE
> > VIEW
> > HASH JOIN
> > VIEW
> > SORT GROUP BY
> > PARTITION RANGE ALL
> > TABLE ACCESS FULL WKLYJOBFACT
> > PARTITION RANGE ALL
> > TABLE ACCESS FULL WKLYJOBFACT
> >
> > 33 rows selected.
> >
> >
> > Table truncated.
> >
> > SQL>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Henry Poras
> INET: hporas_at_etal.uri.edu
>
>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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Henry Poras
> INET: hporas_at_etal.uri.edu
>
>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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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).

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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 Thu Aug 07 2003 - 09:44:31 CDT

Original text of this message

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