精华内容
下载资源
问答
  • 2020-05-06 18:39:48

    --查看索引

    select * from all_indexes where table_name = '表名(大写)';

    --查看索引和列名

    select* from all_ind_columns where table_name = '表名(大写)';

    --增加索引

    create index I_IC_HANDRESERVE_26 ON IC_HANDRESERVE(CBIZID);

    create index 索引名 ON 表名(字段名);

     

    更多相关内容
  • 当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优化器对列使用索引,因而与采用索引的情况相比较,查询会花费更多的时间。 庆幸的是,如果在使用函数的这些列中包含了字符型数据,可以用这样一种...
  • date字段上建立了索引,但是sql语句中使用的where条件如下:WHERE to_char(start_time,’yyyy-mm-hh hh24:mi:ss’) > ‘2009-08-01 00:00:00′ AND to_char(start_time,’yyyy-mm-hh hh24:mi:ss’) < '2009-08...

    date字段上建立了索引,但是sql语句中使用的where条件如下:

    WHERE to_char(start_time,’yyyy-mm-hh hh24:mi:ss’) > ‘2009-08-01 00:00:00′ AND to_char(start_time,’yyyy-mm-hh hh24:mi:ss’) < '2009-08-31 23:59:59';

    因为使用了to_char函数,造成了索引没有使用。正确的写法如下:

    WHERE start_time > to_date(‘2009-08-01 00:00:00′ ,’yyyy-mm-hh hh24:mi:ss’) AND start_time < to_date('2009-08-31 23:59:59', ,'yyyy-mm-hh hh24:mi:ss');

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    以下内容引自itpub论坛:

    问题:

    环境:AIX 4.3.3,oracle9.2RAC,表mytab共有数据15万条左右上。

    在日期型字段(mydate)上建了一个索引(create index idx1 on mytab(mydate)),

    可是SQL(select mydate from mytab where to_char(mydate,’yyyymmdd’)=’20040310’)根本不使用这个索引。

    后来在该字段上建了一个函数索引:to_char(mydate,’yyyymmdd’)之后,该sql可以使用这个索引了,但是一旦加上order by mydate(select mydate from mytab where to_char(mydate,’yyyymmdd’)=’20040310′ order by mydate)之后,oracle又不使用索引了。真是非常困惑。

    把数据倒到Redhat AS3+oracle10上,现象也很费解。建索引:create index idx1 on mytab(to_char(mydate,’yyyymmdd’)),SQL(select mydate from mytab where to_char(mydate,’yyyymmdd’)=’20040310’)和SQL(select mydate from mytab where to_char(mydate,’yyyymmdd’)=’20040310′ order by mydate)都能使用该索引。

    那位大虾能解释上述现象啊?

    答复:

    我觉得你用to_char转换索引字段不可取

    where to_char(mydate,’yyyymmdd’)=’20040310’)

    可以转换

    where mydate >= to_date(‘20040310′,’yyyymmdd’)

    and mydate < to_date('20040311','yyyymmdd')

    原帖地址:http://www.itpub.net/thread-208701-1-1.html

    标签:索引

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

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

    为时间字段加索引(待更进)

    一、问题

    1、描述:日期不一致
    • 在centos7中运行docker,docker中运行mysql,在IDEA中将日期数据写入dates表中

    • dates表
      在这里插入图片描述

    • 插入日期数据

    @Test
    void contextLoads() {
      Date date = new Date(System.currentTimeMillis());
      System.out.println(date);   //Tue Mar 24 13:08:49 CST 2020
      questionMapper.insertdateTime(date);
    }
    
    • mysql中dates数据

    在这里插入图片描述

    2、原因:时区不同

    ​ 发现后台输入和数据库中的数据相差了8个小时,因为系统时区和docker中的mysql时区相差8个时区

    [root@localhost ~]# docker exec -it mysql01 /bin/bash
    root@1c202aea2496:/# mysql -uroot -p  
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2604
    Server version: 5.7.29 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show variables like '%time_zone%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | UTC    |
    | time_zone        | SYSTEM |
    +------------------+--------+
    2 rows in set (0.01 sec)
    

    备注:

    • 在连接mysql时记得有"-u"参数,否则会出现Ignoring query to other database错误
    3、解决方法: 时区修改
    mysql> set global time_zone = '+8:00';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like '%time_zone%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | UTC    |
    | time_zone        | SYSTEM |
    +------------------+--------+
    2 rows in set (0.02 sec)
    
    • 再次插入日期数据
    Tue Mar 24 13:32:39 CST 2020
    
    • 数据查看
      在这里插入图片描述

    二、datetime和varchar类型效率比较

    ​ 我记得之前我是通过修改了my.conf文件,进而修改了docker中mysql的日期格式,起效果后,但是下次重启mysql时,启动不容器。打开错误日志后,告诉我my.conf里出现错误,我迫不得已将日期格式改了回来,将datetime改成了varchar类型,来存储时间。

    ​ 至于如何让docker中的mysql重启的,详见这里

    ​ 那么datetime和varchar在查找和内存消耗方面有什么区别吗?

    以下测试摘录于这里

    1、背景

    大家都知道数据库表字段设计得是否合理,对查询速度的快慢至关重要,下面做个简单的测试,看下差距有多大

    2、开始

    1、在数据库中新建两个表 test1(不合理的表)test2(合理表),两张表的 send_time 字段的类型不一样

    CREATE TABLE `test1` (
    	 `id` int(11) NOT NULL AUTO_INCREMENT,
    	`send_time` varchar(20) DEFAULT NULL,
    	PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    CREATE TABLE `test2` (
    	 `id` int(11) NOT NULL AUTO_INCREMENT,
    	`send_time` datetime DEFAULT NULL,
    	PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    

    2、新建存储过程,导入10 000 000 条测试数据(运行过程可能需要几分钟),运行结束后再将数据导入表test2保证两张表的数据一致

    BEGIN
    DECLARE i INT DEFAULT 0;
    START TRANSACTION;
    	WHILE i<10000000 DO
    INSERT INTO test1(send_time) VALUES(from_unixtime(1541302365+FLOOR(rand()*154130236),"%Y-%m-%d %H:%i:%s"));
    SET i=i+1;
    END WHILE;
    COMMIT;
    END
    
    

    3、查看表信息可得:
    在这里插入图片描述
    在这里插入图片描述

    结论:varchar 表字段占用存储空间 是 datetime 表的三倍左右
    

    4、查询速度比较

    	SELECT * FROM test1 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
    	SELECT * FROM test2 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
    	
    	结果比较
    	test1			test2
    	2.653s			1.833s
    	2.650s			1.866s
    
    

    5、给表添加索引(执行过程需要几分钟),并查询速度比较

    	-- 添加索引		
    	ALTER TABLE `test1` ADD INDEX `n_sendtime` (`send_time`) ;
    	ALTER TABLE `test2` ADD INDEX `n_sendtime` (`send_time`) ;
    
    	-- 查询速度比较:
    	SELECT * FROM test1 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
    	SELECT * FROM test2 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
    	
    	结果比较
    	test1			test2
    	0.084s			0.048s
    	0.062s			0.046s
    	0.062s			0.048s
    	0.066s			0.047s
    
    

    4、结论

    • 合理的字段类型不论对 查询速度 或是 数据存储 都至关重要
    • 时间字段用dateTime等时间类型,不要用varchar类型

    三、时间字段加索引

    1、聚集索引
    • 表记录的排列顺序和与索引排列顺序一致

    • 一个表中只能拥有一个聚集索引

    • 修改慢。为了保证表中记录的物理和索引顺序一致,在记录插入的时候,会对数据页重新排序

    • 聚集索引的叶节点就是最终的数据节点

    2、非聚集索引
    • 逻辑顺序与磁盘上行的物理存储顺序不同
    • 一个表中可以拥有多个非聚集索引
    • 非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据的指针
    动作描述使用聚集索引使用非聚集索引
    列经常被分组排序
    返回某范围内的数据不应
    一个或极少不同值不应不应
    小数目的不同值不应
    大数目的不同值不应
    频繁更新的列不应
    外键列
    主键列
    频繁修改索引列不应
    3、时间字段是否适合加索引

    以下摘自此处

    • 可以建立索引的;至于建立聚集索引或者是非聚集索引,那要看你这个时间字段的具体情况以及使用或变更频繁程度。

    • 一般来说,适合建立聚集索引的要求:“既不能绝大多数都相同,又不能只有极少数相同”的规则。

    • 先说说一个误区:有人认为:只要建立索引就能显著提高查询速度。这个想法是很错误的。建立非聚集索引,确实,一般情况下可以提高速度,但是一般并不会达到你想要的速度。只有在适当的列建立适当的(聚集)索引,才能达到满意的效果

    • 考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据,在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

    聚集索引和非聚集索引根本区别以及使用方式

    ​ 这就得看看项目中对该时间字段的具体操作了。

    ---- 待更新

    四、参考文档

    1、聚集索引和非聚集索引根本区别以及使用方式

    2、mysql索引的应用

    3、聚集索引和非聚集索引 简析与对比

    4、索引深入浅出(3/10):聚集索引的B树结构

    展开全文
  • NULL 博文链接:https://hackpro.iteye.com/blog/1845366
  • Oracle多个字段联合索引保证唯一性

    千次阅读 2021-09-26 16:00:30
    今天在开发过程中遇到了需要两个字段联合保证唯一性,所以查阅了一些资料和博客,梳理一下以便之后用到!!! 首先,你要搞明白什么是唯一索引和唯一约束,其次它们之间的区别是什么? 前提 二者的定义 唯一...
  • Oracle数据库操作中,为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢?如何解决呢?本文我们主要介绍这部分内容,接下来让我们一起来了解一下。  不走索引大体有以下几个原因: ...
  • left join关联关系字段加索引

    千次阅读 2021-05-01 06:21:10
    left join关联关系字段加索引2018-07-04left join是相当耗资源的操作,如果关联的字段没有索引的话,速度是很慢的,所以如果有left join的话,最好用索引字段取关联。创建索引会消耗大量资源,会导致数据库死锁,最好...
  • oracle 全文检索 oracle全文索引 多列字段检索,匹配多列字段搜索功能。
  • 时间字段加索引

    万次阅读 2018-11-14 22:05:20
    微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别:...
  • oracle clob类型字段查询方法

    千次阅读 2021-05-03 05:00:16
    今天要查询clob类型字段为空的记录。颇费一番周折才找到一个方法,记录以供参考。select s.testcaseid from b_t_testcasestepinfo s where pjid = '2013WARD120006'and actorphase = 1and not (s.stepid = 1 and s....
  • 转:首先,在大多数情况下,复合索引比单字段索引好.以税务系统的SB_ZSXX(申报类_征收信息表)为例,该表为税务系统最大的交易表.如果分别按纳税人识别号,税务机关代码,月份3个字段查询,每个字段在该表中的可选性或约束...
  • Oracle数据添加索引时请online

    千次阅读 2020-06-04 16:34:22
    create index 索引名 on表名(字段名)online; 问题描述 添加索引大家都知道,但是添加索引的过程中Oracle会锁全表, 不久前因为添加索引的问题,造成了线上很多笔业务出现了异常 解决方案 online关键字是...
  • oracle数据库中字段为空时如何建立索引 直接在字段有空值的列上建立一个函数索引 这里我们用到 decode函数 我们可以建立一个函数索引 DECODE(字段名,NULL,‘0’,NULL) 建立好函数索引之后 我们想要用上此索引 查询...
  • 怎样让日期范围走索引?

    千次阅读 2021-05-04 02:32:32
    满意答案Oracle中我们经常使用Date字段类型记录日期和时间,有的时候还在这个字段上建立索引。然后通过Java程序访问数据库的时候,我们很自然的类似这样使用:select * from table where endDate>? and endDate,...
  • oracle数据库加索引

    千次阅读 2021-03-26 09:44:01
    CREATE INDEX IDEN_IDNUMBER–>索引名称 ON SIDENTITY–>表名 ( IDNUMBER–>字段名 );
  • oracle数据库添加索引

    千次阅读 2020-08-11 10:25:15
    第一步先查看原先的索引(如果没创建过的话应该默认有一条主键的索引) select index_name from all_...create index索引名 on picture (字段名); 切记: 索引并不是创建越多越好 ,自行搜索索引创建规则。 ...
  • 需求是表里的某个字段存储的值是以逗号分隔开来的,要求根据分隔的每一个值都能查出来数据,但是不能使用like查询。 数据是这样的: 查询的sql如下: select * from ( select guid, regexp_substr(st_responsible...
  • ERROR at line 1: ORA-01408: such column list already indexed 因此Oracle在创建唯一约束时已经创建了索引.但如果我单独创建索引,它就会接受这些索引 create index table1_idx1 on table1(column1); create index...
  • 虽然存储介质和数据处理技术的发展也很快,但是仍然不能满足用户的需求,为了使用户的大量的数据在读写操作和查询中速度更快,Oracle提供了对表和索引进行分区的技术,以改善大型应用系统的性能。
  • 时间字段都要加索引

    千次阅读 2018-01-04 17:30:00
    时间字段都要加索引
  • 3、查询用户表的索引(非聚集索引): 4、查询用户表的主键(聚集索引): 5、查询表的索引 6、查询表的主键 7、查找表的唯一性约束(包括名称,构成列): 8、查找表的外键 查询外键约束的列名: 查询引用表的键的...
  • Oracle 分区表上的索引

    千次阅读 2021-05-02 07:35:23
    分区表上的索引分为:本地(局部)索引(local index) 和 全局索引(global index)1、本地索引(1)普通索引SQL > CREATE INDEX INDEX_NAME ON TABLE (COLUMN)local(partition part_idx_01 tablespace index_space01,...
  • Select *FROM dba_ind_columnsWhere index_owner='TEST73' AND table_name=upper('tx_log_t')可以查询数据库的表上面的索引字段。参考博文:Question: I added an index hint in my query, but the hint is being ...
  • 选择索引字段的原则:在WHERE子句中最频繁使用的字段联接语句中的联接字段选择高选择性的字段(如果很少的字段拥有相同值,即有很多独特值,则选择性很好)Oracle在UNIQUE和主键字段上自动建立索引在选择性很差的字段上建...
  • 通常我们库中数据量大时,往往会降低我们的查询效率,那么增加索引可以提高查询效率。 CREATE INDEX IDEN_IDNUMBER-->索引名称 ON SIDENTITY-->表名 ( IDNUMBER-->字段名 );
  • 主要大家介绍了关于Oracle Index索引无效的原因与解决方法,文中通过示例代码以及图文介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • NULL 博文链接:https://panshaobinsb.iteye.com/blog/1772716

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 122,162
精华内容 48,864
关键字:

oracle给时间字段加索引