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