Ora2pg之索引转换

Oracle索引和Postgresql索引

一、Oracle索引

1.1 索引的创建语法：

CREATE UNIUQE | BITMAP INDEX <schema>.<index_name> 

      ON <schema>.<table_name> 

           (<column_name> | <expression> ASC | DESC, 

            <column_name> | <expression> ASC | DESC,...) 

     TABLESPACE <tablespace_name> 

     STORAGE <storage_settings> 

     LOGGING | NOLOGGING 

    COMPUTE STATISTICS 

     NOCOMPRESS | COMPRESS<nn> 

     NOSORT | REVERSE 

     PARTITION | GLOBAL PARTITION<partition_setting>

相关说明

1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。

2）<column_name> | ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”

3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)

4）STORAGE：可进一步设置表空间的存储参数

5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)

6）COMPUTE STATISTICS：创建新索引时收集统计信息

7）NOCOMPRESS | COMPRESS：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)

8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值

9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

1.2 索引类型

B树索引 位图索引 HASH索引 索引编排表
反转键索引 基于函数的索引 分区索引 本地和全局索引

• B树索引（默认）

--单列索引
create index indx_on_dep_id
on emp(dep_id);
--组合索引（压缩）
CREATE INDEX indx_emp_name_salary
   ON emp (emp_name, salary)
   COMPRESS 1;
-- NOLOGGING模式，加快速度，节省时间，减少产生的日志量
create index indx_on_dep_id_no
on emp(dep_id)
 NOSORT
 NOLOGGING;
--  Cluster Index表簇索引
CREATE CLUSTER personnel (department NUMBER(4));
CREATE INDEX idx_personnel ON CLUSTER personnel; 
--  XMLType类型的表中建索引
CREATE TABLE xwarehouses (
   warehouse_id        NUMBER,
   warehouse_spec      XMLTYPE)
   XMLTYPE warehouse_spec STORE AS CLOB;
 

• 位图索引

CREATE BITMAP INDEX indx_on_leadid
   ON emp(lead_id)

• HASH索引

见分区索引

• 唯一索引

 CREATE UNIQUE INDEX indx_on_emp_name ON emp(emp_name) TABLESPACE users;

• 反转索引

create index indx_dep_id_reverse on emp(dep_id) REVERSE;

• 基于函数的索引

CREATE INDEX indx_upper ON emp (UPPER(emp_name)); 

• 分区索引

--  Range-Partitioned Global Index
 CREATE INDEX cost_ix ON emp (salary)
   GLOBAL PARTITION BY RANGE (salary)
      (PARTITION p1 VALUES LESS THAN (1000),
       PARTITION p2 VALUES LESS THAN (2500),
       PARTITION p3 VALUES LESS THAN (MAXVALUE));
       
 --Hash-Partitioned Global Index
 CREATE INDEX salary_ix ON emp (salary)
  GLOBAL PARTITION BY HASH (salary)
  PARTITIONS 4;

• 本地和局部
  全局索引(global index)：即可以分区，也可以不分区。即可以建range分区，也可以建hash分区，即可建于分区表，又可创建于非分区表上，就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作。
  本地索引(local index)：其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地索引，其索引分区的维护自动进行，就是说你add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区。

二、Postgresql索引

1.1 语法

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

1.2 类型
PostgreSQL提供了几种索引类型：B-树，哈希，GIST，SP-GiST和GIN。每个索引类型使用不同的算法，是最适合于不同类型的查询。默认情况下，CREATE INDEX命令创建B-tree索引，适合最常见的情况。

• B树

--单列索引
create index indx_on_dep_id
on emp(dep_id);
--组合索引（压缩）
CREATE INDEX indx_emp_name_salary
   ON emp (emp_name, salary);

• GIN index

CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);