index_result_ok.txt

orale培训教材包括了所有的sql说明和实例

目的：直观的表现出index的作用。不要求掌握测评方法。

--@oracle_home\dbms\admin\utlxplan.sql
--在当前schema生成plan_table;


--建立没有index的表
SQL> 
drop table person;
create table person (id number(10),name varchar2(20));

表已创建。


--使用plsql插入100000条纪录.
set serveroutput on

使用truncate_insert_ok.txt中的插入语句

SQL> declare
  2  n_loop number;
  3  s_time date;
  4  --s_time varchar2;
  5  e_time date;
  6  begin
  7    select sysdate into s_time from dual ;
  8    for n_loop in 1..100000 loop
  9   insert into person values(n_loop,'name'||n_loop);
 10   commit;
 11    end loop;
 12    select sysdate into e_time from dual ;
 13    dbms_output.put_line(to_char((e_time),'hh:mi:ss'));
 14    dbms_output.put_line(to_char((s_time),'hh:mi:ss'));
 15  end;
 16  /
08:17:21
08:14:04

PL/SQL 过程已成功完成。用时3分17秒


--分析查询语句
--分析表analyze table 表名statistics
SQL>analyze table person compute statistics; 

--测试查询1，删空分析结果存储表
delete from plan_table;
commit;
explain plan for select * from person where name='1234name';

select operation,cost from plan_table;

--测试查询2
delete from plan_table;
commit;
explain plan for select * from person where id=1234;

select operation,cost from plan_table;




----------------------------------------------------------------------------------------
--建立带index的table
drop table person;
SQL> create table person (id number(10) ,name varchar2(20));
表已创建。

create index ind_1 on person(id);

--插入100000条纪录
SQL> declare
  2  n_loop number;
  3  s_time date;
  4  --s_time varchar2;
  5  e_time date;
  6  begin
  7    select sysdate into s_time from dual ;
  8    for n_loop in 1..100000 loop
  9   insert into person values(n_loop,'name'||n_loop);
 10   commit;
 11    end loop;
 12    select sysdate into e_time from dual ;
 13    dbms_output.put_line(to_char((e_time),'hh:mi:ss'));
 14    dbms_output.put_line(to_char((s_time),'hh:mi:ss'));
 15  end;
 16  /
08:27:52
08:23:03

PL/SQL 过程已成功完成。使用idnex,插入用时4分49秒



analyze table person compute statistics;
--分析

--测试查询1,删空分析结果存储表
delete from plan_table;
commit;


explain plan for select * from person where name='1234name';

--结果
SQL> select operation,cost from plan_table;

OPERATION                            COST
------------------------------ ----------
TABLE ACCESS                           42
SELECT STATEMENT                       42
--没有index的花费





--测试查询2
--索引起作用时

--clear plan table
delete from plan_table
commit;



SQL> explain plan for select * from person where id=1234;

已解释。

SQL> select operation ,cost from plan_table;

OPERATION                            COST
------------------------------ ----------
TABLE ACCESS                            2
INDEX                                   1
SELECT STATEMENT                        2
--有index的花费,cost大量降低!




--验证：where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的，
--因此它不得不进行表搜索，而没有使用该列上面的索引

delete from plan_table;
commit;

explain plan for select * from person where id/3=111;

select operation,cost from plan_table;

---result---------------------------
SQL> select operation,cost from plan_table;

OPERATION                            COST
------------------------------ ----------
SELECT STATEMENT                       42
TABLE ACCESS                           42

--没有使用该列上面的索引.因为id/3，对列的任何操作结果都是在SQL运行时逐行计算得到的

SQL> delete from plan_table;

已删除2行。




explain plan for select * from person where id=111*3;

select operation,cost from plan_table;

已解释。

SQL> select operation ,cost from plan_table;

OPERATION                            COST
------------------------------ ----------
SELECT STATEMENT                        2
TABLE ACCESS                            2
INDEX                                   1

--index启用，cost大量降低!









--复合索引   见本目录  多字段索引测评结果.txt-------------------------------------------------------------
--删除基于id的索引
drop index ind_1;
--建立基于id,name的ind_2
create index ind_2 on person (id,name);

analyze table person compute statistics;

--使用两字段index的第2字段 name
explain plan for select * from person where name='name123';
select cost, operation from plan_table;
explain plan for select * from person where id='123';
select cost, operation from plan_table;


explain plan for select * from person where substring(id,1,2) = 11;


--思考：建立基于name的索引ind_2
--选取name含123的person
--where substring(name,5,3)='123';
--另name like '%123%';