如何在Oracle 12c中使用条件创建索引? [英] How to create indexes with conditions in Oracle 12c?
本文介绍了如何在Oracle 12c中使用条件创建索引?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想将PostgreSQL数据库迁移到Oracle 12c,例如,在下面的表创建中,我发现了一些困难:
I want to migrate a PostgreSQL database into Oracle 12c, and for example in the table creation below, I've found some difficults:
- 如何(如果可能)使用 where 子句创建这些唯一索引?
- 如何正确添加此主键pessoa_id?
- 为什么会出现此错误缺少右括号"?
- How to (if possible) to create these unique indexes with where clause?
- How to properly add this primary key pessoa_id?
- Why I get this error 'missing right parenthesis'?
代码:
create table nano.pessoa (
id GENERATED BY DEFAULT ON NULL AS IDENTITY,
nome varchar(99) not null,
sobrenome varchar(99) not null,
nascimento_dt date,
registro varchar(32) not null,
fisica_sn bool default true, -- no caso de pf, true, pf, falso
matriz_sn bool default false, -- no caso de pf, é o genero, masculino = true
confirmado_sn bool default false,
constraint pessoa_id primary key(id)
);
create unique index pessoa_juridica_cnpj_ix on pessoas.pessoa (registro) where (fisica_sn = false);
create unique index pessoa_fisica_ix on pessoas.pessoa (nome, sobrenome, nascimento_dt) where (fisica_sn = true);
create unique index pessoa_fisica_cpf_ix on pessoas.pessoa (registro) where ((fisica_sn = true) and ((registro <> null) and (registro <> '')));
推荐答案
Oracle DB中没有条件索引,您可以将逻辑转换为基于函数的索引.
there is no conditional index in Oracle DB, you can convert the logic to be function based indexes.
以下内容:
create unique index pessoa_juridica_cnpj_ix on pessoa (registro) where (fisica_sn = false);
在Oracle SQL中出现:
becomes in Oracle SQL :
create unique index pessoa_juridica_cnpj_ix on pessoa (case when fisica_sn = false then registro end) ;
这篇关于如何在Oracle 12c中使用条件创建索引?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文