精华内容
下载资源
问答
  • Oralce 给多个字段建索引,是直接给所有字段建立一个索引,还是每个字段分别建立索引。 以前没有接触,现在刚接触,不懂,谢谢!
  • 探究Oracle 12c 中在同一个列上建立多个索引 Oracle 12c允许在同一组列上建立多个索引,但是只有一个索引是可见的,并且所有索引在某些方面可以有不同。  示例 Non-Partitioned Tables 例如,我们可以创建一个...

    探究Oracle 12c 中在同一个列上建立多个索引

    Oracle 12c允许在同一组列上建立多个索引,但是只有一个索引是可见的,并且所有索引在某些方面可以有不同。 

    示例

    Non-Partitioned Tables

    例如,我们可以创建一个非分区表t1,并插入测试数据。

    然后在 create_date列上创建一个索引;当第二次使用语句 

    CREATE INDEX t1_idx2 ON t1(created_date) INVISIBLE;

    在 created_date列上再次创建索引时,提示错误。

    SQL> CREATE TABLE t1 (
      2    id            NUMBER,
      3    description   VARCHAR2(50),
      4    created_date  DATE
      5  );
    
    表已创建。
    
    已用时间:  00: 00: 00.08
    SQL>
    SQL> INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY'));
    
    已创建 1 行。
    
    已用时间:  00: 00: 00.03
    
    SQL> INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY'));
    
    已创建 1 行。
    
    已用时间:  00: 00: 00.01
    
    
    SQL> INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY'));
    
    已创建 1 行。
    
    已用时间:  00: 00: 00.00
    
    
    SQL> COMMIT;
    
    提交完成。
    
    已用时间:  00: 00: 00.00
    SQL> CREATE INDEX t1_idx1 ON t1(created_date) VISIBLE;
    
    索引已创建。
    
    已用时间:  00: 00: 00.01
    SQL> CREATE INDEX t1_idx2 ON t1(created_date) INVISIBLE;
    CREATE INDEX t1_idx2 ON t1(created_date) INVISIBLE
                               *
    第 1 行出现错误:
    ORA-01408: 此列列表已索引
    
    
    已用时间:  00: 00: 00.06
    SQL> CREATE BITMAP INDEX t1_idx3 ON t1(created_date) INVISIBLE;
    
    索引已创建。
    
    已用时间:  00: 00: 00.11
    SQL>
    

    但是可以使用这个语句在created_date列上来创建一个BITMAP类型的索引:

    CREATE BITMAP INDEX t1_idx3 ON t1(created_date) INVISIBLE;

     Partitioned Tables

    创建一个分区表t1:

    SQL> DROP TABLE t1 PURGE;
    
    表已删除。
    
    SQL>
    SQL> CREATE TABLE t1 (
      2    id            NUMBER,
      3    description   VARCHAR2(50),
      4    created_date  DATE
      5  )
      6  PARTITION BY RANGE (created_date) (
      7    PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
      8    PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users,
      9    PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE users
     10  );
    
    表已创建。
    
    SQL>
    SQL> INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY'));
    
    已创建 1 行。
    
    SQL> INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY'));
    
    已创建 1 行。
    
    SQL> INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY'));
    
    已创建 1 行。
    
    SQL> COMMIT;
    
    提交完成。
    
    SQL>

     在分区表t1上创建一个索引:

    CREATE INDEX t1_idx1 ON t1(created_date) GLOBAL VISIBLE;
    
    SQL> CREATE INDEX t1_idx1 ON t1(created_date) GLOBAL VISIBLE;
    
    索引已创建。
    
    SQL>
    

     在不同的分区上创建索引;

    CREATE INDEX t1_idx2 ON t1(created_date) GLOBAL
    PARTITION BY RANGE (created_date) (
      PARTITION t1_p1 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
      PARTITION t1_p2 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users,
      PARTITION t1_p3 VALUES LESS THAN (MAXVALUE) TABLESPACE users
    )
    INVISIBLE;
    SQL> CREATE INDEX t1_idx2 ON t1(created_date) GLOBAL
      2  PARTITION BY RANGE (created_date) (
      3    PARTITION t1_p1 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
      4    PARTITION t1_p2 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users,
      5    PARTITION t1_p3 VALUES LESS THAN (MAXVALUE) TABLESPACE users
      6  )
      7  INVISIBLE;
    
    索引已创建。
    
    SQL>
    SQL> CREATE INDEX t1_idx3 ON t1(created_date) LOCAL INVISIBLE;
    
    索引已创建。
    
    SQL> CREATE BITMAP INDEX t1_idx4 ON t1(created_date) LOCAL INVISIBLE;
    
    索引已创建。
    
    SQL>

    为什么要使用多索引?即为什么要在一个列上创建多个索引呢?

    可以通过在一个列上创建不同的索引,通过改变索引的可见性(visible或者invisible)来测试索引的影响。

    可以通过下面的查询来查看索引的可见性

    SQL> -- Check visibility of indexes.
    SQL> COLUMN index_name FORMAT A10
    SQL> COLUMN index_type FORMAT A10
    SQL> COLUMN partitioned FORMAT A12
    SQL> COLUMN locality FORMAT A8
    SQL> COLUMN visibility FORMAT A10
    SQL>
    SQL> SELECT a.index_name,
      2         a.index_type,
      3         a.partitioned,
      4         b.partitioning_type,
      5         b.locality,
      6         a.visibility
      7  FROM   user_indexes a
      8         LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
      9  ORDER BY index_name;
    
    INDEX_NAME INDEX_TYPE PARTITIONED  PARTITIONING_TYPE  LOCALITY VISIBILITY
    ---------- ---------- ------------ ------------------ -------- ----------
    IDX_DEPTCO NORMAL     NO                                       VISIBLE
    PY_DEPTNO
    
    IDX_DEPTNO NORMAL     NO                                       VISIBLE
    IDX_MGR    NORMAL     NO                                       VISIBLE
    PK_DEPT    NORMAL     NO                                       VISIBLE
    PK_EMP     NORMAL     NO                                       VISIBLE
    T1_IDX1    NORMAL     NO                                       VISIBLE
    T1_IDX2    NORMAL     YES          RANGE              GLOBAL   INVISIBLE
    T1_IDX3    NORMAL     YES          RANGE              LOCAL    INVISIBLE
    T1_IDX4    BITMAP     YES          RANGE              LOCAL    INVISIBLE
    TEST_TAB_I NORMAL     NO                                       VISIBLE
    D
    
    
    已选择 10 行。
    
    SQL>

    看看查询中索引的使用情况。执行下面的查询,通过查看执行计划发现使用了索引,

    SQL> SET AUTOTRACE TRACE EXPLAIN
    SQL> SELECT *
      2  FROM   t1
      3  WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 1106166644
    
    ----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                           |         |     1 |    49 |     1   (0)| 00:00:01 |       |       |
    |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1      |     1 |    49 |     1   (0)| 00:00:01 |     1 |     1 |
    |*  2 |   INDEX RANGE SCAN                         | T1_IDX1 |     1 |       |     1   (0)| 00:00:01 |       |       |
    ----------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("CREATED_DATE"=TO_DATE(' 2014-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
    
    SQL>

    改变索引的状态

    SQL> -- Switch indexes.
    SQL> ALTER INDEX t1_idx1 INVISIBLE;
    
    索引已更改。
    
    SQL> ALTER INDEX t1_idx2 VISIBLE;
    
    索引已更改。

    查看索引的状态是否改变: 

    SQL> -- Check visibility of indexes.
    SQL> SELECT a.index_name,
      2         a.index_type,
      3         a.partitioned,
      4         b.partitioning_type,
      5         b.locality,
      6         a.visibility
      7  FROM   user_indexes a
      8         LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
      9  ORDER BY index_name;
    
    INDEX_NAME INDEX_TYPE PARTITIONED  PARTITIONING_TYPE  LOCALITY VISIBILITY
    ---------- ---------- ------------ ------------------ -------- ----------
    IDX_DEPTCO NORMAL     NO                                       VISIBLE
    PY_DEPTNO
    
    IDX_DEPTNO NORMAL     NO                                       VISIBLE
    IDX_MGR    NORMAL     NO                                       VISIBLE
    PK_DEPT    NORMAL     NO                                       VISIBLE
    PK_EMP     NORMAL     NO                                       VISIBLE
    T1_IDX1    NORMAL     NO                                       INVISIBLE
    T1_IDX2    NORMAL     YES          RANGE              GLOBAL   VISIBLE
    T1_IDX3    NORMAL     YES          RANGE              LOCAL    INVISIBLE
    T1_IDX4    BITMAP     YES          RANGE              LOCAL    INVISIBLE
    TEST_TAB_I NORMAL     NO                                       VISIBLE
    D

    执行查询,查看索引是否可用:

    SQL> -- Test the index usage.
    SQL> SET AUTOTRACE TRACE EXPLAIN
    SQL>
    SQL> SELECT *
      2  FROM   t1
      3  WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 3769679070
    
    -----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                            |         |     1 |    49 |     1   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE SINGLE                     |         |     1 |    49 |     1   (0)| 00:00:01 |     1 |     1 |
    |   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1      |     1 |    49 |     1   (0)| 00:00:01 |     1 |     1 |
    |*  3 |    INDEX RANGE SCAN                         | T1_IDX2 |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
    -----------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("CREATED_DATE"=TO_DATE(' 2014-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
    
    SQL>
    

    改变索引的状态:

    SQL> -- Switch indexes.
    SQL> ALTER INDEX t1_idx3 INVISIBLE;
    
    索引已更改。
    
    SQL> ALTER INDEX t1_idx4 VISIBLE;
    ALTER INDEX t1_idx4 VISIBLE
    *
    第 1 行出现错误:
    ORA-14147: 同一列集上已存在定义的 VISIBLE 索引。
    
    
    SQL>
    SQL>
    SQL> -- Check visibility of indexes.
    SQL> SELECT a.index_name,
      2         a.index_type,
      3         a.partitioned,
      4         b.partitioning_type,
      5         b.locality,
      6         a.visibility
      7  FROM   user_indexes a
      8         LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
      9  ORDER BY index_name;
    
    INDEX_NAME INDEX_TYPE PARTITIONED  PARTITIONING_TYPE  LOCALITY VISIBILITY
    ---------- ---------- ------------ ------------------ -------- ----------
    IDX_DEPTCO NORMAL     NO                                       VISIBLE
    PY_DEPTNO
    
    IDX_DEPTNO NORMAL     NO                                       VISIBLE
    IDX_MGR    NORMAL     NO                                       VISIBLE
    PK_DEPT    NORMAL     NO                                       VISIBLE
    PK_EMP     NORMAL     NO                                       VISIBLE
    T1_IDX1    NORMAL     NO                                       INVISIBLE
    T1_IDX2    NORMAL     YES          RANGE              GLOBAL   VISIBLE
    T1_IDX3    NORMAL     YES          RANGE              LOCAL    INVISIBLE
    T1_IDX4    BITMAP     YES          RANGE              LOCAL    INVISIBLE
    TEST_TAB_I NORMAL     NO                                       VISIBLE
    D
    
    
    已选择 10 行。
    
    SQL>

     

    展开全文
  •  以左边为驱动,左边表的数据自然全有了,所以要再dept表建立id索引,我这举的是特例了,因为部门表id是主键,自动建立索引了。   二.3张表分析  select * form users left join dept on ...

    一.2张表分析

        select * form users left join dept on users.deptId = dept.id

        以左边为驱动,左边表的数据自然全有了,所以要再dept表建立id索引,我这举的是个特例了,因为部门表id是主键,自动建立索引了。

     

    二.3张表分析

        select * form users left join dept on users.deptId = dept.id left join wages on user.id = wages.user_id

        此时需要在部门表id,wages表user_id 建立索引

     

    总结:左连接到右边表建立索引,右连接你懂的。

    展开全文
  • 随着不停的在表里插入数据,就会不停的在数据页里插入数据,然后一个数据页放满了就会分裂成多个数据页,这个...在一个索引B+树中,有一些特性,那就是数据页/索引页里面的记录都是组成一个单向链表的,而且是按照数据

    随着不停的在表里插入数据,就会不停的在数据页里插入数据,然后一个数据页放满了就会分裂成多个数据页,这个时候就需要索引页去指向各个数据页。然后如果数据页太多了,那么索引页里里的数据页指针也就会太多了,索引页也必然会放满的,此时索引页也会分裂成多个,再形成更上层的索引页。

    默认情况下MySQL建立的聚簇索引都是基于主键的值来组织索引的,聚簇索引的叶子节点都是数据页,里面放的就是插入的一行一行的完整的数据了!

    在一个索引B+树中,有一些特性,那就是数据页/索引页里面的记录都是组成一个单向链表的,而且是按照数据大小有序排列的;然后数据页/索引页互相之间都是组成双向链表的,而且也都是按照数据大小有序排列的,所以其实B+树索引是一个完全有序的数据结构,无论是页内还是页之间。

    正是因为这个有序的B+树索引结构,才能在查找数据的时候,直接从根节点开始按照数据值大小一层一层往下找,这个效率是非常高的。

    然后如果是针对主键之外的字段建立索引的话,实际上本质就是为那个字段的值重新建立另外一颗B+树索引,那个索引B+树的叶子节点,存放的都是数据页,里面放的都是字段的值和主键值,然后每一层索引页里存放的都是下层页的引用,包括页内的排序规则,页之间的排序规则,B+树索引的搜索规则,都是一样的。

    但是唯一要清晰记住的一点是,假设要根据其它字段的索引来搜索,那么只能基于其它字段的索引B+树快速查找到那个值所对应的主键,接着再次做回表查询,基于主键在聚簇索引的B+树里,重新从根节点开始查找那个主键值,找到主键值对应的完整数据。

    在MySQL的表里建立一些字段对应的索引,好处是什么?好处显而易见了,可以直接根据某个字段的索引B+树来查找数据,不需要全表搜索,性能提升是很高的。

    但是坏处呢?索引当然有缺点了,主要是两个缺点,一个是空间上的,一个是时间上的。

    空间上而言,要是给很多字段创建很多的索引,那必须会有很多棵索引B+树,每一棵B+树都要占用很多的磁盘空间啊!所以要是搞的索引太多了,是很耗费磁盘空间的。

    其次,要是搞了很多索引,那么在进行增删改查的时候,每次都需要维护各个索引的数据有序性,因为每个索引B+树都要求页内是按照值大小排序的,页之间也是有序的,下一个页的所有值必须大于上一个页的所有值!所以不停的增删改查,必然会导致各个数据页之间的值大小可能会没有顺序,比如下一个数据页里插入了一个比较小的值,居然比上一个数据页的值要小!此时就没办法了,只能进行数据页的挪动,维护页之间的顺序。

    或者是不停的插入数据,各个索引的数据页就要不停的分裂,不停的增加新的索引页,这个过程都是耗费时间的。

    所以要是一个表里搞的索引太多了,很可能就会导致增删改的速度就比较差了,也许查询速度确实是可以提高,但是增删改就会受到影响,因此通常来说,是不建议一个表里搞的索引太多的!那么怎么才能尽量用最少的索引满足最多的查询请求,还不至于让索引占用太多磁盘空间,影响增删改性能呢?这就需要深入理解索引的使用规则了,SQL语句要怎么写,才能用上索引B+树来查询!

     

     

    展开全文
  • 查询访问一个表的两个索引

    千次阅读 2012-07-12 09:39:33
    经常看到有人提出这样的疑问,我在同一张建立多个索引,为什么Oracle每次 都选择一个,而不能同时利用多个索引呢。一般来说,常见的访问同一张的两个以 上索引,存在三种情况,AND-EQUAL、INDEX HASH JOIN和...
    原文地址:http://yangtingkun.itpub.net/post/468/499769
    
    经常看到有人提出这样的疑问,我在同一张表上建立了多个索引,为什么Oracle每次
    都选择一个,而不能同时利用多个索引呢。一般来说,常见的访问同一张表的两个以
    上索引,存在三种情况,AND-EQUAL、INDEX HASH JOIN和BITMAP INDEX AND/OR。
    
    此外,还有一个设计上的疑问,如果有A、B、C三个字段,都可能作为查询条件,是
    建立多个复合索引好,还是建立三个单列的索引。这个问题之所以不好回答是因为
    和业务或者说和查询的模式有很大的关系,不过如果理解了Oracle什么时候会选择
    一个以上的索引来访问表,就会对于理解如何设计合理的索引有很大的帮助。
    
    简单介绍一下AND-EQUAL执行计划。
    首先建立一个测试表:
    
    SQL> CREATE TABLE T_DOUBLE_IND 
    2 (ID NUMBER, 
    3 NAME VARCHAR2(30), 
    4 TYPE VARCHAR2(30), 
    5 CONTENTS VARCHAR2(4000));
    
    Table created.
    
    SQL> INSERT INTO T_DOUBLE_IND
    2 SELECT ROWNUM, 
    3 OBJECT_NAME, 
    4 OBJECT_TYPE, 
    5 LPAD('A', 1000, 'A')
    6 FROM DBA_OBJECTS;
    
    75856 rows created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> CREATE INDEX IND_DOUBLE_NAME
    2 ON T_DOUBLE_IND (NAME);
    
    Index created.
    
    SQL> CREATE INDEX IND_DOUBLE_TYPE
    2 ON T_DOUBLE_IND (TYPE);
    
    Index created.
    
    现在建立了一个测试表和两个索引,下面首先来看看AND-EQUAL执行方式:
    
    SQL> SELECT ID, NAME, TYPE
    2 FROM T_DOUBLE_IND
    3 WHERE NAME = 'T_DOUBLE_IND'
    4 AND TYPE = 'TABLE';
    
    ID NAME TYPE
    ---------- ------------------------------ ------------------------------
    75688 T_DOUBLE_IND TABLE
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 474554719
    
    -------------------------------------------------------
    | Id | Operation | Name |
    -------------------------------------------------------
    | 0 | SELECT STATEMENT | |
    | 1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND |
    | 2 | AND-EQUAL | |
    |* 3 | INDEX RANGE SCAN | IND_DOUBLE_NAME |
    |* 4 | INDEX RANGE SCAN | IND_DOUBLE_TYPE |
    -------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    3 - access("NAME"='T_DOUBLE_IND')
    4 - access("TYPE"='TABLE')
    
    Note
    -----
    - rule based optimizer used (consider using cbo)
    
    由于指定了两个列,且两个列上都包含索引,Oracle选择了扫描两个索引,并
    使用了AND-EQUAL执行计划。这种扫描方式是分别通过两个索引获取索引键值
    对应的ROWID,然后合并两个扫描中相等的ROWID,并通过这个ROWID来扫描表。
    
    不过观察执行计划可以发现,当前的优化模式是RBO,如果收集一下索引,
    再次运行同样的查询:
    
    SQL> SHOW PARAMETER OPTIMIZER_MODE
    
    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_mode string CHOOSE
    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')
    
    PL/SQL procedure successfully completed.
    
    SQL> SELECT ID, NAME, TYPE
    2 FROM T_DOUBLE_IND
    3 WHERE NAME = 'T_DOUBLE_IND'
    4 AND TYPE = 'TABLE';
    
    ID NAME TYPE
    ---------- ------------------------------ ------------------------------
    75688 T_DOUBLE_IND TABLE
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3887138334
    
    --------------------------------------------------------------------------------------------
    | Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------
    | 0| SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
    |* 1| TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 1 | 37 | 3 (0)| 00:00:01 |
    |* 2| INDEX RANGE SCAN | IND_DOUBLE_NAME | 2 | | 1 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    1 - filter("TYPE"='TABLE')
    2 - access("NAME"='T_DOUBLE_IND')
    
    可以看到,收集统计信息后,优化模式变为CBO,但是Oracle只选择了一个
    索引进行扫描。对于当前的情况,NAME列的选择度非常高,因此这种方式
    的代价最低。
    
    SQL> SELECT /*+ INDEX(A IND_DOUBLE_NAME) INDEX(A IND_DOUBLE_TYPE) */
    2 ID, NAME, TYPE
    3 FROM T_DOUBLE_IND
    4 WHERE NAME = 'T_DOUBLE_IND'
    5 AND TYPE = 'TABLE';
    
    ID NAME TYPE
    ---------- ------------------------------ ------------------------------
    75689 T_DOUBLE_IND TABLE
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3887138334
    
    --------------------------------------------------------------------------------------------
    | Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
    |*1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 1 | 37 | 3 (0)| 00:00:01 |
    |*2 | INDEX RANGE SCAN | IND_DOUBLE_NAME | 2 | | 1 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    1 - filter("TYPE"='TABLE')
    2 - access("NAME"='T_DOUBLE_IND')
    
    即使通过HINT指定两个索引,CBO优化器也只是会选择其中一个索引来进行扫描,
    而自动忽略另一个选择度低的索引。只有使用AND_EQUAL提示,才能在CBO的情况
    下使用AND-EQUAL执行计划:
    
    SQL> SELECT /*+ AND_EQUAL(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ ID, NAME, TYPE
    2 FROM T_DOUBLE_IND A
    3 WHERE NAME = 'T_DOUBLE_IND'
    4 AND TYPE = 'TABLE';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 474554719
    
    --------------------------------------------------------------------------------------------
    | Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 37 | 21 (0)| 00:00:01 |
    |*1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 1 | 37 | 21 (0)| 00:00:01 |
    | 2 | AND-EQUAL | | | | | |
    |*3 | INDEX RANGE SCAN | IND_DOUBLE_NAME | 2 | | 1 (0)| 00:00:01 |
    |*4 | INDEX RANGE SCAN | IND_DOUBLE_TYPE | 9286 | | 20 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    1 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
    3 - access("NAME"='T_DOUBLE_IND')
    4 - access("TYPE"='TABLE')
    
    在CBO下,Oracle不会自动选择AND-EQUAL执行计划,这是因为Oracle可以根据直方图来
    判断一个列上各个值的选择度,但是从统计信息无法获得两个不同的列合AND-EQUAL之
    后的选择度,这个操作后,如果得到的ROWID记录很少,那么这个查询的效率就会很高,
    如果AND-EQUAL后得到大量的ROWID,那么查询的效率就会很差,这时应该选择全表扫描
    或其他的执行计划。由于CBO都是根据统计信息分析得到的结果,而AND-EQUAL的结果对
    于CBO是未知的,因此CBO不会选择AND-EQAUL这种扫描方式。
    
    如果要Oracle获得这种统计信息,最简单的方法就是建立一个复合索引,Oracle在分析
    索引列的时候自动会分析两个列的组合情况,从而在查询的时候可以准确的判断是否应
    该使用这个复合索引。
    
    SQL> SET AUTOT OFF
    SQL> CREATE INDEX IND_DOU_NAM_TYP 
    2 ON T_DOUBLE_IND
    3 (NAME, TYPE);
    
    Index created.
    
    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')
    
    PL/SQL procedure successfully completed.
    
    SQL> SET AUTOT TRACE EXP
    SQL> SELECT ID, NAME, TYPE
    2 FROM T_DOUBLE_IND
    3 WHERE NAME = 'T_DOUBLE_IND'
    4 AND TYPE = 'TABLE';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1808990274
    
    --------------------------------------------------------------------------------------------
    | Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 2 | 74 | 3 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 2 | 74 | 3 (0)| 00:00:01 |
    |*2 | INDEX RANGE SCAN | IND_DOU_NAM_TYP | 2 | | 1 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    2 - access("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
    
    SQL> SET AUTOT OFF
    SQL> UPDATE T_DOUBLE_IND
    2 SET NAME = 'T_DOUBLE_IND'
    3 WHERE ID <= 20000;
    
    20000 rows updated.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')
    
    PL/SQL procedure successfully completed.
    
    SQL> SET AUTOT TRACE EXP
    SQL> SELECT ID, NAME, TYPE
    2 FROM T_DOUBLE_IND
    3 WHERE NAME = 'T_DOUBLE_IND'
    4 AND TYPE = 'TABLE';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1808990274
    
    --------------------------------------------------------------------------------------------
    | Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 2277 | 77418 | 1225 (1)| 00:00:18 |
    | 1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 2277 | 77418 | 1225 (1)| 00:00:18 |
    |*2 | INDEX RANGE SCAN | IND_DOU_NAM_TYP | 2277 | | 11 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    2 - access("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
    
    SQL> SET AUTOT OFF
    SQL> UPDATE T_DOUBLE_IND
    2 SET TYPE = 'TABLE'
    3 WHERE ID <= 20000;
    
    20000 rows updated.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')
    
    PL/SQL procedure successfully completed.
    
    SQL> SET AUTOT TRACE EXP
    SQL> SELECT ID, NAME, TYPE
    2 FROM T_DOUBLE_IND
    3 WHERE NAME = 'T_DOUBLE_IND'
    4 AND TYPE = 'TABLE';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4134729579
    
    ----------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 7778 | 258K| 2010 (1)| 00:00:29 |
    |* 1 | TABLE ACCESS FULL| T_DOUBLE_IND | 7778 | 258K| 2010 (1)| 00:00:29 |
    ----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    1 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
    
    在建立复合索引后,对于刚才的查询,Oracle会自动选择复合索引。即使将表中
    的20000条记录全部更新为NAME=’T_DOUBLE_BIN’,由于TYPE列的选项条件,在加
    上索引相对于表来说要窄得多,Oracle仍然选择了复合索引扫描。
    
    如果将TYPE列也更新20000条记录,使得NAME=’T_DOUBLE_IND’和TYPE=’TABLE’的
    选择性很差,这时Oracle就会自动选择全表扫描来代替索引扫描。

    展开全文
  • 一个表哪怕只做查询操作,索引也不宜过多, 因为所以太会导致查询选择索引出现开销(当然指定了索引可以最低限度的降低开销). 从我自己的实际工作情况来看, 所以得建立要全局考虑,就是不要仅仅只考虑一张表的索引怎么...
  • 例如你有一个 学生。 字段包含 学号, 班级, 姓名,性别, 出生年月日。 你创建一个 组合索引 ( 班级, 姓名) 那么 SELECT * FROM 学生 WHERE 班级='2010级3班' AND 姓名='张三' 将使用索引. SELECT * FROM...
  • 请阅读Data Normalization,General Indexing概念和Foreign Key约束,以保持数据清理与引用完整性.这将让你走.将数据存储在数组中可能...以下应该让你开始一个良好的开始,因为你修补. Joins.create table student( stu...
  • 我们在下篇讨论索引列次序的问题,首先看一下列独立索引的情况,以下面的结构为例:CREATE TABLE test (c1 INT,c2 INT,c3 INT,KEY(c1),KEY(c2),KEY(c3),);使用这种索引策略通常是一些权威的建议(例如在WHERE...
  • 表操作 user_id 与 id对应,被称为外键foreign key。foreign key要和它引用的类型保持一致,所以...要创建中间表,需要明白中间表和两个表之间的关系,应该都是两个表对中间表,的...
  • SOLR对多个(关联)创建索引

    万次阅读 2016-09-30 16:56:29
     首先在数据库创建一张,本来有一个tuser了,我又创建了一个role,就两三个字段,没什么特殊的,然后在里面随便添加几条数据即可,看一下数据库: 1.2data-config.xml  数据库创建完成之后就是...
  • 关系型数据库中,表关联是很常见的事情,HubbleDotNet 可以对部分情况的表关联形式建立关联的全文索引,这样用户就不需要专门建一个大表 来解决表关联时的全文索引问题。 下面以为数据库现有表建立全文...
  • 达梦数据库支持聚集索引,复合索引,函数索引,唯一索引,位图索引等等。下面我们就一起来创建达梦数据库在各种场景中的索引。...如果在装载数据之前创建了一个多个索引,那么在插入每行时DM数据...
  • 表连接速度慢,试着在两个表建立cluster。 两个表还不在同一个 schema下面: 试着将他们建立在同一个schema下面,  SQL> create table cmdb.configuration_item_cluster1 cluster monitor
  • 问题描述:24颗CPU/48G内存的RAC系统上,只跑了一点应用,CPU却占到50%...查一下吧,我晕,全是一些大扫描,的个数也不,全系统总共才400张,索引,基本上没建几。这些SQL还真是缺索引,基本上是up...
  • 多列索引多个单列索引

    千次阅读 2015-07-28 09:44:28
    现在有个问题,如果一个表多个列需要建立索引,是把所有列建成一个索引,还是对每一个列建一个索引,上篇文章做了一个介绍,这是作者得出的结论,Conclusion: For benchmarked queries we can see Multiple...
  • 1、如果经常需要同时对两个字段进行AND查询,那么使用两个单独索引不如建立一个复合索引,因为两个单独索引通常数据库只能使用其中一个,而使用复合索引因为索引本身就对应到两个字段上的,效率会有很大提高。...
  • mysql 多列索引多个单列索引

    万次阅读 2013-07-05 11:01:51
    现在有个问题,如果一个表多个列需要建立索引,是把所有列建成一个索引,还是对每一个列建一个索引,上篇文章做了一个介绍,这是作者得出的结论,Conclusion: For benchmarked queries we can see Multiple ...
  • 用hibernate 操作oracle数据库,表主键自增,我们通常要为表建立序列,你也可以不建立... 我先说一下绝大多数网民对此种问题的回答:最好为每个表建立一个索引,这样方便维护和管理! 临外部分人认为 个表...
  • SQL索引建立原则和使用

    万次阅读 多人点赞 2017-12-01 18:52:21
    前言 SQL索引有两种,聚集索引和非聚集索引 聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理... 聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个建立索引的原则: ...
  • 自己写脚本检测这类语句,比如order by rand(), 超过一定时间用Kill query thread_id 给杀掉。那能不能不要杀掉而让他正常运行,但是又不影响其他的请求呢?那mysql 8.0 引入的资源组(resource grou...
  • 最近一直在学习Lucene,今天分享一个个人的简单开源项目,可以将数据库中特定的数据索引化,并支持了增量更新,近实时查询以及线程索引建立的相关功能。 1.需求 为数据库中特定的的数据提供全文检索功能,...
  • 经常看到有人提出这样的疑问,我在同一张建立多个索引,为什么Oracle每次都选择一个,而不能同时利用多个索引呢。一般来说,常见的访问同一张的两个以上索引,存在三种情况,AND-EQUAL、INDEX HASH JOIN和...
  • 经常看到有人提出这样的疑问,我在同一张建立多个索引,为什么Oracle每次都选择一个,而不能同时利用多个索引呢。一般来说,常见的访问同一张的两个以上索引,存在三种情况,AND-EQUAL、INDEX HASH J...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,051
精华内容 1,220
关键字:

一个表建立多个索引