但行好事
莫论前程❤

Oracle实战总结—-基础知识介绍

数据库三范式

解释: 1NF:原子性 字段不可再分,否则就不是关系数据库;

​ 2NF:唯一性 一个表只说明一个事物;

​ 3NF:每列都与主键有直接关系,不存在传递依赖;

SQL语句的处理过程

img

解析(PARSE)        绑定(BIND)         执行(EXECUTE)       提取(FETCH)

解析(PARSE):

  1. 在共享池中查找SQL语句
  2. 检查语法
  3. 检查语义和相关的权限
  4. 合并(MERGE)视图定义和子查询
  5. 确定执行计划

绑定(BIND):

  1. 在语句中查找绑定变量
  2. 赋值(或重新赋值)

执行(EXECUTE):

  1. 应用执行计划
  2. 执行必要的I/O和排序操作

提取(FETCH):

  1. 从查询结果中返回记录
  2. 必要时进行排序
  3. 使用ARRAY FETCH机制

操纵语言

DML(DataManipulationLanguage,数据操作语言)

用于检索或者修改数据。

  • select:用于检索数据; (DQL数据查询语言)
  • insert:用于增加数据到数据库;
  • update:用于从数据库中修改现存的数据
  • delete:用于从数据库中删除数据。

DDL(DataDefinitionLanguage,数据定义语言):

用于定义数据的结构,比如创建、修改或者删除数据库对象。可以用于创建用户和重建数据库对象。

  • create table:创建表
  • alter table
  • drop table:删除表
  • create index
  • drop index

DCL(DataControlLanguage,数据控制语言):

用于定义数据库用户的权限。

  • alter password
  • grant
  • revoke
  • create synonym

数据字典

v$session    user_segments    dba_indexes    v$sqltext    all_objects

dba_、user_、v$_、all_开头视图之间的区别?

Oracle系统的管理信息存放在元数据表中,为了便于访问这些元数据建立了很多视图,称为数据字典。这些视图可以分为动态和静态两大类。

  • v$打头的称为动态性能视图,里面的内容是Oracle运行中的状态,可以认为是内存中的信息;
  • dba_、user_、all_打头的为静态数据字典,里面的内容在用户访问Oracle时并不会随时发生变化。
  • dba_打头的存放数据库中所有相关对象的信息,例如你要知道当前系统中有哪些表对象,就可以查询dba_tables这个视图;
  • user_打头的是当前用户拥有的所有对象的信息;
  • all_打头的是当前用户有权限访问的所有对象的信息(不一定拥有该对象,只要可以访问就算)

数据字典有三个主要用途:

● Oracle 利用数据字典来获取关于用户,方案对象,及存储结构(storagestructure)的信息。

● 每当数据库中执行了 DDL 语句后,Oracle 将对数据字典进行修改。

数据字典-实用例子

查询当前正在执行的sql

select a.USERNAME,
       a.SID,
       'alter system kill session ''' || a.sid || ',' || a.serial# || ''';' activeSQL,
       b.SQL_TEXT,
       b.SQL_FULLTEXT,
       b.SQL_ID,
       a.CLIENT_INFO ip,
       a.MACHINE,
       a.OSUSER,
       last_call_et
  from v$session a, v$sqlarea b
 where a.SQL_ADDRESS = b.ADDRESS

查询当前被锁的表

select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''':' activeSQL,
       s.SID,
       s.SERIAL#,
       s.MODULE,
       s.PROCESS,
       l.CTIME,
       o.OBJECT_NAME,
       s.MACHINE,
       s.OSUSER
  from v$transaction t, v$session s, v$lock l, all_objects o
 where t.SES_ADDR = s.SADDR
   and s.SID = l.SID
   and l.TYPE = 'TM'
   and l.ID1 = o.OBJECT_ID

Dual虚拟表

  • 我们先从名称来说,dual不是缩写词,本身就是完整的单词。dual名词意思是对数,做形容词时是指二重的,二元的。
  • Oracle中的dual表是一个单行单列的虚拟表。
  • Dual表是oracle与数据字典一起自动创建的一个表,这个表只有1列:DUMMY,数据类型为VERCHAR2(1)dual表中只有一个数据'X', Oracle有内部逻辑保证dual表中永远只有一条数据。
  • Dual表主要用来选择系统变量或求一个表达式的值。
  • DUAL表可以执行插入、更新、删除操作,还可以执行drop操作。但是不要去执行drop表的操作,否则会使系统不能用,数据库起不了,会报Database startup crashes with ORA-1092错误。

Dual虚拟表-实用例子

查看当前连接用户

SQL> select user from dual;
USER

查看当前日期、时间

SQL> select sysdate from dual;
2018-11-24

当作计算器用

SQL> select 1+2 from dual;
3

查看序列值

SQL> create sequence aaa increment by 1 start with 1;
SQL> select aaa.nextval from dual;
1

UNDO表空间

你对数据执行修改时,数据库会生成undo信息,这样万一你执行的事务或语句由于某种原因失败了,或者如果你用一条ROLLBACK语句请求回滚,就可以利用这些undo信息将数据放回到修改前的样子。

redo用于在失败时重放事务(即恢复事务),undo则用于取消一条语句或一组语句的作用。

undo表空间用于存放undo数据,当执行DML操作(insert、update、delete)时,oracle会将这些操作的旧数据写入到undo段。

undo的作用:

  • 事务处理回退
  • 事务处理恢复
  • 读一致性
  • 闪回数据

归档空间

Oracle数据库有联机重做日志,这个日志是记录对数据库所做的修改。比如插入,删除,更新数据等,对这些操作都会记录在联机重做日志里。

数据库使用归档方式运行时才可以进行灾难性恢复非归档模式只能做冷备份,并且恢复时只能做完全备份.最近一次完全备份到系统出错期间的数据不能恢复.归档模式可以做热备份,并且可以做增量备份,可以做部分恢复.

# 管理员权限登录
sqlplus / as sysdba

########################################################
# 清理归档
 rman
 connect target /
 crosscheck archivelog all;
 delete expired archivelog all; 

# 归档状态查看
  SQL> archive log list

# 在Linux查看归档空间大小
bdf 或 df -H

########################################################
# 开启归档
  a. 关闭数据库shutdown immediate
  b. startup mount
  c. alter database archivelog
  d. alter database open

# 关闭归档
  a. 关闭数据库shutdown immediate
  b. startup mount
  c. alter database noarchivelog
  d. alter database open

临时表空间

​ 临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候,并且当PGAsort_area_size大小不够时,将会把数据放入临时表空间里进行排序。像数据库中一些操作: CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。

临时表空间是NOLOGGING模式以及它不保存永久类型对象,因此即使数据库损毁,做Recovery也不需要恢复Temporary Tablespace

img

如何查看表空间

select a.tablespace_name,
       round(a.bytes_alloc / 1024 / 1024) megs_alloc,
       round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
       round(maxbytes / 1048576) Max
  from (select f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
          from dba_data_files f
         group by tablespace_name) a,
       (select f.tablespace_name, sum(f.bytes) bytes_free
          from dba_free_space f
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name,
       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /1048576) megs_free,
       round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
       100 -round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
       round(sum(f.maxbytes) / 1048576) max
  from sys.v_$TEMP_SPACE_HEADER h,
       sys.v_$Temp_extent_pool  p,
       dba_temp_files           f
 where p.file_id(+) = h.file_id
   and p.tablespace_name(+) = h.tablespace_name
   and f.file_id = h.file_id
   and f.tablespace_name = h.tablespace_name
 group by h.tablespace_name
 ORDER BY 1

索引 INDEX

  • 是一个方案对象
  • 由Oracle 服务器使用,索引用一个指针来加速行的取回
  • 用快速路径访问方法来快速定位数据,减小磁盘I/O
  • 表和它的索引是无关的
  • 被Oracle 服务器自动地使用和维护
  • 缺点会需要占用服务器的磁盘空间,降低增删改的执行效率

SQL执行计划

​ 简单一点说就是oracle如何执行sql语句的,比如说你去一个地方,事先会计划好怎么坐车一样,先坐公交到哪儿再坐地铁,oracle的执行计划也是如此,就是一步一步执行sql,比如说先查数据再排序,或者先作子查询再作主查询什么的。oracle的执行计划很复杂的,一般我们看到的执行计划都是oracle通过内部算法计算以后选择的一个消耗比较少的执行路径,就像我们去哪里也要选择一条捷径一样的道理。

img

隐式转换规则1

#相当于# 对于INSERT和UPDATE操作,oracle会把插入值或者更新值隐式转换为字段的数据类型。如假如id列的数据类型为number 

update t set id='1'; 
#相当于
update t set id=to_number('1'); 

insert into t(id) values('1') 
#相当于
insert into t values(to_number('1'));

# 对于SELECT语句,oracle会把字段的数据类型隐式转换为变量的数据类型。
# 如假设id列的数据类型为varchar2 
select * from t where id=1; 
#相当于
select * from t where to_number(id)=1;

# 但如果id列的数据类型为number,则
select * from t where id='1'; 
#相当于
select * from t where id=to_number('1');

# 当比较一个字符型和数值型的值时,oracle会把字符型的值隐式转换为数值型。如假设id列的数据类型为number 
select * from t where id='1'; 
#相当于
select * from t where id=to_number('1');

隐式转换规则2

# 当比较字符型和日期型的数据时,oracle会把字符型转换为日期型。
# 如假设create_date为字符型,
select * from t where create_date>sysdate; 
# 相当于
select * from t where to_date(create_date)>sysdate; 

# 假设create_date为date型,
select * from t where create_date>'2006-11-11 11:11:11'; 
# 相当于
select * from t where create_date>to_date('2006-11-11 11:11:11'); 

# 如果调用函数或过程等时,如果输入参数的数据类型与函数或者过程定义的参数数据类型不一直,则oracle会把输入参数的数据类型转换为函数或者过程定义的数据类型。如
# 假设过程如下定义 
p(p_1 number)
exec p('1'); 
# 相当于
exec p(to_number('1'));

隐式转换规则3

# 赋值时,oracle会把等号右边的数据类型转换为左边的数据类型。如
var a number a:='1'; 
# 相当于
a:=to_number('1');

# 用连接操作符(||)时,oracle会把非字符类型的数据转换为字符类型。
select 1||'2' from dual; 
# 相当于
select to_char(1)||'2' from dual;

# 如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算,则oracle会将字符类型的数据转换为合适的数据类型,这些数据类型可能是number、date、rowid等。
# 如果CHAR/VARCHAR2 和NCHAR/NVARCHAR2之间作算术运算,则oracle会将她们都转换为number类型的数据再做比较。

# 比较CHAR/VARCHAR2 和NCHAR/NVARCHAR2时,如果两者字符集不一样,则默认的转换方式是将数据编码从数据库字符集转换为国家字符集。

隐式转换规则4

简单总结:
  • 比较时一般是字符型转换为数值型,字符型转换为日期型
  • 算术运算时一般把字符型转换为数值型,字符型转换为日期型
  • 连接时(||)一般是把数值型转换为字符型,日期型转换为字符型
  • 赋值、调用函数时以定义的变量类型为准。

共享池(SHARED BUFFER POOL)

为了不重复解析相同的SQL语句,在第一次解析之后,Oracle将SQL语句存放在内存中。这块位于系统全局区域SGA(systemglobal area)的共享池(shared buffer poo1)中的内存可以被所有的数据库用户共享。因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前执行过的语句完全相同,Oracle就能很快获得已经被解析的语句以及最好的执行方案。Oracle的这个功能大大地提高了SQL的执行性能并节省了内存的使用。

SQL共享的三个条件

  • 当前被执行的语句和共享池中的语句必须完全相同 (包括大小写、空格、换行等)
  • 两个语句所指的对象必须完全相同 (同义词与表是不同的对象)
  • 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)

共享SQL语句

注意:

Oracle对两者采取的是一种严格匹配策略,要达成共享。SQL语句必
须完全相同(包括空格、换行等)。能够使用共享的语句必须满足三个

条件:

① 字符级的比较。
当前被执行的语句和共享池中的语句必须完全相同。例如:

SELECT  * FROM ATABLE;  # 和下面每一个SQL语句都不同:
SELECT  * from ATABLE
Select  * From Atable;

② 语句所指对象必须完全相同 即两条SQL语句操作的数据库对象必须同一。
③ 语句中必须使用相同命名的绑定变量。如:第一组的两个SQL语句是相同的,可以共享;而第二组中两个语句不同,即使在运行时赋予不同的绑定变量以相同的值:

# 第一组 
select pin,name from people where pin =:blk1.pin;
select pin,name from people where pin =:blk1.pin;
# 第二组
select pin,name from people where pin =:blk1.ot_jnd;
select pin,name from people where pin =:blk1.ov_jnd;

SQL语句的处理过程

img

赞(1) 打赏
未经允许不得转载:刘鹏博客 » Oracle实战总结—-基础知识介绍
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏