oracle为什么创建索引成功却用不了

最新推荐文章于 2024-05-04 08:19:01 发布
最新推荐文章于 2024-05-04 08:19:01 发布
阅读量876 收藏
点赞数
文章标签： oracle 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42613017/article/details/129536936
版权

Oracle创建索引成功却用不了可能有以下几个原因：

  1. 索引字段数据类型不匹配：如果创建索引时指定的字段类型与实际表中的数据类型不匹配，那么索引将不会起作用。

  2. 索引没有被启用：在创建索引时，如果没有指定启用选项，那么索引将默认为禁用状态。在这种情况下，即使索引已经创建成功，也不会起作用。需要手动启用该索引。

  3. 索引失效：如果表中的数据发生了大量变化，例如插入、更新或删除操作，可能会导致索引失效。这时需要重新构建该索引。

  4. 统计信息过期：Oracle中的索引依赖于表的统计信息，如果这些统计信息过期或者不存在，那么索引也无法起作用。可以使用ANALYZE命令或者DBMS_

weixin_42613017
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle删除主键查看主键约束及创建联合主键
01-19
1，主键的删除 ALTER TABLE TABLENAME DROP PRIMARY_KEY 执行上面的SQL可以删除主键；如果不成功可以用 ALTER TABLE TABLENAME DROP CONSTRAINTS COLUMN CASCADE; --删除约束 ALTER TABLE TABLENAME DISABLE PRIMARY_COLUMN ; --设置被设置为主键的列为无效 DROP INDEX INDEX_NAME; --删除主键索引 2,查看主键约束 SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='P' AND
oracle查询不走索引的一些情况（索引失效）
qq_56871738的博客
12-13 4757
oracle查询不走索引的一些情况（索引失效）
oracle 索引失效原因（不考虑SQL习惯）
cjing310的专栏
07-18 524
当出现以下情况时需考虑重建索引或调整优化模式 1、优化模式导致    如果当前优化模式是 first_rows_n 系统根据查询开销会统计出来all_rows更小。 2、索引级别过大     索引从根块到叶快的级别，或是深度。如果级别超过4级（没验证），可能导致统计结果很大。 3、索引表中已删除行过多     表中删除数据，索引会标识当前数据被删除，但是并不会把数据真的删除，删除数据过...
ORACLE重建索引总结
12-13
一、重建索引的前提 1、表上频繁发生update,delete操作； 2、表上发生了alter table ..move操作（move操作导致了rowid变化）。 二、重建索引的标准 1、索引重建是否有必要，一般看索引是否倾斜的严重，是否浪费了空间， 那应该如何才可以判断索引是否倾斜的严重，是否浪费了空间， 对索引进行结构分析（如下）： SQL>Analyze index index_name validate structure; 2、在执行步骤1的session中查询index_stats表，不要到别的session去查询。 SQL>select height,DEL_LF_ROWS/LF_ROWS from index_stats; 说明：当 查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 ， 该索引考虑重建 。 举例： (t_gl_assistbalance 26 万多条信息 ) SQL> select count(*) from t_gl_assistbalance ; 输出结果： COUNT(*) ---------- 265788 SQL> Analyze index IX_GL_ASSTBAL_1 validate structure; Index analyzed SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats; 输出结果： HEIGHT DEL_LF_ROWS/LF_ROWS ---------- ------------------- 4 1 三、重建索引的方式 1、drop 原来的索引，然后再创建索引; 举例： 删除索引：drop index IX_PM_USERGROUP; 创建索引：create index IX_PM_USERGROUP on T_PM_USER (fgroupid); 说明：此方式耗时间，无法在24*7环境中实现，不建议使用。 2 、直接重建： 举例： alter index indexname rebuild; 或alter index indexname rebuild online; 说明：此方式比较快，可以在24*7环境中实现，建议使用此方式。 四、alter index rebuild 内部过程和注意点 alter index rebuild 和alter index rebuil online的区别 1、扫描方式不同 1.1、Rebuild以index fast full scan（or table full scan） 方式读取原索引中的数据来构建一个新的索引，有排序的操作; 1.2、rebuild online 执行表扫描获取数据，有排序的操作; 说明：Rebuild 方式 (index fast full scan or table full scan 取决于统计信息的cost) 举例1 SQL> explain plan for alter index IX_GL_ASSTBAL_1 rebuild; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | | | 2 | SORT CREATE INDEX | | 999K| 4882K| | | 3 | INDEX FAST FULL SCAN | IDX_POLICY_ID2 | 999K| 4882K| | --------------------------------------------------------------------- 举例2 SQL> explain plan for alter index idx_policy_id rebuild; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 2072K| 9M| 461 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID | | | | | 2 | SORT CREATE INDEX | | 2072K| 9M| | | 3 | TABLE ACCESS FULL | TEST_INDEX | 2072K| 9M| 461 | 举例3 ( 注意和 举例1 比较 ) Rebuil online 方式 ： SQL> explain plan for alter index idx_policy_id2 rebuild online; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------| 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | | | 2 | SORT CREATE INDEX | | 999K| 4882K| | | 3 | TABLE ACCESS FULL | TEST_INDEX2 | 999K| 4882K| 3219 | 2 、rebuild 会阻塞 dml 操作 ，rebuild online 不会阻塞 dml 操作 ； 3 、rebuild online 时系统会产生一个 SYS_JOURNAL_xxx 的 IOT 类型的系统临时日志表 ， 所有 rebuild online 时索引的变化都记录在这个表中 ， 当新的索引创建完成后 ， 把这个表的记录维护到新的索引中去 ， 然后 drop 掉旧的索引 ，rebuild online 就完成了。 注意点： 1、 执行rebuild操作时，需要检查表空间是否足够； 2、虽然说rebuild online操作允许dml操作，但是还是建议在业务不繁忙时间段进行； Rebuild操作会产生大量redo log ； 五、重建分区表上的分区索引 重建分区索引方法： Alter index indexname rebuild partition paritionname tablespace tablespacename; Alter index indexname rebuild subpartition partitioname tablespace tablespacename; Partition name 可以从user_ind_partitions查找 Tablepace 参数允许alter index操作更改索引的存储空间； 六、索引状态描述 在数据字典中查看索引状态，发现有三种： valid:当前索引有效 N/A :分区索引 有效 unusable:索引失效 七、术语 1、高基数：简单理解就是表中列的不同值多。 2、低基数：建单理解就是表中的列的不同值少。 3、以删除的叶节点数量：指得是数据行的delete操作从逻辑上删除的索引节点 的数量，要记住oracle在删除数据行后，将 “ 死 “ 节点保留在索引中，这样做可以加快sql删除操作的速度，因此oracle删除数据行后可以不必重新平衡索引。 4、索引高度：索引高度是指由于数据行的插入操作而产生的索引层数，当表中添加大量数据时，oracle将生成索引的新层次以适应加入的数据行，因此，oracle索引可能有4层，但是这只会出现在索引数中产生大量插入操作的区域。Oracle索引的三层结构可以支持数百万的项目，而具备4层或是更多层的需要重建。 5、每次索引访问的读取数：是指利用索引读取一数据行时所需要的逻辑I/O操作数，逻辑读取不必是物理读取，因为索引的许多内容已经保存在数据缓冲区，然而，任何数据大于10的索引都需要重建。 6、什么时候重建呢？ 察看 dba_indexes 中的 blevel 。这列是说明索引从根块到叶快的级别，或是深度。如果级别大于等于4。则需要重建， 如下 ：Select index_name,blevel from dba_indexes where blevel>=4. 另一个从重建中受益的指标显然是当该索引中的被删除项占总的项数的百分比。如果在20%以上时，也应当重建，如下 SQL>analyze index index_name validate structure SQL>select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name= ’ index_name ’ 就能看到是否这个索引被删除的百分比。 7、什么样的重建方式更好？ （1）、建索引的办法： 1.1、删除并从头开始建立索引。 1.2 、 使用 alter index index_name rebuild 命令重建索引。 1.3 、 使用 alter index index_name coalesce 命令重建索引。 （2）、下面讨论一下这三种方法的优缺点： 2.1、删除并从头开始建索引：方法是最慢的，最耗时的。一般不建议。 2.2、Alter index index_name rebuild 快速重建索引的一种有效的办法，因为使用现有索引项来重建新索引，如果客户操作时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题，alter index index_name rebuild online。 但是,由于新旧索引在建立时同时存在，因此，使用这种技巧则需要有额外的磁盘空间可临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。 Alter index index_name rebuild tablespace tablespace_name 。 这个命令的执行步骤如下： 首先，逐一读取现有索引，以获取索引的关键字。 其次，按新的结构填写临时数据段。 最后，一旦操作成功，删除原有索引树，降临时数据段重命名为新的索引。 需要注意的是alter index index_name rebuild 命令中必须使用tablespace字句，以保证重建工作是在现有索引相同的表空间进行。 2.3、alter index index_name coalesce 使用带有coalesce参数时重建期间不需要额外空间，它只是在重建索引时将处于同一个索引分支内的叶块拼合起来，这最大限度的减少了与查询过程中相关的潜在的加锁问题，但是，coalesce选项不能用来将一个索引转移到其他表空间。 八、其他 1、truncate 分区操作和truncate 普通表的区别？ 1.1、Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响； 1.2、Truncate 分区操作不会释放全局索引中的空间，而truncate 普通表会释放索引所占空间； 2、rename 表名操作对索引没有影响，因为rename操作只是更改了数据字典，表中数据行的rowid并没有发生变化 总结： 1、判断是否需要重建索引： SQL>analyze index index_name validate structure; SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats; （ 或 Select index_name,blevel from dba_indexes where blevel>=4 ）; 说明 ： 当查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 ， 该索引考虑重建 ； 2 、重建索引方法 ： 方法一、 Alter index index_name rebuild tablespace tablespace_name; 优点：是快速重建索引的一种有效的办法，可以用来将一个索引移到新的表空间。 缺点：重建期间需要额外空间。 方法二、 alter index index_name coalesce; 优点：重建期间不需要额外空间。 缺点：coalesce选项不能用来将一个索引转移到其他表空间。
Oracle创建视图(View)
12-06
视图：是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表，Oracle的数据库对象分为五种：表，视图，序列，索引和同义词。 视图是存储在数据字典里的一条select语句。通过创建视图可以提取数据的逻辑上的集合或组合。 视图的优点： 1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。 2.用户通过简单的查询可以从复杂查询中得到结果。 3.维护数据的独立性，试图可从多个表检索数据。 4.对于相同的数据可产生不同的视图。 视图分为简单视图和复杂视图： 1、简单视图只从单表里获取数据，复杂视图从多表； 2、简单视图不包含函数和数据组，复杂视图包含； 3、简单视图可以实现DML操作，复杂视图不可以。 语法结构：创建视图 CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY] 语法解析： OR REPLACE ：若所创建的试图已经存在，则替换旧视图； FORCE：不管基表是否存在ORACLE都会自动创建该视图(即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用)； NOFORCE ：如果基表不存在，无法创建视图，该项是默认选项(只有基表都存在ORACLE才会创建该视图)。 alias：为视图产生的列定义的别名； subquery ：一条完整的SELECT语句，可以在该语句中定义别名； WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束； WITH READ ONLY ：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制(比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作)，WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。 案例3：基于EMP表和DEPT表创建视图 代码演示：视图 SQL> CREATE OR REPLACE VIEW EMPDETAIL 2 AS 3 SELECT EMPNO,ENAME,JOB,HIREDATE,EMP.DEPTNO,DNAME 4 FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO 5 WITH READ ONLY 6 / VIEW CREATED SQL> SELECT * FROM EMPDETAIL; ① EMPNO ENAME JOB HIREDATE DEPTNO DNAME 7369 SMITH CLERK 17-12月-80 20 RESEARCH 7499 ALLEN SALESMAN 20-2月 -81 30 SALES 7521 WARD SALESMAN 22-2月 -81 30 SALES 7566 JONES MANAGER 02-4月 -81 20 RESEARCH 7654 MARTIN SALESMAN 28-9月 -81 30 SALES 7698 BLAKE MANAGER 01-5月 -81 30 SALES 7782 CLARK MANAGER 09-6月 -81 10 ACCOUNTING 7788 SCOTT ANALYST 19-4月 -87 20 RESEARCH 7839 KING PRESIDENT 17-11月-81 10 ACCOUNTING 7844 TURNER SALESMAN 08-9月 -81 30 SALES 7876 ADAMS CLERK 23-5月 -87 20 RESEARCH 7900 JAMES CLERK 03-12月-81 30 SALES 7902 FORD ANALYST 03-12月-81 20 RESEARCH 7934
Oracle建立索引后没走
weixin_35756637的博客
01-01 782
如果 Oracle 建立索引后没有走，则可能是因为 Oracle 数据库中的查询优化器没有选择使用该索引。这可能是因为该索引不适合查询的过滤条件或排序要求，或者因为使用该索引的代价比不使用该索引的代价要高。 要解决这个问题，可以尝试改变索引的结构或收集更多有关表、索引和查询的统计信息，以帮助查询优化器作出更好的决策。还可以尝试使用一些数据库性能分析工具，如视图、指标和计划等，来诊断问题并找到解决方...
Oracle数据库创建索引后，索引不起作用的原因。
qq_44085065的博客
09-16 2088
‍1,<> 2,单独的>,<,(有时会用到，有时不会) 3,like "%_" 百分号在前. 4,表没分析. 5,单独引用复合索引里非第一位置的索引列. 6,字符型字段为数字时在where条件里不添加引号. 7,对索引列进行运算.需要建立函数索引. 8,not in ,not exist. 9,当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。 10, 索引失效。 11,基于cost成本分析(oracle因为走全表成本会更小)：查询小表,或者返回值大概在10
oracle执行计划中不走索引的情况
Melgards的博客
06-14 3525
转换成"Y"<=‘30a’ AND “Y”>=‘10a’select * from t where y not in (‘1a’,‘2a’);转换成"Y"<>‘1a’ AND “Y”<>‘2a’select * from t where y in (‘1a’,‘2a’);转换成"Y"=‘1a’ OR “Y”=‘2a’转换成"Y">=‘900a’索引列出现了函数，但是没有创建函数索引，那么就会导致索引失效。
oracle 索引无法被使用的N种情况以及应对方法
Hehuyi_In的博客
05-17 4543
有时我们创建了字段上的索引，但是通过执行计划却发现索引并没有被使用，还是会使用全表扫描。随着表上数据量的增长，性能会越来越差。如果不能查明原因，就只能盲目的靠不断扩容硬件来缓解（不是解决）这类问题，投资巨大，收益甚微。有时实在没办法，只能通过定期清空表来解决。 对于大表来说，能否使用到索引，效率上可能会相差上万倍（表越大，性能差距就越大）。下面我们就列举有索引，但是没有被使用的10几种情况，并给出对应的解决方法： 1.索引状态为UNUSABLE --普通索引 select * fro...
Oracle执行计划不走索引的原因总结
LIXG425的专栏
10-15 595
[size=medium] http://wzan315.blog.163.com/blog/static/371926362012224104352639/ 在Oracle数据库操作中，为什么有时一个表的某个字段明明有索引，当观察一些语的执行计划确不走索引呢？如何解决呢？本文我们主要就介绍这部分内容，接下来就让我们一起来了解一下。 一、 不走索引大体有以下几个原因： ...
用SQL建立索引的方法步骤
12-15
进入查询窗口后，输入下面的语句： CREATE INDEX mycolumn_index ON mytable (myclumn) 这个语句建立了一个名为mycolumn_index的索引。你可以给一个索引起任何名字，但你应该在索引名中包含所索引的字段名，这对你将来弄清楚建立该索引的意图是有帮助的。 注意： 在本书中你执行任何SQL语句，都会收到如下的信息： This command did not return data,and it did not return any rows 这说明该语句执行成功了。 索引mycolumn_index对表mytable的mycolumn字段进行。这是个
Oracle数据库表定时备份表 表名动态拼接时间戳存储过程
01-06
1、备份Oracle数据库表时添加动态时间戳，解决了ORA-00922: missing or invalid option 错误。 2、备份表结构和数据，还备份索引、序列、触发器等对象，提高了备份速度。但需要手动备份原始表的序列。 3、备份策略是存储过程，方便执行，每次备份表都有时间戳。 4、若备份文件过大，可以考虑增加备份频率或分批备份。 5、如遇到表正在被使用或锁定的情况，可能导致备份失败。建议在备份前确保表处于可备份状态。 6、在某些情况下，由于权限问题，可能无法成功创建或访问备份文件。确保执行备份的数据库用户具有适当的文件系统权限和数据库访问权限。权限错误码：ORA-01031:insufficient privileges 7、经典备份语句：CREATE TABLE TB_B AS SELECT * FROM TB_A; 本存储过程解决备份表名称时动态添加时间戳，例如表名称 TB_B20240106； 8、资源内容有： 8.1、P_TIMER_BAK_TABLE定时备份表 表名动态拼接时间戳.sql 8.2、Readme.txt
oracle索引失效原因
qq_15325579的博客
03-02 1018
Oracle索引失效的原因 1使用否定关键字 !=, select * fromdrama where id 2单独使用不等式关键字 直接用>或 3使用null关键字 is null或is not null 可能会失效 4左模糊或全模糊 like %放在前面 5索引列用函数 6条件中字符型字段赋值数字 7使用复合索引， 不使用第一个索引列 8索引失效 9刚建立新表，
oracle 索引未生效
fengqinyun168的专栏
05-06 1087
[code="java"] 不走索引大体有以下几个原因： • 你在Instance级别所用的是all_rows的方式 • 你的表的统计信息(最可能的原因) • 你的表很小，上文提到过的,Oracle的优化器认为不值得走索引。 解决方法： 可以修改init.ora中的OPTIMIZER_MODE这个参数,把它改为Rule或Choose,重起数据库。也可以使用4中所提的Hint。 不走索...
Oracle索引失效
热门推荐
桃李不言下自成蹊
07-07 6万+
SQL开发中经常遇到索引失效导致查询速度缓慢的情况， 究竟哪些情况会导致索引失效呢， 以下以Oracle数据库为例列举几种可能导致索引失效的情况
Oracle索引失效不起作用如何解决
snail_csdn的博客
11-01 6976
贴出千辛万苦找到的解决办法。当某表的某个字段建了索引，但是查询发现没有走索引，此时可能是索引失效。 1：强制走索引 原sql: select * from 表名 where id = 12345; 强制走索引: select /*+index(表名,索引名)*/ * from 表名 where id = 12345; 2：如果发现强制走索引查询成功，则使用 analyze table 表...
Oracle 不使用索引，索引失效的几种情况
soo的专栏
01-27 819
1, 2,单独的>, 3,like "%_" 百分号在前. 4,表没分析. 5,单独引用复合索引里非第一位置的索引列. 6,字符型字段为数字时在where条件里不添加引号. 7,对索引列进行运算.需要建立函数索引. 8,not in ,not exist. 9,当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。 10, 索引失效。 11,基于cost成
Oracle增加索引后，查看执行计划不生效问题
涛哥是个大帅比
01-26 1337
Oracle增加索引后，查看执行计划不生效解决方案：使用Analyze --① 分析表的所有字段，所有索引，所有索引字段。其中：① = ② analyze table student compute statistics; --② 分析表的所有字段，所有索引，所有索引字段。其中：① = ② analyze table student compute statistics for table for all indexes for all columns; --③ 只分析所有有索引的字段 ana
基于springboot的社区医院信息平台源码数据库
最新发布
s123456sj的博客
05-04 803
随着信息技术在管理上越来越深入而广泛的应用，管理信息系统的实施在技术上已逐步成熟。本文介绍了社区医院信息平台的开发全过程。通过分析社区医院信息平台管理的不足，创建了一个计算机管理社区医院信息平台的方案。文章介绍了社区医院信息平台的系统分析部分，包括可行性分析等，系统设计部分主要介绍了系统功能设计和数据库设计。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值