PostgreSQL与Oracle的B-tree索引区别

今天发现PostgreSQL的B-tree索引与Oracle的B-tree索引区别还比较大，就我目前发现的区别来说，主要是以下4点：
1.PostgreSQL中索引会存储NULL，而Oracle不会；
2.PostgreSQL中建立索引时，可以使用where来建立部分索引，而Oracle不能；
3.PostgreSQL中可以对同一列建立两个相同的索引，而Oracle不能；
4.PostgreSQL中可以使用concurrently关键字达到创建索引时不阻塞表的DML的功能，Oracle也有online参数实现类似的功能。（刚开始写错了，忘还有online了）



--PostgreSQL下的测试

testdb=> select version();
                                                    version                                                    
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 64-bit
(1 row)

testdb=> \d+ abc
                         Table "public.abc"
 Column |  Type   | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              |
Has OIDs: no

testdb=> create index concurrently idx_id_first on abc(id);
CREATE INDEX
testdb=>
testdb=> \d+ abc
                         Table "public.abc"
 Column |  Type   | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              |
Indexes:
    "idx_id_first" btree (id)
Has OIDs: no

testdb=> create index concurrently idx_id_second on abc(id);
CREATE INDEX
testdb=>
testdb=> \d+ abc
                         Table "public.abc"
 Column |  Type   | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              |
Indexes:
    "idx_id_first" btree (id)
    "idx_id_second" btree (id)
Has OIDs: no

测试结果：同一列上可以建立相同的两个索引。
这个特点对于维护索引来说，很方便。不用在删除旧索引后急着建立新索引。直接先使用concurrently关键字（用来防止建立索引时阻塞表的DML）建立新的索引，再删除旧的索引。



--Oracle下的测试

SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0    Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
 
SQL> create index idx_tmp_emp on scott.emp(job);
 
Index created
 
SQL> create index idx_tmp_emp2 on scott.emp(job);
 
create index idx_tmp_emp2 on scott.emp(job)
 
ORA-01408: such column list already indexed
 
SQL> create index idx_tmp_emp2 on scott.emp(job desc);
 
Index created

测试结果：同一列，不能建立相同的索引；但是，可以通过使用ASC或DESC来建立一个类似但不尽相同的索引。



PostgreSQL中以普通方式创建索引：
    索引创建时需要与表数据保持同步，执行创建语句时系统需要对表进行锁定，期间将会对表的delete、update、insert事务进行阻塞。在生产系统中创建索引需要注意创建的时间，如果对大数据量的表创建索引时，更应该选在系统空闲时。

PostgreSQL中使用CONCURRENTLY关键字创建索引：
    为了加快索引的创建速度postgresql引入了CONCURRENTLY选项，对索引进行并发创建。CONCURRENTLY选项能够使系统在不阻塞write操作的情况下执行索引创建。其实质是执行两次的表扫描，尽管解决了write的问题，但是需要更多的IO和CPU开销。