Query slows down when run from inside a view

tuningGurus hello,

I have a query (I call it query1) which is about half a page long.

It's mildly complex; it has several joins and some inline views.

I got it to a state where it runs in about 8 seconds.

Next, I moved the query into a view.

When I SELECT * from the view, it takes 70 seconds.

(I call this query2).

So I used AUTOTRACE inside sqlplus to investigate further.

query1 has a very different execution plan than query2.

Is it possible to tell the Oracle optimizer to use the query1 Execution Plan when it sees query2?

Here is the banner from my Oracle kernel:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options

-jd Received on Fri Dec 03 2004 - 20:38:32 CST