概述
今天主要介绍下Oracle索引的常见执行计划:
这里用简单的测试案例,体会下索引使用这些执行计划的场景。
1. 准备测试环境
创建测试表和索引:
- drop table test_objects;
- create table test_objects as select * from all_objects;
- create index idx_test_objects_1 on test_objects(owner, object_name, subobject_name);
- create index idx_test_objects_2 on test_objects(object_id);
- desc test_objects;
查看测试表上的索引信息:
- select index_name, column_name, column_position from user_ind_columns where table_name = 'TEST_OBJECTS';
分析表并清空测试环境的shared_pool和buffer_cache:
- analyze table test_objects compute statistics;
- alter system flush shared_pool;
- alter system flush buffer_cache;
2. 准备SQL语句
根据不同执行计划的场景,编写SQL语句:
- --INDEX RANGE SCAN(索引的范围扫描)
- SELECT owner, object_name FROM test_objects WHERE owner = 'SYS' AND object_name = 'DBMS_OUTPUT';
- --INDEX SKIP SCAN(针对MAX(),MIN()函数的查询)
- SELECT owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
- --INDEX FAST FULL SCAN(索引的快速全扫描,多块读,无序)
- SELECT owner, object_name FROM test_objects;
- --INDEX FULL SCAN(索引的全扫描,单块读,有序)
- SELECT owner, object_name FROM test_objects order by 1, 2;
- --INDEX FULL SCAN (MIN/MAX)(针对MAX(),MIN()函数的查询)
- SELECT max(object_id) FROM test_objects;
3. 实验论证
结果如下:
(1) INDEX RANGE SCAN
- set autotrace traceonly
- SELECT owner, object_name FROM test_objects WHERE owner = 'SYS' AND object_name = 'DBMS_OUTPUT';
(2) INDEX SKIP SCAN
- SELECT owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
(3) INDEX FAST FULL SCAN
- SELECT owner, object_name FROM test_objects;
(4) INDEX FULL SCAN
- SELECT owner, object_name FROM test_objects order by 1, 2;
(5) INDEX FULL SCAN (MIN/MAX)
- SELECT max(object_id) FROM test_objects;
无论是Windows还是macOS,都少不了各种小工具的加持。作为操作系统中必不可少的...
本文重点给大家介绍AjaxFileUpload+Struts2实现多文件上传功能,具体实现代码大...
SQL可以独立完成数据库生命周期中的全部活动,包括定义关系模式、录入数据、建立...
在用java进行web业务开发的时候,对于页面上接收到的参数,除了极少数是步可预知...
display-namedefaultroot/display-name servlet servlet-namedebugjsp/servlet-n...
我们在用ajax请求数据时,可能会遇到一次点击多次触发的可能。 (比如说:ajax ...
MySQL 运维 - 从零开始学习 一、数据库类型 ? 常见的数据库类型 二、数据库管理...
信号章节 -- 信号章节总体概要 信号基本概念 信号是异步事件,发送信号的线程可...
本文实例为大家分享了JS实现纸牌发牌动画的具体代码,供大家参考,具体内容如下 ...
在Sun的Java JDK 1.40版本中,Java自带了支持正则表达式的包,本文就抛砖引玉地...