| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Views and Predicates, part 2
8.1.7.4 on Solaris 8.
NO_MERGE seems to not have any effect. (The NO_MERGE goes on my query, and not in the view definition, right?)
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
-----Original Message-----
From: Larry Elkins [mailto:elkinsl_at_flash.net]=20
Sent: Friday, January 30, 2004 5:29 PM
To: Bobak, Mark
Subject: RE: Views and Predicates, part 2
What version are you on? What happens with a NO_MERGE hint? Now you know
why
I touched on it earlier, with the anticipation that your re-write might
involve an in-line view (well, that's the route I went, so thought you
might
try the same).
Regards,
Larry G. Elkins
The Elkins Organization Inc.
elkinsl_at_flash.net
214.954.1781
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Bobak, Mark
> Sent: Friday, January 30, 2004 4:07 PM
> To: oracle-l_at_freelists.org
> Subject: Views and Predicates, part 2
>
>
> 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,
> =3D20
>
decode(xcpag.doc_id,NULL,length(cpag.cpag_text),length(xcpag.cpag_text))
> cpag_text_length,
> =3D20
> decode(xcpag.doc_id,NULL,cpag.cpag_calc_date,xcpag.cpag_calc_date)
> cpag_calc_date,
> =3D20
>
decode(xcpag.doc_id,NULL,cpag.cpag_day_effective,xcpag.cpag_day_effectiv
> e) cpag_day_effective,
> =3D20
> 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 =3D3D xcpag.doc_id(+)
> /
>
> Now, when I do something like 'select * from xan_mjb where doc_id
=3D3D1;'
> 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 =3D3D 1
> SQL> /
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=3D3DCHOOSE (Cost=3D3D2 Card=3D3D1 =
=3D
> Bytes=3D3D216)
> 1 0 VIEW OF 'XAN_MJB' (Cost=3D3D2 Card=3D3D1 Bytes=3D3D216)
> 2 1 WINDOW (BUFFER)
> 3 2 FILTER
> 4 3 NESTED LOOPS (OUTER)
> 5 4 TABLE ACCESS (BY INDEX ROWID) OF
> 'COMPRESSED_AGREEMENTS' (Cost=3D3D1 Card=3D3D1 Bytes=3D3D101)
> 6 5 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01'
> (NON-UNIQUE) (Cost=3D3D3 Card=3D3D1)
> 7 4 TABLE ACCESS (BY INDEX ROWID) OF
> 'COMPRESSED_AGREEMENTS' (Cost=3D3D1 Card=3D3D2 Bytes=3D3D200)
> 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=3D3DCHOOSE (Cost=3D3D21314212 =3D
> Card=3D3D213117
> 251 Bytes=3D3D10442745299)
> 1 0 FILTER
> 2 1 NESTED LOOPS (OUTER)
> 3 2 NESTED LOOPS (Cost=3D3D2487 Card=3D3D213117251 =3D
> Bytes=3D3D6393517530)
> 4 3 VIEW OF 'VW_NSO_1' (Cost=3D3D37 Card=3D3D8168 =3D
> Bytes=3D3D106184)
> 5 4 SORT (UNIQUE) (Cost=3D3D37 Card=3D3D8168
Bytes=3D3D106184)
> 6 5 TABLE ACCESS (FULL) OF 'GTT_GADA_DOCS'
(Cost=3D3D10
> Card=3D3D8168 Bytes=3D3D106184)
> 7 3 TABLE ACCESS (BY INDEX ROWID) OF
> 'COMPRESSED_AGREEMENTS' (Cost=3D3D1 Card=3D3D2609173 =
Bytes=3D3D44355941)
> 8 7 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01'
(NON-UNIQUE)
> (Cost=3D3D2 Card=3D3D2609173)
> 9 2 TABLE ACCESS (BY INDEX ROWID) OF
'COMPRESSED_AGREEMENTS'
> (Cost=3D3D1 Card=3D3D270 Bytes=3D3D5130)
> 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=3D3DCHOOSE (Cost=3D3D267151 =3D
> Card=3D3D21311725
> 1 Bytes=3D3D48803850479)
> 1 0 MERGE JOIN (Cost=3D3D267151 Card=3D3D213117251 =3D
> Bytes=3D3D48803850479)
> 2 1 VIEW OF 'XAN_MJB' (Cost=3D3D267088 Card=3D3D2609173
> Bytes=3D3D563581368)
> 3 2 WINDOW (BUFFER)
> 4 3 FILTER
> 5 4 NESTED LOOPS (OUTER)
> 6 5 TABLE ACCESS (BY INDEX ROWID) OF
> 'COMPRESSED_AGREEMENTS' (Cost=3D3D6171 Card=3D3D2609173 =
Bytes=3D3D263526473)
> 7 6 INDEX (FULL SCAN) OF 'CPAG_CNST_UK01' (UNIQUE)
> (Cost=3D3D6613 Card=3D3D2609173)
> 8 5 TABLE ACCESS (BY INDEX ROWID) OF
> 'COMPRESSED_AGREEMENTS' (Cost=3D3D1 Card=3D3D270 Bytes=3D3D27000)
> 9 8 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01'
> (NON-UNIQUE)
> 10 1 SORT (JOIN) (Cost=3D3D64 Card=3D3D8168 Bytes=3D3D106184)
> 11 10 VIEW OF 'VW_NSO_1' (Cost=3D3D37 Card=3D3D8168 =3D
> Bytes=3D3D106184)
> 12 11 SORT (UNIQUE) (Cost=3D3D37 Card=3D3D8168 =
Bytes=3D3D106184)
> 13 12 TABLE ACCESS (FULL) OF 'GTT_GADA_DOCS' =
(Cost=3D3D10
> Card=3D3D8168 Bytes=3D3D106184)
>
> 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
> -----------------------------------------------------------------
>
-- 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:28:55 CST
 |
 |