Re: Oracle CBO query cost - retrieve value?

On Oct 7, 10:37 pm, DA Morgan <damor..._at_psoug.org> wrote:
> csn..._at_gmail.com wrote:
> > Urm, Morgan, you didn't really think that was a question on syntax,
> > did you?
>
> > To the OP, don't look at cost. It is not for consumption by the end-
> > user.
>
> > You have a bind-variable peeking problem.
>
> > Construct say 3 different cursors each with a different stabilised
> > plan based on selectivity of the inputs, and call the appropriate
> > cursor based on the input variable. Or reject it altogether.
>
> > Comes down to: know your data.
>
> Given the syntax error ... you are assuming that what you are seeing
> is the real statement. I'm not willing to make that assumption.
>
> The OP has now stated: "I anonymised & simplified the query data
> incorrectly" so, given that it has been simplified, the real issue
> may bear no relationship to what has been posted.
>
> To the OP. Anonymizing, ok, rename the tables and columns if you wish.
> But simplifying makes the exercise meaningless.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Daniel, here is what I wrote

> I know I can see the cost in the explain plan table. My question is
> how can I get this value and decide in our pl/sql & java middleware
> whether to proceed or show the user the refine your search webpage.

Here is what you wrote

>
> To the OP. Anonymizing, ok, rename the tables and columns if you wish.
> But simplifying makes the exercise meaningless.

An attempt to answer my question has not been made and it does not have anything to do with the query syntax or simplification of it. My question, as I wrote clearly, is "how can I get the cost of a query so I can make a decision whether to show the user a 'refine your search' webpage or proceed by executing the query". Who cares what the query is, I want to get its cost in advance of performing the query so I can decide whether to perform it or whether to reject it and get the user to refine their search criteria.

Barry Received on Mon Oct 08 2007 - 08:19:47 CDT