sql 索引 index之oracle篇

今天尝试一下，以面试官的视角来说说索引。

 

面试官：看你做过一段时间的数据库维护，简单介绍下索引吧？

你：索引是为表服务的一种数据结构，默认是B-TREE（Balance Tree, 平衡树）。它可以帮助提高查询速度，但是会占用额外的磁盘空间。

 

面试官： 你刚刚说的那个B-TREE介绍一下？

你： B-TREE规定如果树有M阶，叶子节点可以有M个节点，至少有M/2个子节点，根节点除外，它至少得有两个子节点。

 

面试官：那它和二叉树相比是如何提高效率的？

你：一次查询就相当于一次二分查找，因为是M叉树，而不是二叉树，所以可以减少了同等数据量，查找时经过的子节点数，而且规定必须至少有M/2个子节点，一定程度上保证了叶子节点的利用率。所以节省了存取的时间。

 

面试官： 可以，你刚说索引占用了额外的磁盘空间，怎么计算它占用了多少磁盘呢？

你： 哦，Oracle把数据库做了逻辑划分，最大的是tablespace，它包含N个segment，segment包含N个extent，extent包含N个block，这个block就是磁盘上的数据块，不过Oracle会按自己的格式重新格式化磁盘的数据块。在dba的表里有一张dba_segments表。

-- 可以利用它来计算索引的大小。

select round(sum (bytes)/1024/1024/1024, 2) || ' GB' AS index_size

from dba_segments

where segment_type= 'INDEX' and segment_name = 'PM_PROJECT_PK';

-- 还可以计算表的大小。

select round(sum (bytes)/1024/1024/1024, 2) || ' GB' AS table_size

from dba_segments

where segment_type= 'TABLE' and segment_name = 'PM_PROJECT';

 

面试官：行，那要是索引占用的空间太大，想给它做分区，你会怎么做？

你：索引是用来服务表的，所以索引分区应该是基于表的分区的来做，索引分区一般选择做成本地索引，可以由数据库自己维护，全局索引不能和表的分区结构匹配，最多只能做个范围分区。

分区的话先准备tablespace，然后创建表时按tablespace分区，索引也是一样，按tablespace分区，就是表和索引要放在同一个表空间里。分区关键字就是partition。

CREATE TABLESPACE tbs_1 DATAFILE '/path/tbs1.dat' SIZE 10M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

-- 同上 tbs_2, tbs_3

CREATE TABLE PM_PROJECT (

    ID NUMBER(18),

    NAME VARCHAR2(256),

    PARTITION BY RANGE(ID) (

        PARTITION D1 VALUES LESS THAN (5300000) TABLSPACE TBS_1,

        PARTITION D2 VALUES LESS THAN (5600000) TABLSPACE TBS_2,

        PARTITION D3 VALUES LESS THAN (MAXVALUE) TABLSPACE TBS_3

    )

);



CREATE INDEX PM_PROJECT_IND ON PM_PROJECT (ID)

LOCAL

(

    PARTITION D1 TABLSPACE TBS_1,

    PARTITION D2 TABLSPACE TBS_2,

    PARTITION D3 TABLSPACE TBS_3

);

最后通过视图 DBA_IND_PARTITIONS 查看索引分区信息。

 

面试官：可以可以，除了普通的索引，我们经常要用到函数，但是有时会导致索引失效，特别是在使用用户自定义函数的时候。你会怎么解决这个问题？

你：Oracle提供基于函数的索引，所以直接为使用函数的列创建基于函数的索引就行了。

 

面试官：那如何查看一个表是否有基于函数的索引呢？

你： 首先我利用视图dba_indexes去查下这个表的索引，并且配合index_type=FUNCTION-BASED NORMAL关键字找出。然后从视图DBA_IND_COLUMNS, DBA_IND_EXPRESSIONS分别找出列和函数的表达式。

SELECT * FROM dba_indexes

WHERE TABLE_NAME = 'PM_PROJECT' AND INDEX_TYPE = 'FUNCTION-BASED NORMAL';

-- IX_PM_PROJECT_ACTIVITY



SELECT *

FROM DBA_IND_COLUMNS

WHERE index_name = 'IX_PM_PROJECT_ACTIVITY';

-- SYS_NC00031$



SELECT *

FROM DBA_IND_EXPRESSIONS

WHERE index_name = 'IX_PM_PROJECT_ACTIVITY';

-- COLUMN_EXPRESSION UPPER(TO_CHAR("PROJECT_ACTIVITY_DATE",'DD-MON-RRRR'))

面试官： 行，会去等通知吧。