Stored Outline Not Used

Giganews Newsgroups
Subject: Stored Outline Not Used
Posted by:  thtsang_…@yahoo.com.hk
Date: 19 Sep 2006

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

Replies