oracle实现数据目录共享,oracle 目录

TABLESPACE 表空间名;

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

索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高效率而引入，是一个独立于表的对象，可以存放在与表不同的表空间中。索引记录中存有索引关键字和指向表中数据的指针(地址)。对索引进行的I/O操作比对表进行操作要少很多。索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引。查询

分类可以按逻辑设计和物理实现来分类。

索引逻辑分类

单列索引：基于一列的操作

多列索引：组合索引，最多为32列。组合索引的列不一定与表中列顺序相同。

惟一索引：列的值各不相同。

非惟一索引：列的值允许相同。

基于函数的惟一索引：利用表中一列或多列基于函数表达式所创建的索引。既可以是B-树，也可以是位图索引。

索引物理分类

分区或非分区索引，非分区既可以是B－树，也可以是位图索引。

B-树：包括正常或反转关键字索引，反转关键字在数据库优化中介绍。

位图索引

索引的存储方式

虽然所有索引都使用 B 树结构，但术语“B 树索引”通常与存储每个关键字的行标识列表的索引关联。

B 树索引结构

至上而下，是根结点、分枝结点及叶子结点，叶子结点中有指向表中数据行的索引行。叶子结点被双向链表在一起，以方便按索引关键字升序或降序扫描。

索引的顶部为根，其中包含指向索引中下一级掂，下一级为分枝块，分枝块又指向索引中下一级的块，最低一级为叶节点，其中包含指向表行的索引项。叶块为双重链接，有助于按关键字值的升序和降序扫描索引。

索引项叶节点的格式

索引项由以下三部分组成：

? 项标题(entry header)，存储列数和锁定信息

? 关键字列的“长度- 值”(length-value pairs) ，必需成对出现，定义了列长度，紧跟在列长度之后的就是列的值。

? 行的行标识(RowID)， 包含关键字值。

索引项叶结点弟地征

在非分区表上的 B 树索引中：

? 如果多行倪有相同的关键字值，并且索引没有被压缩，则关键字值重复存放。

? 没有索引项与所有关键字列都为 NULL 的行对应，即如果某列值为Null，则不存储相应的索引项。如果Where子句中索引的所在列值为null，Oracle将不使用索引进行全表扫描。

? 因为所有行都属于同一段，所以使用受限行标识指向表中的行，使用RowID可以节省索引存储空间。

DML操作对索引的影响

当在表上执行 DML 操作时，Oracle 服务器将自动维护所有的索引，下面解释 DML命令对索引的影响：

? 插入(Insert)操作导致在适当的块中插入索引项。

? 删除(Delete)行只导致逻辑删除索引项，删除的行所用的空间不能用于新项，直到删除块中的所有项。

? 更新(Update)操作将选删除，再插入，除了在创建时，其它任何时候PCTFREE 设置对索引都没有影响，即使索引块空间少于 PCTFREE 指定的空间，仍可以向索引块添加新项。

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

1什么是索引

索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据。具体来说：

1.1索引类似书的目录结构

1.2Oracle的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度

1.3索引直接指向包含所查询值的行的位置，减少磁盘I/O

1.4与所索引的表是相互独立的物理结构

1.5Oracle自动使用并维护索引，插入、删除、更新表后，自动更新索引

1.6语法：CREATE INDEX index ON table (column[, column]...);

1.7Oracle存储索引的数据结构是B-树，位图索引也是如此，只不过是叶子节点不同B-数索引；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。

2什么是ROWID

ROWID存储了row在数据文件中的具体位置：64位编码的数据，A-Z, a-z, 0-9, +,和/;row在数据块中的存储方式:比如,ROwID=OOOOOOFFFBBBBBBRRR,含义是：

OOOOOO：data object number,对应dba_objects.data_object_id；

FFF：file#,对应v$datafile.file#；

BBBBBB：block#；

RRR：row#；

3何时使用索引

3.1查询返回的记录数

3.2排序表<40%

3.3非排序表<7%

3.4表的碎片较多(频繁增加、删除)

4使用索引的目的

4.1加快查询速度

4.2减少I/O操作

4.3消除磁盘排序

5了解索引的工作原理

表：emp

目标：查询Frank的工资salary

建立索引：create index emp_name_idx on emp(name);

6索引的分类

6.1唯一索引

6.1.1何时创建：当某列任意两行的值都不相同

6.1.2当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立

6.1.3语法：CREATE UNIQUE INDEX index ON table (column);

6.2组合索引

6.2.1何时创建：当两个或多个列经常一起出现在where条件中时，则在这些列上同时创建组合索引

6.2.2组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面

6.2.3演示(组合列，单独列)

6.3位图索引

6.3.1何时创建：列中有非常多的重复的值时候。例如某列保存了“性别”信息。Where条件中包含了很多OR操作符。较少的update操作，因为要相应的跟新所有的bitmap

6.3.2结构：位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。

6.3.3优点：位图以一种压缩格式存放，因此占用的磁盘空间比标准索引要小得多

6.3.4语法：CREATE BITMAP INDEX index ON table (column[, column]...)

6.3.5create table bitmaptable as select * from indextable where owner in('SYS','PUBLIC');

6.4基于函数的索引

6.4.1何时创建：在WHERE条件语句中包含函数或者表达式时

6.4.2函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。

6.4.3语法：CREATE INDEX index ON table (FUNCTION(column));

7管理索引的准则

7.1在表中插入数据后创建索引。在用SQL*Loader或import工具插入或装载数据后，建立索引比较有效；

7.2索引正确的表和列

7.2.1经常检索排序大表中40%或非排序表7%的行，建议建索引；

7.2.2为了改善多表关联，索引列用于联结；

7.2.3列中的值相对比较唯一；

7.2.4取值范围(大：B*树索引，小：位图索引)；

7.2.5Date型列一般适合基于函数的索引；

7.2.6列中有许多空值，不适合建立索引

7.3为性能而安排索引列

7.3.1经常一起使用多个字段检索记录，组合索引比单索引更有效；把最常用的列放在最前面，例：dx_groupid_serv_id(groupid,serv_id)，在where条件中使用groupid或groupid,serv_id，查询将使用索引，若仅用到serv_id字段，则索引无效；合并/拆分不必要的索引。

7.4限制每个表索引的数量

7.4.1一个表可以有几百个索引(你会这样做吗？)，但是对于频繁插入和更新表，索引越多系统CPU，I/O负担就越重；建议每张表不超过5个索引。

7.5删除不再需要的索引

7.5.1索引无效，集中表现在该使用基于函数的索引或位图索引，而使用了B*树索引；

7.5.2应用中的查询不使用索引；

7.5.3重建索引之前必须先删除索引，若用alter index … rebuild重建索引，则不必删除索引。

7.6索引数据块空间使用

创建索引时指定表空间，特别是在建立主键时，应明确指定表空间；合理设定pctfress，注意：不能给索引指定pctused；。估计索引的大小和合理地设置存储参数，默认为表空间大小，或initial与next设置成一样大。

7.7考虑并行创建索引

对大表可以采用并行创建索引，在并行创建索引时，存储参数被每个查询服务器进程分别使用，例如：initial为1M，并行度为8，则创建索引期间至少要消耗8M空间；

7.8考虑用nologging创建索引

7.8.1对大表创建索引可以使用nologging来减少重做日志；

7.8.2节省重做日志文件的空间；

7.8.3缩短创建索引的时间；

7.8.4改善了并行创建大索引时的性能。

8怎样建立最佳索引

8.1明确地创建索引

create index index_name on table_name(field_name)tablespace tablespace_namepctfree 5initrans 2maxtrans 255storage(minextents 1maxextents 16382pctincrease 0);

8.2创建基于函数的索引

常用与UPPER、LOWER、TO_CHAR(date)等函数分类上，例：create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;

8.3创建位图索引

对基数较小，且基数相对稳定的列建立索引时，首先应该考虑位图索引，例：create bitmap index idx_bitm on class (classno) tablespace tablespace_name;

8.4明确地创建唯一索引

可以用create unique index语句来创建唯一索引，例：create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;

8.5创建与约束相关的索引

可以用using index字句，为与unique和primary key约束相关的索引，例如：alter table table_nameadd constraint PK_primary_keyname primary key (field_name)using index tablespace tablespace_name；据产生大量db file sequential read锁等待；

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

索引分类：

唯一索引，作用是数据约束，保证数据唯一，还有就是数据索引，提高查询效率

一般索引，只有数据索引的作用。

唯一索引：

Sql代码

createuniqueindexidx_employee_empnameonemployee(empname);

Sql代码

 

createuniqueindexidx_employee_empnameonemployee(empname);

create unique index idx_employee_empname on employee(empname);

一般索引：

Sql代码

createindexidx_employee_addressonemployee(address);

createuniqueindexidx_employee_field1_field2onemployee(field1,field2);

Sql代码

 

createindexidx_employee_addressonemployee(address);

createuniqueindexidx_employee_field1_field2onemployee(field1,field2);

create index idx_employee_address on employee(address);

create unique index idx_employee_field1_field2 on employee(field1,field2);

函数索引：

Sql代码

createindexidx_product_nvl_priceonproduct(nvl(price,0.0));

Sql代码

 

createindexidx_product_nvl_priceonproduct(nvl(price,0.0));

create index idx_product_nvl_price on product(nvl(price,0.0));

删除索引：

Sql代码

dropindexidx_employee_empname;

Sql代码

 

dropindexidx_employee_empname;

drop index idx_employee_empname;

指定索引表空间

Sql代码

createindexidx_employee_addressonemployee(address) tablespace indexs;

Sql代码

 

createindexidx_employee_addressonemployee(address) tablespace indexs;

create index idx_employee_address on employee(address) tablespace indexs;

查看索引

Sql代码

select*fromuser_indexeswheretable_name ='SHOP_PD'

Sql代码

 

select*fromuser_indexeswheretable_name ='SHOP_PD'