10G,LINUX as3

更改优化器
alter session set optimizer_boal=choose;－－first_rows

创建OUTLINE,看执行计划，如果计划合适，就为SQL创建OUTLINE
create or replace outline myoutline
for category mycategory
on
select empno ,ename from emp where empno>0;
或者
alter session set create_stored_outlines=true;
alter session set create_stored_outlines=false;

-----注意，以上两种方式产生的OUTLINE有区别： 用例子来说明。
<见CURSOR_CHARING=FORCE与大刚>

让SESSION使用OUTLINE
alter session set use_stored_outline=mycategory;
alter session set use_stored_outline=false;

重建OUTLINE
select * from user_outlines;
alter session set optimizer_boal=first_rows;
alter outline outline_name rebuild;

查看大刚所使用到的索引
select * from dba_outline_hints ;

让OUTLINE用上HINT
create or replace view dept as
select /*+ use_hash(dept) */ from scott.dept
create or replace view emp as
select /*+ use_hash(emp) */ * from scott.emp

create or replace outline my_outline
for category my_category
on select * from emp,dept where emp.deptno=dept.deptnot;

让用户登陆后就用OUTLINE
create or replace trigger hyf_logon
after logon on database
begin
if (user='HYF') then
execute immediate 'alter session set use_stored_outlines=hyfcategory';
endif;
end;

大刚的改名
alter outline my_outline rename to NEW_NAME;

改变类别
alter outline outline_name chang category to new_category_name;
exec outln_pkg.update_by_cat(old_category_name,new_category_name);
用于把一个类别并入另一个类别

删除大刚
drop outline outline_name;
exec outln_pkg.drop_unused; --删除所有使用过的大刚
exec outln_pkg.drop_by_cat('categoryname'); --按类别删除
－－注意，DROP USER 不删除大刚

CURSOR_CHARING=FORCE与大刚
SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select * from Test2 where object_name='CON$';
OWNER OBJECT_NAME
-------------- -------------------
SYS CON$

SQL> create or replace outline my_outline1
2>for category mycat1
3>on
4>select * from Test2 where object_name='CON$' ;

Outline created.

SQL> alter session set create_stored_outlines=true; --隐式产生大刚

Session altered.

SQL> select * from Test2 where object_name='CON$' ;

OWNER OBJECT_NAME
-------------- -------------------
SYS CON$