Argh! Ok, I re-wrote the view, like this: create or replace view xan_mjb as select doc_id, cpag_text, cpag_text_length, cpag_calc_date, max(cpag_day_effective) over (partition by doc_id) cpag_day_effective, cpag_format_mask, aud_type, vlad_id from( select /*+ ordered use_nl(xcpag) */ cpag.doc_id doc_id, decode(xcpag.doc_id,NULL,cpag.cpag_text,xcpag.cpag_text) cpag_text, =20 decode(xcpag.doc_id,NULL,length(cpag.cpag_text),length(xcpag.cpag_text)) cpag_text_length, =20 decode(xcpag.doc_id,NULL,cpag.cpag_calc_date,xcpag.cpag_calc_date) cpag_calc_date, =20 decode(xcpag.doc_id,NULL,cpag.cpag_day_effective,xcpag.cpag_day_effectiv e) cpag_day_effective, =20 decode(xcpag.doc_id,NULL,cpag.cpag_format_mask,xcpag.cpag_format_mask) cpag_format_mask, decode(xcpag.doc_id,NULL,'PQ','XN') aud_type, decode(xcpag.doc_id,NULL,NULL,xcpag.vlad_id) vlad_id from adds.compressed_agreements cpag, xanrights.compressed_agreements xcpag where cpag.doc_id =3D xcpag.doc_id(+) / Now, when I do something like 'select * from xan_mjb where doc_id =3D1;' it does the right thing and I get an index range scan, rather than a full scan: SQL> l 1* select * from xan_mjb where doc_id =3D 1 SQL> / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D2 Card=3D1 = Bytes=3D216) 1 0 VIEW OF 'XAN_MJB' (Cost=3D2 Card=3D1 Bytes=3D216) 2 1 WINDOW (BUFFER) 3 2 FILTER 4 3 NESTED LOOPS (OUTER) 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'COMPRESSED_AGREEMENTS' (Cost=3D1 Card=3D1 Bytes=3D101) 6 5 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01' (NON-UNIQUE) (Cost=3D3 Card=3D1) 7 4 TABLE ACCESS (BY INDEX ROWID) OF 'COMPRESSED_AGREEMENTS' (Cost=3D1 Card=3D2 Bytes=3D200) 8 7 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01' (NON-UNIQUE) So far, so good. Now, I'd also like to join this view w/ a GTT that contains a list of doc_ids. So, I do something like: SQL> select doc_id, cpag_calc_date,cpag_format_mask,aud_type, vlad_id 2 from xan_mjb xm where xm.doc_id in(select ggd_doc_id from gtt_gada_docs); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D21314212 = Card=3D213117 251 Bytes=3D10442745299) 1 0 FILTER 2 1 NESTED LOOPS (OUTER) 3 2 NESTED LOOPS (Cost=3D2487 Card=3D213117251 = Bytes=3D6393517530) 4 3 VIEW OF 'VW_NSO_1' (Cost=3D37 Card=3D8168 = Bytes=3D106184) 5 4 SORT (UNIQUE) (Cost=3D37 Card=3D8168 Bytes=3D106184) 6 5 TABLE ACCESS (FULL) OF 'GTT_GADA_DOCS' (Cost=3D10 Card=3D8168 Bytes=3D106184) 7 3 TABLE ACCESS (BY INDEX ROWID) OF 'COMPRESSED_AGREEMENTS' (Cost=3D1 Card=3D2609173 Bytes=3D44355941) 8 7 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01' (NON-UNIQUE) (Cost=3D2 Card=3D2609173) 9 2 TABLE ACCESS (BY INDEX ROWID) OF 'COMPRESSED_AGREEMENTS' (Cost=3D1 Card=3D270 Bytes=3D5130) 10 9 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01' (NON-UNIQUE) And that looks great! All right! And then I change it to: 1* select * from xan_mjb xm where xm.doc_id in(select ggd_doc_id from gtt_gada_docs) SQL> / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D267151 = Card=3D21311725 1 Bytes=3D48803850479) 1 0 MERGE JOIN (Cost=3D267151 Card=3D213117251 = Bytes=3D48803850479) 2 1 VIEW OF 'XAN_MJB' (Cost=3D267088 Card=3D2609173 Bytes=3D563581368) 3 2 WINDOW (BUFFER) 4 3 FILTER 5 4 NESTED LOOPS (OUTER) 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'COMPRESSED_AGREEMENTS' (Cost=3D6171 Card=3D2609173 Bytes=3D263526473) 7 6 INDEX (FULL SCAN) OF 'CPAG_CNST_UK01' (UNIQUE) (Cost=3D6613 Card=3D2609173) 8 5 TABLE ACCESS (BY INDEX ROWID) OF 'COMPRESSED_AGREEMENTS' (Cost=3D1 Card=3D270 Bytes=3D27000) 9 8 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01' (NON-UNIQUE) 10 1 SORT (JOIN) (Cost=3D64 Card=3D8168 Bytes=3D106184) 11 10 VIEW OF 'VW_NSO_1' (Cost=3D37 Card=3D8168 = Bytes=3D106184) 12 11 SORT (UNIQUE) (Cost=3D37 Card=3D8168 Bytes=3D106184) 13 12 TABLE ACCESS (FULL) OF 'GTT_GADA_DOCS' (Cost=3D10 Card=3D8168 Bytes=3D106184) And it falls apart....sigh.....Why would it suddenly do this to me, just cause I added columns to the select list? Note that I've narrowed it down the the CPAG_DAY_EFFECTIVE column. If that column is in the list, bad things happen. If it's not, everything is great. Looking for clues..... Thanks, -Mark PS I've tried every hint under the sun, I've tried rewriting the temp table subselect as a join, and making it the driving table, no dice.... Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Horace Walpole ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------