index_result_ok.txt

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

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


--建立没有index的表
SQL> 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 过程已成功完成。


--分析查询语句
--分析表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
SQL> create table person (id number(10) primary key,name varchar2(20));

表已创建。

--插入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 过程已成功完成。




--测试查询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的花费