精华内容
下载资源
问答
  • Oracle组合索引的使用详解

    千次阅读 2018-08-11 17:49:02
    Oracle中可以创建组合索引,即同时包含两个或两个以上列的索引。在组合索引的使用方面,Oracle有以下特点: 1、 当使用基于规则的优化器(RBO)时,只有当组合索引的前导列出现在SQL
    原文地址为:Oracle中组合索引的使用详解

    Oracle中可以创建组合索引,即同时包含两个或两个以上列的索引。在组合索引的使用方面,Oracle有以下特点:

        1、 当使用基于规则的优化器(RBO)时,只有当组合索引的前导列出现在SQL语句的where子句中时,才会使用到该索引;

        2、 在使用Oracle9i之前的基于成本的优化器(CBO)时, 只有当组合索引的前导列出现在SQL语句的where子句中时,才可能会使用到该索引,这取决于优化器计算的使用索引的成本和使用全表扫描的成本,Oracle会自动选择成本低的访问路径(请见下面的测试1和测试2);

        3、 从Oracle9i起,Oracle引入了一种新的索引扫描方式——索引跳跃扫描(index skip scan),这种扫描方式只有基于成本的优化器(CBO)才能使用。这样,当SQL语句的where子句中即使没有组合索引的前导列,并且索引跳跃扫描的成本低于其他扫描方式的成本时,Oracle就会使用该方式扫描组合索引(请见下面的测试3);

        4、 Oracle优化器有时会做出错误的选择,因为它再“聪明”,也不如我们SQL语句编写人员更清楚表中数据的分布,在这种情况下,通过使用提示(hint),我们可以帮助Oracle优化器作出更好的选择(请见下面的测试4)。

        关于以上情况,我们分别测试如下:

        我们创建测试表T,该表的数据来源于Oracle的数据字典表all_objects,表T的结构如下:

    SQL> desc t

    名称 是否为空? 类型

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

    OWNER NOT NULL VARCHAR2(30)

    OBJECT_NAME NOT NULL VARCHAR2(30)

    SUBOBJECT_NAME VARCHAR2(30)

    OBJECT_ID NOT NULL NUMBER

    DATA_OBJECT_ID NUMBER

    OBJECT_TYPE VARCHAR2(18)

    CREATED NOT NULL DATE

    LAST_DDL_TIME NOT NULL DATE

    TIMESTAMP VARCHAR2(19)

    STATUS VARCHAR2(7)

    TEMPORARY VARCHAR2(1)

    GENERATED VARCHAR2(1)

    SECONDARY VARCHAR2(1)


    表中的数据分布情况如下:

    SQL> select object_type,count(*) from t group by object_type;

    OBJECT_TYPE COUNT(*)

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

    CONSUMER GROUP 20

    EVALUATION CONTEXT 10

    FUNCTION 360

    INDEX 69

    LIBRARY 20

    LOB 20

    OPERATOR 20

    PACKAGE 1210

    PROCEDURE 130

    SYNONYM 16100

    TABLE 180

    TYPE 2750

    VIEW 8600

    已选择13行。

    SQL> select count(*) from t;

    COUNT(*)

    ----------

    29489

        我们在表T上创建如下索引并对其进行分析:

    SQL> create index indx_t on t(object_type,object_name);

    索引已创建。

    SQL> ANALYZE TABLE T COMPUTE STATISTICS

    2 FOR TABLE

    3 FOR ALL INDEXES

    4 FOR ALL INDEXED COLUMNS

    5 /

    表已分析。

    现在让我们编写几条SQL语句来测试一下Oracle优化器对访问路径的选择:

        测试1)

    SQL> set autotrace traceonly

    SQL> SELECT * FROM T WHERE OBJECT_TYPE='LOB';

    已选择20行。

    Execution Plan

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

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=20 Bytes=1740)

    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=22 Card=20 Bytes=1740)

    2 1 INDEX (RANGE SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=2 Card=20)

        正如我们所期望的,由于使用了组合索引的前导列并且访问了表中的少量记录,Oracle明智地选择了索引扫描。那么,如果我们访问表中的大量数据时,Oracle会选择什么样的访问路径呢?请看下面的测试:


        测试2)

    SQL> SELECT * FROM T WHERE OBJECT_TYPE='SYNONYM';

    已选择16100行。

    Execution Plan

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

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=16100 Bytes=1400700)

    1 0 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=16100 Bytes=1400700)

    Statistics

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

    0 recursive calls

    0 db block gets

    1438 consistent gets

    13 physical reads

    0 redo size

    941307 bytes sent via SQL*Net to client

    12306 bytes received via SQL*Net from client

    1075 SQL*Net roundtrips to/from client

    0 sorts (memory)

    0 sorts (disk)

    16100 rows processed

        很明显,即使使用了组合索引的前导列,但是由于访问了表中的大量数据,Oracle选择了不使用索引而直接使用全表扫描,因为优化器认为全表扫描的成本更低,但事实是不是真的这样的?我们通过增加提示(hint)来强制它使用索引来看看:


    SQL> SELECT/**//*+ INDEX (T INDX_T)*/ * FROM T WHERE OBJECT_TYPE='SYNONYM';

    已选择16100行。

    Execution Plan

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

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16180 Card=16100 Bytes=1400700)

    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=16180 Card=16100 Bytes=1400700)

    2 1 INDEX (RANGE SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=80 Card=16100)

    Statistics

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

    0 recursive calls

    0 db block gets

    17253 consistent gets

    16 physical reads

    0 redo size

    298734 bytes sent via SQL*Net to client

    12306 bytes received via SQL*Net from client

    1075 SQL*Net roundtrips to/from client

    0 sorts (memory)

    0 sorts (disk)

    16100 rows processed

        从以上结果可以看出,在访问大量数据的情况下,使用索引确实会导致更高的执行成本,这从statistics部分的逻辑读取数(consistent gets)就可以看出,使用索引导致的逻辑读取数是不使用索引导致的逻辑读的10倍还多。因此,Oracle明智地选择了全表扫描而不是索引扫描。

    下面,让我们来看看where子句中没有索引前导列的情况:


        测试3)


    SQL> select * from t where object_name= 'DEPT';

    已选择10行。

    Execution Plan

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

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=14 Bytes=1218)

    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=29 Card=14 Bytes=1218)

    2 1 INDEX (SKIP SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=14 Card=14)

    Statistics

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

    0 recursive calls

    0 db block gets

    24 consistent gets

    0 physical reads

    0 redo size

    1224 bytes sent via SQL*Net to client

    503 bytes received via SQL*Net from client

    2 SQL*Net roundtrips to/from client

    0 sorts (memory)

    0 sorts (disk)

    10 rows processed

        OK!由于只查询了10条数据,即使没有使用前导列,Oracle正确地选择了索引跳跃扫描。我们再来看看如果不使用索引跳跃扫描,该语句的成本:

    SQL> select/**//*+ NO_INDEX(T INDX_T)*/ * from t where object_name= 'DEPT';

    已选择10行。

    Execution Plan

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

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=14 Bytes=1218)

    1 0 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=14 Bytes=1218)

    Statistics

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

    0 recursive calls

    0 db block gets

    375 consistent gets

    17 physical reads

    0 redo size

    1224 bytes sent via SQL*Net to client

    503 bytes received via SQL*Net from client

    2 SQL*Net roundtrips to/from client

    0 sorts (memory)

    0 sorts (disk)

    10 rows processed

        正如我们所料,不使用索引所导致的逻辑读(375)确实比使用索引的逻辑读多(24),达到10倍以上。

    继续我们的测试,现在我们来看看Oracle不选择使用索引的情况:


        测试4)

    SQL> select * from t where object_name LIKE 'DE%';

    已选择180行。

    Execution Plan

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

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=37 Bytes=3219)

    1 0 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=37 Bytes=3219)

    Statistics

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

    0 recursive calls

    0 db block gets

    386 consistent gets

    16 physical reads

    0 redo size

    12614 bytes sent via SQL*Net to client

    624 bytes received via SQL*Net from client

    13 SQL*Net roundtrips to/from client

    0 sorts (memory)

    0 sorts (disk)

    180 rows processed

        这次只选择了180条数据,跟表T中总的数据量29489条相比,显然只是很小的一部分,但是Oracle还是选择了全表扫描,有386个逻辑读。这种情况下,如果我们强制使用索引,情况会怎样呢?

    SQL> select/**//*+ INDEX(T INDX_T)*/ * from t where object_name LIKE 'DE%';

    已选择180行。

    Execution Plan

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

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=182 Card=37 Bytes=3219)

    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=182 Card=37 Bytes=3219)

    2 1 INDEX (FULL SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=144 Card=37)

    Statistics

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

    0 recursive calls

    0 db block gets

    335 consistent gets

    0 physical reads

    0 redo size

    4479 bytes sent via SQL*Net to client

    624 bytes received via SQL*Net from client

    13 SQL*Net roundtrips to/from client

    0 sorts (memory)

    0 sorts (disk)

    180 rows processed

        通过添加提示(hint),我们强制Oracle使用了索引扫描(index full scan),执行了335个逻辑读,比使用全表扫描的时候少了一些。

        由此可见,Oracle优化器有时会做出错误的选择,因为它再“聪明”,也不如我们SQL语句编写人员更清楚表中数据的分布,在这种情况下,通过使用提示(hint),我们可以帮助Oracle优化器作出更好的选择。


    转载请注明本文地址:Oracle中组合索引的使用详解
    展开全文
  • Oracle创建删除索引

    千次阅读 2019-05-06 10:27:44
    索引创建 create index index_name ontable(column_name1,column_name2); 创建唯一索引 create index unique index_name ontable(column_name1,column_name2); 索引的删除。 drop index index_name; ...

    索引的创建

    create  index  index_name  on  table(column_name1,column_name2);

    创建唯一索引

    create  index  unique  index_name  on  table(column_name1,column_name2);

     

    索引的删除。

    drop index index_name;

     

    以下两条语句是等价的,删除掉table_name中的索引index_name。

    DROP INDEX index_name ON talbe_name

    ALTER TABLE table_name DROP INDEX index_name

     

    alter index  index_name  monitoring  usage;-----监视索引是否被用到

    alter index index_name nomonitoring  usage;----取消监视

    展开全文
  • oracle 创建唯一索引

    万次阅读 2015-12-21 17:07:42
    oracle中的unique约束是用来保证表中的某一类,或者表中的某一类组合起来不重复的一种手段。我们在可以在创建表时或者创建好后通过修改表的方式来创建oracle中的unique约束。 下面是一些创建unique约束的例子: ...

    oracle中的unique约束是用来保证表中的某一类,或者表中的某一类组合起来不重复的一种手段。我们在可以在创建表时或者创建好后通过修改表的方式来创建oracle中的unique约束。

    下面是一些创建unique约束的例子:
    create table unique_test
    (id number,
    fname varchar2(20),
    lname varchar2(20),
    address varchar2(100),
    email varchar2(40),
    constraint name_unique unique(fname,lname))

    在这里我们建立了一个表unique_test,并将其中的fname和lname组合起来建立了一个唯一约束。

    我们也还可以在表创建完成后手动的通过修改表的方式来增加约束,例如:
    alter table unique_test
    add constraint email_unique unique(email);

    下面我们来往表里面插入数据,
    insert into unique_test(id,fname,lname) values(1,’德华’,'刘’)

    这一行可以正常的被插入

    因为我们在建立表时曾把fname和lname联合起来作为一个约束,因为如果我们希望再次插入刘德华,
    insert into unique_test(id,fname,lname) values(2,’德华’,'刘’)
    是会出现如下错误的:
    ORA-00001: 违反唯一约束条件 (SYS.NAME_UNIQUE)

    但我们如果改为如下值:
    insert into unique_test(id,fname,lname) values(2,’学友’,'张’);
    又可以正常插入了。

    有些朋友可能会有疑问,我们不是为email也建立的一个唯一约束吗?为什么这两行数据都没有为email列赋值,也就是或两行的email列都是空值,而插入也成功了呢?

    这是因为一个空值(null)的含义是该列当前的状态是不存在,他永远不可能会与另外一个空值相等。所以也就不存在违反唯一约束之说了。


    转来的。学习下。


    展开全文
  • ORACLE创建唯一索引

    2020-05-12 18:34:45
    oracle创建唯一索引 Create UNIQUE Index PK_INSTANCE_ID on TB_DD_APPROVAL_OUT ( INSTANCE_ID ) oracle基于多列的唯一性约束 alter table tb_products add constraint tb_products_u1 unique (product_id,product_...
    • oracle创建唯一索引
    Create UNIQUE Index PK_INSTANCE_ID on TB_DD_APPROVAL_OUT
    (
     INSTANCE_ID
    )
    
    
    • oracle基于多列的唯一性约束
     alter table tb_products  
     add constraint  tb_products_u1  
     unique (product_id,product_name);  
    
    • 基于单列的唯一性约束示例
    create table tb_supplier  
    (  
      supplier_id          number not null  
     ,supplier_name        varchar2(50)  
     ,contact_name         varchar2(50)  
     ,CONSTRAINT tb_supplier_u1 UNIQUE (supplier_id)--创建表时创建唯一性约束  
    );  
    

    oracle创建唯一约束与创建唯一索引有所不

    • 创建唯一约束会在Oracle中创建一个Constraint,同时也会创建一个该约束对应的唯一索引。

    • 创建唯一索引只会创建一个唯一索引,不会创建Constraint。
      也就是说其实唯一约束是通过创建唯一索引来实现的。对于开发人员来说这两者有什么区别吗?好像没有。都是不能插入重复的值。在删除时这两者也有一定的区别,删除唯一约束时可以只删除约束而不删除对应的索引,所以对于的列还是必须唯一的,而删除了唯一索引的话就可以插入不唯一的值。

    展开全文
  • Oracle组合索引

    2019-05-24 14:28:27
    INDEX SKIP SCAN是9i之后提供的新功能,实现了即使WHERE条件中不存在索引的前导列也可以使用索引。但是INDEX SKIP SCAN是需要下面几个条件的: 1,版本9i及以后 2,CBO(表和索引都经过分析) 3,索引的前导列重复值...
  • Oracle 创建索引语法

    千次阅读 2018-05-08 23:06:32
    Oracle 的索引可分为5种,它们包括唯一索引、组合索引、反向键索引、位图索引和基于函数的索引。1、创建索引的标准语法以下为引用内容:  CREATE INDEX 索引名 ON 表名 (列名)TABLESPACE 表空间名;例如:以下为引用...
  • oracle 创建索引

    千次阅读 多人点赞 2014-12-31 09:19:51
    oracle的索引分为5种:唯一索引,组合索引,反向键索引,位图索引,基于函数的索引 创建Oracle索引的标准语法: CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名; 创建唯一索引: CREATE unique ...
  • oracle创建索引语句

    千次阅读 2020-06-30 21:46:51
    oracle : 单索引 create index 索引名称 on table(column) 删除索引 drop index 索引名称 复合索引 create index WBSINDEX ON project_info(wbs,is_delete) 查询某张表中所有索引 select * from ALL_INDEXS ...
  • oracle创建索引

    2011-09-06 09:12:16
    oracle的索引分为5种:唯一索引,组合索引,反向键索引,位图索引,基于函数的索引 创建索引的标准语法: CREATE INDEX 索引名 ON 表名 (列名)  TABLESPACE 表空间名; 创建唯一索引: CREATE unique IND
  • Oracle创建索引

    2010-04-28 09:08:33
    Oracle创建索引语法 oracle的索引分为5种:唯一索引,组合索引,反向键索引,位图索引,基于函数的索引 创建索引的标准语法: CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE...
  • oracle创建索引 查看索引状态

    千次阅读 2017-09-06 10:08:00
    Oracle对象教程:索引(Index)创建使用,索引:对数据库表中的某些列进行排序,便于提高查询效率。 当我们在某本书中查找特定的章节内容时,可以先从书的目录着手,找到该章节所在的页码,然后快速的定位到该页。这种...
  • oracle组合索引

    千次阅读 2017-08-23 15:28:22
    组合索引的使用情况分析
  • 1、创建索引 ...3、创建组合索引 create index 索引名 on 表名(列名1,,列名2); *查看目标表中已添加的索引 * */ --在数据库中查找表名 select * from user_tables where table_name lik...
  • oracle创建、删除索引等操作

    万次阅读 多人点赞 2018-05-02 09:52:06
    1、创建索引 ...3、创建组合索引 create index 索引名 on 表名(列名1,,列名2); 4、查询索引 --根据索引名,查询表索引字段 select * from user_ind_columns where index_name='索引名'; --根据表名...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 39,764
精华内容 15,905
关键字:

oracle创建组合索引