RE: Views and predicates.....

Hi Tom,

Thanks for the reply.  Nope, I thought of that.  CPAG_CNST_UK01 is on
COMPRESSED_AGREEMENTS, so I should be ok there.

Any other thoughts?

-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: Mercadante, Thomas F [mailto:thomas.mercadante@xxxxxxxxxxxxxxxxx]=20
Sent: Friday, January 30, 2004 11:25 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Cc: Bobak, Mark
Subject: RE: Views and predicates.....


Mark,

this may be as simple as selecting the correct doc_id column.  Is the=20
CPAG_CNST_UK01 index in the COMPRESSED_AGREEMENTS table?  If not, then
change your select statement (within the view) to query the doc_id
column
from the correct table.

Good Luck

Tom Mercadante
Oracle Certified Professional


-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
Sent: Friday, January 30, 2004 11:09 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Views and predicates.....


Ok, so I have a view defined as:
create or replace view xan_comp_view
as
select /*+ ordered use_nl(xcpag) */
       distinct 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,
       decode(xcpag.doc_id,NULL,max(cpag.cpag_day_effective) over
(partition by cpag.doc_id),
                                max(xcpag.cpag_day_effective) over
(partition by xcpag.doc_id)) cpag_day_effective,
       decode(xcpag.doc_id,NULL,'PQ','XN') aud_type
from compressed_agreements cpag,
     xan_compressed_agreements xcpag
WHERE decode(xcpag.doc_id,NULL,cpag.CPAG_DAY_EFFECTIVE,1) <=3D
=20
decode(xcpag.doc_id,NULL,decode(sign(trunc(sysdate)-cpag.cpag_calc_date)
,-1,0, trunc(sysdate)-cpag.cpag_calc_date + NVL(cpag.C
PAG_DAY_EFFECTIVE,0)),1)
  and decode(xcpag.doc_id,NULL,1,xcpag.CPAG_DAY_EFFECTIVE) <=3D
=20
decode(xcpag.doc_id,NULL,1,decode(sign(trunc(sysdate)-xcpag.cpag_calc_da
te),-1,0,
      trunc(sysdate)-xcpag.cpag_calc_date +
NVL(xcpag.CPAG_DAY_EFFECTIVE,0)))
  and cpag.doc_id =3D xcpag.doc_id(+)
/
If I do 'select * from xan_comp_view;'
The plan is:
SQL> select * from xan_comp_view
  2  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D496561 =
Card=3D1948518
          Bytes=3D335145096)
   1    0   VIEW OF 'XAN_COMP_VIEW' (Cost=3D496561 Card=3D1948518
Bytes=3D335145096)
   2    1     SORT (UNIQUE) (Cost=3D496561 Card=3D1948518 =
Bytes=3D378012492)
   3    2       WINDOW (SORT)
   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=3D258308127)
   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=3D25650)
  10    9                 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01'
(NON-UNIQUE)

This is fine, and I'm ok w/ it.  But, I don't plan on ever using the
view this way.  What I plan to do is something like:
'select * from xan_comp_view where doc_id =3D 1'
When I do that, the plan looks like:
SQL> select * from xan_mjb where doc_id  =3D1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D496561 =
Card=3D1948518
Bytes=3D335145096)
   1    0   VIEW OF 'XAN_MJB' (Cost=3D496561 Card=3D1948518 =
Bytes=3D335145096)
   2    1     SORT (UNIQUE) (Cost=3D496561 Card=3D1948518 =
Bytes=3D378012492)
   3    2       WINDOW (SORT)
   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=3D258308127)
   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=3D25650)
  10    9                 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01'
(NON-UNIQUE)


What I want to see here is an INDEX (UNIQUE SCAN) on CPAG_CNST_UK01',
since I'm providing the 'doc_id=3D1' predicate.  However, it insists on
the FULL SCAN, which, of course, is killing my performance.

Can anyone think of a way that I can convince the optimizer to do what I
want?  If I take the SQL that defines the view, and execute it as a SQL,
with the 'DOC_ID=3D1' predicate, it does exactly what I want.  As soon =
as
I put the SQL into the view, it stops working.

Help!

Thanks in advance,

-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


----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: