Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Stored Outline Not Used
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
![]() |
![]() |