Please help with a Baffling SQL problem

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:21:45 CDT