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);

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值