Oracle小结(权限、用户、表空间、数据类型、Oracle的SQL函数、SQL语句、索引、心得、分区)

Oracle 同时被 2 个专栏收录
1 篇文章 0 订阅
1 篇文章 0 订阅

命令窗口/SQL窗口

命令窗口(分配权限、角色):即为sqplus窗口,DCL等基本的命令都可以执行。
sql窗口(日常数据库操作):仅可执行DDL、select、DML
DML(data manipulation language)数据操纵语言:它们是select、update、insert、delete。
DDL(data definition language)数据定义语言:DDL比DML要多,主要的命令有create、alter、drop等,DDL主要是用在定义或改变表(table)结构,数据类型,表之间的链接和约束等初始化工作上,大多在建立表时使用。
DCL(DataControlLanguage)数据库控制语言:是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。
SQL语言16种基本命令:
DML命令: select,insert,update,delete,merge
DDL命令: create,alter,drop,rename,truncate,comment
TCL命令: savepoint,rollback,commit,grant,revoke 

角色与权限

Oracle有很多权限(Oracle11g有208种),这么多的权限通过角色的划分给用户集中赋予一些权限(Oracle对于数据库是有严格权限控制的,这与MySQL很不同)。
在system用户下
查看Oracle权限表(Oracle11g有208种权限): select * from system_privilege_map order by name;
查看Oracle角色表(Oracle11g有55种角色): select * from dba_roles;

Oracle自带用户

sys
级别:Oracle的一个超级用户,最高权限。
主要作用:用来维护系统信息和管理实例。
登录角色:只能以SYSDBA或SYSOPER角色登录。
System
级别:Oracle默认的管理员,拥有DBA权限。
主要作用:用来管理Oracle数据库的用户、权限和存储等。
登录角色:只能以Normal角色登录。
scott   
级别:Oracle的一个普通示例用户 。
主要作用:为初学者提供一些简单的应用示例,测试数据。
登录角色:只能以Normal角色登录。
注:创建数据库时默认该用户为锁定状态,使用前需解锁。默认密码为tiger。
sysman
级别:是Oracle数据库中用于EM管理的用户,管理员级别。
主要作用:用来操作企业管理器
注:如果不用EM可以删除该用户。

常用角色(这三个就够日常使用)

  1. dba角色
    dba角色具有所有的系统权限,及with admin option选项,默认的dba用户为sys和system他们可以将任何系统权限授予其它用户。
  2. connect角色
    create session 创建连接权限
  3. resource角色
    resource角色具有应用开发人员所需要的其它权限,比如建立存储过程、触发器等。resource角色隐含了unlimited tablespace系统权限。
    create trigger  创建触发器
    create sequence 创建序列
    create type     创建类型权限
    create procedure创建过程
    create cluster  创建集群
    create operator 创建运营商
    create indextype创建索引类型
    create table    创建表

使用系统角色(在命令窗口下执行)

创建 jun 用户 密码为 123456 
create user jun identified by '123456';
赋给 jun 用户 connect 角色和 resource 角色
grant connect,resource to jun;

自定义角色

  • 建立不验证角色(公用的角色)
    create role 角色名 not identified;
  • 建立角色(激活该角色需提供口令)
    create role 角色名 identified by 口令;

角色授权(和用户授权没太大区别,在命令窗口执行)

  1. 使用 system 用户创建自定义角色 liu_vip
    create role liu_vip not identified;
  2. 给自定义角色 liu_vip 授权 给与 session 权利和对 student 表的操作
    grant create session to liu_vip;
    grant select on student to liu_vip;
    grant insert on student to liu_vip;
    grant update on student to liu_vip;
    让 jun 用户修改 student 表的结构
    grant alter on student to jun;
    让 jun 可以在 student 上建立索引
    grant index on student to jun;
    一次把 student 的所有权限赋给 jun
    grant all on student to jun; 
  3. 通过将授权过的角色 liu_vip 给用 jun 户进行授权
    grant liu_vip to jun;
    分配角色是由DBA来完成的,其它用户身份分配角色,户必需具有grant any role的系统权限
    grant 角色名 to 用户名 [with admin option];

一些关于角色/权限的SQL

显示所有角色
select * from dba_roles;
显示角色具有的系统权限
select privilege,admin_option from role_sys_privs where role='角色名';
显示角色具有的对象权限(dba_tab_privs可查角色具有的对象权限、列的权限)
select * from dba_tab_privs where grantee='角色名';
显示用户所有的角色,及默认角色(dba_role_privs可显示用户的所有角色、当前默认的角色)
select granted_role,default_role from dba_role_privs where grantee='角色名';
查看当前用户的表
select table_name from user_tables;
查询oracle中所有的系统权限,一般是dba
select * from system_privilege_map order by name;
查询当前用户具有什么样的系统权限
select * from user_sys_privs;
查询当前用户在别人的表上,具有什么样的对象权限
select * from user_tab_privs; (查看对表的权限)
select * from user_col_privs; (查看对表的列的权限)
撤消权限:revoke 角色 from 用户名
删除角色:drop role 
删除用户:drop user 

表空间(存N个表的物理空间)

oracle 数据库就是指的oracle整体,一般一个机器上只安装一个oracle数据库
oracle建立好以后,实际上oracle的数据库文件是一个一个的DBF文件,然后N个DBF文件组成一个表空间,表就建立在表空间下。
自建的表可以选择指定的表空间(如果不写,就放在你这个用户的默认表空间(users)里,这个表空间是系统自己建立的)。
临时表空间也可以用,但只能将临时表放在里面,临时表空间主要放一些临时数据,比如一个复杂的sql语句,
系统会将中间数据放在临时表空间里暂存。临时表空间会自己删除(可以选择会话结束就删除)。

Oracle自带的表空间

system      存放sys用户的表、视图以及存储过程等数据库对象系统表空间。
sysaux       作为example的辅助表空间。
undotbs1   存储一些撤销信息的。
temp          存储SQL语句处理的表和索引信息的临时表空间。
users         存储数据库用户创建的数据库对象的永久表空间。
example    安装Oracle11g数据库事例来使用的表空间。

表空间分类

  1. 永久表空间:存放数据库中永久化存储的对象,表、视图、存储过程等。
  2. 临时表空间:存放数据库操作中中间执行的过程,执行结束后存放的内容被自动释放掉,不进行永久保存。
  3. UNDO表空间:保存事务所修改数据的旧值,也就是被修改前的数据。利用UNDO表空间可以对数据进行撤销、回滚。

查看表空间

select t.tablespace_name,round(sum(bytes /(1024 * 1024)),0)ts_size 
from dba_tablespaces t,dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name; 

创建表空间(system用户下(sql窗口))

永久表空间:
创建表空间:create tablespace jun1 datafile 'D:\oraclespace\jun1.dbf' size 50M;
自动扩展大小:create tablespace jun2 datafile 'D:\oraclespace\jun2.dbf' size 50M autoextend on next 10M;
设置最大空间:create tablespace jun3 datafile 'D:\oraclespace\jun3.dbf' size 50M autoextend on next 10M maxsize 1024M;
更改当前用户默认表空间:alter database default tablespace jun3;表空间改名:alter tablespace jun1 rename to south1;
删除表空间:drop tablespace jun1 including contents and datafiles;
临时表空间:
语法: create temporary tablespace 表空间名 tempfile '存放路径/数据文件名.dbf' SIZE XX;
SQL: create temporary tablespace test2_tablespace tempfile 'test2file.dbf' size 10M;
注: SIZE表示数据文件的大小,如果没有指定数据文件的路径,默认存放在ORACL的安装目录下。

序列(作用是生成唯一的主键值)

创建语法:
create sequence sequence_name
[start with num]
[increment by increment]
[maxvalue num|nomaxvalue]
[minvalue num|nominvalue]
[cycle|nocycle]
[cache num|nocache]
属性:
start with:从某一整数开始,升、降序默认值为1。
increment by:增长数。
maxvalue:最大值。
nomaxvalue:默认最大值选项,升序为10的27次方,降序默认值-1。
minvalue:最小值。
nominvalue:默认最小值,升序为1,降序为-10的26次方。
cycle:最大值爆表时,从新开始。
nocycle:不从新开始,爆表就报错。
cache:选择cache时,该序列会根据序列规则生成一组序列号,保留在内存中。
nocache:不生成序列号。
SQL创建 :
create sequence myseq 
  minvalue 1 
  start with 1 
  nomaxvalue 
  incretment by 1 
  nocycle
  cache 30 
可通过该序列对象的 currval 和nextval 访问该序列当前值和下一值。
使用序列 
select myseq.currval from dual 
select myseq.nextval from dual 
修改序列
alter sequence myseq 
maxvalue 99999 
minvalue 11111 
删除: drop sequence myseq 

数据类型

字符型

char:定长类型,同样保存在数据库字符集中,如果保存的数据长度没有达到设定的长度,则会使用空格填充。
varchar2:可变长度的字符数据,使用数据库字符集存储;设置该字符类型时会指定相应的长度,例如varchar2(100),100表示100字节(100byte),在保存数字或者英文时没有什么区别,保存汉字时,GBK可以保存100/2=50个汉字,UTF-8可以保存100/3=33个汉字。
nvarchar2:和varchar2相似,区别在于该类型使用国家语言字符集存贮(oracle选择的附加字符集),为了增强Oracle字符处理能力。通常使用这种字符集存储的数据类型使用频率不高。通常使用这种字符集存储的数据类型使用频率不高。

数字型

number:数字数据,可以指定它的精度和小数位数;通常格式number(precision, scale) ,precision表示精度(1-38),指的有效数字的位数,scale表示小数位数(-84-127)。
integer(number子类):存储整型数据,小数位数为0,相当于NUMBER(P,0)。
float(number子类):一个ANSI数据类型,精度为126位二进制、38位十进制的浮点数。另外Oracle还提供BINARY_FLOAT和BINARY_DOUBLE作为备选。
binary_float:是32位、单精度浮点数字数据类型。可以支持至少6位精度,每个binary_float的值需要 5 个字节,包括长度字节。
binary_double:是为64位,双精度浮点数字数据类型。每个binary_double的值需要9个字节,包括长度字节。

日期型

date:存储日期类型,可以精确到秒,通常存储格式是年-月-日 时:分:秒。
datestamp:和DATE相比,这个可以精确到纳秒,存储时间精度更高。
timestamp with timezone:和datestamp一样,只不过可以在设置时候指定时区。
interval year to month:记录两个date或datestamp之间的以年和月的时间间隔。
interval day to second:记录两个date或datestamp之间的以天和秒的时间间隔。

大对象类型

clob:它存储单字节和多字节字符数据,支持固定宽度和可变宽度的字符集,可存(4 gigabytes-1)*(database block size)大小的字符
nclob:它存储unicode类型的数据,支持固定宽度和可变宽度的字符集,保存在国家语言字符集中,存储(4 gigabytes-1)*(database block size)大小的文本数据。
blob:它存储非结构化的二进制数据大对象(没有字符集语义的比特流),一般是图像、声音、视频等文件。最多存储(4 gigabytes-1)*(database block size)的二进制数据。
bfile:二进制文件,存储在数据库外的系统文件,只读的,数据库会将该文件当二进制文件处理。

raw&long raw类型

long:它存储变长字符串,最多达2G的字符数据(2GB是指2千兆字节,而不是2千兆字符),
与varchar2或char类型一样,存储在long类型中的文本要进行字符集转换。ORACLE建议开发中使用clob替代long类型。支持long列只是为了保证向后兼容性。clob类型比long类型的限制要少得多。 
long类型的限制如下:
1.一个表中只有一列可以为LONG型。
2.LONG列不能定义为主键或唯一约束,
3.不能建立索引
4.LONG数据不能指定正则表达式。
5.函数或存储过程不能接受LONG数据类型的参数。
6.LONG列不能出现在WHERE子句或完整性约束(除了可能会出现NULL和NOT NULL约束)
raw:可变长度的二进制数据,这种数据类型存储的数据不会发生字符集转换,最多可以存储2,000字节。
rowid:指向表中行的位置的指针,里面是物理地址,是Oracle专有数据类型。

常见函数

字符串类型及函数:

lpad() 向左补全字符串:select lpad(stuno,6,'0') from t_user3;
rpad() 向右补全字符串:select rpad(stuno,6,'0') from t_user3;
lower() 返回字符串小写:select lower(userName) from t_user3;
upper() 返回字符串大写:select upper(userName) from t_user3;
initcap() 单词首字符大写:select initcap(userName) from t_user3;
length() 返回字符串长度:select length(password) from t_user3;
substr() 截取字符串:select substr(userName,1,2) from t_user3;
instr() 获取字符串出现的位置:select instr(password,'23',2,2) from t_user3;
ltrim() 删除左侧空格:select ltrim(userName) from t_user3;
rtrim() 删除右侧空格:select rtrim(userName) from t_user3;
trim() 删除两侧空格:select trim(userName) from t_user3;
concat() 串联字符串:select concat(userName,password) from t_user3;
reverse() 反转字符串:select reverse(userName) from t_user3;

数值类型及函数

abs() 求绝对值;select abs(n1) from t_number where id=1;
round() 四舍五入:select round(n1,2) from t_number where id=1;
ceil() 向上取整:select ceil(n1) from t_number where id=2;
floor 向下取整:select floor(n1) from t_number where id=2;
Mod()取模:select mod(5,3) from dual;
Sign()正负性:select sign(n1) from t_number where id=1;
Sqrt() 求平方根:select sqrt(9) from dual;
Power()求乘方:select power(2,3) from dual;
Trunc()截取:select trunc(123.456,2) from dual;
To_char() 格式化数值:常见的字符匹配有0、9、,、$、FM、L、C
select to_char(123.45,'0000.000') from dual;
select to_char(123.45,'9999.999') from dual;
select to_char(123123,'99,999,999.99') from dual;
select to_char(123123.3,'FM99,999,999.99') from dual;
select to_char(123123.3,'$99,999,999.99') from dual;
select to_char(123123.3,'L99,999,999.99') from dual;
select to_char(123123.3,'99,999,999.99C') from dual;

日期类型及函数

Add_months 添加月份select add_months(d1,2) from t_date where id=1;
Last_day 返回指定日期月份的最后一天select last_day(d1) from t_date where id=1;
update t_date set d3=to_date('2016-12-20','YYYY-MM-DD') where id=1;
update t_date set d3=to_date('2016-12-20 18:31:34','YYYY-MM-DD HH24:MI:SS') where id=1;
Months_between 返回两个日期的相差月数select months_between(d1,d3) from t_date where id=1;
next_day 返回特定日期之后的一周内的日期:select next_day(d1,2) from t_date where id=1;
Trunc 截取日期:
select trunc(d1,'YYYY') from t_date where id=1;
select trunc(d1,'MM') from t_date where id=1;
select trunc(d1,'DD') from t_date where id=1;
select trunc(d1,'HH') from t_date where id=1;
select trunc(d1,'MI') from t_date where id=1;
Extract 返回日期的某个域:
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
select extract(Hour from systimestamp) from dual;
select extract(minute from systimestamp) from dual;
select extract(second from systimestamp) from dual;
To_char 将日期转换成字符串:
select to_char(d1,'YYYY-MM-DD') from t_date where id=1;
select to_char(d1,'YYYY-MM-DD HH24:MI:SS') from t_date where id=1;

一些值得推荐的的SQL(Oracle11g的sys用户下亲测皆可用)

检查Oracle实例状态
select instance_name,host_name,startup_time,status,database_status from v$instance;
select name,log_mode,open_mode from v$database;

检查Oracle控制文件状态
select status,name from v$controlfile;

检查Oracle在线日志状态
select group#,status,type,member from v$logfile;

检查Oracle表空间的状态
select tablespace_name,status from dba_tablespaces;

检查Oracle所有数据文件状态
select name,status from v$datafile;
select file_name,status from dba_data_files;

检查无效对象
select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM'; 

检查所有回滚段状态
select segment_name,status from dba_rollback_segs;

检查Oracle初始化文件中相关参数值
select resource_name,max_utilization,initial_allocation,limit_value from v$resource_limit;

检查数据库连接情况
select count(*) from v$session;

检查表空间使用情况
select f.tablespace_name,a.total,f.free,round((f.free/a.total)*100) "% Free"
 from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+) order by "% Free";

检查system表空间内的内容
select distinct(owner) from dba_tables where tablespace_name='SYSTEM' and owner!='SYS' and owner!='SYSTEM' 
union 
select distinct(owner) from dba_indexes where tablespace_name='SYSTEM' and owner!='SYS' and owner!='SYSTEM';

检查对象的下一扩展与表空间的最大扩展值
select a.table_name, a.next_extent, a.tablespace_name from all_tables a, 
(select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name ) f 
where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk 
union 
select a.index_name, a.next_extent, a.tablespace_name from all_indexes a, 
(select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name ) f 
where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk;

检查数据库的等待事件
select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';

Disk Read 最高的SQL语句的获取
SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM<=5;

查找性能差的sql
SELECT * FROM 
(SELECT PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC) 

等待时间最多的5个系统等待事件的获取
SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;

检查运行很久的SQL
SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;

检查消耗CPU最高的进程
SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&1%';

检查碎片程度高的表
SELECT segment_name table_name,COUNT(*) extents 
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*)=
(SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name);

检查表空间的 I/O 比例
SELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME "FILE",F.PHYRDS PYR, F.PHYBLKRD PBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW 
FROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME;

检查文件系统的 I/O 比例
SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME", A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTS 
FROM V$DATAFILE A, V$FILESTAT B WHERE A.FILE# = B.FILE#;

查看是否有僵死进程
select spid from v$process where addr not in (select paddr from v$session);

检查共享池命中率
select sum(pinhits)/sum(pins)*100 from v$librarycache;

监控数据量的增长情况
select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
from (select tablespace_name,sum(bytes) total
from dba_free_space group by tablespace_name) A,
(select tablespace_name,sum(bytes) total
from dba_data_files group by tablespace_name) B where A.tablespace_name=B.tablespace_name;

检查失效的索引
select index_name,table_name,tablespace_name,status From dba_indexes Where owner='CTAIS2' And status<>'VALID';

检查不起作用的约束
SELECT owner, constraint_name, table_name, constraint_type, status 
FROM dba_constraints WHERE status ='DISABLE' and constraint_type='P';

检查无效的trigger
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';

获取当前 trace 文件路径脚本
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc'
AS "trace_file_name" FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr)
p,(SELECT	t.INSTANCE
FROM v$thread t, v$parameter v WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, (SELECT VALUE
FROM v$parameter WHERE NAME = 'user_dump_dest') d;

将不同表存到相同的物理数据块(Data Blocks)中。多表数据存储在相同数据块后,读取的物理块数减少,便于多表联合查询。对于单表的查询,但增、删、改等操作时,性能不高。
聚集索引中的索引列,称为聚集键(cluster key),其决定了数据在物理上的存储位置,这些列不应常更新。聚集键通常是其聚集表中同某个表的主键相关联的某表的外键。

创建簇

以SYSDBA角色登录并赋予用户相关权限:
grant alter any cluster to siege;

create cluster cluster_student(sid number)
pctused 40
pctfree 10
size 1024
storage(
       initial 128k
       next 128k
       minextents 2
       maxextents 20
)tablespace learning;

索引

创建索引:
create unique|bitmap index<schema>.<index_name>
  on<schema>.<table_name>
  (<column_name>|<expression>asc|desc,
   <column_name>|<expression>asc|desc...)
  tablespace<tablespace_name>
  storage<storage_setting>
  logging|nologging
  compute statistics
  nocompress|compress<nn>
  nosort|reverse
  partition|global partition<partition_setting>
说明:
unique|bitmap:  指定unique为唯一索引,bitmap为位图索引。
<column_name>|<expression>asc|desc:  对多列进行联合索引,当为expression时是"基于函数的索引"。
tablespace:  指定放索引的表空间(索引和表不在一个表空间效率更高)。
storage:  进一步设置表空间存储参数。
logging|nologging:  是否对索引产生重做日志。
compute statistics:  创建索引时收集统计信息。
nocompress|compress<nn>:  是否使用键压缩(使用键压缩可以删除一个键列中的重复值)
nosort|reverse:  与表中相同顺序创建索引,或者相反顺序。
partition|global partition<partition_setting>:  可以对分区表、未分区表上的索引进行分区。

普通索引
不加任何限制的索引。
create table user (id number(10),name char(10),pass char(10),age number(3),address number(10,2)); --建表
create index ordinary_suoyin on user(name); --建普通索引
唯一索引
不允许具有索引值相同的行,从而禁止重复的索引或键值。每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。
create unique index only_suoyin on user(pass);         -- 建唯一索引
主键索引
alter table user add constraint zhujian_parimary primary key(id);     -- 建主键
create unique index primary_suoyin on user(id);   --建索引
组合索引
某个索引包含有多个已索引的列,这个索引为组合(concatented)索引。

索引分类

  • B 树索引(Btree默认类型)
  • 位图索引
  • HASH 索引
  • 索引组织表索引
  •  反转键(reverse key)索引 基于函数的索引
  • 分区索引(本地和全局索引) 位图连接索引

B 树索引(Btree默认类型)
Oracle中默认的通用索引。可以单列也可以是组合/复合(多个列)的索引,最多可以包括32 列。 
特点
适合与大量的增、删、改(OLTP),不能用包含 OR 操作符的查询;
适合高基数的列(唯一值多),典型的树状结构,每个结点都是数据块。

位图索引
适合于决策支持系统(Decision Support System,DSS)和数据仓库,
不适合通过事务处理应用程序访问的表。可以使用较少到中等基数(不同值的数量)的列访问非常大的表。
尽管位图索引最多可达30个列,通常它们都只用于少量的列。
特点:
适合与决策支持系统;做 UPDATE 代价非常高;
非常适合 OR 操作符的查询;基数比较少的时候才能建位图索引;
技巧:
对于有较低基数的列需要使用位图索引。性别列就是这样一个例子,它有两个值:男或女(基数仅为 2)。
位图对于低基数(少量的不同值)列来说非常快,索引的尺寸相对于 B 树索引来说小了很多。
位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比 B 树做得好;多个会话同时向表中插入行时不应该使用位图索引。
在一个查询中合并多个位图索引后,可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。
位图索引不能被声明为唯一索引,位图索引的最大长度为30。 

hash索引 
使用 hash 索引必须要使用 hash 集群。建立一个集群或 hash 集群的同时,也就定义了一个集群键。
hash 索引可能是访问数据库中数据的最快方法。但,集群键上不同值的数目必须在创建 hash 集群之前就要知道。需要在创建 hash 集群的时候指定这个值。
这个键告诉 Oracle 如何在集群上存储表。在存储数据时,所有与这个集群键相关的行都被存储在一个数据库块上。
如果不能为集群的未来增长分配好附加的空间,hash 集群可能就不是最好的选择。
如果应用程序经常在集群表上进行全表扫描,hash 集群可能也不是最好的选择。
通常,HASH 对于一些包含有序值的静态数据非常有效。 
技巧:hash 索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。 

索引组织表
索引组织表会把表的存储结构改成 B 树结构,以表的主键进行排序。
对于一些涉及精确匹配和范围搜索的语句,索引组织表提供了一种基于键的快速数据访问机制。
基于主键值的 update 和 delete 语句的性能也同样得以提高,这是因为行在物理上有序。
键列的值在表和索引中都没有重复,存储的空间也少。
如果不会频繁地根据主键列查询数据,则需要在索引组织表中的其他列上创建二级索引。。
对于常通过对主键的精确匹配或范围扫描进行访问的表,可考虑使用索引组织表。
技巧:可以在索引组织表上建立二级索引。

反转键索引
在数据载入期间,部分索引和磁盘会比其他部分使用频繁得多。
Oracle提供反转键索引,数据以反转键索引存储,这些数据的值就会与原先存储的数值相反。
这样,数据1234就被存储成4321。索引会为每次新插入的行,更新不同的索引块。 
技巧:如果您的磁盘容量有限,同时还要执行大量的有序载入,可以使用反转键索引。 
不可将反转键索引与位图索引或索引组织表结合使用。因为不能对位图索引和索引组织表进行反转键处理。

基于函数的索引
在表中创建基于函数的索引。没有基于函数的索引,任何在列上执行了函数的查询都不能使用索引。
对列表达式 UPPER(job)创建索引,而不是直接在 job 列上建立索引,如:
create index EMP$UPPER_JOB on emp(UPPER(job)); --创建函数索引

分区索引
通过把一个索引分成多个片断,访问更小的片断(也更快),还可以把这些片断分别存放在不同的磁盘驱动器上(避免 I/O 问题)。
B 树和位图索引都可以被分区, HASH 索引不可以被分区。
分区方法:
表被分区而索引未被分区、表未被分区而索引被分区、表和索引都被分区。
本地分区索引(通常使用的索引):
可使用与表相同的分区键和范围界限来对本地索引分区。分区只包含了它所关联的表分区的键和 rowid。
本地索引可以是 B 树或位图索引。支持分区独立性,对于单独的分区可以增、截、删、分、脱机等处理,而不用同时删除或重建索引。Oracle 自动维护这些本地索引。
全局分区索引(只能是B树索引): 
在一个索引分区中包含来自多个表分区的键。一个全局分区索引的分区键是分区表中不同的或指定一个范围的值。
在创建全局分区索引时,必须定义分区键的范围和值。Oracle 在默认情况下不会维护全局分区索引。
如果一个分区被截、增、割、删等,就必须重建全局分区索引,除非在修改表时指定 alter table 命令的 update global index 子句。

索引不足 
1.创建索引和维护索引要耗费时间,随着数据量的增加而增加。  
2.索引要占物理空间,如果要建立聚簇索引,需要的空间就会更大。  
3.表中的数据增、删、改的时,索引也要动态维护,降低了数据的维护速度。  


索引列的特点 
1.经常需要搜索的列上。
2.主键的列上,强制该列的唯一性。
3.常用连接的列,这些列主要是一些外键,加快连接的速度。
4.常需要根据范围进行搜索的列上建索引,索引已经排序,指定的范围是连续的。
5.常需要排序的列上建索引,索引已经排序可以加快排序查询时间。
6.常使用在 WHERE 子句中的列上面创建索引,加快条件的判断速度。


不应该建索引列的特点 
1.查询中很少使用的列。 
2.有很少数据值的列。 
3.对于那些定义为 blob 数据类型的列不应该增加索引。

索引使用心得

1.null的使用

NULL 值并没有被定义。在 SQL 语句中使用 NULL 会有很多的麻烦。建表时,把索引的列设成 NOT NULL。
2.不匹配的数据类型
account_number 字段有索引是一个 VARCHAR2 类型。
Oracle 可以自动把 where 子句变成 to_number(account_number)=990354,
这样就限制了索引的使用,改成第二条查询就可以使用索引。
select bank_name,address from banks where account_number = 990354;
select bank_name,address from banks where account_number ='990354'; 
3.函数的使用
如果不使用基于函数的索引,那么在 SQL 语句的 WHERE 子句中对索引的列使用函数时,优化器会忽略这些索引。 
下面的查询不会使用索引(不是基于函数的索引) 
select empno,ename from emp where trunc(hiredate)='01-MAY-81';
改成下面的语句(不用函数),可以通过索引进行查找。 
select empno,ename from emp where hiredate<(to_date('01-MAY-81')+0.9999); 

约束

像主键、唯一等等其实都是一种约束。
create table z_test1(a char(10));   ---创建表
alter table z_test1 add constraint PK_Z_TEST1 primary key(a);  ----添加主键
create table z_test2(a char(10));     ---创建表
alter table z_test2 add constraint UQ_Z_TEST1 unique(a);  ----添加唯一约束
其中主键约束比唯一约束更严格,不能为空。

数据库中的键(key)又称为关键字,是逻辑结构不是数据库的物理部分。
主键(primary)
属于唯一键,是一个比较特殊的唯一键。区别在于主键不可为空。
唯一键(unique)
即一个或一组列,其中没有重复的记录。可以唯一标示一条记录。
外键(foreign)
外键表示了两个关系之间的联系。此表的某字段与另一表字段关联,此字段为外键,也称为外关键字。

分区

Oracle的分区表可以包括多个分区,每个分区都是一个独立的段(segment),放到不同的表空间中。
查询时可以通过查询表来访问各个分区中的数据,也可以通过在查询时直接指定分区的方法来进行查询。
Oracle官方给的两条建议
1.Tables greater than 2GB should always be considered for partitioning.
2.Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only

分区优点:
1.由于将数据分散到各个分区中,减少了数据损坏的可能性。
2.可以对单独的分区进行备份和恢复。
3.可以将分区映射到不同的物理磁盘上,来分散 IO。
4.提高可管理性、可用性和性能。

分区类型:
1.范围分区(range)
2.哈希分区(hash)
3.列表分区(list)
4.范围-哈希复合分区(range-hash)
5.范围-列表复合分区(range-list)

Range 分区:

应用范围较广的表分区,以列的值的范围来做分区条件。
如按照时间划分,2019年1月的数据放到a分区,2月的数据放到b分区...在创建的时候,需要指定基于的列,以及分区的范围值。创建maxvalue分区存放不在指定范围内的记录。 
如:
create table pdba (id number, time date) partition by range (time) 

partition p1 values less than (to_date('2019-10-1', 'yyyy-mm-dd')), 
partition p2 values less than (to_date('2019-11-1', 'yyyy-mm-dd')), 
partition p3 values less than (to_date('2019-12-1', 'yyyy-mm-dd')), 
partition p4 values less than (maxvalue) 
)

Hash 分区:

将表中的数据平均分到指定的几个区中,列所在分区是依据分区列的hash值自动分配,hash分区也可以支持多个依赖列。 
如: 
create table test  
(  transaction_id number primary key,  
   item_id number(8) not null )  
partition by hash(transaction_id)  

partition part_01 tablespace tablespace01,  
partition part_02 tablespace tablespace02,  
partition part_03 tablespace tablespace03  
); 

List 分区:

该分区列只能有一个,不能像 range 或者 hash 分区那样同时指定多个列做为分区依赖列,但它的单个分区对应值可以是多个。
分区时须确定区内列可能存在的值,插入的列值不在分区范围内,则插入/更新就失败。通常建一个 default 分区存储那些不在指定范围内的记录。 
create table custaddr
(
id varchar2(15 byte) not null,
areacode varchar2(4 byte)
)partition by list (areacode)
(partition t_list025 values ('025'), 
 partition t_list372 values ('372'), 
 partition t_list510 values ('510'),  
 partition p_other values (default)
)

组合分区:

某表按照某列分区之后,仍大,或一些其它的需求,可通过分区内建子分区的方式将分区再分区。
range-hash:
create table test  
( transaction_id number primary key,  
  transaction_date date  )  
partition by range(transaction_date) subpartition by hash(transaction_id) subpartitions 3 store in (tablespace01,tablespace02,tablespace03)  
( partition part_01 values less than(to_date(’2009-01-01’,’yyyy-mm-dd’)), 
  partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)), 
  partition part_03 values less than(maxvalue) ); 

  • 1
    点赞
  • 0
    评论
  • 7
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值