Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Stored Outline Not Used

Stored Outline Not Used

From: <thtsang_yh_at_yahoo.com.hk>
Date: 19 Sep 2006 01:03:54 -0700
Message-ID: <1158653034.022793.74950@b28g2000cwb.googlegroups.com>


I am maintaining an old system. One stupid SQL statement used an "IN list" to select some records and relies on the output order, but it does not use an order by clause! It worked with the RBO. However, if CBO is used, the order is different and cause program error. I don't want to modify the source code. (I understand that the RBO is dying but the system is dying too, so don't worry about this) Therefore, I am trying to create a stored outline. I did something like:

alter session set optimizer_mode=rule;

create outline myoutline as
select something from stupid_sql ...;

Afterwards, on the development envrionment, everything works as expected. When stored outline is enabled, the SQL outputs in correct order.

alter session set use_stored_outlines=true; select something from stupid_sql ...;

However, in QA environment, the outline seems not used. It still return the order as if using CBO.
What's more strange is that if SQL_TRACE is used, the result is as desired!

alter session set use_stored_outlines=true; select something from stupid_sql ...;
-- Outputs in undesired order

alter session set sql_trace=true;
select something from stupid_sql ...;
-- Outputs in desired order

alter session set sql_trace=false;
select something from stupid_sql ...;
-- Outputs in undesired order again

Am I missing something? Or stored outline is not supposed to work in such situation? Or there is a bug?

OS: Redhat Enterprise 4.0
Oracle: 10g Release 2, 32 bit Received on Tue Sep 19 2006 - 03:03:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US