精华内容
下载资源
问答
  • oracle 排序字段加索引
    2021-05-04 03:49:30

    SQL> select rownum,t1. * from t1 where rownum >0  order by name desc;

    ROWNUM         ID NAME

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

    2          2 yourlove

    7          6 xifenfei

    5          8 ting

    6          7 really

    4          4 game

    8          5 feng

    3          3 eygle

    1          1 about

    已选择8行。

    SQL> alter table t1 add constraint pkKey primary key(name);

    表已更改。

    SQL> select rownum,t1. * from t1 where rownum >0  order by name desc;

    ROWNUM         ID NAME

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

    1          2 yourlove

    2          6 xifenfei

    3          8 ting

    4          7 really

    5          4 game

    6          5 feng

    7          3 eygle

    8          1 about

    已选择8行。

    SQL> select rownum,t1. * from t1 where rownum <3  order by name desc;

    ROWNUM         ID NAME

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

    1          2 yourlove

    2          6 xifenfei

    SQL> select rownum,t1. * from t1 where id>2  and rownum <3  order by name desc;

    ROWNUM         ID NAME

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

    1          6 xifenfei

    2          8 ting

    SQL> select rownum,t1. * from t1 where id>2 and rownum > 1 and rownum <3  order

    by name desc;

    未选定行

    SQL>

    注释:

    rownum和order by哪个先执行,由优化器决定的。优化器根据具体情况决定他们俩的执行顺序:

    一般先前者再后者。在orderby 的字段上加主键或索引即可让oracle先按该字段排序,然后再rownum。

    还有,有rownum的限制条件(如where子句里有rownum>2)在如where子句里,虽然where子句要早于rownum执行(rownum和order by都要晚于select子句执行,而select子句又晚于where子句执行),但是在where子句里面的rownum限制条件还是没有随着where子句执行而执行。

    更多相关内容
  • 转:首先,在大多数情况下,复合索引比单字段索引好.以税务系统的SB_ZSXX(申报类_征收信息表)为例,该表为税务系统最大的交易表.如果分别按纳税人识别号,税务机关代码,月份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

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

    展开全文
  • ORDER BY通常会有两种实现方法,一个是利用有序索引自动实现,也就是说利用有序索引的有序性就不再另做排序操作了。另一个是把结果选好之后再排序。用有序索引这种,当然是最快的,不过有一些限制条件,来看下面的...

    ORDER BY通常会有两种实现方法,一个是利用有序索引自动实现,也就是说利用有序索引的有序性就不再另做排序操作了。另一个是把结果选好之后再排序。

    用有序索引这种,当然是最快的,不过有一些限制条件,来看下面的测试。

    测试数据:student表有两个字段id ,sid ,id是主键。一共有20W条记录,id从1到200000,sid也是从1到200000的数据。

    第一种情况 : order by的字段不在where条件也不在select中

    select sid from student where sid < 50000 order by id;

    uid-27226965-id-4200305.html

    第二种情况 : order by的字段不在where条件但在select中。

    select id,sid from student where sid < 50000 order by id;

    uid-27226965-id-4200305.html

    第三种情况 : order by的字段在where条件但不在select中。

    select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id;

    uid-27226965-id-4200305.html

    第四种情况 : order by的字段在where条件但不在select中。倒序排列

    select sid from student where sid < 50000 and id < 50000 order by id desc;

    uid-27226965-id-4200305.html

    测试结果:

    order by的字段不在where条件不在select中     有排序操作

    order by的字段不在where条件但在select中     有排序操作

    order by的字段在where条件但不在select中     无排序操作

    order by的字段在where条件但不在select中(倒序)     无排序操作

    结论:

    当order by 字段出现在where条件中时,才会利用索引而无需排序操作。其他情况,order by不会出现排序操作。

    分析:

    为什么只有order by 字段出现在where条件中时,才会利用该字段的索引而避免排序。这要说到数据库如何取到我们需要的数据了。

    一条SQL实际上可以分为三步。

    1.得到数据

    2.处理数据

    3.返回处理后的数据

    比如上面的这条语句select sid from student where sid < 50000 and id < 50000 order by id desc

    第一步:根据where条件和统计信息生成执行计划,得到数据。

    第二步:将得到的数据排序。

    当执行处理数据(order by)时,数据库会先查看第一步的执行计划,看order by 的字段是否在执行计划中利用了索引。如果是,则可以利用索引顺序而直接取得已经排好序的数据。如果不是,则排序操作。

    第三步:返回排序后的数据。

    另外:

    上面的5万的数据sort只用了25ms,也许大家觉得sort不怎么占用资源。可是,由于上面的表的数据是有序的,所以排序花费的时间较少。如果 是个比较无序的表,sort时间就会增加很多了。另外排序操作一般都是在内存里进行的,对于数据库来说是一种CPU的消耗,由于现在CPU的性能增强,对 于普通的几十条或上百条记录排序对系统的影响也不会很大。但是当你的记录集增加到上百万条以上时,你需要注意是否一定要这么做了,大记录集排序不仅增加了 CPU开销,而且可能会由于内存不足发生硬盘排序的现象,当发生硬盘排序时性能会急剧下降。

    注:ORACLE或者DB2都有一个空间来供SORT操作使用(上面所说的内存排序),如ORACLE中是用户全局区(UGA),里面有SORT_AREA_SIZE等参数的设置。如果当排序的数据量大时,就会出现排序溢出(硬盘排序),这时的性能就会降低很多了。

    总结:

    当order by 中的字段出现在where条件中时,才会利用索引而不排序,更准确的说,order by 中的字段在执行计划中利用了索引时,不用排序操作。

    这个结论不仅对order by有效,对其他需要排序的操作也有效。比如group by 、union 、distinct等。

    展开全文
  • 选择索引字段的原则:在WHERE子句中最频繁使用的字段联接语句中的联接字段选择高选择性的字段(如果很少的字段拥有相同值,即有很多独特值,则选择性很好)Oracle在UNIQUE和主键字段上自动建立索引在选择性很差的字段上建...

    一、B-Tree索引

    1.  选择索引字段的原则:

    在WHERE子句中最频繁使用的字段

    联接语句中的联接字段

    选择高选择性的字段(如果很少的字段拥有相同值,即有很多独特值,则选择性很好)

    Oracle在UNIQUE和主键字段上自动建立索引

    在选择性很差的字段上建索引只有在这个字段的值分布非常倾斜的情况下才有益(在这种情况下,某一,两个字段值比其它字段值少出现很多)

    不要在很少独特值的字段上建B-TREE索引,在这种情况下,你可以考虑在这些字段上建位图索引.在联机事务处理环境下,并发性非常高,索引经常被修改,所以不应该建位图索引

    不要在经常被修改的字段上建索引.当有UPDATE,DELETE,INSETT操作时,ORACLE除了要更新表的数据外,同时也要更新索引,而且就象更新数据一样,或产生还原和重做条目

    不要在有用到函数的字段上建索引,ORACLE在这种情况,优化器不会用到索引,除非你建立函数索引

    可以考虑在外键字段上建索引,这些索引允许当在主表上UPDATE,DELETE操作时,不需要共享子表的锁,这非常适用于在父表和子表上有很多并发的INSERT,UPDATE和DELETE操作的情况

    当建立索引后,请比较一下索引后所获得的查询性能的提高和UPDATE,DELETE,INSERT操作性能上的损失,比较得失后,再最后决定是否需建立这个索引

    2.  选择建立复合索引

    复合索引的优点:

    改善选择性:复合索引比单个字段的索引更具选择性

    减少I/O:如果要查询的字段刚好全部包含在复合索引的字段里,则ORACLE只须访问索引,无须访问表

    什么情况下优化器会用到复合索引呢?

    (a) 当SQL语句的WHERE子句中有用到复合索引的领导字段时,ORACLE优化器会考虑用到复合索引来访问.

    (b) 当某几个字段在SQL语句的WHERE子句中经常通过AND操作符联合在一起使用作为过滤谓词,并且这几个字段合在一起时选择性比各自单个字段的选择性要更好时,可

    能考虑用这几个字段来建立复合索引.

    (c) 当有几个查询语句都是查询同样的几个字段值时,则可以考虑在这几个字段上建立复合索引.

    复合索引字段排序的原则:

    确保在WHERE子句中使用到的字段是复合索引的领导字段

    如果某个字段在WHERE子句中最频繁使用,则在建立复合索引时,考虑把这个字段排在第一位(在CREATE INDEX语句中)

    如果所有的字段在WHERE子句中使用频率相同,则将最具选择性的字段排在最前面,将最不具选择性的字段排在最后面

    如果所有的字段在WHERE子句中使用频率相同,如果数据在物理上是按某一个字段排序的,则考虑将这个字段放在复合索引的第一位

    二、位图索引

    什么情况下位图索引能够改善查询的性能呢?

    WHERE子句包含多个谓词于中低基数的字段

    单个的谓词在这些中低基数的字段上选取大量的行

    已经有位图索引创建于某些或全部的这些中低基数的字段上

    被查询的表包含很多行

    可以在单一个表上建立多个位图索引,因此,位图索引能够改善包含冗长WHERE子句的复杂查询的性能,在合计查询和星形模型的联接查询语句中,位图索引也可以提供比较优良的性能

    位图索引与B-TREE索引的比较

    位图索引更节省存储空间

    位图索引比较适用于数据仓库环境,但不适于联机事务处理环境.在数据仓库环境,数据维护通常上通过批量INSERT和批量UPDATE来完成的,所以索引的维护被延迟直到DML操作结束.举例:当你批量插入1000行数据时,这些插入的行被放置到排序缓存中(SORT BUFFER),然后批处理更新这1000个索引条目,所以,每一个位图段在每一个DML操作中只需更新一次,即使在那个位图段里有多行被更新

    一个键值的压缩位图是由一个或多个位图段所组成,每一个位图段大约相当于半个BLOCK SIZE那么大,锁的最小粒度是一个位图段,在联机事务处理环境,如果多个事务执行同时的更新(即并发的更新),使用位图索引就会影响UPDATE,INSERT,DELETE性能了

    一个B-TREE索引的条目只包含一个ROWID,因此,当一个索引条目被锁定,即一行被锁定.但是对于位图索引, 一个索引条目潜在地有可能包含一段ROWID(即某一个范围内的ROWID,有多个ROWID),当一个位图索引条目被锁定时,则这个条目包含的那一段ROWID都被锁定,从而影响并发性.当一个位图段内的ROWID的数量越多时,并发性就越差.虽然如此,对于BULK INSERT,UPDATE和DELETE,位图索引的性能还是比B-TREE索引要好0b1331709591d260c1c78e86d0c51c18.png

    展开全文
  • 为时间字段加索引

    千次阅读 2020-03-24 18:29:58
    文章目录为时间字段加索引(待更进)一、问题1、描述:日期不一致2、原因:时区不同3、解决方法: 时区修改二、datetime和varchar类型效率比较1、背景2、开始三、时间字段加索引1、聚集索引2、非聚集索引3、时间字段...
  • oracle排序、回表扫描和联合索引

    千次阅读 2018-07-03 16:46:04
    一.oracle回表扫描例子:test 表 10w数据,10个字段,主键为id1.select * from test where id = 5回表扫描介绍:执行这个sql的执行计划,可以看到,首先它走了主键的索引,可以看到一个index rang...
  • 1. 建测试表s_table,并在empno和ename列上创建唯一索引table s_table as select * from test where empno<10;Table created.unique index idx_s on s_table(empno,ename);Index created.empno,ename from s_...
  • 系统:Linux AS3.0+ Oracle 9i RAC Dedicated Server OLTP 系统 概述 优化器采用的是RULE ,现有一个表tab1,有字段 a,b ,c ,d ,e . 都是varchar2 类型 , 建立index 有c单独索引, a, b ,c 联合索引, 现在有sql ...
  • 问题的起因是DBA通知说在一套之前上线的系统中,对日期创建索引无法生效,导致大量的日期查询效率无法通过创建索引得到提升。通过分析得出如下的结论:在JDBC中一般通过prepareStatement的各种setXxx()来传递参数,...
  • 背景: 为了提高数据库效率,建索引是家常便饭;那么...
  • oracle 索引失效的原因

    千次阅读 2021-05-08 05:19:36
    引起oracle索引失效的原因很多: 1、在索引列上使用函数。如SUBSTR,DECODE,INSTR等,对索引列进行运算.需要建立函数索引就可以解决了。 创建函数索引方式: 表dept,有col_1,col_2,现在对col_1做upper函数索引 这样...
  • Oracle索引

    千次阅读 2021-05-01 03:35:30
    Oracle索引Oracle索引(index)最大的作用是用来优化数据库查询的效率,提升数据库的查询性能。...创建索引就是对某些特定列中的数据进行排序或归类,生成独立的索引表。在某列上创建索引后,如果该列出现在查询条...
  • 降序索引降序索引是8i里面新出现的一种索引,是B*Tree的另一个衍生物,它的变化就是列在索引中的储存方式从升序变成了降序,在某些场合下降序索引将会起作用。举个例子,我们来查询一张表并进行排序:SQL> select ...
  • 试想这样一种情况,一个插入非常频繁的表A,在列ID存在一个索引INDEX_A_ID,假如索引的生成顺序是每次插入1。如:123124125那么这几个索引键值非常非常大的可能是存储在一个数据块上的,而在集群环境中,数据块...
  • oracle为什么不走索引

    2021-05-01 11:38:00
    总是使用索引。总是从驱动表开始(from子句最右边的表)。只有在不可避免的情况下,才使用全表扫描。任何索引都可以基于成本的优化器。需要表、索引的统计资料Analyze table customer compute statistics;Analyze ...
  • Oracle创建索引的基本规则

    千次阅读 2021-05-03 05:20:03
    选择索引字段的原则:在WHERE子句中最频繁使用的字段联接语句中的联接字段选择高选择性的字段(如果很少的字段拥有相同值,即有很多独特值,则选择性很好)Oracle在UNIQUE和主键字段上自动建立索引在选择性很差的字段上...
  • Oracle中的索引详解(整理)

    千次阅读 2021-05-04 08:51:04
    对一列或多列建的索引索引分离于表,作为一个单独的个体存在,除了可以根据单个字段创建索引,也可以根据多列创建索引Oracle要求创建索引最多不可超过32列。 基于函数(function-based)的索引:基于某些函数...
  • oracle三大索引类型

    千次阅读 2021-09-09 14:25:04
    统计类查询SQL:count(), avg(), sum(), max(), min() 排序操作:order by字段建立索引 去重操作:distinct UNION/UNION ALL:union all不需要去重,不需要排序 2. 联合索引 应用场景一:SQL查询列很少,建立查询列...
  • 第一:索引查询cassandra是支持创建二级索引的,索引可以创建在除了第一个主键之外所有的列上,当然有些类型除外,例… oracle查询当前用户下所有的表,包括所有的字段 背景: ​ 前两天接到一个需求,做一个展示所有表名,...
  • 时间索引无效,很头疼

    千次阅读 2021-05-07 02:50:56
    数据仓库中有个表A这个表A 中共有记录数1182481条我执行一个查询 ,这个查询...=DATE'2012-2-1'AND DIM_DATE我DIM_DATE日期列上也已经建立了索引,按道理查询结果只占总数的1%,应该走索引啊,可是它偏偏走了全表扫描...
  • 浅谈Oracle优化与索引

    千次阅读 2021-08-16 11:24:26
    1.对需要经常用到查询的字段建立索引; 2.利用Oracle的解析方式进行数据优化,Oracle的解析方式是自后而前,自右向左,自下而上的方式进行解析SQL,所以利用这个原理,from后面如果有3个以上的表连接查询,将被其他...
  • Oracle 索引命中详解

    千次阅读 2021-05-03 06:31:48
    整理一下Oracle数据查询索引命中情况,仅供参考,有问题欢迎指出修正:创建测试表:CREATE TABLE TEST_HZ(A VARCHAR(20),B VARCHAR(20),C VARCHAR(20));插入数据: 本人比较懒,然后就直接建序列插数据create ...
  • 一、简介 索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与...
  • oracle索引详解

    千次阅读 2021-05-01 08:31:45
    索引对于Oracle学习来说,非常重要,在数据量巨大的状况下,使用恰到好处的索引,将会使得数据查询时间大大减少。1、索引的创建语法:CREATE UNIUQE | BITMAP INDEX .ON .( | ASC | DESC, | ASC | DESC,...)...
  • --table_name这张表的索引应当将idcard作为索引,这样就会查询的快 select a.name,a.age,a.idcard from table_name where a.idcard='123456' --这里可将tablename1的upload_date创建为索引, --同时where后面的...
  • 复合索引顺序选择性问题(一)

    千次阅读 2021-05-03 06:00:20
    索引是我们经常选择的数据表检索优化方案之一。其中,复合索引是我们经常选择的策略。那么,构建索引列的顺序上,有何种差异和需要注意的方面呢?下面我们通过实验来进行说明...Connected to Oracle DatabaseEnterpr...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 59,001
精华内容 23,600
热门标签
关键字:

oracle 排序字段加索引