Re: Query too slow ... control execution order with optimizer hints?

Chris Neubauer (water.world_at_gmx.de) wrote:
: Hi,

: I try to create a view, based on two existing views in my database. The
: output of the two existing views are about 200 records each, in less than
: one second. Now the new query sould join the first two views, but this works
: very slow, I get the result after 1800 seconds. It seems to depend on the
: execution order, is there any way to force oracle first to execute the
: sub-views and then to join the two resultsets? Are there any optimizer-hints
: to control this behavior? And if so, where and how do I have to place them?

: select a.field1, sum(b.field2)
: from query1 a, query2 b
: where a.key=b.key
: group by a.field1

: Regards

$0.02

The two underlying views will return data as fast as oracle can retrieve the data.

However, the join view has to wait until the data from the two views aligns on the joined columns. That alignment might not happen until much later - e.g. if the two views present the joined columns in different orders then oracle may have to read both underlying views in their entirety and then sort them (both) before it can make the rows line up for the join.

Understanding how oracle is doing the join is important. Explain plan is the usual tool to know what oracle is doing.

On a more personal note, you need to do a mental excersize - if you can't walk yourself conceptualy through the data and indexes to retrieve the data efficiently yourself then neither can oracle.

Sometimes the only work around is to use materialized views.

In previous versions of oracle, the where clause of an upper level view had minimal impact on the efficiency of the underlying views. (So for example if an upper view restricted the data to one item, then that restriction did not get "pushed down" into the lower views, so they still appear to take the time necessary to retrieve all their normal amount of data). This was my observation, not based on input from oracle. I have no idea if the newest version of oracle has this type of optimization.

An extreme solution, based on the above observation, is that if the underlying views present lots of data that will be thrown away later, is to find away to restrict the data in the underlying views. E.g. on one app our underlying views are joined with a parameter table indexed by session id. Each view showns a very flexible subset of the data, and upper level joins never have problems because they join very little data. Received on Mon Dec 16 2002 - 12:27:00 CST