Views and Predicates, part 2

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 30 Jan 2004 17:06:38 -0500
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFBDC@bosmail00.bos.il.pqe>

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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jan 30 2004 - 16:06:38 CST