Re: Please help with a Baffling SQL problem

Hi Nasir,
A few remarks
- as (select ... ) D, is this legal SQL. Maybe it is, but then the syntax has changed
- most likely the problem is caused by the IN clause and probably you can't avoid it, right? The optimizer has usually no understanding of what is in the in list, it doesn't see the structure. With the rule based optimizer sometimes it was transformed in office_id = a or office_id = b, this happened more or less only if the table with the where clause was driving ie the first to be selected. What you need to do here is, in a sqlplus session issue the following statement before you run that query set autotrace on explain.
This will dump the query plan to the standard output, after which you can safely interrupt the statement, as the plan goes first. If you need help with interpreting it, I'm willing to do this.

Hth
Sybrand Bakker, Oracle DBA

nnoor wrote:

> Hi,
>
> I am having a problems with the way a few queries are
> behaving and would appreciate help in understanding the
> causes.
>
> We are running a dedicated NT4 box (P200 Pro, 512MB RAM,
> Ultra SCSII drives) as the Oracle server version 7.3.
>
> 1).
> I have a query of the form:
> Select x,y,x from A, B, C,
> (select d,e,f from blah) D
> where ...
>
> Note that a sub-query is part of the "from" list. If I
> add a field in the final where clause [e.g. WHERE
> OFFICE_ID in (1,2,3,4,5)] the query slows to the crawl.
> If I take this field out of the where clause, the results
> come back in a couple of seconds. The field (Office_Id in
> this case) has an index defined on it. If I turn this
> query into a view (with out the OFFICE_ID field in the
> WHERE clause), it still is snappy and returns results in a
> couple of seconds. Adding OFFICE_ID in the WHERE clause in
> the query against the view does NOT slow it down!
> e.g. select * from above_query_view where office_id in (...).
>
> That is very puzzling to me. I thought that the Oracle had
> ability to modify the view query on the fly to incorporate
> user criteria. QUESTION: Why did having an indexed field in
> the WHERE clause of the original query made it a dog? Why did
> having the same field in the WHERE clause of the query that
> ran against the view did NOT have any negative impact?
>
> 2).
> If we run a query against the above View and add an ORDER BY
> clause, it will send it to a never never land. The result
> set returned by the query contains only 2500 records and
> doing an order on those records should not take forever.
> As a test, I ran a query on a table which returned a
> non-indexed field sorted, the result set had 45,000 records
> in it and it only took 2 seconds to come back with the
> result. Why is it taking 10-15 minutes to sort only 2500
> records. Taking the ORDER BY clause out returns the response
> time back to under a couple of seconds.
> The View is based on the following query:
>
> Select a, b, c, calc_field
> From x, y, z, (select calc_field, ... from ...)
> Where ...
>
> Following query against the View is FAST:
> Select * from above_query_view
>
> Following is a dog:
> Select * from above_query_view order by CALC_FIELD
>
> What gives? It is very puzzling to me.
>
> Thanks for all your help!
>
> Regards,
> Nasir (nnoor_at_cris.com)
Received on Thu Sep 03 1998 - 12:45:13 CDT