精华内容
下载资源
问答
  • 今天在开发过程中遇到了需要两个字段联合保证唯一性,所以查阅了一些资料和博客,梳理一下以便之后用到!!! 首先,你要搞明白什么是唯一索引和唯一约束,其次它们之间的区别是什么? 前提 二者的定义 唯一...

         今天在开发过程中遇到了需要两个字段联合保证唯一性,所以查阅了一些资料和博客,梳理一下以便之后用到!!!

      首先,你要搞明白什么是唯一索引和唯一约束,其次它们之间的区别是什么?

    前提

    二者的定义

     唯一索引:一种索引,不允许具有索引值相同的行,从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值,并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。     

     唯一约束:唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的(联合字段中,可以包含空值。)   

    备注:在Oracle中,唯一约束最多可以有32列。唯一约束可以在创建表时或使用ALTER TABLE语句创建

    二者的区别

    约束和索引,前者是用来检查数据的正确性,后者用来实现数据查询的优化,目的不同。
    唯一约束与唯一索引有所不同
    1、创建唯一约束会在Oracle中创建一个Constraint,同时也会创建一个该约束对应的唯一索引。
    2、创建唯一索引只会创建一个唯一索引,不会创建Constraint。也就是说其实唯一约束是通过创建唯一索引来实现的。
    在删除时这两者也有一定的区别:删除唯一约束时可以只删除约束而不删除对应的索引,所以对应的列还是必须唯一的,而删除了唯一索引的话就可以插入不唯一的值。

    目标

        接下来该干正事了,怎么创建联合索引保证唯一性!!!

    --示例表
    CREATE TABLE BIZ_PERSON(
      VARCHAR2(32) primary key ,
      NAME VARCHAR2(32) ,
      AGE  VARCHAR2(32)
    );
    
    --创建两个字段唯一索引,此时已能保证数据的唯一性
    CREATE UNIQUE INDEX UNIQUE_AGE_NAME ON 
    BIZ_PERSON(NVL2(NAME,AGE,NULL),NVL2(NAME,NAME,NULL));
    
    --如何查询当前表的索引和约束?
    --查询索引
    SELECT 
    b.uniqueness, 
    a.index_name, 
    a.table_name, 
    a.column_name 
    FROM all_ind_columns a, all_indexes b
    WHERE a.index_name=b.index_name AND a.table_name = upper('BIZ_PERSON')
    --查询约束
    SELECT * FROM all_constraints WHERE table_name='BIZ_PERSON';
     
    --删除 唯一索引
    drop index UNIQUE_AGE_NAME;
    --删除 唯一约束
    ALTER TABLE BIZ_PERSON DROP CONSTRAINT 约束名称;
     

    知识点扩展

    NULL指的是空值,或者非法值。
    NVL (expr1, expr2)->expr1为NULL,返回expr2;不为NULL,返回expr1。注意两者的类型要一致
    NVL2 (expr1, expr2, expr3) ->expr1不为NULL,返回expr2;为NULL,返回expr3。expr2和expr3类型不同的话,expr3会转换为expr2的类型
    NULLIF (expr1, expr2) ->相等返回NULL,不等返回expr1

    参考资料

    唯一索引_百度百科 (baidu.com)   

    唯一约束_百度百科 (baidu.com)                                                  

    (3条消息) oracle的联合约束(多个字段的唯一性)_sqiucheng的专栏-CSDN博客

       

        

    展开全文
  • 如果分别按纳税人识别号,税务机关代码,月份3个字段查询,每个字段在该表中的可选性或约束性都不强,如一个纳税人识别号有很纳税记录,一个税务机关代码和同一月份记录就更了,所以3个字段合起来,"某个纳税人识别号+...

    转:

    首先,在大多数情况下,复合索引比单字段索引好.以税务系统的SB_ZSXX(申报类_征收信息表)为例,该表为税务系统最大的交易表.如果分别按纳税人识别号,税务机关代码,月份3个字段查询,每个字段在该表中的可选性或约束性都不强,如一个纳税人识别号有很多纳税记录,一个税务机关代码和同一月份记录就更多了,所以3个字段合起来,"某个纳税人识别号+某个税务机关代码+某月"的记录就少多了.因此复合索引比单字段索引的效率高多了.很多系统就是靠新建一些合适的复合索引,使效率大幅度提高.

    但是,复合索引比单字段索引的内容原理复杂,复合索引有两个重要原则需要把握: 前缀性和可选性.如果糊里糊涂的滥用复合索引,效果适得其反.

    以例子来说明,例子如下:

    假设在员工表(emp)的(ename,job,mgr)3个字段上建了一个索引,例如索引名叫idx_1.3个字段分别为员工姓名,工作和所属经理号.然后,写如下一个查询语句,并不断进行查询条件和次序的排列组合,例如:

    Sql代码

    select * from emp where ename = 'a' and job = 'b' and mgr = 3 ;

    select * from emp where job = 'b' and ename = 'a' and mgr = 3 ;

    select * from emp where mgr = 3 and ename = 'a' and job = 'b' ;

    select * from emp where mgr = 3 and job = 'b' and ename = 'a' ;

    select * from emp where job = 'b' and mgr = 3 and ename = 'a' ;

    .....

    回答问题:在各种条件组合情况下,刚才建的索引(idx_1) 是用还是不用?也就是说对emp表的访问是全表扫描还是按索引(idx_1)访问?

    答案是 :  上述语句中只要有ename='a'条件,就能用上索引(ind_1),而不是全表扫描(这就是复合索引的前缀性).

    复合索引的原理和设计建议

    1.复合索引的第一个建议: 前缀性(Prefixing)

    先从例子说起.假设省,市,县分别用3个字段存储数据,并建立了一个复合索引.请记住: oracle索引,包括复合索引都是排序的.例如该复合索引在数据库索引树上是这样排序的,即先按省排序,再按市排序,最后按县排序:

    省  市  县

    北京  北京  东城

    北京  北京  西城

    北京  北京  海淀

    ... ...

    黑龙江  哈尔滨  道里区

    黑龙江  哈尔滨  道外区

    黑龙江  哈尔滨  香坊区

    ... ...

    黑龙江  齐齐哈尔 龙沙区

    黑龙江  齐齐哈尔 铁锋区

    黑龙江  齐齐哈尔 富拉尔基区

    ... ...

    湖南  长沙  芙蓉区

    湖南  长沙  岳路区

    湖南  长沙  开福区

    ... ...

    oracle不是智能的,它只会按图索骥,该索引结构是先按省排序的,所以只要给出省名,就能使用索引.如果没有省名,oracle就成了无头苍蝇,乱找一气,变成了全表扫描了.例如,如果你只给一个县条件,如"开福区",oracle肯定不会使用该索引了.

    2.关于skip scan index

    有时候复合索引第一个字段没有在语句中出现,oralce也会使用该索引.对,这叫oralce的skip scan index功能,oracle 9i才提供的.

    skip scan index功能适合于什么情况呢?如果oracle发现第一个字段值很少的情况下,例如假设emp表有gender(性别)字段,并且建立了(gender,ename,job,mgr)复合索引.因为性别只有男和女,所以为了提高索引的利用率,oracle可将这个索引拆成('男',ename,job,mgr),('女',ename,job,mgr)两个复合索引.这样即便没有gender条件,oracle也会分别到男索引树和女索引树进行搜索.

    但是,(gender,ename,job,mgr)索引本身设计是不合理的,它违背了复合索引的第二个原理,可选性(Selectivity),见下面描述.

    3.复合索引的第二个原理:可选性(Selectivity)

    您可能会问:复合索引中如何排序字段顺序?这时就要用到复合索引的第二个原理:可选性(Selectivity)规则.oracle建议按字段可选性高低进行排序,即字段值多的排在前面.例如,(ename,job,mgr,gender),(县,市,省).这是因为,字段值多,可选性越强,定位的记录越少,查询效率越高.例如,全国可能只有一个"开福区",而湖南省的记录则太多了.

    4.复合索引设计建议

    (1).分析SQL语句中的约束条件字段.

    (2).如果约束条件字段比较固定,则优先考虑创建针对多字段的普通B*树复合索引.如果同时涉及到月份,纳税人识别号,税务机关代码3个字段的条件,则可以考虑建立一个复合索引.

    (3).如果单字段是主键或唯一字段,或者可选性非常高的字段,尽管约束条件比较固定,也不一定要建成复合索引,可建成单字段索引,降低复合索引开销.

    (4).在复合索引设计中,需首先考虑复合索引的第一个设计原理:复合索引的前缀性.即在SQL语句中,只有将复合索引的第一个字段作为约束条件,该复合索引才会启用.

    (5).在复合索引设计中,其实应考虑复合索引的可选性.即按可选性高低,进行复合索引字段的排序.例如上述索引的字段排序顺序为:纳税人识别号,税务机关代码,月份.

    (6).如果条件涉及的字段不固定,组合比较灵活,则分别为月份,税务机关代码和纳税人识别号3个字段建立索引.

    (7).如果是多表连接SQL语句,注意是否可以在被驱动表(drived table)的连接字段与该表的其他约束条件字段上创建复合索引.

    (8).通过多种SQL分析工具,分析执行计划以量化形式评估效果.

    ---------------------

    作者:lovely可爱欧辰

    来源:CSDN

    原文:https://blog.csdn.net/liqfyiyi/article/details/13290201

    版权声明:本文为博主原创文章,转载请附上博文链接!

    展开全文
  • 当我在两列上创建索引时 create index table1_idx1 on table1(column1,coulmn2); ERROR at line 1: ORA-01408: such column list already indexed 因此Oracle在创建唯一约束时已经创建了索引.但如果我单独创建...

    我有桌子

    create table1(

    column1 number(10,column2 number(10),column3 number(10)

    );

    column1是主键

    column2和column3是外键

    我在2列上创建了唯一约束

    alter table table1

    add constraint table1_contr1 unique(column1,column2)

    using index tablespace tbs1;

    当我在两个列上创建索引时

    create index table1_idx1 on table1(column1,coulmn2);

    ERROR at line 1:

    ORA-01408: such column list already indexed

    因此Oracle在创建唯一约束时已经创建了索引.但如果我单独创建索引,它就会接受这些索引

    create index table1_idx1 on table1(column1);

    create index table2_idx2 on table2(column2);

    现在我的问题是,在对两列都有唯一约束后,我还需要担心在每列上创建索引吗?在访问对象时,每列上没有索引会对性能产生影响吗?

    这是在oracle 11R2上.

    展开全文
  • Oracle修改字段类型后索引错误的解决方案起因:由于在查询中需要用到 UNION ALL 操作,而表中的存在 LONG 类型字段,操作无法完成,根据具体业务场景,将 LONG 类型字段修改为 CLOB 类型。修改后,UNION ALL 操作...

    Oracle修改字段类型后索引错误的解决方案

    起因:

    由于在查询中需要用到 UNION ALL 操作,而表中的存在 LONG 类型字段,操作无法完成,根据具体业务场景,将 LONG 类型字段修改为 CLOB 类型。修改后,UNION ALL 操作可行,但是子表的增删改操作出现了问题,提示为父表的 INDEX 不可用。

    这里顺便说一句:如果要从VARCHAR2类型修改为特殊类型CLOB,那么不能直接从VARCHAR2转换为CLOB,需要使用LONG类型,来做一个过渡(可以先修改为LONG类型,然后从LONG修改为CLOB)。

    解决方案:

    1.同事前不久也遇到了这个问题,他的解决方案是:备份旧的表,删除表,然后再导入数据,并且依然需要重建主键等。对没有直接操作权限的ORCALE服务器恐怕有难度。所以这里归根结底还是drop and re-create。

    2.重建索引。Oracle重建索引有多种方式,如 drop and re-create、rebuild、rebuild online等。这里采用的是第一种方式删除创建。需要的一些脚本如下(TB_SCENERY是父表【景点表】,TB_SCENERY_TICKETS是子表【景点门票表】):

    --查询主外键,表名一定要大写

    select constraint_name from user_constraints where table_name = 'TB_SCENERY';

    select constraint_name from user_constraints where table_name = 'TB_SCENERY_TICKETS';

    --删除主外键,注意大小写

    alter table TB_SCENERY drop constraint SCENERY_PK_ID;

    alter table TB_SCENERY_TICKETS drop constraint SCENERY_ID;

    --增加主键(增加主键时会自动建立关于主键的索引)

    alter table TB_SCENERY add constraint SCENERY_PK_ID primary key (ID) ;

    --增加外键

    alter table TB_SCENERY_TICKETS add constraint SCENERY_PK_ID foreign key (SCENERY_ID) references TB_SCENERY (ID);

    --查询表的相关索引

    select index_name,index_type,table_name from user_indexes where table_name='TB_SCENERY';

    --删除索引[强制]

    DROP INDEX SCENERY_PK_ID [FORCE];

    --查询哪些表没有建立索引

    SELECT table_name FROM User_tables t WHERE NOT EXISTS (SELECT table_name FROM User_constraints c WHERE constraint_type = 'P' AND t.table_name=c.table_name)

    这里的步骤是:

    1.查询子表的外键名称,删除对应的外键,以及外键对应的索引

    2.查询主表的主键名称,删除对应的主键,以及主键对应的索引

    3.建立主表主键(主键索引会自动建立),建立子表外键,建立外键索引

    3.drop and re-create 方式的有点是速度快,缺点是会影响原有的SQL查询,如果考虑这个影响那就可以采用rebuild的方式(IDX_TEST_C1这是索引名称):

    ALTER INDEX IDX_TEST_C1 REBUILD;

    其实开始是尝试的是rebuild的方式,但是失败了,无可奈何只能采用drop and re-create 方式。

    展开全文
  • SQL> select * from v$...BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionPL/SQL Relea...
  • 11我们知道索引列如果出现null值会导致索引失效。具体情况如下:表中数据如下: 可见storeid列有null值。当执行查询语句:select * from demo where ...有两种方法:1、建立函数索引 create index xxx on 表名(列...
  • 如员工表,员工工号(code)一般是唯一的,但是员工职位可以变化,那么就会出现条记录,但是只会有一条记录的active为‘Y’,其余的都为‘N’,这样就不能在code这个字段上加唯一索引。实际的情况是如果active为‘Y...
  • 3、经常与其他表进行连接的表,在连接字段上应该建立索引;4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;5、索引应该建在选择性高的字段上;6、索引应该建在小字段上,对于大的文本字段甚至...
  • 当where子句对某一列使用函数时,除非利用这...但如果使用了这些函数,则会出现一问题:这些函数会阻碍Oracle优化器对列使用索引,因而与采用索引的情况相比较,查询会花费更的时间。 庆幸的是,如果在使用函...
  • 天萃荷净ORACLE 12C 支持在相同列创建多个索引,从ORACLE 12C开始允许在同一列上创建不同类型的index,用来做性能调优,不同类型的index主要包括(B*TREE vs BITMAP,Local vs Global)1.ORACLE 11GSQL> select * ...
  • Oracle 19c 联合索引 vs 多个索引 我们在创建索引的时候。到底是 选择联合索引还是选择多个单列索引? 环境准备 数据库oracle 19c 100万条随机数据 单列索引多个) 建表 (多个单列索引测试): CREATE ...
  • 复合索引用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引)。复合索引的创建方法与创建单一索引的方法完全一样。但复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引。如何某表的某个字段有...
  • 4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引; 5、索引应该建在选择性高的字段上; 6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引; 7、复合索引的建立需要进行仔细分析...
  • Oracle 大表建立索引

    2021-05-03 03:46:54
    Oracle 大表建立索引祖仙教小凡仙 海鲨数据库架构师 数据库有2亿记录的表,发现须要添加一联合索引,结果就采用普通的create index index_name on tablename (entp_id,sell_date),结果悲剧了,把全部的DML语句都...
  • oracle索引详解

    2021-05-01 08:31:45
    索引对于Oracle学习来说,非常重要,在数据量巨大的状况下,使用恰到好处的索引,将会使得数据查询时间大大减少。1、索引的创建语法:CREATE UNIUQE | BITMAP INDEX .ON .( | ASC | DESC, | ASC | DESC,...)...
  • 下面是转换出来的查询语句 SELECT * FROM (SELECT "Project1"."C1" AS "C1", "Project1"."ID" AS "ID", "Project1"."NVC_ORDERBY" AS "NVC_ORDERBY", "Project...如果我不去掉这两排序字段,如何建立索引优化查询速度
  • ORACLE数据库,有一表,有1.3千万行数据,经常和别的表作交叉查询,比如统计... 比如再建立个索引?哈哈SELECT "INP_BILL_DETAIL"."PATIENT_ID", 病人ID"INP_BILL_DETAIL"."VISIT_ID", 病人住院次数"INP_BILL_D...
  • 当某些行具有空值时,应如何索引日期列?...使用date列上的索引,另一个不为null列我对选择的想法是:到1:表必须有许多不同的值来使用位图索引到2:我必须为此目的添加一个字段,并在我想要检索空日期...
  • oracle为什么不走索引

    2021-05-03 04:40:43
    ename from tb2 whereempno=...(这可能性非常大)借网上文章看看后,还是不知原因,文章如下oracle 优化器不走索引原因SQL优化器简介基于规则的优化器。总是使用索引。总是从驱动表开始(from子句最右边的表)。只...
  • 展开全部上面这张图e5a48de588b...下面再写几种常见索引的创建吧:(1) create index 索引名 on 表(字段名); //创建B树索引,一般用的OLTP中(2) create bitmap index 索引名on表(字段名); //...
  • Oracle 大表创建索引

    2021-05-05 01:09:17
    结果就采用普通的create index index_name on tablename (entp_id,sell_date),结果悲剧了,把所有的DML语句都阻塞了,导致系统不能正常使用,还好是晚上10点,用户不是非常,1小时候,索引结束,阻塞解决;...
  • 即有很独特值,则选择性很好)Oracle在UNIQUE和主键字段上自动建立索引在选择性很差的字段上建索引只有在这个字段的值分布非常倾斜的情况下才有益(在这种情况下,某一,两个字段值比其它字段值少出现很)不要在很少...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 63,875
精华内容 25,550
关键字:

oracle多个字段建立索引