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. 创建合理的表结构，可以适当对数据进行冗余，减少子查询



软件下载提取码：qwer
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/ty0910/p/14339226.html