精华内容
下载资源
问答
  • 2021-04-30 08:26:58

    我有桌子

    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多个字段联合索引保证唯一性

    千次阅读 2021-09-26 16:00:30
    今天在开发过程中遇到了需要两个字段联合保证唯一性,所以查阅了一些资料和博客,梳理一下以便之后用到!!! 首先,你要搞明白什么是唯一索引和唯一约束,其次它们之间的区别是什么? 前提 二者的定义 唯一...

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

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

    前提

    二者的定义

     唯一索引:一种索引,不允许具有索引值相同的行,从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值,并在每次使用 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

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

    展开全文
  • 当where子句对某一列使用函数时,除非利用...但如果使用了这些函数,则会出现一问题:这些函数会阻碍Oracle优化器对列使用索引,因而与采用索引的情况相比较,查询会花费更的时间。庆幸的是,如果在使用函数的这...

    当where子句对某一列使用函数时,除非利用这个简单的技术强制索引,否则Oracle优化器不能在查询中使用索引。

    通常情况下,如果在WHERE子句中不使用诸如UPPER、REPLACE或SUBSTRD等函数,就不能对指定列建立特定的条件。但如果使用了这些函数,则会出现一个问题:这些函数会阻碍Oracle优化器对列使用索引,因而与采用索引的情况相比较,查询会花费更多的时间。

    庆幸的是,如果在使用函数的这些列中包含了字符型数据,可以用这样一种方法修改查询语句,以达到强制性使用索引,更有效地运行查询。这篇文章介绍了涉及的技术,并说明了在两种典型情况下怎样实现。

    大小写混合情况

    在讨论由于函数修改了列的内容,如何强制使用索引前,让我们首先看看为什么Oracle优化器在这种情况下不能使用索引。假定我们要搜寻包含了大小写混合的数据,如在表1中ADDRESS表的NAME列。因为数据是用户输入的,我们无法使用已经统一改为大写的数据。为了找到每一个名为john的地址,我们使用包含了UPPER子句的查询语句。如下所示:

    [coce]SQL> select address from address where upper(name) like 'JOHN';[/coce]

    在运行这个查询语句前,如果我们运行了命令"set autotrace on", 将会得到下列结果,其中包含了执行过程:

    [coce]ADDRESS

    cleveland

    1 row selected.

    Execution Plan

    SELECT STATEMENT

    TABLE ACCESS FULL ADDRESS[/coce]

    可以看到,在这种情况下,Oracle优化器对ADDRESS表作了一次完整的扫描,而没有使用NAME列的索引。这是因为索引是根据列中数据的实际值建立的,而UPPER函数已经将字符转换成大写,即修改了这些值,因此该查询不能使用这列的索引。优化器不能与索引项比较"JOHN",没有索引项对应于"JOHN"-只有"john" 。

    值得庆幸的是,如果在这种情况下想要强制使用索引,有一种简便的方法:只要在WHERE子句中增加一个或多个特定的条件,用于测试索引值,并减少需要扫描的行,但这并没有修改原来SQL编码中的条件。以下列查询语句为例:

    [coce]SQL> select address from address where upper(name) like 'JO%' AND (name

    like 'J%' or name like 'j%');[/coce]

    使用这种查询语句(已设置AUTOTRACE),可得到下列结果:

    [coce]ADDRESS

    cleveland

    1 row selected.

    Execution Plan

    SELECT STATEMENT

    CONCATENATION

    TABLE ACCESS BY INDEX ROWID ADDRESS

    INDEX RANGE SCAN ADDRESS_I

    TABLE ACCESS BY INDEX ROWID ADDRESS

    INDEX RANGE SCAN ADDRESS_I[/coce]

    现在,优化器为WHERE子句中AND联结的两个语句中每一个语句确定的范围进行扫描----第二个语句没有引用函数,因而使用了索引。在两个范围扫描后,将运行结果合并。

    在这个例子中,如果数据库有成百上千行,可以用下列方法扩充WHERE子句,进一步缩小扫描范围:

    [coce]select address from address where upper(name) like 'JOHN' AND (name like 'JO%'

    or name like 'jo%' or name like 'Jo' or name like 'jO' );[/coce]

    得到的结果与以前相同,但是,其执行过程如下所示,表明有4个扫描范围。

    [code]Execution Plan

    SELECT STATEMENT

    CONCATENATION

    TABLE ACCESS BY INDEX ROWID ADDRESS

    INDEX RANGE SCAN ADDRESS_I

    TABLE ACCESS BY INDEX ROWID ADDRESS

    INDEX RANGE SCAN ADDRESS_I

    TABLE ACCESS BY INDEX ROWID ADDRESS

    INDEX RANGE SCAN ADDRESS_I

    TABLE ACCESS BY INDEX ROWID ADDRESS

    INDEX RANGE SCAN ADDRESS_I[/coce]

    如果试图进一步提高查询速度,我们可以在特定的"name like"条件中指明3个或更多的字符。然而,这样做会使得WHERE子句十分笨重。因为需要大小写字符所有可能的组合-joh ,Joh,jOh,joH等等。除此之外,指定一个或两个字符已足以加快查询的运行速度了。

    现在让我们看看,当我们引用不同的函数时,怎样运用这个基本技术。

    使用REPLACE的情况

    正如名字不总是以大写输入一样,电话号码也会以许多格式出现: 如 123-456-7890, 123 456 7890,(123)456-7890 等等。

    如果在列名为PHONE_NUMBER中搜寻上述号码时,可能需要使用函数REPLACE以保证统一的格式。如果在PHONE_NUMBER列中只包含空格、连字符和数字,where 子句可以如下所示:

    [coce]WHERE replace(replace(phone_number , '-' ) , ' ' ) = '1234567890'[/coce]

    WHERE子句两次使用REPLACE函数去掉了连字符和空格,保证了电话号码是简单的数字串。然而,该函数阻止了优化器在该列使用索引。因此,我们按如下方法修改WHERE子句,以强制执行索引。

    [coce]WHERE replace(replace(phone_number, '-' ) , ' ' ) = '1234567890'[/coce]

    AND phone_number like '123% '如果我们知道数据中可能包含圆括号,WHERE子句会稍微复杂一点。我们可以再增加REPLACE函数(去掉圆括号、连字符和空格),按如下所示扩充增加的条件:

    [coce]WHERE replace(replace(replace(replace(phone_number , ' - ' ) ,' '), '( ' )

    , ' ) ' ) = '1234567890'

    AND (phone number like ' 123% ' or phone_number like ' (123% ' ) '[/coce]

    该例强调了巧妙地选用WHERE子句条件的重要性,而且,这些条件不会改变查询结果。你的选择应基于完全了解该列中存在的信息类型。在该例中,我们需要知道PHONE_NUMBER数据中存在几种不同的格式,这样,我们能够修改WHERE子句而不会影响查询结果。

    正确的条件

    以后当你遇到包含CHARACTER数据修改函数列的WHERE子句时,应考虑怎样利用增加一个或两个特定的条件,迫使优化器使用索引。适当地选择一组特定的条件能减少扫描行,并且强制使用索引不会影响查询结果----但却提高了查询的执行速度。

    展开全文
  • 但如果使用了这些函数,则会出现一问题:这些函数会阻碍Oracle优化器对列使用索引,因而与采用索引的情况相比较,查询会花费更的时间。 庆幸的是,如果在使用函数的这些列中包含了字符型数据,可以用这样一种...
  • * 假如表A有索引(a, b, c),然后现在查询SQL是 ``` select * from A, B where b = xxx and a = xxx and d = xxx and c = xxx ``` 请问这种情况下生效的索引依然是(a, b, c)三列么?
  • oracle多个字段组成唯一索引约束

    千次阅读 2018-11-26 18:59:02
    已经验证!!!欢迎相互学习交流 --原来EXPENSE_ITEM_CODE, EXPENSE_TYPE_CODE, EXP_REPORT_TYPE_CODE这三个为唯一索引...注意事项(报错dupplicate keys found,若表中要创建的唯一索引约束的四个字段在数据库该表...
  • 背景: 为了提高数据库效率,建索引是家常便饭;那么...
  • 需求是表里的某个字段存储的值是以逗号分隔开来的,要求根据分隔的每一值都能查出来数据,但是不能使用like查询。 数据是这样的: 查询的sql如下: select * from ( select guid, regexp_substr(st_responsible...
  • oracle 批量更新之将一表的数据批量更新至另一oracle 批量更新之将一表的数据批量更新至另一表 CreationTime--2018年7月3日17点38分 Author:Marydon Oracle 将一表的指定字段的值更新至另一 ......
  • Oracle 19c 联合索引 vs 多个索引 我们在创建索引的时候。到底是 选择联合索引还是选择多个单列索引? 环境准备 数据库oracle 19c 100万条随机数据 单列索引多个) 建表 (多个单列索引测试): CREATE ...
  • 优化器采用的是RULE ,现有一表tab1,有字段 a,b ,c ,d ,e . 都是varchar2 类型 , 建立index 有c单独索引, a, b ,c 联合索引, 现在有sql 语句 : selectcount(*) fromtab1 where a=:1 and b=:2 a...
  • 只有一个多字段索引 PK_AV01 primary key (AV1, AV2,AV3) 我查下面的语句SELECT * FROM AV01 WHERE AV1='';能用上PK_AV01这索引 假如我的索引是只有AV1字段的, PK_AV02 primary key (AV1) 那么我用PK...
  • Oracle索引建立原则  · 确定针对该表的操作...  · 在经常存取的多个列上建立复合索引,但要注意复合索引的建立顺序要按照使用的频度来确定;  · 尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长
  • oracle 索引问题梳理

    2022-02-11 14:25:35
    索引是建立在表的一列或列上且进行排序的一种结构 通过指针快速定位数据行的方法,减少CPU和磁盘的I/O 索引是由Oracle自动使用和维护的,数据更新会自动传播到所有相关的索引 索引存在双面性,表面存在过多的索引...
  • Oracle数据库的字段类型

    千次阅读 2021-05-08 18:02:12
    字 段 类 型CHAR 固定长度字符串 最大长度2000 bytesVARCHAR2 可变长度的字符串 最大长度4000 bytes 可做索引的最大长度749NCHAR 根据字符集而定的固定长度字符串 最大长度2000 bytesNVARCHAR2 根据字符集而定的可变...
  • 1、联合索引是由多个字段组成的索引。 2、查询时使用联合索引的一个字段,如果这个字段在联合索引中所有字段的第一个,那就会用到索引,否则就无法使用到索引。 3、联合索引IDX(字段A,字段B,字段C,字段D),当仅...
  • ORACLE创建索引思路

    2021-12-23 17:37:25
    ORACLE创建索引思路 何为索引 索引是一种数据结构,是为了更快速的访问到数据。 索引是各种关系数据库系统最常见的一种逻辑单元,是关系数据库系统举足轻重的重要组成部分,对于提高检索数据速度有着至关重要的作用...
  • -- 然后设置索引, 下一次再查找的时候,从指定的索引位置开始(不是从0开始找了) i := j + len1; -- 结果追加一行. str_split.EXTEND; -- 设置结果内容. str_split(str_split.COUNT) := ty_row_str_...
  • 查看oracle中表的索引

    千次阅读 2021-05-08 07:19:15
    oracle中表的索引信息存在user_indexes 和user_ind_columns 两张表里面,其中,user_indexes 系统视图存放是索引的名称以及该索引是否是唯一索引等信息,user_ind_columns统视图存放的是索引名称,对应的表和列等sql...
  • oracle的联合约束(多个字段的唯一性)
  • 即有很独特值,则选择性很好)Oracle在UNIQUE和主键字段上自动建立索引在选择性很差的字段上建索引只有在这个字段的值分布非常倾斜的情况下才有益(在这种情况下,某一,两个字段值比其它字段值少出现很)不要在很少...
  • Oracle 添加主键和索引

    千次阅读 2021-05-08 00:35:31
    数据的主键和索引一般情况下都是必须的,特别是表有大量数据的时候,索引和主键更是必不可少,这样可以提供数据的查询效率;一、创建表的同时创建主键约束(1)无命名create table student (studentid int primary key...
  • oracle 索引优化

    2022-01-22 21:33:36
    oracle 索引选择 索引优化 查询优化
  • Oracle中的索引详解(整理)

    千次阅读 2021-05-04 08:51:04
    4、 演示 必须要分析表,并且 query_rewrite_enabled=TRUE 或者使用提示/*+ INDEX(ic_index)*/ 八、 反向键索引 目的:比如索引值是一个自动增长的列: 多个用户对集中在少数块上的索引行进行修改,容易引起资源的...
  • oracle 多个 left join

    千次阅读 2021-05-03 03:30:26
    场景:Oracle中SQL优化:left join查询语句,多个字段关联同一个表有关问题Oracle中SQL优化:left join查询语句,多个字段关联同一个表问题tabA表,此表字段:code1,code2,code3tabB表:此表字段:id,code,code...
  • 在网上看到oracle全文索引都是对一张表一个字段进行全文检索,我想对多个表多个字段按照关键字的匹配度排序,sql语句如下 select score(1) ,score(2), CDA.AREANAME,CDS.STREETNAME from C_DICT_STREET cds

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 106,060
精华内容 42,424
关键字:

oracle 多个字段 索引