Oracle第十二课

Oracle第十二课

一、课后作业讲解

• 去掉班级里没有学生的班级:exists效率更高

  DELETE FROM t_clazz WHERE NOT EXISTS (SELECT * FROM t_student WHERE t_student.clazz=t_clazz.id);

• 更新B表中的c2为A表中的c2

  UPDATE B SET c2=(SELECT c2 FROM A WHERE A.c1=B.c1) WHERE c1=(SELECT c1 FROM A WHERE A.c1=B.c1);

二、PGA（程序缓存区）

• program global area

• 为了某个用户进程所服务的，这个内存区不是共享的，只有这个用户的的服务进程本身能够访问它自己的PGA区

• 功能介绍

  • 为排序设置合理的排序区大小（SORT AREA）

  • 会话区保存着用户的权限等重要信息(USER SESSON DATA)

  • 堆栈区保存变量信息(STACK SPACE)

    select * from emp where deptno=10;
    select * from emp where deptno=20;
    -- 两条SQL不一样，需要编译两次
    select * from emp where deptno=?;
    -- 传入参数10或20
    -- 这是一样的，只需要编译一次

  • 游标区 (CURSOR STATE)

    • 游标区域是动态的，打开游标会自动创建区域，关闭游标会自动释放区域

三、SGA(系统全局区)

• system global area

• 对系统内的所有进程都是共享的，当多个用户同时连接到一个例程时，所有的用户进程，服务进程都可以共享使用这个SGA区。

• 一个Oracle实例，一个SGA区

• 查看SGA的各种pool,在v$sgastat

  select pool ,sum(bytes) bytes from v$sgastat group by pool;

  SGA由java pool（java 池）、shared pool（共享池）、large pool（大池） 和没有名字的池组成。其中那块没有名字的内存又包括块缓冲区（缓存的数据库块）、重做日志缓冲区和“固定SGA”区专用的内存。

  1. Fixed SGA 固定SGA

     通过这个区来查其他区的地址

  2. Redo Buffer 重做缓冲区

     如果数据需要写到在线重做日志中，则在写至磁盘之前要在重做缓冲区（redo buffer）中临时缓存这些数据。

  3. Block Buffer 块缓冲区缓存

     Oracle将数据库块写至磁盘之前，另外从磁盘读取数据库块之后，就会把这些数据库块存储在块缓冲区缓存（block buffer cache）中。

  4. shared pool（共享池）

     共享池就是Oracle缓存一些“程序”数据的地方。在解析一个查询时，解析得到的表示（representation）就缓存在那里。在完成解析整个查 询的任务之前， Oracle会搜索共享池，看看这个工作是否已经完成。你运行的PL/SQL代码就在共享池中缓存，所以下一次运行时，Oracle不会再次从磁盘重新读 取。PL/SQL代码不仅在这里缓存，还会在这里共享。如果有1 000个会话都在执行同样的代码，那么只会加载这个代码的一个副本，并由所有会话共享。Oracle把系统参数存储在共享池中。数据字典缓存（关于数据库 对象的已缓存信息）也存储在这里。简单地讲，就像是厨房的水池一样，什么东西都往共享池里放。我的理解是：这是个共用的东西，大家都可以用。比如一个用户 进行一次查询，在解析之前，查看共享池，这个sql语句是否已经缓存在这里了。如果在，他就没有必要再去进行解析了，因为已经解析好了，直接拿来用就可以 了！这也是绑定变量为什么可以改善oracle性能的原因！

  5. large pool 大池

     大块内存分配则是得到一块内存后加以使用，然后就到此为止，没有必要缓存这个内存。

     把原来属于共享 池里面的一些特殊的内存拿出来进行不同的处理。因为这些内存用完之后就可以立即释放，而共享池的内存不存在释放问题，因为是大家共享的。

  6. Java pool Java池

     在数据库中运行Java代码时用到这部分内存。

  7. Stream pool 流池

     流是用来共享和复制数据的工具。

四、存储空间

• 从小到大

  数据块，分区，段，表空间

• 介绍

  1. 数据块:block

     数据块设置比较大，那么一次读取的数据行较多，相应对SGA内存消耗比较大，特定查询引发的换入换出可能较多。如果设置的过小，频繁的IO逻辑物理读也会引起性能问题。

  2. 区：extent

     在进行存储数据信息的时候，Oracle将分配数据块进行存储，但是不能保证所有分配的数据块都是连续的结构。所以，出现分区extent的概念，表示一系列连续的数据块集合。

     数据段是分区的上层组织单位

  3. 表空间：tablespace

     表空间中有多个段，表空间时最大的，可以对应多个文件

五、分区表(一个表在不同的表空间上)

1. 范围分区

   根据某列的值的范围分区，如果值为null，则放到最大值的区中

   如：员工的sal分区：1-1500，15001-3000，3001-无穷大

2. 散列

   根据字段的hash值进行均匀分布，尽可能地实现各分区所散列的数据相等。

3. 列表

   列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。

4. 复合

   根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区同再使用散列分区的一种分区方法。

5. 恢复方法

   数据库运行在archive 模式下，那么一旦数据库损坏则可以通过冷备份(热备份)和归档备份将数据库恢复到断点状态。

6. 优点：

   • 增强可用性，一个分区坏了，其他分区还可以用

   • 均衡I/O，可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能

   • 提高性能，对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快，在数据仓库的TP查询特别有用。

   • 分区对用户透明，用户感觉时同一张表

7. 示例

   -- 1.建立3个表空间
   CREATE TABLESPACE tb1 DATAFILE 'tb1.dbf' SIZE 1m;
   CREATE TABLESPACE tb2 DATAFILE 'tb2.dbf' SIZE 1m;
   CREATE TABLESPACE tb3 DATAFILE 'tb3.dbf' SIZE 1m;
   -- 2.建立分区表，
   -- 根据范围分区
   CREATE TABLE t_area(ID NUMBER PRIMARY KEY,money NUMBER)
   PARTITION BY RANGE(money)
   (
   PARTITION p1 VALUES LESS THAN (1500) TABLESPACE tb1,
   PARTITION p2 VALUES LESS THAN (3000) TABLESPACE tb2,
   PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE tb3
   );
   -- hash分区
   CREATE TABLE t_area(ID NUMBER PRIMARY KEY,money NUMBER)
   PARTITION BY HASH(money)
   (
   PARTITION p1  TABLESPACE tb1,
   PARTITION p2  TABLESPACE tb2,
   PARTITION p3  TABLESPACE tb3
   );
   -- 3.插入数据
   INSERT INTO t_area VALUES(sq.nextval,1000);-- 将插入到分区p1
   INSERT INTO t_area VALUES(sq.nextval,2000);-- 将插入到分区p2
   INSERT INTO t_area VALUES(sq.nextval,5000);-- 将插入到分区p3
   -- 4.查询数据，在相应分区查询相应的money
   SELECT * FROM t_area PARTITION(p1);-- 查询p1分区
   SELECT * FROM t_area PARTITION(p2);-- 查询p2分区
   SELECT * FROM t_area PARTITION(p3);-- 查询p3分区
   -- 查询6000工资员工的信息，在p3分区查找
   select * from t_area partition(p3) where money=6000;

   • 删除分区

     -- 内容，分区都删除
     ALTER TABLE yourTable DROP PARTITION partionName1;
     -- 只清除数据
     ALTER TABLE yourTable TRUNCATE PARTITION partionName1;

六、分区索引

• 全局索引

  create index 索引名称 on 表名(字段) global
  ​
  partition by range (字段)
  (
    partition index_part1 values less than (60),
  ​
    partition index_part2 values less than (80),
  ​
    partition index_partmax values less than (maxvalue)
  );

• 局部索引

  create index 索引名 on 表名 (字段1, 字段2) local;

七、union

SELECT empno FROM emp
UNION
SELECT empno FROM emp;

1. 多条sql的结果合并，但是每条sql的列数相同

2. 每条sql的列的类型要相互匹配，char对char，number对number

3. union和union all的区别：一个合并，一个不合并

   • union结果没有重复的

   • union all结果重复

八、导入导出

1. 导出

   • pl/sql导出：tools->export

   • 命令行导出：

     exp scott/scott@orcl file="d:/1.sql"

2. 导入

   • pl/sql导入：tools->import

   • 命令行导入：

     imp scott/scott@orcl file="d:/1.sql" full=y
     -- 成功终止导入，说明导入成功

九、SQL优化

1. 写出具体的列名，不写*，减少SQL的分析时间，不用再去数据字典中找列的信息。

2. 创建索引

3. 对索引列的过滤时，不用函数，不要隐匿转换数据类型，不用模糊匹配开头查询

4. 创建合理的表结构，可以适当对数据进行冗余，减少子查询