Oracle 12C 同列多索引

來源:互聯網
上載者:User

Oracle 12C 同列多索引

在Oracle 12c中,Oracle提供了可以在同一個表的列上建立不同的索引,用來做SQL的效能最佳化,但是僅有一個索引是可見的。

下面是一個樣本
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  alter session set container=pdb1;

Session altered.

SQL> CREATE TABLE t (
  2  id            NUMBER,
  3    ename  VARCHAR2(50)
  4  );


Table created.


SQL> CREATE INDEX t_idx1 ON t(ename) VISIBLE;


Index created.


SQL> CREATE INDEX t_idx2 ON t(ename) INVISIBLE;
CREATE INDEX t_idx2 ON t(ename) INVISIBLE
                        *
ERROR at line 1:
ORA-01408: such column list already indexed

 


SQL> CREATE BITMAP INDEX t_idx3 ON t(ename) INVISIBLE;


Index created.
SQL> l
  1  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 where a.table_name='T'
  9* ORDER BY index_name
SQL> /


INDEX_NAME INDEX_TYPE PARTITIONED  PARTITIONING_TYPE          LOCALITY VISIBILITY
---------- ---------- ------------ --------------------------- -------- ----------
T_IDX1    NORMAL    NO                                                VISIBLE
T_IDX3    BITMAP    NO                                                INVISIBLE

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.