RE: Views and Predicates, part 2
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Jan 2004 17:35:01 -0500
Also, I tried the following:
1 select /*+ ordered use_nl(xm) */ * from gtt_gada_docs ggd, xan_mjb
xm
2* where xm.doc_id =3Dggd.ggd_doc_id
SQL> /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D2181572344 =
Card=3D2131
17251 Bytes=3D51574374742)
1 0 NESTED LOOPS (Cost=3D2181572344 Card=3D213117251
Bytes=3D51574374742)
2 1 TABLE ACCESS (FULL) OF 'GTT_GADA_DOCS' (Cost=3D10 =
Card=3D8168
Bytes=3D212368)
3 1 VIEW OF 'XAN_MJB'
4 3 WINDOW (BUFFER)
5 4 FILTER
6 5 NESTED LOOPS (OUTER)
7 6 TABLE ACCESS (BY INDEX ROWID) OF
'COMPRESSED_AGREEMENTS' (Cost=3D6171 Card=3D2609173 Bytes=3D263526473)
8 7 INDEX (FULL SCAN) OF 'CPAG_CNST_UK01' (UNIQUE)
(Cost=3D6613 Card=3D2609173)
9 6 TABLE ACCESS (BY INDEX ROWID) OF
'COMPRESSED_AGREEMENTS' (Cost=3D1 Card=3D270 Bytes=3D27000)
10 9 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01'
(NON-UNIQUE)
But, that still leaves me w/ the INDEX (FULL SCAN) on CPAG_CNST_UK01.
And again, simply dropping CPAG_DAY_EFFECTIVE from the select list, and
I get the
plan I want. I'm stumped....
-Mark
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: Bobak, Mark=20
Sent: Friday, January 30, 2004 5:29 PM
To: Larry Elkins
Cc: oracle-l@xxxxxxxxxxxxx
Subject: 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@xxxxxxxxx]=3D20
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@xxxxxxxxx
214.954.1781
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Bobak, Mark
> Sent: Friday, January 30, 2004 4:07 PM
> To: oracle-l@xxxxxxxxxxxxx
> 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,
> =3D3D20
>
decode(xcpag.doc_id,NULL,length(cpag.cpag_text),length(xcpag.cpag_text))
> cpag_text_length,
> =3D3D20
> decode(xcpag.doc_id,NULL,cpag.cpag_calc_date,xcpag.cpag_calc_date)
> cpag_calc_date,
> =3D3D20
>
decode(xcpag.doc_id,NULL,cpag.cpag_day_effective,xcpag.cpag_day_effectiv
> e) cpag_day_effective,
> =3D3D20
> 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 =3D3D3D xcpag.doc_id(+)
> /
>
> Now, when I do something like 'select * from xan_mjb where doc_id
=3D3D3D1;'
> 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 =3D3D3D 1
> SQL> /
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=3D3D3DCHOOSE (Cost=3D3D3D2 =
Card=3D3D3D1
=3D
=3D3D
> Bytes=3D3D3D216)
> 1 0 VIEW OF 'XAN_MJB' (Cost=3D3D3D2 Card=3D3D3D1 =
Bytes=3D3D3D216)
> 2 1 WINDOW (BUFFER)
> 3 2 FILTER
> 4 3 NESTED LOOPS (OUTER)
> 5 4 TABLE ACCESS (BY INDEX ROWID) OF
> 'COMPRESSED_AGREEMENTS' (Cost=3D3D3D1 Card=3D3D3D1 Bytes=3D3D3D101)
> 6 5 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01'
> (NON-UNIQUE) (Cost=3D3D3D3 Card=3D3D3D1)
> 7 4 TABLE ACCESS (BY INDEX ROWID) OF
> 'COMPRESSED_AGREEMENTS' (Cost=3D3D3D1 Card=3D3D3D2 Bytes=3D3D3D200)
> 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=3D3D3DCHOOSE (Cost=3D3D3D21314212 =
=3D3D
> Card=3D3D3D213117
> 251 Bytes=3D3D3D10442745299)
> 1 0 FILTER
> 2 1 NESTED LOOPS (OUTER)
> 3 2 NESTED LOOPS (Cost=3D3D3D2487 Card=3D3D3D213117251 =
=3D3D
> Bytes=3D3D3D6393517530)
> 4 3 VIEW OF 'VW_NSO_1' (Cost=3D3D3D37 Card=3D3D3D8168 =
=3D3D
> Bytes=3D3D3D106184)
> 5 4 SORT (UNIQUE) (Cost=3D3D3D37 Card=3D3D3D8168
Bytes=3D3D3D106184)
> 6 5 TABLE ACCESS (FULL) OF 'GTT_GADA_DOCS'
(Cost=3D3D3D10
> Card=3D3D3D8168 Bytes=3D3D3D106184)
> 7 3 TABLE ACCESS (BY INDEX ROWID) OF
> 'COMPRESSED_AGREEMENTS' (Cost=3D3D3D1 Card=3D3D3D2609173 =3D
Bytes=3D3D3D44355941)
> 8 7 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01'
(NON-UNIQUE)
> (Cost=3D3D3D2 Card=3D3D3D2609173)
> 9 2 TABLE ACCESS (BY INDEX ROWID) OF
'COMPRESSED_AGREEMENTS'
> (Cost=3D3D3D1 Card=3D3D3D270 Bytes=3D3D3D5130)
> 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=3D3D3DCHOOSE (Cost=3D3D3D267151 =
=3D3D
> Card=3D3D3D21311725
> 1 Bytes=3D3D3D48803850479)
> 1 0 MERGE JOIN (Cost=3D3D3D267151 Card=3D3D3D213117251 =3D3D
> Bytes=3D3D3D48803850479)
> 2 1 VIEW OF 'XAN_MJB' (Cost=3D3D3D267088 Card=3D3D3D2609173
> Bytes=3D3D3D563581368)
> 3 2 WINDOW (BUFFER)
> 4 3 FILTER
> 5 4 NESTED LOOPS (OUTER)
> 6 5 TABLE ACCESS (BY INDEX ROWID) OF
> 'COMPRESSED_AGREEMENTS' (Cost=3D3D3D6171 Card=3D3D3D2609173 =3D
Bytes=3D3D3D263526473)
> 7 6 INDEX (FULL SCAN) OF 'CPAG_CNST_UK01' (UNIQUE)
> (Cost=3D3D3D6613 Card=3D3D3D2609173)
> 8 5 TABLE ACCESS (BY INDEX ROWID) OF
> 'COMPRESSED_AGREEMENTS' (Cost=3D3D3D1 Card=3D3D3D270 =
Bytes=3D3D3D27000)
> 9 8 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01'
> (NON-UNIQUE)
> 10 1 SORT (JOIN) (Cost=3D3D3D64 Card=3D3D3D8168 =
Bytes=3D3D3D106184)
> 11 10 VIEW OF 'VW_NSO_1' (Cost=3D3D3D37 Card=3D3D3D8168 =
=3D3D
> Bytes=3D3D3D106184)
> 12 11 SORT (UNIQUE) (Cost=3D3D3D37 Card=3D3D3D8168 =3D
Bytes=3D3D3D106184)
> 13 12 TABLE ACCESS (FULL) OF 'GTT_GADA_DOCS' =3D
(Cost=3D3D3D10
> Card=3D3D3D8168 Bytes=3D3D3D106184)
>
> 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
> -----------------------------------------------------------------
>
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
Other related posts:
