Just a quick thought - is your sort_area_size big enough?
Vaughan
nnoor wrote in message <6smj79$o6e_at_journal.concentric.net>...
>
>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 Fri Sep 04 1998 - 08:02:48 CDT
