Oracle大数据and索引

来源：互联网发布：sql判断库存是否足够编辑：程序博客网时间：2024/05/16 06:30

Oracle大数据和索引

1 第一步：创建表

DROP TABLE MULTI_INDEX_TABLE ;

CREATE TABLE MULTI_INDEX_TABLE (

MI_ID VARCHAR2(10) PRIMARY KEY ,

MI_USERNAME VARCHAR2(10) ,

MI_PASSWORD VARCHAR2(10) ,

MI_EMAIL VARCHAR2(15) ,

MI_SEX CHAR(2) ,

MI_ADDRESS VARCHAR2(20)

);

2 第二步：创建(复合)索引

赋予用户使用索引的权限：grant execute on ctx_ddl to scott ;

为表MULTI_INDEX_TABLE创建复合索引：

CREATE INDEX MULTI_INDEX ON MULTI_INDEX_TABLE(MI_USERNAME,MI_EMAIL,MI_ADDRESS);

3 第三步：灌入大量数据

利用存储过程给表MULTI_INDEX_TABLE灌入大量条数据：

DECLARE

start_value number:=1 ;

end_value number:=100000 ;

BEGIN

FOR V_count IN start_value..end_value Loop

INSERT INTO MULTI_INDEX_TABLE VALUES(V_count ,'COB','123456','123433@SINA.COM','MA','Zhuhai,China') ;

COMMIT ;

end Loop;

END;

4 第四步：测试索引效果

4.1 打开AUTOTRACE功能

1) SET AUTOTRACE OFF -------------------- 不生成AUTOTRACE报告，这是缺省模式

2) SET AUTOTRACE ON EXPLAIN --------- AUTOTRACE只显示优化器执行路径报告

3) SET AUTOTRACE ON STATISTICS ----- 只显示执行统计信息

4) SET AUTOTRACE ON ---------------------- 包含执行计划和统计信息

5) SET AUTOTRACE TRACEONLY --------- 同set autotrace on，但是不显示查询输出

4.2 测试索引效果

输入SQL语句：

SELECT * FROM

(SELECT a.*, rownum rownum_

FROM (SELECT /*+index (MULTI_INDEX_TABLE MULTI_INDEX)*/ *

FROM multi_index_table WHERE mi_username = 'COLA') a

WHERE rownum <= 10) b

WHERE b.rownum_ > 0;

注：该SQL语句可能无法直接执行，需先将其复制到文本文档中并删除空白行，再复制执行即可。

由于开启了AUTOTRACE，执行SQL语句时显示主要是执行计划和统计信息：

返回的执行计划：

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

Plan hash value: 2358119074

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

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

(%CPU)| Time |

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

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

| 0 | SELECT STATEMENT | | 10 | 590 |

2 (0)| 00:00:01 |

|* 1 | VIEW | | 10 | 590 |

2 (0)| 00:00:01 |

|* 2 | COUNT STOPKEY | | | |

| |

| 3 | TABLE ACCESS BY INDEX ROWID| MULTI_INDEX_TABLE | 187K| 8417K|

2 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | MULTI_INDEX | 187K| |

1 (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

1 - filter("B"."R">0)

2 - filter(ROWNUM<=10)

4 - access("MI_USERNAME"='COLA')

Note

-----

- dynamic sampling used for this statement

从返回的执行计划可知：

|* 4 | INDEX RANGE SCAN |MULTI_INDEX | 187K| |

1 (0)| 00:00:01 |

表达执行该SQL语句时，使用了创建的复合索引MULTI_INDEX；

4 - access("MI_USERNAME"='COLA')

表达执行该SQL语句时，字段MI_USERNAME使用索引匹配。