准备

　　为了更好的理解这些提示，先建立以下一些表。为了更加容易理解，会对一些常用提示给出例子，并给出对比，加深对提示的理解。

create table t_huang (f1_num number(10)  not  null,


f2_char varchar2(20)  not  null,

f3_numnull number(10) null,

f4_num  number(10),

f5_char  varchar2(20));

alter table t_huang

   add constraint PK_T_HUANG primary key (f1_num)

using index;

create index ix_huang_f2_char  on t_huang (

   f2_char ASC

);

create index ix_huang_f23_char  on t_huang (

   f2_char, f3_numnull ASC

);

create index ix_huang_f4_num  on t_huang (

   f4_num DESC

);

begin

for i in 1..1000000 loop

    insert into t_huang values(

        i, to_char(dbms_random.random()),

dbms_random.random(), dbms_random.random(),

to_char(dbms_random.random()));

end loop;

commit;

end;

/





create table t_wei (f1_num2 number(10)  not  null,


f2_char2 varchar2(20)  not  null,

f3_numnull2 number(10) null,

f4_num2  number(10),

f5_char2  varchar2(20));

alter table t_wei

   add constraint PK_T_WEI primary key (f1_num2)

using index;

create index ix_wei_f234_char  on t_wei (

   f2_char2, f3_numnull2, f4_num2 ASC

);

begin

for i in 1..10000 loop

    insert into t_wei values(

        i, to_char(dbms_random.random()),

dbms_random.random(), dbms_random.random(),

to_char(dbms_random.random()));

end loop;

commit;

end;

/





create table t_fuyuncat (f1_num3 number(10)  not  null,


f2_char3 varchar2(20)  not  null,

f3_numnull3 number(10) null,

f4_num3  number(10),

f5_char3  varchar2(20));

alter table t_fuyuncat

   add constraint PK_T_FUYUNCAT primary key (f1_num3)

using index;

create index ix_fuyuncat_f23_char  on t_fuyuncat (

   f2_char3, f3_numnull3 ASC

);

begin

for i in 1..100000 loop

    insert into t_fuyuncat values(

        i, to_char(dbms_random.random()),

dbms_random.random(), dbms_random.random(),

to_char(dbms_random.random()));

end loop;

commit;

end;

/

　　并且做好以下准备工作：

$sqlplus “/as sysdba”

SQL>@/opt/oracle/product/9.2/sqlplus/admin/plustrce

SQL>grant plustrace to hw;

SQL>conn hw/hw


SQL>@/opt/oracle/product/9.2/rdbms/admin/utlxplan

SQL>set timing on


SQL>set autot trace