索引index

来源：互联网发布：手机淘宝客服聊天记录编辑：程序博客网时间：2024/05/16 05:26

索引index

是对数据库表中一列或多列的值进行排序的一种数据结构，是某个表中某个列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单，其为表数据提供快速检索路径，可大大提高数据检索的性能，但相对不利于DML操作，oracle 建议一个表上索引数量不要超过7个。

索引的特点：

大大加快数据检索

索引由oracle自动维护使用，对用户透明

索引独立于数据存放，其是否存在不影响数据本身

索引需要占用物理空间，并随索引的多少和数据量的增加而增加

创建和维护索引需耗费时间，并随索引的多少盒数据量的增加而增加

索引的结构 emp :4kb/record,0.16k for empno & rowid for each record

索引块的数量

每个数据块可用空间=8192*0.9（1-pctfree）*0.88(1-开销)=6488b

SQL> create table cc (a char(8));

Table created.

SQL> begin

2 for i in 1..50000 loop

3 insert into cc values (i);

4 commit;

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> create index ind_cc on cc(a);

Index created.

SQL> analyze table bb computestatistics;

Table analyzed.

SQL> select leaf_blocks,blevel fromdba_indexes where index_name='IND_CC';

LEAF_BLOCKS BLEVEL

----------- ----------

140 1

BB表leaf块中索引条目结构

行头2B

a列长度1B

A列列值8b

Rowid长度1B

Rowid值6B

BB表leaf块的数量=139

SQL> select 6488/18 from dual;

6488/18

----------

360.444444

【一个块放360行记录】

SQL> select 50000/360 from dual;

50000/360

----------

138.888889【50000需要139个Leaf块】

BB表Bran块中索引条目结构

行头2B

A列长度

A列列值8B

叶块地址长度1B

叶块地址4B

BB表Branch块的数量=139/(6488/16)

SQL> select6488/16 from dual;

6488/16

----------

405.5

【一个块中可以放405行的branch索引】

SQL> createtable bb (a char(8));

Table created.

SQL> begin

2 forI in 1..500000 loop

3 insert into bb values (I);

4 endloop;

5 end;

6 /

PL/SQL proceduresuccessfully completed.

SQL> analyze table bb computestatistics;

Table analyzed.

SQL> select leaf_blocks,blevel fromdba_indexes where index_name='IND_BB';

no rows selected

SQL> create index ind_bb on bb(a);

Index created.

SQL> commit;

Commit complete.

SQL> select leaf_blocks,blevel fromdba_indexes where index_name='IND_BB';

LEAF_BLOCKS BLEVEL

----------- ----------

1393 2

按索引键分类：单列索引，组合索引

单列索引是在表的某个列上所建立的索引；

组合索引是在表的某些列上所建立的索引，where 子句可按最左前缀规则引用其部分或全部列；多个单列索引效率低于一个组合索引。

SQL> alter table trans1 drop constraintkey01 ;

Table altered.

SQL> create index index1_trans1 ontrans1 (tranno);

Index created.

SQL> create index index2_trans1 ontrans1 (tranno,itemname,price);

【建立单列索引和组合索引】

Index created.

SQL> select * from trans1;

TRANNO ITEMNAME PRICE SALEDATE

---------- ---------- -----------------------------

7 777 700 2006-01-02 00:00:00

3 333 300 2007-05-30 00:00:00

2 555 200 2007-05-30 00:00:00

6 777 700 2018-01-02 00:00:00

SQL> select * from trans1 wheretranno=2;

TRANNO ITEMNAME PRICE SALEDATE

---------- ---------- -----------------------------

2 555 200 2007-05-30 00:00:00

SQL> select * from trans1 where tranno=3and itemname='333';

TRANNO ITEMNAME PRICE SALEDATE

---------- ---------- ---------- -------------------

3 333 300 2007-05-30 00:00:00

SQL> select * from trans1 where tranno=6and itemname='777' and price=700;

TRANNO ITEMNAME PRICE SALEDATE

---------- ---------- -----------------------------

6 777 700 2018-01-02 00:00:00

【按照最左前缀规则，仅上述查询可利用到索引；（最常用列应放在组合索引最左边）】

SQL>select * from trans1 where itemname='555';

TRANNO ITEMNAME PRICE SALEDATE

---------- ---------- -----------------------------

2 555 200 2007-05-30 00:00:00

按数据结构分类：聚簇索引，非聚簇索引

聚簇索引即建立在聚簇上的索引，创建聚簇索引时，需要对；已有表数据重新进行排序（若表中已有数据），即删除原始的表数据后再将排序结果按物理顺序插回。故聚簇索引建立完毕后，建立聚簇索引的列中的数据已经全部按序排列。

一个表只能包含一个聚簇索引，但该索引可以包含多个列。

在B-树中，聚簇索引的叶层就是数据页。

非聚簇索引类似书本索引，索引与数据存放在不同物理区域，建立非聚簇索引时数据本身并不进行排序。

一个表中可含多个非聚簇索引。

在B-树中，非聚簇索引的叶层仍是索引页，其以指针指向数据页实际页实际存储位置。

SQL> create index index_clu_emp_dept oncluster clu_emp_dept;

create index index_clu_emp_dept on clusterclu_emp_dept

ERROR at line 1:

ORA-00943: cluster does not exist

SQL> create cluster clu_emp_dept(deptnum number(4)) tablespace data01;

Clustercreated.

SQL>CREATE index index_clu_emp_dept on cluster clu_emp_dept;

Indexcreated.

SQL>select cluster_name,tablespace_name,single_table from dba_clusters wherecluster_name='CLU_EMP_DEPT';

CLUSTER_NAME TABLESPACE_NAME SINGLE_TAB

------------------------------------------------------------ ----------

CLU_EMP_DEPT DATA01 N

按唯一性分类：唯一性索引，非唯一性索引

唯一索引保证表中没有两行在定义索引的列上具有重复值，oracle自动为主键和唯一键列创建唯一索引；主键本身就是唯一索引，反之不成立（唯一索引允许一个null值），唯一性索引比非唯一性索引效率高，故在一般情况下，在无重复值的列上应尽量建立唯一性索引。【主键约束不允许为空，唯一约束允许为空】

若为某个表的某个列创建了唯一值索引，则即使这个列没有唯一值约束，也会被强制限制不能插入重复记录。

SQL> select table_name,index_name fromdba_indexes where table_name='TRANS1';

TABLE_NAME INDEX_NAME

------------------------------ ------------------------------

TRANS1 INDEX1_TRANS1

TRANS1 INDEX2_TRANS1

SQL> drop index index1_trans1;

Index dropped.

SQL> drop index index2_trans1;

Index dropped.

SQL> create unique index index_unique ontrans1 (tranno);

Index created.

索引操作

SQL> alter index index_trans1 rename toindex_trans1_01;

alter index index_trans1 rename toindex_trans1_01

ERROR at line 1:

ORA-01418: specified index does not exist

SQL> alter index index_unique rename toindex_trans1_01;

Index altered.

SQL> drop index index_trans1_01;

SQL> select object_name from dba_objectswhere object_type='INDEX';

SQL> SELECT INDEX_NAME FROM DBA_INDEXES;

【查询系统中所有索引】

INDEX_NAME

------------------------------

SYS_C005978

SYS_C005979

SYS_IL0000054779C00002$$

KEY00

SYS_C006203

KEY02

KEY03

KEY04

KEY05

SYS_C006204

SYS_C006205

SYS_C006206

SYS_C006207

IND_BB

IND_CC

SQL> select table_name,index_name fromdba_indexes where table_name='TRANS1';

【查询指定表上所建的全部索引】

SQL> select table_name,index_name fromdba_indexes where table_name='TRANS1';

TABLE_NAME INDEX_NAME

------------------------------------------------------------

TRANS1 INDEX_UNIQUE

SQL> selectindex_name,index_type,table_name,table_type,tablespace_name from dba_indexeswhere index_name='INDEX_TRANS1';

【查询指定索引的类型，索引所对应的表，及存储该索引的表空间】

SQL> selectindex_name,index_type,table_name,table_type,tablespace_name from dba_indexes whereindex_name='INDEX_UNIQUE';

INDEX_NAME INDEX_TYPE TABLE_NAME

------------------------------ ---------------------------------------------------------

TABLE_TYPE TABLESPACE_NAME

----------- ------------------------------

INDEX_UNIQUE NORMAL TRANS1

TABLE SYSTEM

SQL> select index_name,table_name,column_name fromdba_ind_columns whereindex_name='INDEX_UNIQUE';

【查询指定索引所对应的列】

INDEX_NAME TABLE_NAME

------------------------------------------------------------

COLUMN_NAME

----------------------------------------------------------------------------------------------------

INDEX_UNIQUE TRANS1

TRANNO

序列生成器（sequence generator）

用于产生一个不重复的整数值序列供应用使用，序列以指定步长增加或减少序列值，直到达到序列规定的最大或最小值，序列生成器避免了在应用层实现序列而引起的性能瓶颈。

SQL> create sequence seq_test;【建立序列生成器】

Sequence created.

Increment（增量，增加；增额；盈余） by 3【指定增长步长，负数递减，不能为0（默认递增，步长为1）】

Start with 100【指定初始值（默认为1）】

Maxvalue 900 【指定递增序列的最大值（默认为10^27）】

Minvalue 1【指定递减序列的最小值（默认为-10^26）】

Cycle 【到达极值后是否重新循环开始计数（默认NO）】

SQL> select seq_test.nextval from dual;

NEXTVAL

----------

SQL> select seq_test.nextval from dual;

NEXTVAL

----------

【查看序列生成器的下个生成值，且序列生成器递增一个步长】

SQL> select seq_test.currval from dual;

CURRVAL

----------

SQL> select seq_test.currval from dual;

CURRVAL

----------

【查看序列生成器的当前生成值，且序列生成器不递减】

SQL> select * from dba_sequences where sequence_name='SEQ_TEST';

【查看指定序列生成器的相关情况】

SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BYC O

------------------------------------------------------------ ---------- ---------- ------------ - -

CACHE_SIZE LAST_NUMBER

---------- -----------

SYS SEQ_TEST 11.0000E+27 1 N N

20 21

SQL> desc dba_sequences;

Name Null? Type

------------------------------------------------------------- ------------------------------------

SEQUENCE_OWNER NOTNULL VARCHAR2(30)

SEQUENCE_NAME NOT NULL VARCHAR2(30)

MIN_VALUE NUMBER

MAX_VALUE NUMBER

INCREMENT_BY NOT NULL NUMBER

CYCLE_FLAG VARCHAR2(1)

ORDER_FLAG VARCHAR2(1)

CACHE_SIZE NOT NULL NUMBER

LAST_NUMBER

SQL> insert into trans1 values(seq_test.nextval,'itemname',&price,sysdate);

Enter value for price: 890

old 1: insert into trans1 values(seq_test.nextval,'itemname',&price,sysdate)

new 1: insert into trans1 values (seq_test.nextval,'itemname',890,sysdate)

【使用序列生成器】

1 row created.

SQL> select * from trans1;

TRANNO ITEMNAME PRICE SALEDATE

---------- ---------- -----------------------------

7 777 700 2006-01-02 00:00:00

3 333 300 2007-05-30 00:00:00

2 555 200 2007-05-30 00:00:00

6 777 700 2018-01-02 00:00:00

4 itemname 2345 2012-10-08 15:11:37

5itemname 890 2012-10-08 15:12:11

SQL> alter sequence seq_test incrementby 2 maxvalue 88 minvalue 8 nocycle;

alter sequence seq_test increment by 2maxvalue 88 minvalue 8 nocycle

ERROR at line 1:

ORA-04007: MINVALUE cannot be made toexceed the current value

SQL> alter sequence seq_test incrementby 2 maxvalue 88 minvalue 1 nocycle;

【修改生成器未来生成参数，不能修改start值】

Sequence altered.

SQL> select seq_test.nextval from dual;

NEXTVAL

----------

SQL> select seq_test.nextval from dual;

NEXTVAL

----------

SQL> drop sequence seq_test;

Sequence dropped.

SQL> alter sequence seq_test incrementby 5 maxvalue 10 minvalue 3 nocycle;

alter sequence seq_test increment by 5maxvalue 10 minvalue 3 nocycle

ERROR at line 1:

ORA-04007: MINVALUE cannot be made toexceed the current value

SQL> alter sequence seq_test incrementby 5 maxvalue 10 minvalue 0 nocycle;

Sequence altered.

SQL> select seq_test.nextval from dual;

NEXTVAL

----------

SQL> alter sequence seq_test incrementby 5 maxvalue 10 minvalue 0 nocycle;

Sequence altered.

SQL> select seq_test.nextval from dual;

NEXTVAL

----------

SQL> alter sequence seq_test incrementby 5 maxvalue 10 minvalue -2 nocycle;

Sequence altered.

SQL> alter sequence seq_test incrementby 5 maxvalue 10 minvalue -3 cycle;

alter sequence seq_test increment by 5maxvalue 10 minvalue -3 cycle

ERROR at line 1:

ORA-04013: number to CACHE must be less thanone cycle

SQL> alter sequence seq_test incrementby 5 maxvalue 666666666 minvalue -3 cycle;

Sequence altered.

SQL> drop sequence seq_test;

【删除序列生成器】

Sequence dropped.

数据库链路（Link）

是在分布式数据库中用来表示到一个远程数据库的连接路径的数据库对象。用来方便地直接调用远程数据库中各种对象。

[oracle@desktop241 admin]$ vim tnsnames.ora

# tnsnames.ora Network Configuration File:/u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

abc=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = desktop241.example.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora10)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

SQL> create public database link linkabconnect to system identified by "zouhao" using 'abc';

【建立数据库链路】

Database link created.

SQL> select * from iii@linkab;

【通过数据库链路操作远程数据库对象】

no rows selected

SQL> drop * from iii@linkab;

no rows selected

SQL> select * from dba_db_links;

【查询数据库链路信息】

OWNER DB_LINK USERNAM HOST CREATED

------- ----------------------------------------------- -------------------- -------------------

PUBLIC LINKABC.REGRESS.RDBMS.DEV.US.ORACLE.COM SCOTT abc 2012-10-08 17:20:10

PUBLIC LINKAB.REGRESS.RDBMS.DEV.US.ORACLE.COM SYSTEM abc 2012-10-08 17:25:44

SQL> drop public database link linkabc;

【删除数据库链路】

Database link dropped.

SQL> drop public database link linkab;

Database link dropped.

同义词（synonym）

同义词是为数据库对象或其他同义词指定的别名。公用同义词可被数据库中所有用户存取，专用同义词仅能被指定用户及其他被授权的用户存取。

同义词的特点：

可屏蔽对象的名字及其持有者；

为分布式数据库的远程对象提供位置透明性；

为用户简化sql语句。

SQL> select table_name from dba_tableswhere table_name='TEST';

【查系统中存在两个test表】

TABLE_NAME

------------------------------

TEST

SQL> select * from scott.test;

【查询时以用户名区分】

----------

bbb

SQL> create public synonym stest for scott.test;

【建立同义词屏蔽用户信息】

Synonym created.

SQL> select * from stest;

【注意须先有目标表的访问权限，才能通过该目标表上所建的公有同义词访问该目标表，此处sys用户先有kitty.test的访问权限，故可通过stest访问该表】

----------

Bbb

SQL> create public synonym ttest fortest@linck2;

【对远程表建同义词屏蔽数据链路信息】

Synonym created.

SQL> select * from dba_synonyms wheresynonym_name='STEST';

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME

------- ------------------------------------------------------------ ------------------------------

DB_LINK

----------------------------------------

PUBLIC STEST SCOTT TEST

SQL> select * from dba_synonyms wheresynonym_name='TTEST';

【查询系统中指定同义词的定义】

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME

------- ------------------------------------------------------------ ------------------------------

DB_LINK

----------------------------------------

PUBLIC TTEST TEST

LINCK2.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> drop public synonym stest;

Synonym dropped.

SQL> drop public synonym rest;

Synonym dropped.

SQL> drop public synonym ttest;

Synonym dropped.【删除同义词】

程序单元（Program unit）

是指存储过程procedure,函数function，包package和触发器trigger。

一个过程和函数，是由SQL语句和PL/SQL语句组合在一起，为执行某一个任务的一个可执行单位（命名PL/SQL块）。一个过程或函数可被建立，在数据库中存储其编译形式，可由用户或数据库应用所执行。过程和函数差别在函数总返回单个值给调用者，而过程没有值返回给调用者。

包提供相关的过程、函数、变量和其它包结构封装起来并存储在一起的一种方法，以方便这些程序单元的组织，允许管理者和应用开发者利用该方法组织如此的程序，以提供更多的功能和提高性能。

触发器是用来在某些条件被满足时自动被触发进行指定的数据处理的程序单元。

程序单元特点：

具有可重用性

抽象和数据隐藏

透明性和安全性

Procedure存储过程

SQL> host vim pro_del.sql

SQL> get pro_del.sql

1 create or replace procedurepro_del

2 (v_no in emp.empno%type) is

3 begin

4 delete from emp whereempno=v_no;

5 dbms_output.put_line('theemployee'||v_no||'has deleted!');

6*end pro_del;

7 /

Procedure created.

SQL> create table emp as select * fromscott.emp;

Table created.

SQL> exec pro_del(7934);

the employee7934has deleted!

【调用指定存储过程（或在其他程序单元中直接调用）】

PL/SQL procedure successfully completed.

SQL> select * from dba_source wherename='PRO_DEL';

OWNER NAME TYPE LINE TEXT

------- ---------- ------------ --------------------------------------------------

SYS PRO_DEL PROCEDURE 1 procedure pro_del

SYS PRO_DEL PROCEDURE 2 (v_no in emp.empno%type) is

SYS PRO_DEL PROCEDURE 3 begin

SYS PRO_DEL PROCEDURE 4 delete from emp where empno=v_no;

SYS PRO_DEL PROCEDURE 5 dbms_output.put_line('the employee'||v_n

o||'has deleted!');

SYS PRO_DEL PROCEDURE 6 end pro_del;

6 rows selected.

SQL> select * from dba_procedures whereobject_name='PRO_DEL';

OWNER OBJECT_NAMPROCEDURE_ OBJECT_ID SUBPROGRAM_IDOVERLOAD OBJECT_TYPE AGG PIP

------- ---------- ---------- ----------------------- ---------- ------------------- --- ---

IMPLTYPEOW IMPLTYPENA PAR INT DET AUTHID

---------- ---------- --- --- ---------------

SYS PRO_DEL 55799 1 PROCEDURE NO NO

NO NO NO DEFINER

SQL> drop procedure pro_del;

【删除指定存储过程】

Procedure dropped.

SQL> insert into employees2(salary,employee_id) values (100,120);

1 row created.

SQL> get emp.sql

1 declare

2 emp_num number(6) := 120;

3 bonus number(6) := 100;

4 merit number(4) := 50;

5 procedure raise_salary (emp_id number,

6 amount in number default 100,

7 extra in number default 50)is

8 begin

9 update employees2 set salary=salary+amount+extra

10 where employee_id=emp_id;

11 end raise_salary;

12 begin

13 raise_salary(emp_num);

14*end;

SQL> /

PL/SQL procedure successfully completed.

SQL> select * from employees2;

EMPLOYEE_ID BONUS MERIT SALARY

----------- ---------- --------------------

120 250

SQL> host vim emp.sql

SQL> get emp.sql

1 declare

2 emp_num number(6) := 120;

3 bonus number(6) := 100;

4 merit number(4) := 50;

5 procedure raise_salary (emp_id number,

6 amount in number default 100,

7 extra in number default 50)is

8 begin

9 update employees2 setsalary=salary+amount+extra

10 where employee_id=emp_id;

11 end raise_salary;

12 begin

13 raise_salary(emp_num,extra=>25);

14*end;

SQL> /

PL/SQL procedure successfully completed.

SQL> select * from employees2;

EMPLOYEE_ID BONUS MERIT SALARY

----------- ---------- --------------------

120 375

IN参数（默认）可将值传给被调用子程序；Out参数将值返回给予程序的调用者；

INOUT参数可将初始值传送给被调用的子程序，并将修改后的值返回给调用者。

SQL> select * from employees;

EMPLOYEE_ID SALARY BONUS MERIT

----------- ---------- --------------------

120 225 50 100

1 100

2 200

3 300

SQL> get emp.sql

1 declare

2 emp_num number(6) := 120;

3 bonus number(6) := 100;

4 merit number(4) := 50;

5 procedure raise_salary (emp_id number,

6 amount in number default 100,

7 extra in number default 50)is

8 begin

9 update employees setsalary=salary+amount+extra

10 where employee_id=emp_id;

11 end raise_salary;

12 begin

13 raise_salary(&emp_num,&amount,&extra);

14*end;

SQL> /

Enter value for emp_num: 1

Enter value for amount: 100

Enter value for extra: 100

old 13: raise_salary(&emp_num,&amount,&extra);

new 13: raise_salary(1,100,100);

PL/SQL procedure successfully completed.

SQL> select * from employees;

EMPLOYEE_ID SALARY BONUS MERIT

----------- ---------- --------------------

120 225 50 100

1 300

2 200

3 300

SQL> select func_fn(4) from dual;

【调用指定函数（或在其他程序单元中直接调用）】

FUNC_FN(4)

----------

SQL> set serverout on

SQL> get func_fn.sql

1 create or replace functionfunc_fn(n in number)

2 return number

3 is

4 begin

5 if n=0 then return 1;

6 else return n*func_fn(n-1);

7 end if;

8* end func_fn;

9 /

Function created.

SQL> execdbms_output.put_line(func_fn(4));

PL/SQL procedure successfully completed.

SQL> select * from dba_source wherename='FUNC_FN';

OWNER NAME TYPE LINE TEXT

------ ---------- ------------ --------------------------------------------

SYS FUNC_FN FUNCTION 1 function func_fn(n in number)

SYS FUNC_FN FUNCTION 2 returnnumber

SYS FUNC_FN FUNCTION 3 is

SYS FUNC_FN FUNCTION 4 begin

SYS FUNC_FN FUNCTION 5 ifn=0 then return 1;

SYS FUNC_FN FUNCTION 6 elsereturn n*func_fn(n-1);

SYS FUNC_FN FUNCTION 7 endif;

SYS FUNC_FN FUNCTION 8 endfunc_fn;

8 rows selected.

SQL> drop function func_fn;

【删除指定函数，从内存中】

Function dropped.

Return statements

1. In functions,a RETURN statement must contain an expression,whichis evaluated (评估)when the RETURNstatement is executed.

2. In procedures,a RETURN statement does not return a value andso cannot contain anexpression.the statementreturns control to the caller before the end of the procedure.