提示：此文章下的code示例皆在cmd下运行，未借助可视化工具，有些code示例略长，可根据目录快速跳转
文章目录
一、Note
1、索引概念
- 索引是一种与表相关的数据库对象
- 无论是物理上还是逻辑上都独立于相应的表
- 如果将字典中的内容当作表，则字典中的目录部分就是索引
2、索引作用
- 引入索引的目的就是为了加快查询的速度
- 索引类似于数的目录，通过查询目录快速寻找相应内容
- 有了索引后就可以快速找到相应数据，从而不必把整张表中的数据全部过滤（不必全表扫描）
- (原理)Oracle 数据库管理系统中 通过建立 被查询数据 与 rowid 的对应关系来实现提高查询效率的目的
3、索引分类
-
单一索引、复合索引
- 单个列具有的索引为单一索引
- 多个列都具有的索引为复合索引
-
唯一索引、非唯一索引
- 唯一索引确保被索引的列中，所有数据都是唯一的，不包含重复的值
- 如果表有 主键约束或唯一约束，那么在执行create table语句或alter table语句时，数据库会自动创建唯一索引，索引名与约束名相同
-
标准索引（ B-Tree Index ）
- Oracle 默认的索引
- 关注二叉树和B树的关系
-
位图索引（ BitMap Index ）
-
函数索引（Function Index ）
4、索引管理
4.1、创建
CREATE [UNIQUE]|[BITMAP] INDEX 索引名 ON 表名( 列名 [ASC|DESC] [ , 列名[ASC | DESC] , ... ] ) ;- 用户得有创建索引的权限、使用表空间的权限
- 索引名建议使用【
i_表名_列名】
4.2、查看
-
与索引有关的数据字典
user_objects
-
object_name:表名、视图名、索引名
- Object_type:表、视图、索引
- status:对象的状态，如valid-可用的
-
user_indexes-
用户拥有的索引信息
-
user_ind_columns
-
-
查看与索引有关的列
4.3、使用
- 只要使用索引对应的列作为查询条件去查询，就有可能使用索引
- 可以通过 执行计划 来查看 查询语句的执行过程（分析是否使用了索引）
4.4、更改
-
改索引名
ALTER INDEX 原索引名 RENAME TO 新索引名 ;
-
合并索引
-
重建索引
-
alter index indexname rebuild; -
alter index indexname rebuild online; -
区别
- 扫描方式不同
rebuild以index fast full scan（ortable full scan） 方式读取原索引中的数据来构建一个新的索引，有排序的操作;
rebuild online执行表扫描获取数据，有排序的操作; rebuild会阻塞dml操作 ，rebuild online不会阻塞dml操作 ；- 虽然说
rebuild online操作允许dml操作，但是还是建议在业务不繁忙时间段进行
- 虽然说
rebuild online时系统会产生一个 SYS_JOURNAL_xxx 的 IOT 类型的系统临时日志表 ， 所有 rebuild online 时索引的变化都记录在这个表中 ， 当新的索引创建完成后 ， 把这个表的记录维护到新的索引中去 ， 然后 drop 掉旧的索引 ，rebuild online 就完成了。- 执行rebuild操作时，需要检查表空间是否足够；
- rebuild操作会产生大量redo log ；
- 扫描方式不同
-
4.5、删除
- 如果某个索引将不再被使用，可以将其删除
DROP INDEX 索引名 ;
5、执行计划
-
概念
- 执行计划（ Explain Plan ）
- 是关于 查询语句 在Oracle DBMS 中的 执行过程 或 访问路径 的描述
-
作用
- 分析某个SQL的性能
- 查看某个SQL执行是否存在问题
-
帮助
- Oracle中提供了帮助命令提示：help set
-
用法
SET AUTOTRACE { OFF | ON | TRANCEONLY } [ EXPLAIN ] [ STATISTICS ]
-
EXPLAIN表示 执行计划STATISTICS表示 统计信息autotrace默认是关闭的autotrace缩写为autot
-
SET AUTOTRACE OFF表示 仅显示 查询结果，不显示 执行计划 和 统计信息SET AUTOTRACE ON表示 既显示 查询结果，又显示 执行计划 和 统计信息SET AUTOTRACE TRACEONLY表示 不显示 查询结果，仅显示 执行计划 和 统计信息SET AUTOTRACE ON EXPLAIN表示 显示 查询结果 和 执行计划 ，不显示 统计信息SET AUTOTRACE ON STATISTICS表示 显示 查询结果 和 统计信息，不显示 执行计划
6、Oracle 查询数据的方式
-
全表扫描（TABLE ACCESS FULL ）
-
通过ROWID访问（TABLE ACCESS ROWID ）
-
索引扫描（INDEX SCAN / INDEX LOOKUP ）
- 索引唯一扫描（ INDEX UNIQUE SCAN ）
- 索引范围扫描（ INDEX RANGE SCAN ）
- 索引全扫描（ INDEX FULL SCAN ）
- 索引快速扫描（ INDEX FAST FULL SCAN ）
二、Code
1、索引：查询与索引有关的数据字典
- 查看与索引有关的数据字典
DESC user_objects ;SELECT object_name , object_type , status FROM user_objects WHERE lower(object_type) = 'index';
DESC user_indexes ;SELECT table_name , index_name , index_type FROM user_indexes ;
DESC user_ind_columns ;SELECT table_name , index_name , column_name FROM user_ind_columns ;
- oracle数据库中，执行 设置查询格式的 脚本（脚本具体内容见下面的Code6）
START D:/SQL/format.sql
2、索引：创建索引/使用索引(查询时即使用)/删除索引
-
select rowid , * from myemp ;错误！ -
select rowid , myemp.* from myemp ;正确 -
创建索引: 提高根据 name 进行查询时的效率
CREATE INDEX i_myemp_name ON myemp ( name ) ;- 当使用 name 作为查询条件去查询 myemp 表中的数据时，就会使用 i_myemp_name 索引
-
删除索引
DROP INDEX i_myemp_name ;
SQL> -- 索引
SQL>
SQL> -- 创建索引
SQL>
SQL> -- 创建一张只有 工号、姓名、岗位、薪水 四个列的表
SQL>
SQL> DROP TABLE myemp PURGE ;
表已删除。
SQL> CREATE TABLE myemp (
2 id NUMBER(10) ,
3 name VARCHAR2(50) ,
4 job VARCHAR2(30) ,
5 salary NUMBER(7,2)
6 );
表已创建。
SQL> -- 通过 INSERT SELECT 方式将 emp 表 和 s_emp 表中的数据 “复制” 到 myemp 表中
SQL>
SQL> INSERT INTO myemp ( id , name , job , salary ) SELECT empno , ename , job , sal FROM emp ;
已创建16行。
SQL> INSERT INTO myemp ( id , name , job , salary ) SELECT id , first_name , title , salary FROM s_emp ;
已创建25行。
SQL> -- 提交事务
SQL> commit ;
提交完成。
SQL> -- 查询 myemp 表中的数据
SQL>
SQL> SELECT * FROM myemp ;
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
9257 HUA'AN CLERK 1300
8526 QIU'XIANG ANALYST 1400
1 Carmen President 2500
2 LaDoris VP, Operations 1450
3 Midori VP, Sales 1400
4 Mark VP, Finance 1450
5 Audry VP, Administration 1550
6 Molly Warehouse Manager 1200
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
7 Roberta Warehouse Manager 1250
8 Ben Warehouse Manager 1100
9 Antoinette Warehouse Manager 1300
10 Marta Warehouse Manager 1307
11 Colin Sales Representative 1400
12 Henry Sales Representative 1490
13 Yasmin Sales Representative 1515
14 Mai Sales Representative 1525
15 Andre Sales Representative 1450
16 Elena Stock Clerk 1400
17 George Stock Clerk 940
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
18 Akira Stock Clerk 1200
19 Vikram Stock Clerk 795
20 Chad Stock Clerk 750
21 Alexander Stock Clerk 850
22 Eddie Stock Clerk 800
23 Radha Stock Clerk 795
24 Bela Stock Clerk 860
25 Sylvie Stock Clerk 1100
已选择41行。
SQL> SELECT * FROM myemp ORDER BY id ;
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
1 Carmen President 2500
2 LaDoris VP, Operations 1450
3 Midori VP, Sales 1400
4 Mark VP, Finance 1450
5 Audry VP, Administration 1550
6 Molly Warehouse Manager 1200
7 Roberta Warehouse Manager 1250
8 Ben Warehouse Manager 1100
9 Antoinette Warehouse Manager 1300
10 Marta Warehouse Manager 1307
11 Colin Sales Representative 1400
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
12 Henry Sales Representative 1490
13 Yasmin Sales Representative 1515
14 Mai Sales Representative 1525
15 Andre Sales Representative 1450
16 Elena Stock Clerk 1400
17 George Stock Clerk 940
18 Akira Stock Clerk 1200
19 Vikram Stock Clerk 795
20 Chad Stock Clerk 750
21 Alexander Stock Clerk 850
22 Eddie Stock Clerk 800
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
23 Radha Stock Clerk 795
24 Bela Stock Clerk 860
25 Sylvie Stock Clerk 1100
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
8526 QIU'XIANG ANALYST 1400
9257 HUA'AN CLERK 1300
已选择41行。
SQL> SELECT rowid , * FROM myemp ;
SELECT rowid , * FROM myemp
*
第 1 行出现错误:
ORA-00936: 缺失表达式
SQL> SELECT rowid , myemp.* FROM myemp ;
ROWID ID NAME JOB SALARY
------------------ ---------- -------------------- ------------------------------ ----------
AAAR+VAAEAAAAG8AAA 7369 SMITH CLERK 800
AAAR+VAAEAAAAG8AAB 7499 ALLEN SALESMAN 1600
AAAR+VAAEAAAAG8AAC 7521 WARD SALESMAN 1250
AAAR+VAAEAAAAG8AAD 7566 JONES MANAGER 2975
AAAR+VAAEAAAAG8AAE 7654 MARTIN SALESMAN 1250
AAAR+VAAEAAAAG8AAF 7698 BLAKE MANAGER 2850
AAAR+VAAEAAAAG8AAG 7782 CLARK MANAGER 2450
AAAR+VAAEAAAAG8AAH 7788 SCOTT ANALYST 3000
AAAR+VAAEAAAAG8AAI 7839 KING PRESIDENT 5000
AAAR+VAAEAAAAG8AAJ 7844 TURNER SALESMAN 1500
AAAR+VAAEAAAAG8AAK 7876 ADAMS CLERK 1100
ROWID ID NAME JOB SALARY
------------------ ---------- -------------------- ------------------------------ ----------
AAAR+VAAEAAAAG8AAL 7900 JAMES CLERK 950
AAAR+VAAEAAAAG8AAM 7902 FORD ANALYST 3000
AAAR+VAAEAAAAG8AAN 7934 MILLER CLERK 1300
AAAR+VAAEAAAAG8AAO 9257 HUA'AN CLERK 1300
AAAR+VAAEAAAAG8AAP 8526 QIU'XIANG ANALYST 1400
AAAR+VAAEAAAAG8AAQ 1 Carmen President 2500
AAAR+VAAEAAAAG8AAR 2 LaDoris VP, Operations 1450
AAAR+VAAEAAAAG8AAS 3 Midori VP, Sales 1400
AAAR+VAAEAAAAG8AAT 4 Mark VP, Finance 1450
AAAR+VAAEAAAAG8AAU 5 Audry VP, Administration 1550
AAAR+VAAEAAAAG8AAV 6 Molly Warehouse Manager 1200
ROWID ID NAME JOB SALARY
------------------ ---------- -------------------- ------------------------------ ----------
AAAR+VAAEAAAAG8AAW 7 Roberta Warehouse Manager 1250
AAAR+VAAEAAAAG8AAX 8 Ben Warehouse Manager 1100
AAAR+VAAEAAAAG8AAY 9 Antoinette Warehouse Manager 1300
AAAR+VAAEAAAAG8AAZ 10 Marta Warehouse Manager 1307
AAAR+VAAEAAAAG8AAa 11 Colin Sales Representative 1400
AAAR+VAAEAAAAG8AAb 12 Henry Sales Representative 1490
AAAR+VAAEAAAAG8AAc 13 Yasmin Sales Representative 1515
AAAR+VAAEAAAAG8AAd 14 Mai Sales Representative 1525
AAAR+VAAEAAAAG8AAe 15 Andre Sales Representative 1450
AAAR+VAAEAAAAG8AAf 16 Elena Stock Clerk 1400
AAAR+VAAEAAAAG8AAg 17 George Stock Clerk 940
ROWID ID NAME JOB SALARY
------------------ ---------- -------------------- ------------------------------ ----------
AAAR+VAAEAAAAG8AAh 18 Akira Stock Clerk 1200
AAAR+VAAEAAAAG8AAi 19 Vikram Stock Clerk 795
AAAR+VAAEAAAAG8AAj 20 Chad Stock Clerk 750
AAAR+VAAEAAAAG8AAk 21 Alexander Stock Clerk 850
AAAR+VAAEAAAAG8AAl 22 Eddie Stock Clerk 800
AAAR+VAAEAAAAG8AAm 23 Radha Stock Clerk 795
AAAR+VAAEAAAAG8AAn 24 Bela Stock Clerk 860
AAAR+VAAEAAAAG8AAo 25 Sylvie Stock Clerk 1100
已选择41行。
SQL> -- 创建索引: 提高根据 name 进行查询时的效率
SQL>
SQL> CREATE INDEX i_myemp_name ON myemp ( name ) ;
索引已创建。
SQL>
SQL> SELECT table_name , index_name , index_type FROM user_indexes WHERE lower(table_name) = 'myemp' ;
TABLE_NAME INDEX_NAME INDEX_TYPE
-------------------- ------------------------------ ---------------------------
MYEMP I_MYEMP_NAME NORMAL
SQL>
SQL>
SQL> -- 当使用 name 作为查询条件去查询 myemp 表中的数据时，就会使用 i_myemp_name 索引
SQL>
SQL> SELECT rowid , myemp.* FROM myemp WHERE name = 'Bela' ;
ROWID ID NAME JOB SALARY
------------------ ---------- -------------------- ------------------------------ ----------
AAAR+VAAEAAAAG8AAn 24 Bela Stock Clerk 860
SQL>
SQL> -- 删除索引
SQL> DROP INDEX i_myemp_name ;
索引已删除。
SQL> SELECT rowid , myemp.* FROM myemp WHERE name = 'Bela' ;
ROWID ID NAME JOB SALARY
------------------ ---------- -------------------- ------------------------------ ----------
AAAR+VAAEAAAAG8AAn 24 Bela Stock Clerk 860
SQL> spool off
3、执行计划:SET AUTOTRACE{ OFF | ON | TRANCEONLY }
-
通过 help 查看 set 命令的用法
help set
-
仅显示 查询结果，不显示 执行计划 和 统计信息：
SET AUTOTRACE OFF -
既显示 查询结果，又显示 执行计划 和 统计信息：
SET AUTOTRACE ON -
若
SET AUTOTRACE ON遇到问题：无法找到会话标识符。启用检查 PLUSTRACE 角色，则此时需要创建 plustrace 角色并授权-
用 sys 用户 以 sysdba 角色登录到 Oracle DBMS 中
connect sys as sysdba
-
确认oracle的基目录(C:\databases\oracle)的(product\11.2.0\dbhome_1\sqlplus\admin)下有plustrce.sql脚本
-
执行 ORACLE_BASE\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql 脚本
-
通过删除语句，确认plustrace角色不存在
drop role plustrace;
-
创建plustrace角色
create role plustrace;
-
plustrce.sql 脚本中的语句会执行( 创建角色并授权 )
-
SQL> grant select on v_$sesstat to plustrace; 授权成功。 SQL> grant select on v_$statname to plustrace; 授权成功。 SQL> grant select on v_$mystat to plustrace; 授权成功。 SQL> grant plustrace to dba with admin option; 授权成功。 SQL> SQL> set echo off
-
-
通过 SYS 用户 将 plustrace 角色授予 ecuter 用户
grant plustrace to ecuter ;
-
重新 以 ecuter 用户 连接到 Oracle DBMS
connect ecuter/ecuter
-
再次尝试执行 SET AUTOTRACE ON
SET AUTOTRACE ON
-
SQL> -- 查看当前用户
SQL> SHOW USER ;
USER 为 "ECUTER"
SQL>
SQL> -- 查询 emp 表中的所有雇员的 empno 、ename 、job 、sal
SQL> SELECT empno , ename , job , sal FROM emp ;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
9257 HUA'AN CLERK 1300
8526 QIU'XIANG ANALYST 1400
已选择16行。
SQL> -- 设置 AUTOTRACE
SQL> SET AUTOTRACE OFF
SQL>
SQL>
SQL> SELECT empno , ename , job , sal FROM emp ;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
9257 HUA'AN CLERK 1300
8526 QIU'XIANG ANALYST 1400
已选择16行。
SQL>
SQL> -- 通过 help 查看 set 命令的用法
SQL>
SQL> help set
SET
---
Sets a system variable to alter the SQL*Plus environment settings
for your current session. For example, to:
- set the display width for data
- customize HTML formatting
- enable or disable printing of column headings
- set the number of lines per page
SET system_variable value
where system_variable and value represent one of the following clauses:
APPI[NFO]{OFF|ON|text} NEWP[AGE] {1|n|NONE}
ARRAY[SIZE] {15|n} NULL text
AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} NUMF[ORMAT] format
AUTOP[RINT] {OFF|ON} NUM[WIDTH] {10|n}
AUTORECOVERY {OFF|ON} PAGES[IZE] {14|n}
AUTOT[RACE] {OFF|ON|TRACE[ONLY]} PAU[SE] {OFF|ON|text}
[EXP[LAIN]] [STAT[ISTICS]] RECSEP {WR[APPED]|EA[CH]|OFF}
BLO[CKTERMINATOR] {.|c|ON|OFF} RECSEPCHAR {_|c}
CMDS[EP] {;|c|OFF|ON} SERVEROUT[PUT] {ON|OFF}
COLSEP {_|text} [SIZE {n | UNLIMITED}]
CON[CAT] {.|c|ON|OFF} [FOR[MAT] {WRA[PPED] |
COPYC[OMMIT] {0|n} WOR[D_WRAPPED] |
COPYTYPECHECK {ON|OFF} TRU[NCATED]}]
DEF[INE] {&|c|ON|OFF} SHIFT[INOUT] {VIS[IBLE] |
DESCRIBE [DEPTH {1|n|ALL}] INV[ISIBLE]}
[LINENUM {OFF|ON}] [INDENT {OFF|ON}] SHOW[MODE] {OFF|ON}
ECHO {OFF|ON} SQLBL[ANKLINES] {OFF|ON}
EDITF[ILE] file_name[.ext] SQLC[ASE] {MIX[ED] |
EMB[EDDED] {OFF|ON} LO[WER] | UP[PER]}
ERRORL[OGGING] {ON|OFF} SQLCO[NTINUE] {> | text}
[TABLE [schema.]tablename] SQLN[UMBER] {ON|OFF}
[TRUNCATE] [IDENTIFIER identifier] SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
ESC[APE] {\|c|OFF|ON} SQLPRE[FIX] {#|c}
ESCCHAR {@|?|%|$|OFF} SQLP[ROMPT] {SQL>|text}
EXITC[OMMIT] {ON|OFF} SQLT[ERMINATOR] {;|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF} SUF[FIX] {SQL|text}
FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} TAB {ON|OFF}
FLU[SH] {ON|OFF} TERM[OUT] {ON|OFF}
HEA[DING] {ON|OFF} TI[ME] {OFF|ON}
HEADS[EP] {||c|ON|OFF} TIMI[NG] {OFF|ON}
INSTANCE [instance_path|LOCAL] TRIM[OUT] {ON|OFF}
LIN[ESIZE] {80|n} TRIMS[POOL] {OFF|ON}
LOBOF[FSET] {1|n} UND[ERLINE] {-|c|ON|OFF}
LOGSOURCE [pathname] VER[IFY] {ON|OFF}
LONG {80|n} WRA[P] {ON|OFF}
LONGC[HUNKSIZE] {80|n} XQUERY {BASEURI text|
MARK[UP] HTML [OFF|ON] ORDERING{UNORDERED|
[HEAD text] [BODY text] [TABLE text] ORDERED|DEFAULT}|
[ENTMAP {ON|OFF}] NODE{BYVALUE|BYREFERENCE|
[SPOOL {OFF|ON}] DEFAULT}|
[PRE[FORMAT] {OFF|ON}] CONTEXT text}
SQL> help set autotrace
SP2-0172: 找不到符合此主题的帮助。
SQL>
SQL> SET AUTOTRACE ON
SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色
SP2-0611: 启用 STATISTICS 报告时出错
SQL>
SQL>
SQL> -- 遇到以上问题时，需要创建 plustrace 角色并授权
SQL> -- 所以，需要用 sys 用户 以 sysdba 角色登录到 Oracle DBMS 中,
SQL> -- 并执行 ORACLE_BASE\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql 脚本
SQL>
SQL> connect sys as sysdba
输入口令:
已连接。
SQL>
SQL> SHOW USER ;
USER 为 "SYS"
SQL>
SQL> -- 使用 START 命令 或 @ 来执行指定的脚本
SQL> START C:\databases\oracle\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql
SQL>
SQL> -- 确认plustrace角色不存在
SQL> drop role plustrace;
drop role plustrace
*
第 1 行出现错误:
ORA-01919: 角色 'PLUSTRACE' 不存在
SQL> -- 创建plustrace角色
SQL> create role plustrace;
角色已创建。
SQL> --开始
SQL> grant select on v_$sesstat to plustrace;
授权成功。
SQL> grant select on v_$statname to plustrace;
授权成功。
SQL> grant select on v_$mystat to plustrace;
授权成功。
SQL> grant plustrace to dba with admin option;
授权成功。
SQL>
SQL> set echo off
SQL> -- 结束
SQL> -- 以上操作就是 plustrce.sql 脚本中的语句( 创建角色并授权 )
SQL>
SQL> SHOW USER ;
USER 为 "SYS"
SQL>
SQL> -- 通过 SYS 用户 将 plustrace 角色授予 ecuter 用户
SQL>
SQL> grant plustrace to ecuter ;
授权成功。
SQL>
SQL> -- 重新 以 ecuter 用户 连接到 Oracle DBMS
SQL>
SQL> connect ecuter/ecuter
已连接。
SQL>
SQL> SHOW USER ;
USER 为 "ECUTER"
SQL>
SQL>
SQL> -- 再次尝试执行 SET AUTOTRACE ON
SQL> SET AUTOTRACE ON
SQL>
SQL> -- 不再提示 “SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色”
SQL>
SQL> -- 再次执行查询语句
SQL> SELECT empno , ename , job , sal FROM emp ;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
9257 HUA'AN CLERK 1300
8526 QIU'XIANG ANALYST 1400
已选择16行。
执行计划
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 368 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 16 | 368 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1285 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16 rows processed
SQL>
SQL>
SQL> -- 关闭 AUTOTRACE
SQL> SET AUTOTRACE OFF
SQL>
SQL> SELECT empno , ename , job , sal FROM emp ;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
9257 HUA'AN CLERK 1300
8526 QIU'XIANG ANALYST 1400
已选择16行。
SQL>
4、对比AUTOTRACE不同的选项/通过执行计划查看查询语句的执行方式
-
启用 AUTOTRACE 使用 SET AUTOTRACE …
SET AUTOTRACE ON
-
表示不显示 查询结果，仅显示 执行计划 和 统计信息
SET AUTOTRACE TRACEONLY ;
-
表示 显示 查询结果 和 执行计划 ，不显示 统计信息
SET AUTOTRACE ON EXPLAIN ;
-
表示 显示 查询结果 和 统计信息，不显示 执行计划
SET AUTOTRACE ON STATISTICS ;
-
针对 name 和 id 创建一个索引
CREATE INDEX ind_myemp_name_id ON myemp ( lower(name) , id ) ;
SQL> -- 执行计划
SQL>
SQL> -- 查询 dept 表中所有数据
SQL>
SQL> SELECT * FROM dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> -- 默认 AUTOTRACE 是关闭的
SQL>
SQL> -- 启用 AUTOTRACE 使用 SET AUTOTRACE ...
SQL>
SQL> SET AUTOTRACE ON
SQL>
SQL> SELECT * FROM dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
执行计划
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
803 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> SET AUTOTRACE TRACEONLY ;
SQL>
SQL> SELECT * FROM dept ;
执行计划
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
803 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL>
SQL> SET AUTOTRACE ON EXPLAIN ;
SQL>
SQL> SELECT * FROM dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
执行计划
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> SET AUTOTRACE ON STATISTICS ;
SQL>
SQL> SELECT * FROM dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
803 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> SET AUTOTRACE ON EXPLAIN ;
SQL>
SQL>
SQL> SELECT * FROM dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
执行计划
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> -- 查询 myemp 表
SQL>
SQL> SELECT * FROM myemp WHERE id = 9527 ;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2418123361
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MYEMP | 1 | 70 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=9527)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT * FROM myemp WHERE id = 9257 ;
ID NAME
---------- --------------------------------------------------
JOB SALARY
------------------------------ ----------
9257 HUA'AN
CLERK 1300
执行计划
----------------------------------------------------------
Plan hash value: 2418123361
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MYEMP | 1 | 70 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=9257)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> COLUMN name FORMAT a20 ;
SQL> SELECT * FROM myemp WHERE id = 9257 ;
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
9257 HUA'AN CLERK 1300
执行计划
----------------------------------------------------------
Plan hash value: 2418123361
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MYEMP | 1 | 70 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=9257)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> -- 使用 有索引的 name 来作为查询条件进行查询
SQL>
SQL> SELECT * FROM myemp WHERE name = 'HUA''AN' ;
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
9257 HUA'AN CLERK 1300
执行计划
----------------------------------------------------------
Plan hash value: 2418123361
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MYEMP | 1 | 70 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='HUA''AN')
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> -- 为 myemp 表创建一个索引，以便于提高根据 name 进行查询时的效率
SQL>
SQL> CREATE INDEX ind_myemp_name ON myemp ( lower(name) );
索引已创建。
SQL>
SQL> SELECT table_name , index_name , index_type FROM user_indexes WHERE lower(table_name)='myemp' ;
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
INDEX_TYPE
---------------------------
MYEMP IND_MYEMP_NAME
FUNCTION-BASED NORMAL
执行计划
----------------------------------------------------------
ERROR:
ORA-01039: 视图基本对象的权限不足
SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错
SQL> SET AUTOTRACE OFF
SQL> SELECT table_name , index_name , index_type FROM user_indexes WHERE lower(table_name)='myemp' ;
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
INDEX_TYPE
---------------------------
MYEMP IND_MYEMP_NAME
FUNCTION-BASED NORMAL
SQL> START format.sql
SQL> /
TABLE_NAME INDEX_NAME INDEX_TYPE
-------------------- ------------------------------ ---------------------------
MYEMP IND_MYEMP_NAME FUNCTION-BASED NORMAL
SQL>
SQL> SET AUTOTRACE ON EXPLAIN ;
SQL>
SQL>
SQL> SELECT * FROM myemp WHERE id = 9257 ;
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
9257 HUA'AN CLERK 1300
执行计划
----------------------------------------------------------
Plan hash value: 2418123361
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MYEMP | 1 | 70 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=9257)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT * FROM myemp WHERE lower(name) = 'hua''an' ;
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
9257 HUA'AN CLERK 1300
执行计划
----------------------------------------------------------
Plan hash value: 2852364061
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MYEMP | 1 | 97 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_MYEMP_NAME | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(LOWER("NAME")='hua''an')
Note
-----
- dynamic sampling used for this statement (level=2)
SQL>
SQL> SELECT * FROM myemp WHERE name = 'HUA''AN';
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
9257 HUA'AN CLERK 1300
执行计划
----------------------------------------------------------
Plan hash value: 2418123361
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MYEMP | 1 | 70 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='HUA''AN')
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT * FROM myemp WHERE lower(name) = 'bela' OR id = 9257 ;
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
9257 HUA'AN CLERK 1300
24 Bela Stock Clerk 860
执行计划
----------------------------------------------------------
Plan hash value: 2418123361
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MYEMP | 1 | 97 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=9257 OR LOWER("NAME")='bela')
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> -- 针对 name 和 id 创建一个索引
SQL> CREATE INDEX ind_myemp_name_id ON myemp ( lower(name) , id ) ;
索引已创建。
SQL> SELECT * FROM myemp WHERE lower(name) = 'bela' OR id = 9257 ;
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
9257 HUA'AN CLERK 1300
24 Bela Stock Clerk 860
执行计划
----------------------------------------------------------
Plan hash value: 2418123361
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MYEMP | 1 | 97 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=9257 OR LOWER("NAME")='bela')
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT * FROM myemp WHERE lower(name) = 'bela' AND id = 24 ;
ID NAME JOB SALARY
---------- -------------------- ------------------------------ ----------
24 Bela Stock Clerk 860
执行计划
----------------------------------------------------------
Plan hash value: 773055317
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MYEMP | 1 | 97 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_MYEMP_NAME_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(LOWER("NAME")='bela' AND "ID"=24)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> spool off
5、索引：修改索引名称
-
若create建索引名时，是建在小写的列名上（用lower转为小写）；那么，之后用大写的属性值搜索时就不是走索引，而是全表扫描；
-
查询时指定的条件决定了搜索时会不会走指定的索引
-
由于数据库的表中，字符数据默认大写，因此用lower()转为小写
SELECT table_name , index_name , index_type FROM user_indexes WHERE lower(table_name) = 'myemp';
-
更改索引名
ALTER INDEX ind_myemp_name RENAME TO ind_myemp_lower_name;
SQL> -- 由于数据库的表中，字符数据默认大写，因此用lower()转为小写
SQL> SELECT table_name , index_name , index_type FROM user_indexes
2 WHERE lower(table_name) = 'myemp';
TABLE_NAME INDEX_NAME INDEX_TYPE
-------------------- ------------------------------ ---------------------------
MYEMP IND_MYEMP_NAME_ID FUNCTION-BASED NORMAL
MYEMP IND_MYEMP_NAME FUNCTION-BASED NORMAL
执行计划
----------------------------------------------------------
ERROR:
ORA-01039: 视图基本对象的权限不足
SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错
SQL> -- 不必理会 "ORA-01039: 视图基本对象的权限不足"
SQL>
SQL>
SQL> -- 更改索引名
SQL> ALTER INDEX ind_myemp_name RENAME TO ind_myemp_lower_name
2 ;
索引已更改。
SQL> SELECT table_name , index_name , index_type FROM user_indexes
2 WHERE lower(table_name) = 'myemp';
TABLE_NAME INDEX_NAME INDEX_TYPE
-------------------- ------------------------------ ---------------------------
MYEMP IND_MYEMP_NAME_ID FUNCTION-BASED NORMAL
MYEMP IND_MYEMP_LOWER_NAME FUNCTION-BASED NORMAL
执行计划
----------------------------------------------------------
ERROR:
ORA-01039: 视图基本对象的权限不足
SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错
SQL>
SQL> -- 不必理会 "ORA-01039: 视图基本对象的权限不足"
SQL>
6、用于设置SQL*Plus查询格式的SQL脚本
- 用途1：此脚本可便于规范 user_cons_columns 的列格式;
- 用途2：此脚本可便于规范 其他有这几列 的列格式;
- 用
start format.sql或@ format.sql来执行此脚本
format.sql:
-- linesize
SET linesize 120 ;
-- user_objects
COLUMN object_name FORMAT a20 ;
-- user_cons_columns / user_constraints
COLUMN table_name FORMAT a20 ;
COLUMN column_name FORMAT a20 ;
COLUMN constraint_name FORMAT a30 ;
COLUMN owner FORMAT a10 ;
扫一扫
3231
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
