关于索引的几个问题

最新推荐文章于 2023-11-29 14:23:24 发布
最新推荐文章于 2023-11-29 14:23:24 发布
阅读量342 收藏
点赞数
文章标签： 数据库
 

索引应该是sql优化中的一员大将了，近期学习了一下索引，希望能在此梳理一下我看到的和理解的有关索引的一些知识点。

1、索引列有null值，索引会失效，这是真的吗？

经过试验，是真的！索引列只要有null值，即使加上/*+INDEX(TABLE_NAME,INDEX_NAME)*/查询过程中也不会走索引

CREATE INDEX PK_TEST ON EMP(COMM);

SQL> select/*+INDEX(emp,pk_test)*/ comm from emp;

已选择13行。

执行计划

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

Plan hash value: 3956160932

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |    12 |    24 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    12 |    24 |     3   (0)| 00:00:01 |

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

1.1 如果是组合索引又会出现什么情况呢?好好奇啊，带我来一探究竟。。。。

SQL>create index index_emp on emp(empno,comm);

SQL> select empno,comm from emp;

     EMPNO       COMM

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

      7369

      7499        300

      7521        500

      7566

      7654       1400

      7698

      7782

      7788

      7839

      7844          0

      7876

     EMPNO       COMM

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

      7900

      7902

      7934

已选择14行。

执行计划

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

Plan hash value: 1443012493

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

| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |           |    14 |   364 |     1   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | INDEX_EMP |    14 |   364 |     1   (0)| 00:00:01 |

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

可以看出来，组合索引中有一个字段存在空值，还是可以通过走索引进行查询。但是如果存在两个值都是空值的情况呢？

SQL> select * from t;-----t中存在组合唯一索引t_idx(x,y)

         X          Y

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

         1          1

         1

                    1

执行计划

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

Plan hash value: 1601196873

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     4 |   104 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    |     4 |   104 |     3   (0)| 00:00:01 |

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

SQL> select /*+index(t,t_idx)*/ * from t;

         X          Y

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

         1          1

         1

                    1

 

执行计划

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

Plan hash value: 1601196873

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     4 |   104 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    |     4 |   104 |     3   (0)| 00:00:01 |

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

SQL> analyze index t_idx validate structure;

索引已分析

SQL> select name,lf_rows from index_stats;

NAME                              LF_ROWS

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

T_IDX                                   3

         1

                    1

 

执行计划

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

Plan hash value: 1601196873

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     4 |   104 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    |     4 |   104 |     3   (0)| 00:00:01 |

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

 

SQL> analyze index t_idx validate structure;

索引已分析

SQL> select name,lf_rows from index_stats;

NAME                              LF_ROWS

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

T_IDX                                   3

 最后一行（null,null）并不在索引列中

create table t(x int,y int);

create unique index t_idx on t(x,y);

insert into t(x,y) values (1,null);

insert into t(x,y) values (null,1);

insert into t(x,y) values (null,null);

insert into t(x,y) values (null,null);

insert into t(x,y) values (1,1);

insert into t(x,y) values (1,2);

select* from t;
bb

说明oracle并不认为（null，null）相等，但是（1，null）与（1，null）是相等的。只有在索引列中至少有一个not null值，才会使用索引。

2.视图能使用索引，这是真的么？

SQL> create or replace view v_emp as select * from emp;

视图已创建。

SQL> create index id_test1  on V_EMP(empno);

create index id_test1  on V_EMP(empno)  第 1 行出现错误:

ORA-01702: 视图不适用于此处

SQL> create index id_test1  on V_EMP(ename);

create index id_test1  on V_EMP(ename)

第 1 行出现错误:

ORA-01702: 视图不适用于此处

SQL> select empno from v_emp;

执行计划

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

Plan hash value: 179099197

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

| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |        |    12 |    48 |     1   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | PK_EMP |    12 |    48 |     1   (0)| 00:00:01 |

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

经过试验可以看出，使用视图查询时，可以完全考虑使用基表中已有的索引，对视图建立索引实际上是对基表建立索引

（以上可看，普通视图不能建立索引，但是对于物化视图则可以）。

当基表被删除时，视图会失效，索引会自动被清除。

SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OBJECT_T

YPE='VIEW' AND OWNER='SCOTT';

OWNER      OBJECT_NAM OBJECT_TYP STATUS

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

SCOTT      V_EMP_DEPT VIEW       INVALID

           10

SCOTT      V_EMP_DEPT VIEW       INVALID

           AVG

SCOTT      V_AVG      VIEW             INVALID

SCOTT      V2_DEPT10  VIEW       INVALID

3.太长了，不好布局，后面再接着好了。。。嘿嘿

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30018455/viewspace-1385192/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30018455/viewspace-1385192/

congwan1707
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql索引必须了解的几个重要问题
12-15
本文讲述了mysql索引必须了解的几个重要问题。分享给大家供大家参考，具体如下： 1、索引是做什么的? 索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关...
python 找出list中最大或者最小几个数的索引方法
09-20
在Python编程中，有时我们需要找出列表(list)中的最大或最小几个元素的索引。这个问题在数据分析、算法实现或者日常编程中都有可能遇到。本篇文章将详细介绍如何使用Python找到列表中最大或最小几个数的索引。 首先...
红黑树与AVL树比较
weixin_30703911的博客
09-03 123
链接地址：https://blog.csdn.net/zhangkunrun/article/details/38336543 B树相对于红黑树的区别 在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。为什么会出现这样的情况，我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数...
常见的索引相关问题
最新发布
Syasjy的博客
11-29 749
聚集索引记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。聚集索引对应的缺点就是修改慢， 因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。
有关索引建立的一些注意事项
非衣鲲化的博客
11-23 225
在创建索引以后，频繁的对数据库表进行delete 、update、insert 操作，那么索引的效率会变慢，比如一天内表数据增加百万条，并且是在含有索引的情况下，处理这种问题就要考虑索引建立与否，以及索引的管理 索引建立的类型，一般会建B-tree索引 可以在数据量少的情况下不建索引，当数据量达到一定程度的时候再建索引 索引进建立以后需要维护，可以定期重建索引，重新reindex索引速率会明...
视图与索引
ciqu9915的博客
01-18 421
视图view是我们在开发中经常使用的数据库对象。通过视图，我们可以将复杂查询逻辑屏蔽，向上提供更具有业务含义的数据访问接口。 本质上说，视图是一个SQL语句构成的虚拟表。我们对视图的查询就相当于对SQL语句形成数据...
有关于视图
Fred Pay的专栏
04-26 288
<br />http://tech.16c.cn/mysql/200903022889.html
分享几道关于MySQL索引的重点面试题
01-19
这几道题带你了解索引的几个重要知识点 1. 什么是最左前缀原则？ 以下回答全部是基于MySQL的InnoDB引擎 例如对于下面这一张表 如果我们按照 name 字段来建立索引的话，采用B+树的结构，大概的索引结构如下 如果...
数据库索引失效的几种情况
12-14
2. **多列索引未完全使用**：如果创建了一个多列索引，但在查询中只使用了索引列的一部分，那么从第二列开始的索引将不会被使用。例如，如果有(A, B, C)的联合索引，仅使用A列的查询将不会利用到B和C列的索引。 3. ...
oracle 重建表对应视图失效,Oracle表结构修改触发视图无法正常使用问题
weixin_29811891的博客
04-03 1126
一.问题描述当对视图使用的基表进行表结构修改后，会触发视图的无效以及编译出错问题，必须重建视图解决。二.问题再现1.Oracle10g环境1.1 创建视图测试用两张基表：TestTable和TestUser1.2 创建测试视图VW_TABLEUSERALL和VW_TABLEUSER前者使用了TestTable.*,后者显示指定列。查询视图的状态如下：全部是Valid状态1.3 修改TestTab...
sqlserver 建立索引视图各问题！
11-10 8651
--我的 tab 表有 60398 条数据,大家可以用自己的任意表测试 SELECT * FROM tab GO --新建表MYTABLE,带序号 IF object_id(N'[dbo].[MYTABLE]') IS NOT NULL drop table MYTABLE SELECT IDENTITY(INT,1,1) ID,* INTO MYTABLE FROM tab GO SE
oracle视图无法显示,Oracle创建视图显示无权限
weixin_33260057的博客
04-07 700
问题：使用scott登录Oracle以后，创建视图，提示“权限不够”，怎么解决？回答：这是因为scott这个帐户目前没有创建视图的权限。解决方法为：首先使用system帐户进行登录，其中“tigertiger”为安装Oracle时所指定的密码(可修改)：sqlplus system/tigertiger然后执行：grant create any view to scott提示：授权成功。执行：ex...
ORACLE视图错误处理
Reserved_person的博客
07-21 2451
Oracle视图非常强大的功能之一在于其可以创建一个带有错误的视图。比如说视图里的字段在基表里不存在，该视图仍然可以创建成功，但是非法的且无法执行。    所以在建立错误视图后执行就会报错：oracle错误分析：ora-04063:view view_test has errors  解决：查询视图中查询语句是否可以正确执行，视图字段是否正确 视图名称不能与表名称一样 在创
Update 视图“ORA-01733: 此处不允许虚拟列” 分享
N
03-09 2万+
ORA-01733: 此处不允许虚拟列 原因分析：一般对视图的更新，只能对简单视图进程更新，而不能对多表关联形成的视图进程更新。          对视图的更新，最终要反映到对表的字段的更新上。          Update 视图时，如果视图有分组函数、GROUP BY 子句、DISTINCT 关键字、ROWNUM 等伪列、常量表达式这些时，是不能修改视图的。 //把视图重新cr
Oracle 错误总结及问题解决 ORA
热门推荐
基于Oracle11G
07-08 18万+
ORA 错误大全
多列索引，哪几个有效索引
04-22
多列索引中，有效的索引取决于查询语句中涉及的列和表的结构。一般来说，当查询使用到索引的第一个列时，该索引才会被使用。...因此，你需要根据具体的查询语句和表结构来确定哪几个列是有效的索引列。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值