RE: Can't insert into partition

No, you can put a hint in inner sql and subselects. Some hints you NEED to put on a subselect to make any sense.
Is that sql verbatim? The hint has a syntax error. There is a dot rather than a comma after ps which - pooof - may turn the princely hint into an ugly toad (no pun intended) comment.
Another thnig you can try, since you are using bind variables, is to jack up db_file_multiblock_read_count, hash_multiblock_io_count, hash_area_size, optimizer_index_cost_adj (to 10000), create a stored outline of the sql - hopefully it will use hash joins with all the help, and then revert to the normal init_ora settings and tell oracle to use the stored outline.

At 02:19 PM 8/7/2003 -0800, you wrote:
>Thanks Wolfgang! And thanks to the others who have helped us unravel this
>problem.
>Your suggestion put us on the right track. I started running a SQL
>trace/tkprof, and lo and behold, when the stored procedure submits the SQL,
>CBO does everything as NESTED LOOPS. The next question is how to induce CBO
>to consider HASH JOIN?
> The original query had USE_HASH hints on the subqueries. Somewhere I
>thought I recalled that you could only put hints on the outer SQL statement
>-- is that true? So we tried adding the USE_HASH hint to the overall INSERT
>statement, to no effect. Here is the relevant portion of the tkprof output.
>Thanks again to eveyone.
>
>Dennis Williams
>DBA, 80%OCP, 100% DBA
>Lifetouch, Inc.
>dwilliams_at_lifetouch.com
>
>INSERT /*+ APPEND use_hash(CJS, PS, APC, MD, C, WV1, WV) */INTO CURRJOBFACT
> NOLOGGING ( JOBNBR,SOURCEFISCALYEAR,LIFETOUCHID,PROGRAMID,MARKETINGCODE,
> PLANTRECEIPTDATE,PHOTOGRAPHYDATE,SHIPDATE,SELLINGMETHODCODE,MDRPRIMARYID,
> SUBPROGRAMCODE,TERRCODE,SUBTERRCODE,BIDIND,PDKIND,PDKPARTNBR,RETAKEIND,
>
>RETAKENBR,SHIPPEDJOBIND,PAIDJOBIND,PAYSTATUSIND,PAIDSHIPPEDJOBIND,PREJOBIND,
> PLANTCODE,SHOTQTY,MTDSHIPPEDPKGQTY,MTDCHARGEBACKAMT,MTDTERRCMSNAMT,
>
>MTDTERREARNINGSAMT,MTDCASHRECEIVEDAMT,MTDCASHRETAINEDAMT,MTDESTACCTCMSNAMT,
> MTDACCTCMSNPAIDAMT,MTDGROSSCASHAMT,MTDSALESTAXAMT,YTDSHIPPEDPKGQTY,
> YTDCHARGEBACKAMT,YTDTERRCMSNAMT,YTDTERREARNINGSAMT,YTDCASHRECEIVEDAMT,
> YTDCASHRETAINEDAMT,YTDESTACCTCMSNAMT,YTDACCTCMSNPAIDAMT,YTDGROSSCASHAMT,
> YTDSALESTAXAMT,YTDPERFECTSALEAMT,PRELIMYTDSHIPPEDPKGQTY,
> PRELIMYTDCHARGEBACKAMT,PRELIMYTDTERRCMSNAMT,PRELIMYTDTERREARNINGSAMT,
> PRELIMYTDCASHRECEIVEDAMT,PRELIMYTDCASHRETAINEDAMT,PRELIMYTDESTACCTCMSNAMT,
> PRELIMYTDACCTCMSNPAIDAMT,PRELIMYTDGROSSCASHAMT,PRELIMYTDSALESTAXAMT,
> PRELIMYTDPERFECTSALEAMT,YTDJTEPAIDPKGQTY,YTDPAIDPKGQTY,YTDUNPAIDPKGQTY,
> YTDXNOPURCHASEQTY,PRELIMYTDPAIDPKGSHIPQTY,PRELIMYTDUNPAIDPKGSHIPQTY,
>
>PROOFPOSEQTY,PROOFCOUNTQTY,EXTRACTDATE,ORIGINALRECEIVEDDATE,CMSNSTATUSCODE,
> FIRSTPOSITIVECASHDATE,RENEWALSTATUSCODE,ORIGESTACCTCMSNAMT,AVGPKGPRICE )
> SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */CJS.JOBNBR,
> (columns omitted)
> PS.PRELIMYTDESTACCTCMSNAMT)) ORIGESTACCTCMSNAMT,APC.AVGPKGPRICE FROM
> CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD,(SELECT A1.JOBNBR,
> DECODE(SUM(PAIDOFFERQTY),0,0,SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) /
> SUM(A1.PAIDOFFERQTY) ) AVGPKGPRICE FROM (SELECT DISTINCT A.JOBNBR,
> A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAMT FROM OFFERLOAD_STAGE A WHERE
> A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K' )) A1 GROUP
> BY A1.JOBNBR ) APC,(SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID,
> MD.MARKETINGCODE FROM CURRJOBFACT C,MARKETINGDIM MD WHERE
> C.SOURCEFISCALYEAR < :b1 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C,
> (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE FROM CASHTXNFACT
> WHERE SOURCEFISCALYEAR = :b1 GROUP BY JOBNBR ) WV1,(SELECT X.JOBNBR,
> X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT FROM WKLYJOBFACT X,(SELECT
>JOBNBR,
> MAX(WEEKENDDATE) MAXWEEKENDDATE FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR
>=
> :b1 AND NVL(PRELIMYTDESTACCTCMSNAMT,0) > 0 GROUP BY JOBNBR ) W1 WHERE
> X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV WHERE
> CJS.JOBNBR = PS.JOBNBR (+) AND CJS.JOBNBR = APC.JOBNBR (+) AND
> CJS.MARKETINGCODE = MD.MARKETINGCODE AND CJS.LIFETOUCHID = C.LIFETOUCHID
> (+) AND CJS.MARKETINGCODE = C.MARKETINGCODE (+) AND CJS.JOBNBR =
> WV1.JOBNBR (+) AND CJS.JOBNBR = WV.JOBNBR (+)
>
>
>
>call count cpu elapsed disk query current
>rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 1 0.01 0.01 0 0 0
>0
>Execute 1 351.17 1349.51 1208687 1212777 866
>0
>Fetch 0 0.00 0.00 0 0 0
>0
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 2 351.18 1349.52 1208687 1212777 866
>0
>
>Misses in library cache during parse: 1
>Optimizer goal: CHOOSE
>Parsing user id: 21 (recursive depth: 1)
>
>Rows Row Source Operation
>------- ---------------------------------------------------
> 0 LOAD AS SELECT
> 0 NESTED LOOPS OUTER
> 1 NESTED LOOPS OUTER
> 2 NESTED LOOPS OUTER
> 2 HASH JOIN OUTER
> 6412 HASH JOIN OUTER
> 6412 HASH JOIN
> 246 TABLE ACCESS FULL MARKETINGDIM
> 6412 TABLE ACCESS FULL CURRJOB_STAGE
> 3093 VIEW
> 3093 SORT GROUP BY
> 13728 VIEW
> 13728 SORT UNIQUE
> 35929 TABLE ACCESS FULL OFFERLOAD_STAGE
> 47 VIEW
> 47 SORT UNIQUE
>1222277 NESTED LOOPS
>1222278 PARTITION RANGE ITERATOR PARTITION: KEY (null)
>1222280 TABLE ACCESS FULL CURRJOBFACT PARTITION: KEY (null)
>1222277 INDEX UNIQUE SCAN (object id 2941)
> 2 TABLE ACCESS FULL PERIOD_STAGE
> 0 VIEW
> 55 HASH JOIN
> 110 VIEW
> 110 SORT GROUP BY
> 98 PARTITION RANGE ALL PARTITION: START=1 STOP=31
> 98 TABLE ACCESS FULL WKLYJOBFACT PARTITION: START=1 STOP=31
>32824864 PARTITION RANGE ALL PARTITION: START=1 STOP=31
>32824864 TABLE ACCESS FULL WKLYJOBFACT PARTITION: START=1 STOP=31
> 1 VIEW
> 2324 SORT GROUP BY
> 5908 TABLE ACCESS FULL CASHTXNFACT
>
>
>-----Original Message-----
>Sent: Thursday, August 07, 2003 10:55 AM
>To: Multiple recipients of list ORACLE-L
>
>
>But then it's not the same sql anymore and the access plan can be wildly
>different. You need to use bind variables in your sqlplus session as well.
>Unfortunately, even then it is not guaranteed that you'll get the same plan
>as you get in the plsql proc.
>
>At 06:44 AM 8/7/2003 -0800, you wrote:
> >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?
>
>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).

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).