Re: View HELP Please!

Freeman Robert - IL wrote:
>
> Ron, man, you got me on the version thing. I yell at people who don't say
> what version they are on, and here I am forgetting to do the same. I'm on
> 9iR2.
>
> You are correct that the view would not use the index if I just did a select
> * from it with no additional predicates. However, if I do a select * from a
> view with a predicate in that select statement (like user_id=100) then the
> additional predicate should be merged into the view and a new execution plan
> (using index lookup) should be generated.
>
> Thanks!
>
> RF
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L
> Sent: 2/12/2003 10:04 AM
>
> Robert,
> I will make the assumption that you are on a newer version of Oracle.
>
> If I remember correctly, a view does not use an index and will use a
> full table scan. That could be the cause for the time difference.
> I know that this doesn't answer your question but it might trigger
> other thoughts that solve the problem.
> Ron
>
> >>> FREEMANR_at_tusc.com 02/12/03 10:18AM >>>
> I'm fighting a view.... Someone hand me a big dagger with which to kill
> it.
> I have a view that takes 6 minutes to run when I query it like this:
>
> select
> count(*) from TST_XVW a
> where claim_carrier_key=41721;
>
> Yet, if I take the SQL from the view, add the claim_carrier_key
> predicate to
> it, I get a run time of 6 seconds. I've tried several different hints
> (push_pred, use_nl, etc) and I'm just not seeming to be able to get
> the
> optimizer to give me a good plan. Any help on this would be
> appreciated.
>
> Robert

Robert,

  The way I understand it is that your view is pretty complex. My experience with views is that past some degree of complexity (a hard notion to quantify) Oracle gives up any attempt to rewriting or recombining them and uses them 'as is'. I guess that the boundary increases in complexity with each release of Oracle, but here it is and you usually easily see from the plan whether the stored view was used or if it was broken into its constituent parts. I guess that the execution path it takes doesn't start with the table which contains the 'claim_carrier_key' column when you create the view. I would probably try to do what is considered bad practice by Oracle, i.e. a hint in the view. Some 'ordered' starting with the table which contains claim_carrier_key *might* be appropriate. The snag is that when you apply _other_ conditions to your view, queries may then be far more slower than your 6 mn ...

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).