Oracle

Oracle基础知识详解思维导图含体系结构、索引、函数等。

Oracle

• 概述
  • 简介
    • 常见的数据库管理系统
      • 目前市场上包括Oracle、DB2、Sybase和SQL Server等。 1、Oracle是当今最大的数据库厂商,Oracle公司的数据库产品是世界上第一个商品化的关系型数据库管理系统.也是第一个推出与数据库结合的第四代语言开发工具的数据库产品。 2、DB2是IBM公司于1983年推出的一个商业化关系数据库管理系统，它是基于System R基础上实现的。可以运行在各种不同的操作系统平台上，如UNIX，Windows，OS/2等。 3、Sybase可以运行在不同的操作系统平台上，作为网络数据库，Sybase采用开放的体系结构，支持网络环境下各节点数据库的互相访问。 4、Microsoft SQL Server是微软公司推出的应用于Windows操作系统上的关系数据库产品。Microsoft SQL Server是Microsoft公司从Sybase公司购买技术而开发的产品，与Sybase数据库完全兼容，它支持客户机/服务器结构
    • 拉里.埃里森（Ellison）简历
      • 姓 名：拉里.埃里森，出生于1944年曼哈顿，移民的美国犹太人后裔 婚 史：曾经历过3次婚姻 学 历：读过三所大学，伊利诺斯大学、芝加哥大学、西北大学，后辍学, 没得一个学位! 人生目标：击败微软，成为世界最大的软件企业。 财富：Ellison被《财富》杂志列为世界第五巨富，他拥有的Oracle是世界上最大的数据库软件公司。 惊人手笔：拥有一架拆除武器的意大利产‘马尔切蒂S.211’型战斗机，开着战斗机在太平洋上空和别人进行模拟空战。
  • ORACLE产品
    • Oracle是一个庞大的品牌系统 ： 数据库管理系统：最新版本11g 横向和纵向的产品：如Oracle自己设计生产的Oracle服务器，面对商业客户的商业交易系统（Oracle Exchange），Oracle数据中心等等。 面对程序员的开发工具：如Oracle Designer计算机辅助系统工程（CASE）工具和Oracle Developer开发包。 面对不同对象的应用软件：统称为Oracle应用软件。这些软件使得Oracle被广泛应用到各个领域，同时也为Oracle带来滚滚财源。比较常见的Oracle软件产品包括： 　　·Oracle财务软件（Oracle Financial） 　　·Oracle制造业软件（Oracle Manufacturing） 　　·Oracle人力资源软件（Oracle Human Resources） 　　·Oracle自动控制软件（Oracle Automotive） 　　·Oracle 商业交易系统（Oracle Exchange）
  • ORACLE 数据库发展过程
    • 1979年，RSI，0racle2.0 ：世界第一个基于SQL标准的关系数据库管理系统 1983 年，Oralce3.0：第一个全部用C语言编写的数据库，第一款在 PC 机、小型机及大型机上运行的便携式数据库。 1984年，oracle4.0：增加了读一致性。 1985年，oracle5.0：第一个可以在C/S模式下运行的RDBMS。 1988年，oracle6.0：增加PL/SQL过程化语言。 1992年，oracle7.0：基于UNIX操作系统的版本，多线程服务器体系结构，支持更多用户并发访问。 1997年，oracle8.0：基于JAVA，支持面向对象的开发.使Oracle更适合构造大型应用系统。 1998年，oracle8i：网络数据库，全面支持Internet技术。 2001年，oracle9i：在集群技术、高可用性、商业智能、安全性、系统管理方面实现新突破。 2004.1，Oracle10g：基于网格计算的数据库。 2007.7，Oracle 11g：有400多项功能，经过了1500万个小时的测试，开发工作量达到了3.6万人/月。
  • Oracle数据库应用结构
    • 1. 单用户DBMS
    • 2. 集中式数据库应用构架——大型主机和终端相结合
    • 3. 分布式数据库系统结构
    • 4. 客户机／服务器应用构架(C/S)
    • 5. 多层数据库应用构架
    • 6. Internet数据库应用框架 （网络计算机体系结构，ＮＣＡ）
  • ORACLE的常用工具
    • SQL*PLUS:提供用户和机器的交互式接口， PL/SQL:增加了过程语句:分支、循环、跳转; SQL*Forms：非过程化的设计工具； Pro*C：嵌入式SQL; Pro*C为oracle预编译器，将源代码中的SQL语句转换为函数调用。 SQL*menu：菜单工具 Reporter Builder：报表工具 SQL*net 或 Net8：通讯工具 Graphics Builder:图形工具
  • ORACLE的优越性
    • 1.可以在任何类型的计算机上运行； 2.支持多种操作系统；（UNIX,WINDOWSNT) 3.支持多种网络协议；（TCP/IP、IPX/SPX) 4.可组成多种计算模式：主机/终端、C/S、INTERNET/INTRANET、NC； 5.可移植性好； 6. 丰富的开发工具；（Design2000,SQL*PLUS,SQL*Forms,SQL*menu等） 7.使用结构化查询语言SQL； 8.可以处理大文本。
  • DBA的话题
    • DBA的类型： 1、系统或操作类的DBA：监视所有的实例和服务器，改变数据库的结构，比如增加用户和表空间，等等。 2、应用类的DBA，用 PL/SQL 或Java 、HTML、Web DB或其他数据库编程方法，为最终用户或者其他应用程序开发提供支持。 3、数据类的DBA，他们专于数据的完整性和数据清理，特别是在数据仓库或数据集市这样的环境中。 初级DBA总是以运行基本的数据库任务开始，因此我们这里特别强调系统 D B A的任务。
  • ORACLE的C/S结构
    • 1.C/S结构：分布式处理应用系统，应用执行的任务被分配给两个或多个不同的处理组件，在一个C/S应用中,有服务器、客户和网络。 （1）客户端：应用的前端，功能如下： 提供用户界面，使计算机用户与服务器交互； 验证数据的输入 发布查询信息到数据库服务器端 处理从服务器返回的信息:如创建图形、图 表等。 （2）服务器端:应用的后端，储存数据，管理数据库 功能： 打开一个数据库，使该数据库对每一个访 问者可用； 有严密的安全控制功能，防止未授权用户 对数据库的访问； 有强大的数据库备份和恢复功能； 在众多用户进行访问工作时，维护数据的 完整性和一致性 防止并发事物访问相同数据集产生的错误及破坏性的冲突, （3）网络：连接客户端和服务器； 2.客户端与服务器端的组成和结构
  • DBA任务
    • 1. 软件安装与升级： 升级：用新的文件覆盖旧版本的 Oracle软件，通常把新软件的文件安装在旧版本软件文件的相同目录结构下，覆盖或删除掉已有的同名源文件。而有不同名的旧版本文件则被遗留下来。因此，磁盘上将有全部的新版本文件和一些遗留下来的旧版本文件，十分混乱。 软件移植或重新安装：为新版本软件生成独立的目录结构。你可能同时有两个或更多的 Oracle软件副本在你的系统上，但它们处于不同的相互隔离的目录中。 数据库升级：如果你有一个7.3.2.3.2版本的数据库，并想把它升级成一个7.3.3.6的数据库，关闭旧版本的环境下的数据库，在新版本的环境中运行它，然后再立即进行配置把这个数据库转化成新的版本。 数据库移植：在新版本环境中建立一个新的数据库，对旧的数据库产生一个备份，然后把备份复制到新数据库中。 2. 数据库行为：直接改变数据库的组织和配置的任务。如： ? 决定一个提交的数据库的磁盘存储空间。 在应用处于开发阶段时，监控数据库的性能，以在形成产品之前发现潜在的性能问题。 为数据库提供一个连续不断的调整过程，例如，当需要时调整共享池的空间、数据库块缓冲区、日志缓冲区，等等。 ? 周期性地监视表和表空间的增长及碎片，以保证空间分配不会成为危险问题。 制定备份/恢复计划. 3. 系统问题：在操作系统级上所采取的行动 ? 当需要时执行清除任务。比如从 Oracle软件区域中清除旧的文件，保证归档日志有足够的磁盘空间，等等。 ? 执行数据库性能调整， 确保每日夜间 (或一定周期)的备份运行正常; 4. 开发者问题：帮助开发者完成目标的行为。 对 Oracle工具的基本功能要有充分的了解，以便能判断出问题是由于应用，还是由于工具方面的原因所引起的。 ? 在数据库设计和问题解决方面帮助开发者，包括怎样运行和理解TKPROF及Explain Plan实用程序的输出以调整 SQL查询。 5. 安全问题：指的是开发和维护有关控制你的数据库存取的过程和步骤。 ? 对所有的数据库及文档设置并保持用户及DBA口令。 ? 创建用户和开发者账户， ? 在合适的时候，用Oracle的审计功能来监视表或用户的行为。
• 基础
  • Oracle架构
    • Oracle RDBMS架构
      • 架构图
      • 架构
        • 一般我们所说的Oracle指的是Oracle RDBMS(Relational databases Management system)，一套Oracle数据库管理系统，也称之为Oracle Server。而Oracle Server主要有两大部分： Oracle Server = 实例 + 数据库 (Instance和Database是相互独立的) 数据库 = 数据文件 + 控制文件 +日志文件 实例 = 内存池 + 后台进程 所以可以细分为： Oracle Server = 内存池 + 后台进程 + 数据文件 + 控制文件 + 日志文件 一台Oracle Server支持创建多个Database，而且每个Datacase是互相隔离而独立的。不同的Database拥有属于自己的全套相关文件，例如：有各自的密码文件，参数文件，数据文件，控制文件和日志文件。 Database由一些物理文件(如：存放在存储设备中的二维表文件)组成。二维表存储在Database中，但Database的内容不能被用户直接读取，用户必须通过Oracle instance才能够访问Database，一个Instance只能连接一个Database，但是一个Database可以被多个Instance连接。
    • 内存结构
      • 图解
      • 详解
        • SGA
          • 简介
            • SGA(Sytem Global Area),由所有服务进程和后台进程共享； SGA包含实例数据和控制信息，包含如下内存结构： 1）、Data Buffer Cache：缓存了从磁盘上检索的数据块。 2）、Redo Log Buffer：缓存了写到磁盘之前的重做信息。 3）、Shared Pool：缓存了各用户间共享的结构。 4）、Large Pool：一个可选的额区域，用来缓存大的I/O请求，以支持并行查询、共享服务器模式以及默写备份操作。 5）、Java Pool：保存java虚拟机中特定会话的数据和java代码。 6）、Streams Pool：由Oracle Streams使用。 7）、Keep Buffer Cache：保存 Buffer Cache 中存储的数据，使其时间尽可能长。 8）、Recycle Buffer Cache：保存 Buffer Cache 中即将过期的数据。 9）、nK Block Size Buffer：为与数据库默认数据块大小不同的数据块提供缓存；用来支持表空间传输。 Database Buffer Cach、Shared Pool、Large Pool，Streams Pool与Java Pool根据当前数据库状态，自动调整； Keep Buffer Cache、Recycle Buffer Cache、nK Bolck Size Buffer可以在不关闭实例情况，动态修改。
            • 内存池SGA的默认Size
              • 内存池SGA的默认Size，会在安装Oracle的时候会根据LinuxOS的sysctl.conf参数文件来决定： kernel.shmall = 2097152 kernel.shmmax = 2147483648 kernel.shmmni = 4096 kernel.sem = 25032000100128 net.ipv4.ip_local_port_range = 102465000 net.core.rmem_default = 1048576 net.core.rmem_max = 1048576 net.core.wmem_default = 262144 net.core.wmem_max = 262144 查看SGA的Size： SQL> conn /as sysdba Connected. SQL> show user; USER is"SYS" SQL> select * from v$sga; NAME VALUE -------------------- ---------- Fixed Size 2022144 Variable Size 503317760 Database Buffers 1627389952 Redo Buffers 14753792 SQL> show sga Total System Global Area 2147483648 bytes #对应kernel.shmmax = 2147483648 Fixed Size 2022144 bytes Variable Size 503317760 bytes Database Buffers 1627389952 bytes Redo Buffers 14753792 bytes
          • 结构
            • Data Buffer Cache
              • 1、DB_CACHE_SIZE 　 通过参数DB_CACHE_SIZE可指定DB buffer cache的大小 　 ALTER SYSTEM SET DB_CACHE_SIZE=20M scope=both; 　 服务进程从数据文件读数据到buffer cache；DBWn从buffer cache写数据到数据文件。 　buffer cache的四种状态： 　1）pinned：当前块正在读到cache或正写到磁盘，其他会话等待访问该块。 　2）clean： 　3）free/unused：buffer内为空，为实例刚启动时的状态。 　4）dirty：脏数据，数据块被修改，需要被DBWn刷新到磁盘，才能执行过期处理。
              • 1、Default：相当于一个没有Keep与Recycle池的实例的buffer cache，通过db_cache_size参数指定。 2、为对象明确指定buffer pool 　　　　buffer_pool子句，用来为对象指定默认的buffer pool，是storage子句的一部分。 　 对create与alter table、cluster、index语句有效。 　如果现有对象没有明确指定buffer pool，则默认都指定为default buffer pool，大小为DB_CACHE_SIZE参数设置的值。 语法： 　a.CREATE INDEX cust_idx ON tt(id) STORAGE (BUFFER_POOL KEEP); 　b.ALTER TABLE oe.customers STORAGE (BUFFER_POOL RECYCLE); 　c.ALTER INDEX oe.cust_lname_ix STORAGE (BUFFER_POOL KEEP);
              • 修改缓冲区DB_CACHE_SIZE地方法
                • #Step1. 查看SGA的大小：因为DB_CACHE_SIZE的size受SGA的影响 SQL> show parameter sga_max_size; NAME TYPE VALUE ----------------------------------------------------------------------------- sga_max_size big integer2G #Step2. 查看show parameter shared_pool_size的大小 SQL> show parameter shared_pool_size; NAME TYPE VALUE ----------------------------------------------------------------------------- shared_pool_size big integer0 #Step3. 计算DB_CACHE_SIZE的大小：shared_pool_size + db_cache_size = SGA_MAX_SIZE *70% #Step4. 修改DB_CACHE_SIZE的大小 SQL> alter system set db_cache_size=1433M scope=spfile sid='demo'; System altered. SQL> conn sys /as sysdba Enter password: ******** Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2147483648bytes Fixed Size 2022144bytesVariable Size 503317760bytes Database Buffers 1627389952bytes Redo Buffers 14753792bytes Database mounted. Database opened. SQL> show parameter db_cache_size
            • Redo Log Buffer
              • 1、服务进程从用户空间拷贝每条DML/DDL语句的redo条目到redo log buffer中。 2、redo log buffer是一个可以循环使用的buffer，服务进程拷贝新的redo覆盖掉redo log buffer中已通过LGWR写入磁盘（online redo log）的条目。 3、导致LGWR执行写redo log buffer到online redo log的条件 a.用户执行事务提交commit b.每3秒钟或redo log buffer内已达到1/3满或包含1MB数据 c.DBWn进程将修改的缓冲区写入磁盘时（如果相应的重做日志数据尚未写入磁盘）
            • Shared Pool
              • 1、SHARE_POOL_SIZE 　 1）Share Pool可通过SHARE_POOL_SIZE参数指定： 　 SQL> alter system set shared_pool_size=20M scope=both; 　 2）Share Pool保存的信息被多个会话共享，类型包括： a.Library Cache Library Cache又包含共享SQL区与PL/SQL区： a).共享SQL区保存了分析与编译过的SQL语句。 b).PL/SQL区保存了分析与编译过的PL/SQL块（过程和函数、包、触发器与匿名PL/SQL块）。 b.Data Dictionary Cache 保存了数据字典对象的定义。 c.UGA(User Global Area) UGA内包含了共享服务器模式下的会话信息。 共享服务器模式时，如果large pool没有配置，则UGA保存在Share Pool中。
            • Large Pool
              • 1）Large Pool大小通过LARGE_POOL_SIZE参数指定： SQL> alter system set large_pool_size=20m scope=both; 2）作用: a.为I/O服务进程分配内存 b.为备份与恢复操作分配内存 c.为Oracle共享服务器模式与多个数据库间的联机事务分配内存。 通过从large pool中为共享服务器模式分配会话内存，可以减少share pool因频繁为大对象分配和回收内存而产生的碎片。将大的对象从share pool中分离出来，可以提高shared pool的使用效率，使其可以为新的请求提供服务或者根据需要保留现有的数据。
            • Java Pool
              • 1、JAVA_POOL_SIZE 通过JAVA_POOL_SIZE参数指定java pool大小。 保存了jvm中特定会话的java code和数据。 2、在编译数据库中的java代码和使用数据库中的java资源对象时，都会用到share pool。 java的类加载程序对每个加载的类会使用大约8K的空间。 系统跟踪运行过程中，动态加载的java类，也会使用到share pool。
            • Streams Pool
              • 由Oracle Streams使用。
            • Keep Buffer Cache
              • 通过db_keep_cache_size参数指定。 　该buffer内的数据可能被重用，以降低I/O操作。该池的大小要大于指定到该池的段的总和。 　读入到keep buffer的块不需要过期操作。
            • Recycle Buffer Cache
              • 通过db_recycle_cache_size参数指定。 　该池中的数据被重用机会较小，该池大小要小于分配到该池的段的总和。读入该池的块需要经常执行过期处理。
            • nK Block Size Buffer
              • 同一个数据库中，支持多种大小的数据块缓存。通过DB_nK_CACHE_SIZE参数指定，如 　• DB_2K_CACHE_SIZE 　• DB_4K_CACHE_SIZE 　• DB_8K_CACHE_SIZE 　• DB_16K_CACHE_SIZE 　• DB_32K_CACHE_SIZE 标准块缓存区大小由DB_CACHE_SIZE指定。如标准块为nK，则不能通过DB_nK_CACHE_SIZE来指定标准块缓存区的大小，应由DB_CACHE_SIZE指定。 例，标准块为8K，则数据库可以设置的块缓存大小的参数如下： 　• DB_CACHE_SIZE（指定标准块（这里为8K）的缓存区） 　• DB_2K_CACHE_SIZE（指定块大小为2K的缓存区） 　• DB_4K_CACHE_SIZE（指定块大小为4K的缓存区） 　• DB_16K_CACHE_SIZE（指定块大小为16K的缓存区） 　• DB_32K_CACHE_SIZE（指定块大小为32K的缓存区）
          • ASMM（Automatic Shared Memory Management）
            • 1、SGA_TARGET 1）SGA_TARGET默认值为0，即ASMM被禁用。需要手动设置SGA各中各组件的大小。 2）当SGA_TARGET为非0时，则启用ASMM，自动调整以下各组件大小： 　　　　DB buffer cache（default pool） 　　　　shared pool 　　　　large pool 　　　　streams pool 　　　　java pool 但ASSM中， 以下参数仍需要手动指定： 　　　　log buffer 　　　　keep、recycle、以及非标准块缓冲区 　　　　固定SGA以及其他内部分配。 2、启用ASMM需要将STATISTICS_LEVEL设置成TYPICAL或ALL 3、启用ASMM，自动调整SGA内部组件大小后。若手动指定某一组件值，则该值为该组件的最小值。如 手动设置SGA_TARGET=8G,SHARE_POOL_SIZE=1G，则ASMM在自动调整SGA内部组件大小时，保证share pool不会低于1G。 SQL> SELECT component, current_size/1024/1024 size_mb FROM v$sga_dynamic_components; 4、SGA_MAX_SIZE SGA_MAX_SIZE指定内存中可以分配给SGA的最大值。 SGA_TARGET是一个动态参数，其最大值为SGA_MAX_SIZE指定的值。
        • PGA
          • 简介
            • PGA(Program Global Area),由每个服务进程、后台进程专有；每个进程有一个PGA PGA，每个服务进程私有的内存区域，包含如下结构： 1）、Private SQL Area：包含绑定信息、运行时的内存结构。每个发出sql语句的会话都会有一个Private SQL Area 2）、Cursor、SQL Area：这里的游标是一块内存，不是我们常指的“指针” 3）、Session Memory：为保存会话中的变量以及与会话相关的信息而分配的内存区 4）、Work Area：主要用来做Group By、Order By、位图合并等。
          • 结构
            • Private SQL Area
              • 1、保存了当前会话绑定信息以及运行时内存结构这些信息 2、每个执行sql语句的会话，都有一个private sql area。 3、当多个用户执行相同的sql语句，此sql语句保存在一个称为shared sql area。此share sql area被指定给这些用户的private sql area 4、共享服务器模式：private sql area位于SGA的share pool或large pool中 专用服务器模式：private sql area位于PGA中 存储服务进程执行SQL时的私有数据和控制结构，包括固定区域和运行时区域；固定区域的数据在游标关闭前一直存在，运行时区域在SQL执行时一直存在。Oracle在执行SQL时第一步就是创建运行时区域。对于INSERT、UPDATE、DELETE在SQL执行结束后就是放该区域；对于SELECT需要在所有数据结果集被读取完毕或终止查询才能释放。
            • Cursor、SQL Area
            • Session Memory
              • 保存了会话的变量，如登录信息及其他与会话相关的信息，共享服务器模式下，Session memory是共享的。
            • Work Area
              • PGA的一大部分被分配给Work Area，用来执行如下操作： 对比较复杂的SQL，比如表关联等的统计查询。对于其他SQL操作可能需要一些额外区域做相应操作，如下： a.基于操作符的排序，group by、order by、rollup和窗口函数。这需要排序空间 参数为sort_area_size b.hash散列连接，这需要Hash Join Area 参数为hash_area_size c.位图合并， 参数为bitmap_merge_area_size d.位图创建， 参数为create_bitmap_area_size e.批量装载操作使用的写缓存
    • 进程结构 进程结构主要有后台进程和用户连接进程两大类
      • 用户连接进程
        • 概述
          • 用户连接进程是连接User和Instance之间的桥梁，只有在User与Instance之间建立了连接后，User才能对Oracle Server进行操作。 用户连接进程 = 用户进程 + 服务进程 + PGA
        • 详解
          • 用户进程User Process
            • 当一个Database User请求连接到Oracle Server时，Oracle Server会创建User Process。 User Process的作用： 为Database User与Server Process建立连接 并不会直接与Oracle Server交互 connect连接：是User和Server Process之间的通信通道。
          • Server Process服务进程
            • 用于处理Database User和Oracle Server之间的连接。 当一个User与User Process建立了一个connect后，Oracle Server会创建一个Server Process。然后再由User Process与Server Process建立了连接之后，Server Process会通过用户提交的请求信息来确定与oracle instance建立一个会话。 Server Process的作用： 与Oracle Server直接交互 复制执行和返回结果 Session会话：一个用户通过User Process(本质是通过Server Process)与Oracle Instance建立连接后称之为一个会话，一个用户可以建立多个会话，即同时使用同一个用户可以多次的连接到同一个实例，也就是说多个session可以使用同一个connect。
          • PGA-这里省略
      • 后台进程 常用核心进程（ARCn是非核心进程）：
        • 概述
          • 后台进程主要完成数据库管理任务，是Oracle Instance和Oracle Database联系的纽带，分为核心进程和非核心进程 1. 核心进程：核心进程，必须存在，有一个终止，所有数据库进程全部终止，实例崩溃！其中五大进程全都是核心进程。 2. 非核心进程：完成数据库的额外功能，非核心进程死亡数据库不会崩溃！ 在用户访问数据库时，首先会提交请求，再分配SGA内存，创建并启动后台进程和实例，最后建立连接和会话。Oracle Server运行过程中必须启动上面的前五个进程。否则实例无法创建。 查看后台进程： SQL> select name,description from v$bgprocess where paddr<>'00'; NAME DESCRIPTION ----- ---------------------------------------------------------------- PMON process cleanup PSP0 process spawner 0 MMAN Memory Manager DBW0 db writer process0 LGWR Redo etc. CKPT checkpoint SMON System Monitor Process RECO distributed recovery CJQ0 Job Queue Coordinator QMNC AQ Coordinator MMON Manageability Monitor Process NAME DESCRIPTION ----- ---------------------------------------------------------------- MMNL Manageability Monitor Process2
        • 详解
          • Database writer (DBWn)数据库写进程：
            • 只做一件事，将数据写到磁盘。就是将数据库的变化写入到数据文件。 该进程最多20 个，即使你有36 个CPU 也只能最多有20 个数据库写进程。 进程名称DBW0-DBW9 DBWa-DBWj 注意：数据库写进程越多，写数据的效率越高。该进程的个数应该和cpu的个数对应，如果设置的数据库写进程数大于CPU 的个数也不会有太明显的效果，因为CPU 是分时的。
          • Checkpoint (CKPT)检查点进程：
            • 主要用于更新数据文件头，更新控制文件和触发DBWn数据库写进程。 Ckpt 进程会降低数据库性能，但是提高数据库崩溃时，自我恢复的性能。我们可以理解为阶段性的保存数据，一定的条件满足就触发，执行DBWn存盘操作。
          • Process monitor (PMON)进程监测进程：
            • PMON在后台进程执行失败后负责清理数据库缓存和闲置资源，是Oracle的自动维护机制。 清除死进程 重新启动部分进程（如调度进程） 监听的自动注册 回滚事务 释放锁 释放其他资
          • System monitor (SMON)系统监测进程：
            • SMON启动后会自动的用于在实例崩溃时进行数据库实例自动恢复。 清除作废的排序临时段，回收整理碎片，合并空闲空间，释放临时段，维护闪回的时间点。 在老数据库版本中，当我们大量删除表的时候，会观测到SMON进程很忙，直到把所有的碎片空间都整理完毕。
          • Log writer(LGWR)日志写进程：
            • 主要用于记录数据库的改变和记录数据库被改变之前的原始状态，所以应当对其作多重备份，用于恢复和排错。 激活LGWR的情况： 提交指令 日志缓冲区超过1/3 每三秒 每次DBWn执行之前 归档进程(ARCn)
          • 归档进程(ARCn)是非核心进程
    • 存储结构 Oracle RDBMS存储结构主要由Database组成 又能够将Database分为物理结构和逻辑结构来理解
      • Database物理结构： 是Database在操作系统中的文件集合，即：磁盘上的物理文件，主要由数据文件、控制文件、重做日志文件、归档日志文件、参数文件、口令文件组成
        • Data Files
          • • 包括所有的数据库数据 • 只能属于一个数据库 • 来自于被称为”表空间”的数据库存储逻辑单元 • 可以直接被读进内存，在执行SQL语句的时候，会将相关的数据文件副本加载入数据缓冲区。 • 通过备份策略可以使数据文件得到保护 SQL> select FILE_NAME from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/demo/users01.dbf /u01/app/oracle/oradata/demo/undotbs01.dbf /u01/app/oracle/oradata/demo/sysaux01.dbf /u01/app/oracle/oradata/demo/system01.dbf /u01/app/oracle/oradata/demo/ivantable.dbf
        • Redo Log Files
          • 重做日志文件包含对数据库所做的更改操作记录，在Oracle发生故障时能够恢复数据。 能够恢复数据的原理：重做日志文件会按时间的顺序，将应用于数据库的一连串的变更向量(做了什么操作)存储起来(即将变更的地方标记起来)。其中包含了所有已经完成操作的信息和完成操作之前的数据库状态。如果数据文件受损，就可以将这些变更向量应用于数据文件备份来进行重做(重建)工作，将它恢复到发生故障的那一刻前的状态。重做日志文件又分为下面两种类型： 联机重做日志文件：记录连续的数据库操作 归档日志文件Archived Log Files：用于时间点恢复，当RedoLogFiles存满时，会对这些日志进行归档备份，以便以后还原数据时使用。 查看redo log info： SQL> select member from v$logfile; # v$logfile数据字典，记录了redolog文件的列表 MEMBER -------------------------------------------------------------------------------- /u01/oradata/demo/redo03.log /u01/oradata/demo/redo02.log /u01/oradata/demo/redo01.log
        • Control Files
          • 控制文件包含维护和验证数据库完整性的必要的信息。 它记录了联机重做日志文件、数据文件的位置、更新的归档日志文件的位置。它还存储着维护数据库完整性所需的信息，如数据库名。控制文件是以二进制型式存储的，用户无法修改控制文件的内容。控制文件不过数MB，却起着至关重要的作用。 SQL> select * from v$controlfile; STATUS NAME IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS ------- -------------------------------------------------------------------------------- --------------------- ---------- -------------- /u01/app/oracle/oradata/demo/control01.ctl NO 16384 594 /u01/app/oracle/flash_recovery_area/demo/control02.ctl NO 16384 594
        • Parameter File
          • 实例参数文件，当启动oracle实例时，SGA结构会根据此参数文件的设置内存，后台进程会据此启动。 参数文件和网络连接文件 SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0/dbhome_2/dbs/spfiledemo.ora 其他参数文件也同样位于$ORACLE_HOME/dbs目录中; 网络连接文件位于$ORACLE_HOME/network/admin目录中;
        • Password File
          • 用户通过提交username/password来建立会话，Oracle根据存储在数据字典的用户定义对用户名和口令进行验证。
        • Alert log File(Oracle 11g )
          • 11g的alert log 做了变动，从 oracle 11g 开始，Oracle 数据库以XML与传统的文本两种格式提供 Alert 日志。 新的日志位置由 Automatic Diagnostic Repository (ADR) 决定。可以通过新的初始化参数 DIAGNOSTIC_DEST 控制 ADR BASE 的位置。 SQL> show parameter diagnostic_dest NAME TYPE VALUE ------------------------------------ ----------- ----------------------------- diagnostic_dest string D:/APP/ADMINISTRATOR 如果没有设定这个初始化参数的位置，而设置 ORACLE_BASE 环境变量的话，那么，如果没有设定 ORACLE_BASE , 则 ORACLE_HOME/log 即为 DIAGNOSTIC_DEST。 一些都在这个目录下的日志列表： D:/app/Administrator/diag>dir 2010/05/21 21:00 <DIR> asm 2010/05/27 16:55 <DIR> clients 2010/05/21 21:00 <DIR> crs 2010/05/21 21:00 <DIR> diagtool 2010/05/21 21:00 <DIR> lsnrctl 2010/05/21 21:00 <DIR> netcman 2010/05/21 21:00 <DIR> ofm 2010/06/07 13:56 <DIR> rdbms 2010/05/21 21:00 <DIR> tnslsnr 0 个文件 0 字节 11 个目录 21,367,534,592 可用字节 Alert Log 文件父目录的位置在： ADR_BASE/diag/product_type/product_id/instance_id 这个目录下有 alert/trace/bdump 之类的子目录。 alert log XML 文件位置： SQL> select value from v$diag_info where name ='Diag Alert'; VALUE --------------------------------------------------------------- d:/app/administrator/diag/rdbms/mynewccs/mynewccs/alert Alert log 文本文件位置: SQL> select value from v$diag_info where name ='Diag Trace'; VALUE --------------------------------------------------------------- d:/app/administrator/diag/rdbms/mynewccs/mynewccs/trace ------------------------------------------------------------------------------ 二、可以查看Oracle参数：show parameter dump 然后找到 background_dump_dest 对应的值。该目录下就有alert*.log文件。
      • 逻辑结构 表空间就是典型的Oracle逻辑结构类型 —— 里面存放着若干的数据文件
        • 表空间：用于存储数据库对象的逻辑空间，表空间是在数据库中开辟的一个空间，用于存放数据库的对象，它是信息存储的最大逻辑单位，是存放数据库文件的地方，其中数据又被存放在表空间中的数据文件中。一个数据库可以由多个表空间组成，Oracle的调优就是通过表空间来实现的。（Oracle数据库独特的高级应用） 表空间的作用：分类管理、批量处理； 将琐碎的磁盘文件整合、抽象处理成为逻辑结构。这样更加便于我们去管理数据库。
        • 逻辑空间到物理空间的映射 段、区和块：
      • Oracle RDBMS是怎么运作的
        • 执行一条写入的SQL语句时在RDBMS中都发生了什么
          • 1. 将SQL语句加载入数据库缓冲区 2. 将SQL语句要操作的数据文件副本加载入数据库缓冲区 3. 执行SQL语句，修改数据文件副本，形成“脏缓冲区” 4. CKPT检测到“脏缓冲区”，调用DBWn 5. 在DBWn运行之前，先运行了LGWR，将数据文件的原始状态和数据库的改变记录到Redo Log Files 6. 运行DBWn，将“脏缓冲区的内容写入到数据文件” 7. 同时CKPT修改控制文件和数据文件头 8. SMON回收不必要的空闲资源
        • Oracle RDBMS是怎么运作的
          • 1、User访问Oracle Server之前提交一个请求(包含了db_name、instance_name、username、password等信息)，Oracle Server接收到请求并通过Password File的验证后，分配SGA内存池，启动后台进程同时创建并启动实例。 2、在启动实例之后User Process与Server Process建立Connect。 3、再通过Server process和Oracle Instance完成建立Session。 4、用户执行SQL语句，由server process接收到并直接与Oracle交互。 5、SQL语句通过Server Process到达Oracle Instance，再将SQL载入数据库缓冲区。 6、Server Process通知Oracle Database将与SQL语句相关的数据块副本加载到缓冲区中。 7、在数据库缓存区执行SQL语句，并产生脏“缓冲区”。 8、由CKPT检查点进程检查到“脏缓冲区”，并调用DBWn数据库写进程，但在DBWn执行之前，应该由LGWR先将数据文件的原始状态、数据库的改变等信息记录到Redo Log Files。 9、将更新的内容写入到磁盘中的数据文件。 10、返回结果给用户
    • Redo和Undo浅析
      • 对于一个跟新操作是先修改块呢，还是先写前印象呢（undo)，为了保证undo的准确性应该先进行前印象 undo 的缓存区在哪里？undo是一种特殊的数据文件 redo（重做日志）是保护数据文件的redo ；用户正常入库 数据 如： update t set a= 5 where a=6; a=6 就是Undo 的redo a=5是redo的redo undo 是用来存储一种特殊的数据文件，这种数据文件是原来数据文件的前印象，他在数据库里面的表空间是叫undo tablespace
    • 数据块结构
      • 数据块结构
        • 在Oracle中，不论数据块中存储的是表（table）、索引（index）或簇表（clustered data），其内部结构都是类似的。块结构图如下所示： 本图显示了数据块的各个组成部分，包括：数据块头（包括标准内容和可变内容）（common and variable header），表目录区（table directory），行目录区（row directory），可用空间区（free space），行数据区（row data）。图中两个箭头表示一个数据块中的可用空间区的容量是可变的。 数据块头（包括标准内容和可变内容） 数据块头（header）中包含了此数据块的概要信息，例如块地址（block address）及此数据块所属的段（segment）的类型（例如，表或索引）。 表目录区 如果一个数据表在此数据块中储存了数据行，那么数据表的信息将被记录在数据块的表目录区（table directory）中。 行目录区 此区域包含数据块中存储的数据行的信息（每个数据行片断（row piece） 在行数据区（row data area）中的地址）。[一个数据块中可能保存一个完整的数据行，也可能只保存数据行的一部分 ，所以文中使用row piece] 当一个数据块（data block）的行目录区（row directory）空间被使用后，即使数据行被删除（delete），行目录区空间也不会被回收。举例来说，当一个曾经包含50条记录的数据块被清空 后，其块头（header）的行目录区仍然占用100字节（byte）的空间 管理开销 数据块头（data block header），表目录区（table directory），行目录区（rowdirectory）被统称为管理开销（overhead）。其中 有些开销的容量是固定的；而有些开销的总容量是可变的。数据块中固定及可变管理开销的容量平均在84到107字节（byte）之间。 行数据 数据块（data block）中行数据区（row data）包含了表或索引的实际数据。一个数据行可以跨多个数据块。 可用空间区 在插入新数据行，或在更新数据行需要更多空间时（例如，原来某行最后一个字段为空（trailing null），现在要更新为非空值），将 使用可用空间区（free space）中的空间。如果一个数据块（data block）属于表或簇表的数据段（data segment），或属于索引的索引段（index segment），那么在其可用空间区中还可能会存储事务条目（transaction entry）。如果一个数据块中的数据行（row）正在由INSERT，UPDATE，DELETE，及 SELECT...FOR UPDATE 语句访问，此数据块中就需要保存事务条目。事务条目所需的存储空间依据操作系统而定。在常见的操作系统中事务条目大约需要占用23字节（byte）。 可用空间管理 可用空间可以被手动或自动管理 数据库中，每个段（segment）的可用空间可以被自动管理。段内的可用/已用空间以位图（bitmap）形式记录，这与可用块以列表方式的管理不同。段空间自动管理（Automatic segment-space management）具备以下优势： ● 易于使用● 空间利用效率更高，尤其针对每行数据容量差异大的表（或其他对象）● 能够更好地针对当前数据的情况实时调整● Better multi-instance behavior. in terms of performance/space utilization 用户可以在创建一个本地管理的表空间（locally managed tablespace）时选择自动段空间管理（automatic segment-space management）功能。这样在此表空间内创建的段都将默认地设置为自动段空间管理。
      • 数据块可用空间的有效性及优化
        • 有两种SQL语句可以增加数据块中的可用空间：分别是 DELETE 语句，和将现有数据值更新为占用容量更小值的 UPDATE 语句。在以下两种条件下，上述两中操作释放的空间可以被后续的 INSERT 语句使用： ● 如果 INSERT 语句与上述两种操作在同一事务（transaction）中，且位于释放空间的语句之后，那么 INSERT 语句可以使用被释放的空间。 ● 如果 INSERT 语句与释放空间的语句在不同的事务中（比如两者是由不同的用户提交的），那么只有在释放空间的语句提交后，且插入数据必需使用此数据块时，INSERT 语句才会使用被释放的空间。 数据块（data block）中被释放出的空间未必与可用空间区（free space）相连续。Oracle在满足以下条件时才会将释放的空间合并到可用空间区：（1）INSERT 或 UPDATE 语句选中了一个有足够可用空间容纳新数据的数据块，（2）但是此块中的可用空间不连续，数据无法被写入到数据块中连续的空间里。Oracle只在 满足上述条件时才对数据块中的可用空间进行合并，这样做是为了避免过于频繁的空间合并工作影响数据库性能。
      • 行链接（Row Chaining）及行迁移（Row Migrating）
        • 有两种情况会导致表中某行数据过大，一个数据块（data block）无法容纳。第一种情况，当一行数据被插入时一个数据块就无法容纳。在这种情况下Oracle将这行数据存储在段内的一个数据块链 （chain）中。在插入数据量大的行时常会发生行链接（row chaining），例如一个包含数据类型为 LONG 或LONG RAW 列的数据行。此时行链接不可避免。 第二种情况，原本存储在一个数据块（data block）内的数据行，因为更新操作导致长度增长，而所在数据块的可用空间也不能容纳增长后的数据行。在这种情况下，Oracle将此行数据迁移 （migrate）到新的数据块中。Oracle在被迁移数据行原来所在位置保存一个指向新数据块的指针。被迁移数据行的rowid 保持不变。 当数据行发生链接（chain）或迁移（migrate）时，对其访问将会造成 I/O 性能降低，因为Oracle为获取这些数据行的数据时，必须访问更多的数据块（data block）。
      • PCTFREE，PCTUSED，及行链接（Row Chaining）
        • PCTFREE 参数
          • PCTFREE 介绍
            • PCTFREE 参数用来设置一个数据块（data block）中至少需要保留（reserve）多少可用空间（百分比值），为数据块中已有数据更新时可能发生的数据量增长做准备
          • PCTFREE 的作用
            • 例如，当用户用 CREATE TABLE 语句创建表时指定了以下参数： PCTFREE 20 这个参数设定了此表对应的数据段（data segment）中的每个数据块（datablock）至少保留20%的可用空间，以备块中已有数据更新时使用。只要数据块中行数据区与数据块头的容量之 和不超过数据块总容量的80%，用户就可以向其中插入新数据，数据行被放入行数据区（row data area），相关信息被写入数据块头（overhead area） 下图说明了PCTFREE 的作用：
        • PCTUSED 参数
          • PCTUSED 介绍
            • PCTUSED 参数用于决定一个数据块（data block）是否可被用于插入新数据，她的依据是数据区（row data）与数据块头（overhead）的容量之和占数据块全部容量的最大百分比。当一个数据块中的可用空间比例小于PCTFREE 参数的规定时，Oracle就认为此数据块无法被用于插入新数据，直到数据块中的占用容量比例小于 PCTUSED 参数的限定。在占用容量比例大于 PCTUSED 参数的限定之前，Oracle只在更新数据块内已有数据时才会使用此数据块的可用空间。
          • PCTUSED 的作用
            • 例如，当用户用 CREATE TABLE 语句创建表时指定了以下参数： PCTUSED 40 在例子中，当此表的某数据块占用容量比例高于40%时，Oracle不会将此数据块用于插入新数据行（假设此数据块的可用空间曾经低于 PCTFREE 的限定） PCTUSED 的作用
      • PCTFREE 和 PCTUSED 如何协同发挥作用
        • 上图说明了 PCTFREE 和 PCTUSED 如何共同作用以管理数据块（data block）可用空间的使用。 在第一步中，数据块占用空间比例小于80%时才能插入新数据，因为PCTFREE 参数限定必须保留20%的可用空间用于块内已有数据的更新。在第二步中，对数据块中已有数据的更新操作可以使用数据块中的保留空间。只有当数据块内的占用 空间比例低于40%时才能向其中插入新数据。在第三步中，当数据块内的占用空间比例低于40%时，此数据块再次可以被用于插入新数据。在第四步中，数据块 占用空间比例小于80%时才能插入新数据，因为PCTFREE 参数限定必须保留20%的可用空间用于块内已有数据的更新。此过程如此往复循环。
      • 小结
        • 在新分配的数据块中（data block），可用于插入（insert）数据的空间等于数据块总容量减去数据块头（block overhead）再减去预留可用空间（PCTFREE）。而更新（update）数据块内已有数据可使用数据块中的所有可用空间。因此，更新操作能够使 数据块内的可用空间低于的 PCTFREE 限制，因为这些空间是专为更新操作而预留的。 在每个数据段（data segment）与索引段（index segment）中，Oracle管理着一个或多个可用块列表（free list）--其中列出了所有属于此段的数据扩展（extent），且可用空间比例大于 PCTFREE 限定的数据块。这些块可以被插入（insert）操作使用。当用户提交了 INSERT 语句后，Oracle从可用块列表中选择第一个有效的数据块使用。如果此数据块的可用空间不够容纳 INSERT语句提交的数据，且此块的占用容量已经超过PCTUSED 的限定，Oracle就将其从可用块列表中移出。一个段可以同时使用多个可用块列表，以减少对一个表进行并发插入（concurrent insert）时产生的竞争。 当用户提交了 DELETE 或 UPDATE 语句后，Oracle处理语句并检查相关数据块中的占用空间比例是否小于 PCTUSED 的规定。如果满足，那么这个数据块就被放入当前事务（transaction）正在使用的可用块列表（free list）的头部，如果当前事务还需要写入数据，此块将被首先使用。当事务提交后，此数据块中的可用空间还可被其他事务使用
  • Sql*plus
    • set linesize 设置行宽度 set pagesize 设置页面长度 edit 路径 打开路径所在文档 spool 路径 select…… spool off 将查询结果保存在指定路径 clear 清屏 show error 查看错误 set serveroutput on 打开输出 dbms_output.put_line(“”); 输出数据 desc 表名 查看表结构 二、设置页面显示总行数、宽度和反馈信息 1.设置页面显示总行数 show pagesize; //首先查看目前的pagesize,默认是14 set pagesize 100; //将pagesize设置好100,则可以一次显示够多行记录了 2.设置行的宽度 show linesize; //查看目前的linesize,默认是80 set linesize 200; //设置成200或者更宽都可 3、开、闭反馈信息 set feedback on set feedback off 三、安装帮助信息 1.执行$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql脚本，创建help帮助内容的表模式。 SQL> @?\sqlplus\admin\help\hlpbld.sql 2.执行$ORACLE_HOME/sqlplus/admin/help/helpus.sql脚本，则自动填充help内容 SQL> @?\sqlplus\admin\help\helpus.sql 四、查看命令详情： 如help describe help change help set 五、修改左边提示符（注：这里的on 改为 off 是关闭） 1）、set sqlprompt "_USER >" 显示为当前用户名 2）、set sqlprompt '会话1 >' 显示为“会话” 3）、set sqlprompt '&gname>' 输入：DBA 后显示为DBA 4）、set time on 在前面显示时间 5）、set timing on 设置在sql执行后在后面显示用时（不准确，但可做参考） 6）、设置每一列显示的宽度 column ename format a10 缩写1：column ename for a10 缩写2：col ename for a10 7）使用数字确定列显示的位数（列是数字） col sal for 9999 8）、以美元格式显示 col SAL FOR $99,990 9）、关于数字的格式 http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34510 10）、关闭列格式的设置 col sal off 打开列格式的设置 col sal on 参数永久有效在$ORACLE_HOME/sqlplus/admin/glogin.sql修改（本机 $ORACLE_HOME： E:\app\Administrator\product\11.2.0\dbhome_1）
  • 用户及权限管理
    • conn 用户名/密码 连接数据库 disc 断开连接 show user 查看用户 password 用户名 修改密码 create user 用户名 indetified by 密码 创建用户 create role 角色 not indetifid/indetified by 密码 创建角色（不需要验证/验证） 角色是一组权限（系统权限、对象权限）的集合 grant 系统权限 to 用户名/角色[with admin option] 将权限授予用户或角色[可以将权限授予别人，当回收本级权限时不会级联回收] grant 对象权限 to 用户名/角色[with grant option] 将对象权限授予用户或角色[可以将权限授予别人，当回收本级时会级联回收] revoke 权限/角色 from 用户名/角色 回收权限 alter user 用户名 account lock 锁定用户（该用户无法使用，当用户所属的对象可以使用） alter user 用户名 account unlock 解除锁定 drop user 用户名[cascade] 删除用户[将用户所属对象一并删除] drop role 角色 删除角色，拥有该角色的用户将不再有该角色下的权限 conn 用户名/密码 as sysdba 以sys用户权限登陆（前提该操作系统用户属于dba组）
  • 锁
    • 简介
      • 多用户数据库管理系统的一个主要任务是对并发（concurrency）进行控制，即对多个用户同时访问同一数据进行控制。 锁的分类: 10g中锁分类：如右图 11g中锁分类（看右图）: 1)、DML锁（DML又分为TX（行锁）和TM(表锁)） update .xxxx where ename='SCOTT'; 此时行级排它锁，表级共享锁。（操作当前行不 可操作，操作的当前表的其他行可以查看、更改等。表级不可以修改表结构） 2）、DDL锁 ü 排他DDL锁 ü 共享DDL锁 ü 可中断解析锁(Breakable parse locks) 3）、系统锁 -- 可以手动加锁 LOCK TABLE emp IN EXCLUSIVE MODE; LOCK TABLE emp IN ROW EXCLUSIVE MODE;
    • 一些脚本
      • 例子
        • 例子：set sqlprompt 'SESSION A >'; select ... for update实验 查看回话id： select sid from v$mystat where rownum=1; id=67下： select * from emp where ename='SCOTT' for update; 查一个会话下有哪些锁 set linesize 150 column o_name format a10 column lock_type format a20 column object_name format a15 select sid,type,id1,id2, decode(lmode,0,'None', 1,'Null', 2,'Row share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive') lock_type, request,ctime,block from v$lock where sid=67;
      • 查看锁的类型锁的是那个用户哪张表等
        • 查看锁的类型锁的是那个用户哪张表等：（在第三个窗口中查看的） 实验用sql：update emp set sal=1111 where empno=7369; set linesize 200 col username format a20 col lock_type format a15 col object_name format a30 select oracle_username username,session_id sid, decode(locked_mode,0,'None', 1,'Null', 2,'Row share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exlusive') lock_type, object_name,xidusn,xidslot,xidsqn from v$locked_object,dba_objects where v$locked_object.object_id=dba_objects.object_id;
      • 查看谁在阻塞锁类型等
        • col resource format a20 col sid format 9999 col request for a15 select type||'-'||id1||'-'||id2 "resource",sid, decode(lmode,0,'None', 1,'Null', 2,'Row share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exlusive') lock_type, decode(request,0,'None', 1,'Null', 2,'Row share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exlusive') request, ctime, block from v$lock where type in ('TX','TM') order by "resource",ctime desc;
      • 查看那个在拿锁那个在等锁及类型
        • col sid for a20 col request for 9999 SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || sid as sid, id1, id2, lmode, request, type, ctime FROM V$LOCK WHERE (id1, id2) IN (SELECT id1, id2 FROM V$LOCK WHERE request > 0) ORDER BY id1, ctime desc;
  • 事务
    • 概述
      • 事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，SQL Server能将逻辑相关的一组操作绑定在一起，以便服务器保持数据的完整性。 事务通常是以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK结束。 COMMIT表示提交，即提交事务的所有操作。具体地说就是将事务中所有对数据库的更新写回到磁盘上的物理数据库中去，事务正常结束。 ROLLBACK表示回滚，即在事务运行的过程中发生了某种故障，事务不能继续进行，系统将事务中对数据库的所有以完成的操作全部撤消，滚回到事务开始的状态。 事务的特性(ACID特性) A:原子性(Atomicity) 事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做。 B:一致性(Consistency) 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。 C:隔离性(Isolation) 一个事务的执行不能被其他事务干扰。 D:持续性/永久性(Durability) 一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。 数据操纵的一组集合 savepoint 保存点名字 设置保存点 ……dml rollback to 保存点名字 回退到保存点，取消设置保存点之后的操作 commit 提交 rollback 回退 可以设置多个保存点，但保存点只可以回退一次，提交之后不可回退
    • Oracle事务隔离级别
      • 脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改时，产生脏读。
      • 不可重复读（nonrepeatable read）：同一查询在同一事务中多次进行，在此期间，由于其他事务提交了对数据的修改或删除，每次返回不同的结果（查询还没有将结果集完全返回）。
      • 幻读（phantom read）：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，虽然查询条件相同，每次返回的结果集却不同。
  • 约束
    • 简介： 1、基本 not null 非空 只能在行级定义 unique 唯一 不可以重复，但可以为null 并且可以多处为null check 检查约束 default 默认 primary key 主键 非空的唯一标示 可以自动创建索引 foreign key 外键 跟主表的主键或者唯一约束所在列对应 建表的时候先建主表再建从表，删除的时候先删从表再删主表 2、定义 行级定义：在字段名之后直接定义 表级定义:建完字段后再用constraint定义 constraint 约束名 约束类型 字段 3、修改 alter table 表名 add constraint 约束名 约束类型 字段 modify constraint 约束名 约束类型 字段 drop constraint 约束名 详解及案例： 1、约束分类: 非空、唯一、主键、外键、check； CONSTRAINT_TYPE C (check constraint on a table) P (primary key) U (unique key) R (referential integrity) V (with check option, on a view) O (with read only, on a view) 查看当前数据库有多少对象 select count(*) from user_objects; 输出最后5个对象 COL OBJECT_NAME FOR A20 select a.OBJECT_NAME,a.OBJECT_TYPE from (select OBJECT_NAME,OBJECT_TYPE from user_objects order by CREATED desc) a where rownum <5; 1）非空： create table t_null( id number(10) not null, name varchar2(10)); insert into t_null(name) values('qqq'); create table t_null( id number(10), name varchar2(10)); alter table t_null modify id not null; 约束取名 查看某个表的约束名 select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from USER_CONSTRAINTS where TABLE_NAME='T_NULL'; 非空约束就是用check约束实现的。 2）、表级约束、行级约束 a、表级约束 create table t_null( id number(10), name varchar2(10), check(id is not null) ); b、行级约束 3）、唯一约束： create table t_unique( id number(10) constraint t_uk unique, name varchar2(10)); unique 约束是由索引来维护的。（ B*tree ） -- 表级组合约束 create table t_unique( id number(10), name varchar2(10), unique(id,name) ); 只有表级约束才可以建立组合约束 DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO1 REFERENCES T_DEPT 在这里因为T_DEPT表只有一个主键所以可以REFERENCES T_DEPT 只有参照表没有参照列 建好表后建外键约束 ALTER TABLE t_emp ADD CONSTRAINT FK_DEPT FOREIGN KEY(deptno) REFERENCES t_dept(deptno); 先建表在导数据再建约束这样导数据快一点。 级联删除 alter table t_emp drop constraint FK_DEPTNO1; --去除t_emp中的外键 alter table T_EMP add constraint FK_DEPTNO1 foreign key (DEPTNO) references T_DEPT(DEPTNO) on delete cascade; 也可以不删除而是设置成null on delete set null --外键需要的权限 比如b用户建一张表要引用a的tab1表，那就分配一个权限给b grant REFERENCES on a.tab1 to b; 删主键： alter table t_emp drop primary key; 使约束失效 alter table t_emp disable constraint FK_DEPTNO1; 使约束可用 alter table t_emp enable constraint FK_DEPTNO1; 外键必须为另一个表中的唯一约束列或主键列 案例二：去重 drop table t2; create table t2(c1 int ,c2 varchar2(10)); insert into t2 values (1,'Smith'); insert into t2 values (1,'Smith'); insert into t2 values (2,'John'); insert into t2 values(1,'Jack'); insert into t2 values (2,'Jack'); insert into t2 values (2,'John'); insert into t2 values (2,'John'); commit; 法一： distinct create table tmp_t3 nologging as select distinct * from t3; drop table t3; alter table tmp_t3 rename to t3; 法二：rowid delete from t2 where rowid <> ( select min(rowid) from t2 b where b.c1 = t2.c1 and b.c2 = t2.c2 ) 或 delete from t2 where rowid > ( select min(rowid) from t2 b where b.c1 = t2.c1 and b.c2 = t2.c2 ) 法三：用rowid + group by 的方法 delete from T2 where rowid not in (select min(rowid) from t2 group by c1,c2 ); 或 delete from t2 where not exists (select 1 from (select min(rowid) rid from t2 group by c1,c2) b where b.rid=t2.rowid) 或group by 加count 等。 法四： select c1,c2,rowid rd,row_number() over(partition by c1,c2 order by c1) rn from t2； delete from t2 where rowid in (select b.rd from (select rowid rd,row_number() over(partition by c1,c2 order by c1) rn from t2) b where b.rn > 1);
  • 基本语法
    • DDL
      • 数据定义语言（create、alter、drop） 1、create create table 表名（ 字段1 数据类型（长度）， …… 字段n 数据类型（长度） ） create table 表名 as select…… select * into 新表from 旧表 2、alter alter table 表名 add 字段 数据类型（长度） 增加字段 modify column字段 字段类型（长度） 修改字段 drop 字段 删除字段 rename 旧字段名 to 新字段名 rename 旧表名 to 新表名 3、drop drop table 表名 删除表
    • DML
      • 一般操作
        • 数据操纵语言（insert update delete select） 1、insert insert into 表名 values(字段1，……字段n); 按表的字段依次顺序插入 insert into 表名 （字段1，字段2） values(值1，值2) 按字段插入 insert into 表名（字段……） select…… 将查询结果插入 2、update update 表名 set 字段=值 [where 条件] 3、delete delete from 表名 [where 条件] trunc table 表名（不可回退的删除 速度快） 4、select select [distinct] 字段 from 表名 where 条件 group by 分组 having 分组后筛选条件 order by 排序
      • merge into
        • oracle 9i以后,有merge into语句 语法（如上图）： MERGE INTO -->要插入或更新的表 USING -->要匹配的表 ON (条件) -->要匹配的条件 WHEN MATCHED THEN -->匹配的时候 UPDATE -->更新时,不需要指定表 WHEN NOT MATCHED THEN -->不匹配时 INSERT -->插入时不需要指定表 Update Insert 子句可以使用 Where 1、 例子: create table oldemp as select * from emp; create table newemp as select * from emp where 1=0; insert into newemp(empno,ename) values(1234,'John'); insert into newemp(empno,ename,sal) values(7369,'Smith',8800); insert into newemp(empno,ename,sal) values(7839,'John',300); commit; merge into oldemp o using newemp n on (o.empno=n.empno) when matched then update set o.sal=n.sal when not matched then insert (o.empno,o.ename) values(n.empno,n.ename); 2、 9i 以下的要都写 when matched then when not matched then 11g：可以这么写 （注：delete where (o.ename='KING') ） merge into oldemp o using newemp n on (o.empno=n.empno) when matched then update set o.sal=n.sal delete where (o.ename='KING') --删除oldemp中的ename='KING'的 when not matched then insert (o.empno,o.ename) values(n.empno,n.ename); 3、 on (o.empno=n.empno) 中间的条件可以有多个，且要有索引不然慢。 4、 update 多个： merge into oldemp m using newemp n on (m.empno=n.empno) when matched then update set m.sal = n.sal,m.ename = n.ename when not matched then insert (m.empno,m.ename) values(n.empno,n.ename);
    • 修改字符集
      • 1、两种字符集， 一种主字符集， 一种辅助字符集。 查看主副字符集 col value for a30 select * from nls_database_parameters where parameter like '%CHARACTERSET%'; GBK 中一个汉字两个字节 UTF8 中一个汉字3个字节 UTF16 中一个汉子4个字节 1）、使用主字符集 char varchar2 clob … 2）、使用辅助字符集（辅助字符集只可以是unicode字符集） 在平时使用的字符类型前加n nchar nvarchar2 nclob 2、NLS_LANG NLS_LANG = language_territory.charset 1）、Language： 指定服务器消息的语言， 影响提示信息是中文还是英文 如：select * from emp; 已选择XX行。 2）、Territory： 指定服务器的日期和数字格式 3）、Charset： 指定字符集。 4）、常用： SIMPLIFIED CHINESE_CHINA.ZHS16GBK AMERICAN_AMERICA.ZHS16GBK 3、修改字符集 shutdown immediate; --关数据库 startup mount; select * from nls_session_parameters --看字符集 ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; ALTER SYSTEM SET AQ_TM_PROCESSES=0; ALTER DATABASE OPEN; ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE ZHS16GBK; SHUTDOWN IMMEDIATE; STARTUP;
    • escape 指定转义字符、Q-quote
      • escape 指定转义字符 select employee_id,job_id from emp2 where job_id like 'PU\_%' escape '\'; 第一种: set escape on select employee_id,last_name,job_id from emp2 where job_id like 'PU\_%'; 第二种: set escape off show escape select employee_id,job_id from emp2 where job_id like 'PU\_%' escape '\'; http://blog.csdn.net/holly2008/article/details/23214389 在Oracle中，single-quote(')是一个表示字符串的关键字。所以在字符串中用两个''表示一个实际的单引号字符。 其实Oracle提供了一个Q-quote的表达式，用来简化SQL或PLSQL中字符串的表示 插入这样的值：'Smith' 或 Smith's a、SELECT ename|| q'['s]' || ' emp_number is ' || empno emp_info FROM emp; b、SELECT ename|| q'<'s>' || ' emp_number is ' || empno emp_info FROM emp; c、Select ename||q'( It's ... I can't..)' From Emp; q'< 这里面是内容 >' q'< Smith's >' q'[Smith's ]' q'/Smith's /' q'/Smith's /'
  • 复杂查询
    • 子查询
      • 1、单列查询 select * from 表名 where 字段=（select 字段 from 表名 where 条件） select * from 表名 where 字段 in （select 字段 from 表名 where 条件） 2、多列查询 select * from 表名 where（字段1，字段2）=（select 字段1，字段2 from 表名where 条件） 3、all select * from 表名 where 字段 表达式 all (select ……) Select * From dept d Where d.deptno <All (Select e.deptno From emp e); Select * From dept d Where d.deptno >All (Select e.deptno From emp e); Select * From dept d Where d.deptno =All (Select e.deptno From emp e); 4、any select * from 表名 where 字段 表达式 any (select ……) 5、子表 select * from t1,(select ……)t2
    • 连接查询
      • 1、自然连接 select * from t1,t2 where t1.z=t2.z select * from t1 inner join t2 on t1.z=t2.z 2、左连接(左表全部加右表符合条件的) select * from t1 left join t2 on t1.z=t2.z select * from t1,t2 where t1.z=t2.z(+) 3、右连接 select * from t1 right t2 on t1.z=t2.z select * from t1,t2 where t1.z(+)=t2.z 4、全连接 select * from t1 full out join t2 on t1.z=t2.z
    • 分页查询
      • 正确的分页语句结构： Select * From (Select t.*, rownum rn From (Select e.* From emp e Order By e.sal) t Where rownum <= 10) t Where t.rn > 5; 查看执行计划可以看到有stopkey 1 Plan hash value: 2603316617 2 3 --------------------------------------------------------------------------------- 4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 5 --------------------------------------------------------------------------------- 6 | 0 | SELECT STATEMENT | | 10 | 1000 | 4 (25)| 00:00:01 | 7 |* 1 | VIEW | | 10 | 1000 | 4 (25)| 00:00:01 | 8 |* 2 | COUNT STOPKEY | | | | | | 9 | 3 | VIEW | | 14 | 1218 | 4 (25)| 00:00:01 | 10 |* 4 | SORT ORDER BY STOPKEY| | 14 | 532 | 4 (25)| 00:00:01 | 11 | 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | 12 --------------------------------------------------------------------------------- 13 14 Predicate Information (identified by operation id): 15 --------------------------------------------------- 16 17 1 - filter("T"."RN">5) 18 2 - filter(ROWNUM<=10) 19 4 - filter(ROWNUM<=10)
    • 合并查询
      • union 去重复联合查询 union all不去重复 intersect 取交集 minus 取左表有右表没有的值
    • 闪回查询
      • 基于时间的查询（AS OF TIMESTAMP）
        • Flashback Query这一特性，最常被应用的就是修复误操作的数据了。注意，这并不是说Flashback Query能够恢复数据。Flashback Query本身不会恢复任何操作或修改，也不能告诉你做过什么操作或修改，实际上Flashback Query特性实际应用时，是基于标准SELECT的扩展，借助该特性能够让用户查询到指定时间点的表中的记录，相当于拥有了看到过去的能力，至于恢复，SELECT的结果都出来了，难道还不懂如何执行INSERT TABLE SELECT或CREATE TABLE AS SELECT吗？ 1.1基于时间的查询（AS OF TIMESTAMP） 以前面创建的FLASH_TBL表为例，先来删除几条记录并提交（右图）： 这个时候FLASH_TB1表中ID<10的记录均已被删除，假设过了一会儿用户发现删除操作执行有误，仍需找回那些被误删的记录该怎么办呢？通过备份恢复吗，如果是在8i或之前版本，恐怕是需要这样，自9i之后，使用Flashback Query的特性，我们可以很轻松地恢复记录（注意并不是任何情况下都可以恢复哟，后面会讲到制约Flashback Query的一些因素，这里假设的都是在理想条件下）。 现在就演示应用Flashback Query，首先是找到它，假设当前距离删除数据有5分钟左右的时间，执行SELECT查询语句，并附加AS OF子句，例如 （右图）： 提 示 SYSDATE-5/1440是啥意思，1440又是怎么来的？ 首先60（分）×24=1440，这样就计算出一天拥有多少分钟，SYSDATE是系统函数，用来取得当前的系统时间（以天为单位），SYSDATE-5/1440，得出的就是距当前时间5分钟前的记录了。后面示例中需要计算之前的某个时段时，均是使用这一方法。 你看，我们通过增加AS OF TIMESTAMP的语法，查询到的数据就是5分钟之前的，基于这一结果，可以轻易并且快速地将记录恢复： JSSPRE> INSERT INTO FLASH_TBL SELECT * FROM FLASH_TBL AS OF TIMESTAMP SYSDATE-5/1440 3 WHERE ID<10; 9 rows created. JSSPRE> COMMIT; Commit complete. 成功插入9条记录，查询表中当前的记录
      • 基于SCN的查询（As of scn）
        • 我们建议使用as of scn 的方式执行flashback query，比如需要对多个相互有主外键约束的表进行恢复时，如果使用as of timestamp 的方式，可能会由于时间点不统一的缘故造成数据选择或插入失败，通过scn 方式则能够确保记录的约束一致性。 查看SCN: SELECT dbms_flashback.get_system_change_number FROM dual; SELECT CURRENT_SCN FROM V$DATABASE; SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------- 3765554 删除数据： SQL> delete from test1; 1 row deleted. SQL> commit; 查看删除之前的状态： SQL> select * from test1 as of scn 3765554; ID NAME ---------- ---------- 3764577 1 用Flashback Query恢复之前的数据： SQL> insert into test1 select * from test1 as of scn 3765554; 1 row created. SQL> commit; Commit complete. SQL> select * from test1; ID NAME ---------- ---------- 3764577 1 事实上，Oracle 在内部都是使用scn，即使你指定的是as of timestamp，oracle 也会将其转换成scn，系统时间标记与scn 之间存在一张表，即SYS 下的SMON_SCN_TIME 每隔5 分钟，系统产生一次系统时间标记与scn 的匹配并存入sys.smon_scn_time 表，该表中记录了最近1440个系统时间标记与scn 的匹配记录，由于该表只维护了最近的1440 条记录，因此如果使用as of timestamp 的方式则只能flashback 最近5 天内的数据（假设系统是在持续不断运行并无中断或关机重启之类操作的话）。 注意理解系统时间标记与scn 的每5 分钟匹配一次这句话，举个例子，比如scn:3764577,3765348 分别匹配12-01-12 13:52:00 和12-01-12 13:57:00，则当你通过as of timestamp 查询12-01-12 13:52:00或12-01-12 13:57:00 这段时间点内的时间时，oracle 都会将其匹配为scn:3764577到undo 表空间中查找，也就说在这个时间内，不管你指定的时间点是什么，查询返回的都将是12-01-12 13:52:00 这个时刻的数据。 查看SCN 和timestamp 之间的对应关系： select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss')from sys.smon_scn_time;
    • with as
      • 一般用法
        • with deptno_sales as( select deptno from dept where dname='SALES' ) select ename,sal from emp where deptno=(select * from deptno_sales) 可以看作临时表 求5-10行的数据 with emp_rn as (select rownum rn,emp.* from emp) select * from emp_rn e where rn<=10 and e.rn>=5; -- 查询出部门的总薪水大于所有部门平均总薪水的部门 with dept_costs as( select a.dname dname,sum(b.sal) dept_total from dept a,emp b where a.deptno=b.deptno group by a.dname ), avg_costs as( select sum(dept_total)/count(*) dept_avg from dept_costs ) select dname,dept_total from dept_costs where dept_total > ( select dept_avg from avg_costs) order by dname; 优点： 1. SQL可读性增强。 2. with子查询只执行一次，将结果存储在用户临时表空间中（如果内存够大的话会放到内存中），可以引用多次，增强性能 注意： ctas（create table as select ....）中是不可以使用with as的 如: create table t as (with emp_rn as (select rownum rn,emp.* from emp where rownum<=10) select * from emp_rn e where e.rn>=5);
      • 递归
        • WITH子查询也称为CTE (Common Table Expression)，是ANSI SQL-99标准的一部分。ORACLE从9i开始引入WITH子查询，把它被称作SUBQUERY FACTORING(分解子查询)。 WITH子查询的作用类似于内联视图(INLINE VIEW)。内联视图的定义写作SQL的FROM 后面，只能够引用一次；而WITH子查询需要在引用之前先定义，一旦定义了在整个查询的后续部分就可以按名称来反复引用，从这点来看又很像临时表。 从版本11GR2开始，ORACLE支持递归的WITH, 即允许在WITH子查询的定义中对自身引用。这不是什么新鲜事，其他数据库如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持这一特性。但对于ORACLE用户来说，这一递归特性还是很令人期待的，利用它可以轻易实现以往做不到的、或者很难做到的许多新功能。这一章我们就来探索这一令人兴奋的新特性，并把它和以往的实现手段（主要是CONNECT BY层次查询）作比较。 我们先来看看这个递归WITH子查询的语法： WITH ① query_name ([c_alias [, c_alias]...]) ② AS (subquery) ③ [search_clause] ④ [cycle_clause] ⑤ [,query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]... ①这是子查询的名称，和以往不同的是，必须在括号中把这个子查询的所有列名写出来。 ②AS后面的subquery就是查询语句，递归部分就写在这里。 ③遍历顺序子句，可以指定深度优先或广度优先遍历顺序。 ④循环子句，用于中止遍历中出现的死循环。 ⑤如果还有其他递归子查询，定义同上。 subquery部分由两个成员组成：anchor member(锚点成员) 和 recursive member(递归成员)。它们之间必须用union all联合起来，anchor member 必须写在recursive member前面。 anchor member用来定位递归的入口，锚点成员是一个SELECT语句，它不可以包含自身名称(query_name)。这相当于CONNECT BY查询中的START WITH，典型写法就是: SELECT ... FROM 要遍历的表 WHERE ... (起始条件) 递归成员也是一个SELECT语句，用于定义上下级的关系，它必须包含自身名称(即query_name)，而且仅仅只能引用一次。递归正是体现在对于自身的引用。典型的做法就是把query_name和其他表(一般来说就是你要遍历的表)做一个连接，连接条件表明了上下级的关系。必须注意，在这个query_name中，并不是截止目前为止的所有数据都是可见的，可见的只是上次递归新加入的最近的一层数据。对query_name列的引用相当于CONNECT BY中的PRIOR操作符。当找不到满足条件的下级，遍历就会停止；如果你还有其他的递归出口条件，也可以一起写在WHERE中，当WHERE不满足时，遍历就会停止，这就是在遍历树、图时候的剪枝操作。越早停止则效率越高。 这个递归成员就是程序员发挥创造力的地方，以往在CONNECT BY中做不到的事情，比如沿路径求和、求积等运算，现在都轻而易举。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'来实现。 搜索子句(search_clause)和循环子句(cycle_clause)我们后面的例子中会见到。 一个例子，从scott/tiger的emp表来查找上下级关系： WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必须把结构写出来 SELECT empno, ename, job, mgr, deptno ---- 先写锚点查询，用START WITH的条件 ,1 AS the_level ---- 递归起点，第一层 ,'\'||ename ---- 路径的第一截 ,ename AS top_manager ---- 原来的CONNECT_BY_ROOT FROM EMP WHERE mgr IS NULL ---- 原来的START WITH条件 UNION ALL ---- 下面是递归部分 SELECT e.empno, e.ename, e.job, e.mgr, e.deptno ---- 要加入的新一层数据，来自要遍历的emp表 ,1 + t.the_level ---- 递归层次，在原来的基础上加1。这相当于CONNECT BY查询中的LEVEL伪列 ,t.path||'\'||e.ename ---- 把新的一截路径拼上去 ,t.top_manager ---- 直接继承原来的数据，因为每个路径的根节点只有一个 FROM t, emp e ---- 典型写法，把子查询本身和要遍历的表作一个连接 WHERE t.empno = e.mgr ---- 原来的CONNECT BY条件 ) ---- WITH定义结束 SELECT * FROM T --正序 WITH rs AS (SELECT COLNAME name, row_number () OVER () RN FROM T_CS_WJDR_DRDYMX WHERE WJLXDM ='0044' ORDERBY SXH DESC), RPL (RN, name) AS (SELECT ROOT.RN, CAST (ROOT.name ASVARCHAR (2000)) FROM rs ROOT UNIONALL SELECT CHILD.RN, CHILD.name ||','|| PARENT.name FROM RPL PARENT, rs CHILD WHERE PARENT.RN -1= CHILD.RN) SELECTMAX (name) name FROM RPL GROUPBY RN ORDERBY RN FETCH FIRST 1 ROWS ONLY; --反序 WITH rs AS (SELECT COLNAME name, row_number () OVER () RN FROM T_CS_WJDR_DRDYMX WHERE WJLXDM ='0044' ORDERBY SXH DESC), RPL (RN, name) AS (SELECT ROOT.RN, CAST (ROOT.name ASVARCHAR (2000)) FROM rs ROOT UNIONALL SELECT CHILD.RN, CHILD.name ||','|| PARENT.name FROM RPL PARENT, rs CHILD WHERE PARENT.RN +1= CHILD.RN) SELECTMAX (name) name FROM RPL GROUPBY RN ORDERBY RN desc FETCH FIRST 1 ROWS ONLY; 例子-自由 create table emp_tag(id number primary key,empno NUMBER(4) ,tag varchar2(200)); insert into emp_tag (id, empno, tag) values (1, 7876, '港股通'); insert into emp_tag (id, empno, tag) values (2, 7876, '大消费'); insert into emp_tag (id, empno, tag) values (3, 7876, '大健康'); insert into emp_tag (id, empno, tag) values (4, 7902, '一带一路'); insert into emp_tag (id, empno, tag) values (5, 7902, '食品饮料'); insert into emp_tag (id, empno, tag) values (6, 7902, '农林木业'); insert into emp_tag (id, empno, tag) values (7, 7369, '工业4.0'); insert into emp_tag (id, empno, tag) values (8, 7369, '信息设备'); insert into emp_tag (id, empno, tag) values (9, 7369, '煤炭石油'); insert into emp_tag (id, empno, tag) values (10, 7499, '国企改革'); insert into emp_tag (id, empno, tag) values (11, 7499, '医药'); insert into emp_tag (id, empno, tag) values (12, 7499, '中小盘'); commit; WITH rs AS (SELECT EMPNO, TAG name, row_number() OVER(ORDER BY ID) RN FROM emp_tag), RPL(EMPNO, RN, name)AS (SELECT EMPNO, ROOT.RN,CAST(ROOT.name AS VARCHAR(2000)) FROM rs ROOT UNION ALL SELECT CHILD.EMPNO, CHILD.RN, CHILD.name ||','|| PARENT.name FROM RPL PARENT, rs CHILD WHERE PARENT.RN -1= CHILD.RN AND PARENT.EMPNO = CHILD.EMPNO) SELECT EMPNO,NAME FROM(SELECT EMPNO, NAME, ROW_NUMBER() OVER(PARTITION BY EMPNO ORDER BY LENGTH(NAME) DESC) RN_LENG FROM RPL) T WHERE T.RN_LENG ='1'; 结果：
    • 层次查询
      • 概述
        • 层次结构（等级查询层次查询 ）10g以后才有 Hierarchical Query Pseudocolumns 语法格式 select [level], column, expr... from table [where condition] start with condition connect by [prior column1= column2 | column1 = prior column2]; 使用 start with 确定 第一级，再使用 prior 查找上一级配合 connect by 确定 上一级 和当前级 关系。 组织结构图 select * from emp; SELECT empno,ename,mgr FROM emp CONNECT BY PRIOR empno=mgr; SELECT level,empno,ename,mgr FROM emp CONNECT BY PRIOR empno=mgr; select level,emp.* from emp start with ename='KING' connect by prior empno=mgr order by level; select level,emp.* from emp start with ename='KING' connect by prior mgr=empno
      • 图解
        • 7839>7566>7788>7876 7566>7902>7369 7839>7698>7499 7698>7521 7698>7654 7698>7844 7698>7900 7839>7782>7934 注：上图中第一个框（这里第一个框中的箭头没有画出，与下面黄色箭头是同一个方向）的箭头的指向是由prior 确定的，prior 在那个列的前面就指向哪个列。
      • 配合一些函数使用及例子
        • 简介
          • start with mgr is null 树 根的条件（这里是mgr 是null的为根） connect by prior empno=mgr 等同 connect by mgr = prior empno prior 优先取empno=mgr 的然后mgr 为空的 connect by empno=mgr 和connect by mgr =empno 默认是connect by prior mgr = empno
        • sys_connect_by_path(ename,'/') 使用某个字符分隔输出的列
          • col pename for a50 select ename,sys_connect_by_path(ename,'/') pename from emp start with mgr is null connect by prior empno=mgr ;
        • lpad(' ', 2 * level-1) 左填充这里是对空格填充空格,填充的个数按照2 *（level-1） 来 lpad(a,b[,c]) 还有 rpad a:对a进行填充 b:填充位数必须是可转整数的值或是整数 如果是 '' 则无论a 是什么都不填充 c:使用什么进行填充 如果该参数省略则会用 空格填充
          • col "Path" for a50 select ename,lpad(' ', 2 * level-1)||sys_connect_by_path(ename,'/') "Path" from emp start with mgr is null connect by prior empno=mgr;
        • CONNECT_BY_ISLEAF 判断是否是叶子节点
          • select ename, CONNECT_BY_ISLEAF isleaf, lpad(' ', 2 * level-1)||sys_connect_by_path(ename,'/') "Path" from emp start with mgr is null connect by prior empno=mgr;
        • 从A站点开始，能够到达哪些站点
          • create table bus( dstart varchar2(4), dend varchar2(4) ); insert into bus values('A','B'); insert into bus values('B','C'); insert into bus values('A','D'); insert into bus values('D','M'); insert into bus values('C','E'); insert into bus values('F','M'); commit; 从A站点开始，能够到达哪些站点? Path -------- A-B A-B-C A-B-C-E A-D A-D-M select sys_connect_by_path(dstart,'-') path from bus start with dstart='A' connect by prior dend=dstart; select substr(sys_connect_by_path(dstart,'-')||'-'||dend,2) path from bus start with dstart='A' connect by prior dend=dstart; select 'A'||SYS_CONNECT_BY_PATH(dend, '-') "Path" from bus start with dstart='A' connect by PRIOR dend=dstart; select ltrim(b.bus1, '-') from (select dstart, dend, sys_connect_by_path(dstart, '-') || '-' || dend bus1 from bus start with dstart = 'A' connect by prior dend = dstart) b;
  • rowid
    • 行地址、物理地址 数据库 表空间 段 区 块 一个表空间，可以对应多个数据文件 18位64进制 OOOOOO: 数据库对象号,比如：表，索引，视图，序列，同义词这些都是对象 （在dba_objects 表中） FFF: 表空间相关的数据文件号 BBBBBB: 数据块号 RRR: 在块中的行号（块中的行，是对应表中的一行记录） ROWID是64进制的，分布关系如下: A-Z <==> 0 - 25 (26个) a-z <==> 26 - 51 (26个) 0-9 <==> 52 - 61 (10个) +/ <==> 62 - 63 (2个) 查看块号： Select dbms_rowid.rowid_block_number(rowid) From emp t ;
  • 序列
    • 序列(SEQUENCE)其实是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。 创建序列需要CREATE SEQUENCE系统权限 一般用来创建自增的主键列 CREATE SEQUENCE seq名 [ INCREMENT BY n ] //每次增加多少,默认1 [ START WITH n ] //从几开始,默认1 [ { MAXVALUE n | NOMAXVALUE }] //最大值 ,NOMAXVALUE,没有上限(10 的27 次方 ) [ { MINVALUE n | NOMINVALUE }] //最小值,NOMINVALUE ,没有下限(10的-26次方) [ { CYCLE | NOCYCLE } ] //表示达到上限后,是否回头再循环重新开始 [ { CACHE n | NOCACHE } ] //为增加效率,将值放在缓存中,默认每次放20个. [ { ORDER | NOORDER } ] //是否顺序产生值 当选择使用cache时，如果在cache中存放了1~20；如果使用了1~10的时候断电了。 drop table a; create SEQUENCE a; set long 500 SELECT DBMS_METADATA.GET_DDL('SEQUENCE','A') FROM DUAL; CREATE SEQUENCE empid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 999 NOCACHE NOCYCLE; sequence的两个伪列:NEXTVAL , CURRVAL a)、NEXTVAL表示获取下一个值 b)、CURRVAL表示获取当前值,在使用currval之前,必须使用nextval,否则当前值是没有的. SELECT empid_seq.NEXTVAL FROM dual; SELECT empid_seq.CURRVAL FROM dual; drop table t1; drop table t2; create table t1(id int); create table t2(id int); insert into t1 values(empid_seq.NEXTVAL); insert into t2 values(empid_seq.NEXTVAL); insert into emp2(empno,ename) values(empid_seq.NEXTVAL,'mmm'); START WITH 不能修改 ALTER SEQUENCE empid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE;
  • 同义词
    • conn scott/tiger grant select on emp to hr; conn hr/hr select * from emp; --查不到结果 select * from scott.emp; --可以查到结果 create synonym emp for scott.emp; --在hr用户下 select * from emp; --create 同义词后可以查 删除同义词 DROP SYNONYM emp;
  • 视图
    • 一般视图
      • create view 视图名 as select……
    • 物化视图
      • 我们如果遇到需要从其它系统的数据库中取数据进行统计分析的问题，可疑选择使用ORACLE的ODI工具进行抽数，但是对方提供的数据库用户下没有任何对象，只是有查询所有表的权限，因此无法做数据反向。 于是决定使用物化视图，把对方数据库中的数据拿过来，虽然数据量比较大，但是每月只拿一次，而且如果设置成增量更新，也不会太慢。现在记录下物化视图的创建过程（以一张表为例）。 一、准备条件以及备注 假设双方数据库都是ORACLE10g，需要同步过来的表名叫：GG_ZLX_ZHU，对方数据库用户名：username，密码：password，SID：CPEES。 二、开始干活 1、首先要创建DB_LINK CREATE DATABASE LINK to_cpees CONNECT TO "username" identified by "password" using "CPEES" 其中CPEES为新建的到对方数据库的TNS。执行，现在我们就已经创建了到对方数据库的DB_LINK TO_CPEES。 2、创建Oracle物化视图快速刷新日志 因为上面说过，以后视图的刷新将采用增量刷新的方式，因此，为配合增量刷新，ORACLE要求要在住表上建立物化视图日志。 CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHU WITH PRIMARY KEY INCLUDING NEW VALUES; (上面的SQL要在远程数据库上执行，不能在本地执行) 3、创建Oracle物化视图 Oracle物化视图，从名字上面来开，它应该是属于视图，但是确实物化。其物化是针对普通视图并没有真正的物理存储而言，其实可以简单的把物化视图看做一个物理表（不再做具体解释）。 CREATE MATERIALIZED VIEW GG_ZLX_ZHU --创建物化视图 BUILD IMMEDIATE --在视图编写好后创建 REFRESH FAST WITH PRIMARY KEY --根据主表主键增量刷新（FAST，增量） ON DEMAND -- 在用户需要时，由用户刷新 ENABLE QUERY REWRITE --可读写 AS SELECT * FROM GG_ZLX_ZHU@TO_CPEES; --查询语句 4、视图刷新 根据业务需要，每月不定时刷新，所以不能是JOB，而且数量多，所以也不能一个一个刷新。根据以上条件，选择使用ORACLE自带工具DBMS_MVIEW工具包中REFRESH方法对物化视图进行刷新。该方法有两个参数，第一个参数是需要刷新的物化视图名称，第二个参数是刷新方式。我们可以写存储过来，对每个物化视图调用一次REFRESH方法，也可以使用“，”把物化视图连接以来，一次刷新。如下： CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS BEGIN DBMS_MVIEW.REFRESH('GG_ZLX_ZHU','f'); END P_MVIEW_REFRESH; 或者使用 CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS BEGIN DBMS_MVIEW.REFRESH('GG_ZLX_ZHU,GG_ZLX_FU','ff'); END P_MVIEW_REFRESH; 注意: 1、如果需要同时刷新多个物化视图，必须用逗号把各个物化视图名称连接起来，并对 每个视图都要指明刷新方式（f、增量刷新，c、完全刷新，?、强制刷新）。 2、当日志和物化视图创建好后，删除日志，则需要重新创建物化视图，否则无法增量 刷新。 3、因为上面写的物化视图时根据主键进行更新，因此，主表必须有主键。 4、以上文章中红色是为可替换的，大家可以根据自己项目需求来修改。 希望上面的内容对大家能有帮助。 忘了写删除方法了，日志和物化视图要分开删除 1. DROP MATERIALIZED VIEW LOG ON GG_ZLX_ZHU@TOCPEES; 2. DROP MATERIALIZED VIEW GG_ZLX_ZHU;
  • escape 指定转义字符、Q-quote
    • 1、使用 ESCAPE 关键字定义转义符。在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。 2、如ESCAPE 'escape_character'，允许在字符串中搜索通配符而不是将其作为通配符使用。escape_character 是放在通配符前表示此特殊用途的字符。 3、一般用于含特殊字符的条件查找 escape 指定转义字符 select employee_id,job_id from emp2 where job_id like 'PU\_%' escape '\'; 第一种：开启转义 set escape on select employee_id,last_name,job_id from emp2 where job_id like 'PU\_%'; 第二种：未开启转义，在SQL中指定转义字符 set escape off show escape select employee_id,job_id from emp2 where job_id like 'PU\_%' escape '\'; 第三种：使用 Q-quote 固定输出字符 摘录连接：http://blog.csdn.net/holly2008/article/details/23214389 在Oracle中，single-quote(')是一个表示字符串的关键字。所以在字符串中用两个''表示一个实际的单引号字符。 其实Oracle提供了一个Q-quote的表达式，用来简化SQL或PLSQL中字符串的表示 插入这样的值：'Smith' 或 Smith's a、SELECT ename|| q'['s]' || ' emp_number is ' || empno emp_info FROM emp; b、SELECT ename|| q'<'s>' || ' emp_number is ' || empno emp_info FROM emp; c、Select ename||q'( It's ... I can't..)' From Emp; q'< 这里面是内容 >' q'< Smith's >' q'[Smith's ]' q'/Smith's /' q'/Smith's /'
  • 函数 单行函数：查多行只出一行（多用于分组） 多行函数：查几行出几行
    • 1、聚合函数
      • sum() avg() max() min() count() avg会忽略null值
    • 2、转换函数
      • convert(c1，set1，set2) 将 c1 由set1字符集 转换为 set2字符集 Select convert(ename, 'us7ascii', 'zhs16cgb231280') convertions From emp; Select convert('你好', 'us7ascii', 'zhs16cgb231280') convertions From dual; cast(字段 as 数据类型) to_char() 小数转换成字符 Select To_Char(0.56) otc, To_Char(0.56, '9999990.9999') nfm, To_Char(0.56, 'fm9999990.9999') fm From dual; --结果 OTC .56 NFM 0.5600 ---左边补空右边补0 FM 0.56
    • 3、数值函数
      • abs()绝对值 floor（）小于或等于的最大整数 ceil() 大于等于的最小整数 round() 四舍五入 trunc()截取小数 mod(值1，值2)取余
    • 4、字符串函数
      • substr(字段，起始位置，长度) lower() 将英文换为小写 upper()将英文换为大写 length()查看长度 replace(字段，原值，替换为值)
      • translate转换函数
        • Translate时个体替换而Replace是整体替换 如： 整体替换：将CO作为一个整体替换为** select ename, replace(ename,'CO','**') from emp 个体替换：将C和O作为个体分别替换为* select ename,translate(ename,'CO','**') from emp 1、第二个参数不能为空 2、第三个参数不能为空 3、第二个参数中的字符对应第三个参数中的字符（按照位置对应），如果没有，则为空，然后用对应关系替换第一个参数 4、如果第二个参数字符出现多次，以第一次为准 1）、SELECT TRANSLATE('abcd','abcdef','123456') FROM dual; 2）、SQL> SELECT TRANSLATE('abcd','bafde','1') FROM dual; TR -- 1c ------------看右图--------- 3）、SQL> select translate('abcbbaadef','ba','#@') from dual; TRANSLATE( ---------- @#c##@@def 4）、去除字符串中的所有的大写字母只保留数字 SQL> SELECT TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') from dual; TRAN ---- 2229 2、emp中ename与job中有一个字母相同的sal加500 1）、SQL> SELECT T.ENAME,T.SAL,T.SAL+500 FROM (SELECT SAL,ENAME,JOB,TRANSLATE(ENAME,JOB,CHR(27)) TR FROM EMP) T WHERE T.ENAME <> T.TR; ENAME SAL T.SAL+500 ---------- ---------- ---------- WARD 1250 1750 JONES 2975 3475 MARTIN 1250 1750 BLAKE 2850 3350 CLARK 2450 2950 SCOTT 3000 3500 KING 5000 5500 TURNER 1500 2000 JAMES 950 1450 MILLER 1300 1800 已选择10行。 2）、update emp a set comm=NVL(comm,0)+500 where a.ename<>( select translate(a.ename,b.dname,CHR(27)) from dept b where b.deptno=a.deptno );
    • 5、日期处理函数
      • 获取日期
        • 修改当前会话时间格式： alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select current_date from dual; sysdate ：是服务器的时间； current_date：是会话客服端的时间 （注意：如果服务器在美国，客户端在中国则两个时间不一样）
      • 修改时区
        • 会话级修改：alter session set time_zone='+08:00';
      • systimestamp-精确到秒后6位(最大9位),还包括时区
        • systimestamp -精确到秒后6位(最大9位),还包括时区 select systimestamp from dual;
      • add_mouths-加多少月
        • 加6个月 Select add_months('2017-01-03',6) From dual; 注意：在CMD中，这里需要的第一个参数应该是日期格式，如果是字符串，需要是符合当前会话日期格式的字符串（如下）。 SQL> select add_months('04-1月 -17',5) From dual; ADD_MONTHS('04 -------------- 04-6月 -17 加10分钟 select sysdate, sysdate + 1/24/60*10 from dual;
      • months_between-相隔多少月
        • months_between 是以月为单位的，如果想看相差天数，试用差值乘以31即可。 30/31=0.96774193548387096774193548387097 33/31=1.0645161290322580645161290322581 29/31=0.93548387096774193548387096774194 32/31=1.032258064516129032258064516129 在ORACLE中计算月份的差值，都是月底或是日期相同，都会按整月计算。 如果计算结果不是整数，ORACLE会把31作为分母来计算。
      • next_day-下个周几（参数） 是几号
        • next_day（） 2013-3-1 之后第一个星期五是几号： select next_day('2013-3-1',6) from dual; 中文环境下：select next_day(sysdate,'星期五') from dual; 英文环境下：select next_day(sysdate,'Friday') from dual;
      • last_day-本月最后一天
        • select last_day(add_months(sysdate,-1)) from dual;
      • Extract 函数
        • //oracle中extract()函数从oracle 9i中引入,用于从一个date或者interval类型中截取到特定的部分 //语法如下： EXTRACT ( { YEAR| MONTH| DAY| HOUR| MINUTE| SECOND} | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | TIMEZONE_ABBR } FROM{ date_value | interval_value } ) //我们只可以从一个date类型中截取 year,month,day(date日期的格式为yyyy-mm-dd); //我们只可以从一个 timestamp with timezone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE; select extract(year from date'2011-05-17') year from dual; YEAR ---------- 2011 select extract(month from date'2011-05-17') month from dual; MONTH ---------- 5 select extract(day from date'2011-05-17') day from dual; DAY ---------- 17 //获取两个日期之间的具体时间间隔，extract函数是最好的选择 select extract(day from dt2-dt1) day ,extract(hour from dt2-dt1) hour ,extract(minute from dt2-dt1) minute ,extract(second from dt2-dt1) second from( selectto_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1 ,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2 fromdual) / DAY HOUR MINUTE SECOND ---------- ---------- ---------- ---------- 102 4 1 46 ----------以下这种不可，换成year 也是不行的。------------ Select Extract(month From systimestamp - to_timestamp('20170414 15:02:36', 'yyyyMMdd HH24:mi:ss')) From dual; ---------------------- -- selectextract(year from systimestamp) year ,extract(month from systimestamp) month ,extract(day from systimestamp) day ,extract(minute from systimestamp) minute ,extract(second from systimestamp) second ,extract(timezone_hour from systimestamp) th ,extract(timezone_minute from systimestamp) tm ,extract(timezone_region from systimestamp) tr ,extract(timezone_abbr fromsy stimestamp) ta from dual / YEAR MONTH DAY MINUTE SECOND TH TM TR TA ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ---------- 2011 5 17 7 14.843 8 0 UNKNOWN UNK //
    • 6、其他常用一般函数
      • case 字段 when 值 then … when 值 then… else … end case when 字段=？ then … else … end decode( 字段，条件1，值1，条件2.值2……) Coalesce返回第一个不为空的值 SELECT coalesce(NULL, NULL, NULL, 1, NULL, NULL, 2) AS v FROM dual; dump(字段) 查看字段类型 nvl(字段，值) 若字段为null则值为值
    • 7、分析函数
      • dense_rank() 并列的不计数
        • select * from ( select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) rk from emp) where rk<=3;
      • rank() 并列的计数
        • select * from ( select deptno,ename,sal,rank() over(partition by deptno order by sal desc) rk from emp ) where rk<=3;
      • row_number() 没有并列的概念
        • select * from( select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) rn from emp) where rn<=3;
      • Lead 和lag
        • select value,lead(value,1) over(order by id) as delta from test; --（结果如右图） select value, lead(value,1) over(order by id) delta1,lag(value,1) over(order by id) as delta from test;
      • sum（）over（）
        • select deptno, ename, sal, sum(sal) over(partition by deptno order by empno) 部门连续求和, sum(sal) over(partition by deptno) 部门总和, 100 * round(sal / sum(sal) over(partition by deptno), 4) as 占部门比例 from emp; 注意：--sum(sal) over(partition by deptno order by sal) 这样会怎么样
      • ratio_to_report
        • Ratio_to_report() 括号中就是分子，over() 括号中就是分母，分母缺省就是整个占比。 Ratio_to_report 一般结合partition by 使用。 select sal,ratio_to_report(sal) over() from emp;
      • 边界问题
        • 语法
          • http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174 1、over中使用窗口子句（依赖于order by 子句） 看上图 rows 表示以行来开窗,什么类型都行,可以多列 range 表示以偏移量来开窗,与当前行的偏移量, 只能用number和date类型，而且只能有一列 2、在over中使用order by 时 会有一个默认的窗口子句 range between unbounded preceding and current row unbounded preceding ：下限 是没有边界 current row ：上限 是当前行 3、如下 order by sal 实际上是简写： select deptno,ename,sal,sum(sal) over( order by sal) from emp; 的完整写法： select deptno,ename,sal,sum(sal) over( order by sal range between unbounded preceding and current row ) from emp;
        • rows
          • 前面没有边界，后面到当前行
            • SELECT deptno,ename,sal, sum(sal) OVER(PARTITION BY deptno ORDER BY sal rows between unbounded preceding and current row ) ssal FROM emp WHERE deptno in (10, 20);
          • 前后都没有边界
            • SELECT deptno,ename,sal, sum(sal) OVER(PARTITION BY deptno ORDER BY sal rows between unbounded preceding and unbounded following) ssal FROM emp WHERE deptno in (10, 20);
          • 前面一行到当前行
            • SELECT deptno,ename,sal, sum(sal) OVER(PARTITION BY deptno ORDER BY sal rows between 1 preceding and current row ) ssal FROM emp WHERE deptno in (10, 20);
        • range
          • 当前行加减Range指定范围后所符合的行的数据都进行累加
            • 同一个 deptno下在当前行加减500范围内的sal想加 select deptno,ename,sal, sum(sal) over(partition by deptno order by sal range between 500 preceding and 500 following) deptsal from emp;
          • 没有边界
            • SELECT deptno,ename,sal, sum(sal) OVER(PARTITION BY deptno ORDER BY sal range between unbounded preceding and unbounded following) ssal FROM emp;
          • 当前往前后各两天
            • range between interval '2' day preceding and interval '2' day following select ename,sal,hiredate,sum(sal) over( order by HIREDATE range between interval '2' day preceding and interval '2' day following ) hd from emp;
    • 8、增强型分组函数
      • ROLLUP是单向组合特性来实现合计 rollup(a,b) = (a,b)(a)()
        • 可以转成如下： group by (a,b,c) union all group by (a,b) union all group by (a) union all group by() 以下两个sql的结果相同： select deptno,sum(sal) from emp group by rollup(deptno,sal); select deptno,sum(sal) from emp group by deptno,sal union all select deptno,sum(sal) from emp group by deptno union all select null,sum(sal) from emp order by deptno; Select t.deptno, t.job, Sum(t.sal) sum_sal From emp t Group By Rollup(t.deptno, t.job);
      • CUBE是实现多向组合特性来实现合计 cube(a,b) = (a,b)(a)(b)()
        • 以 下两个sql结果相同： select deptno,job,sum(sal) from emp group by cube(deptno,job); select deptno,job,sum(sal) from emp group by deptno,job union all select deptno,null,sum(sal) from emp group by deptno union all select null,job,sum(sal) from emp group by job union all select null,null,sum(sal) from emp order by 1,2; set linesize 150 col job for a30 select deptno,nvl(job,decode(grouping(job),1,'所有部门'||deptno||'的工资总额和总人数')) job,sum(sal),count(empno) from emp group by cube ( deptno, job);
      • grouping函数
        • select deptno,job,grouping(deptno),grouping(job),sum(sal) from emp group by rollup(deptno,job); 汇总的是 1 不是汇总的是 0 如果显示“1”表示GROUPING函数对应的列（例如JOB字段）是由于ROLLUP函数所产生的空值对应的信息，即对此列进行汇总计算后的结果。 如果显示“0”表示此行对应的这列未参与ROLLUP函数分组汇总活动。 同样也适用于CUBE
      • grouping sets -- 指定统计列
        • 1、(deptno, job),(deptno)
          • select deptno, job, sum(sal) from emp group by grouping sets((deptno, job),(deptno));
        • 2、(deptno, job)
          • select deptno, job, sum(sal) from emp group by grouping sets((deptno, job));
        • 3、deptno, job
          • select deptno, job, sum(sal) from emp group by grouping sets(deptno, job);
        • 4、(deptno, job),()
          • select deptno, job, sum(sal) from emp group by grouping sets((deptno, job),());
      • 小结
        • rollup(a,b) 统计列包含：(a,b)、(a)、() rollup(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a)、() cube(a,b) 统计列包含：(a,b)、(a)、(b)、() cube(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()
    • 9、行列互转函数，pivot（行转列） 和 unpivot（列转行）
      • unpivot列转行
        • --查看员工薪资构成种类及金额 Select * From (Select t.ename, Nvl(t.comm, 0) comm, t.sal From emp t) Unpivot(sal_val For sal_type In(comm, sal));
      • pivot行转列
        • With Tmp As (Select '小明' sname, '数学' subject, 98 score From dual Union All Select '小明' sname, '英语' subject, 100 score From dual Union All Select '小明' sname, '语文' subject, 96 score From dual Union All Select '小红' sname, '数学' subject, 97 score From dual Union All Select '小红' sname, '英语' subject, 99 score From dual Union All Select '小红' sname, '语文' subject, 100 score From dual Union All Select '大山' sname, '数学' subject, 98 score From dual Union All Select '大山' sname, '英语' subject, 100 score From dual Union All Select '大山' sname, '语文' subject, 95 score From dual) Select * From Tmp Pivot (Sum(score) For subject In('语文', '数学', '英语')) t;
    • 10、横向求最大值和最小值，greatest、least
      • select greatest(30,59,20), least(30,100,10) from dual;
    • 11、UTL_RAW包下的一些有用函数
      • CAST_TO_VARCHAR2-查看lob字段的内容
        • Select UTL_RAW.CAST_TO_VARCHAR2(col) From ...
      • CAST_TO_RAW-解决不同字符集的数据库之间的相互访问
        • 由于字符集不同所以数据库访问常常出现乱码,可以使用UTL_RAW.CAST_TO_RAW函数来解决这个问题 如下: [sql]view plaincopy select PATIENTID, VISITID, CREATEDATETIME, RECIPTNO, WARDID, DEPARMTMENT, UTL_RAW.CAST_TO_RAW(WARDNAME) as WARDNAME, UTL_RAW.CAST_TO_RAW(DEPARMTMENTNAME) as DEPARMTMENTNAME, UTL_RAW.CAST_TO_RAW(SETTLEDTYPE) SETTLEDTYPE, UTL_RAW.CAST_TO_RAW( PREPAYMENT) PREPAYMENT, UTL_RAW.CAST_TO_RAW(SUMMARY) SUMMARY, UTL_RAW.CAST_TO_RAW(OPERATORNAME) OPERATORNAME from LY_PrepaymentDetailInfo where PATIENTID = 'PID123434' and VISITID = 1 ORDER BY CREATEDATETIME 上面SQL中的后面几列会出现中文字符,所以加上UTL_RAW.CAST_TO_RAW函数可以正确显示中文字符 官方文档:http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_raw.htm
    • 12、一些函数在日期处理时的特殊用法
      • 1、to_char
        • 今天是星期几？ select to_char(sysdate,'day') from dual; 今天是今年的第几天 select to_char(sysdate,'ddd') from dual;
      • 2、round-取离当前时间最近的年初或月初
        • Select sysdate,round(to_date(sysdate,'yyyy-mm-dd'),'month') from dual; --小于半年取当年初，大于半年则取下年初（小于等于6月30 为上半年） select sysdate,round(sysdate,'year') from dual; select sysdate,round(sysdate,'yyyy') from dual; --小于半月取当月初，大于半月则取下月初（小于等于15 为上半月） select sysdate,round(sysdate,'month') from dual; select sysdate,round(sysdate,'mm') from dual;
      • 3、trunc-返回的都是日期格式的第一天
        • 返回的都是日期格式 第一天 --返回今年第一天 select trunc(sysdate,'yyyy') from dual; --本月第一天 Select trunc(sysdate,'month') from dual; -- 本周第一天 Select trunc(sysdate,'day') from dual; -- 本周第一天 trunc(sysdate,'d')
      • 4、例子
        • 今天是今年的第几天? SELECT ROUND(SYSDATE-trunc(sysdate,’yyyy’)) FROM DUAL; SELECT to_char(SYSDATE,'ddd') FROM dual; select ceil(sysdate-trunc(sysdate,'year')) from dual; select trunc(sysdate-trunc(sysdate,'yyyy')) + 1 from dual; 今年有多少天? SELECT ROUND(add_months(trunc(sysdate,’yyyy’),12)-trunc(sysdate,’yyyy’)) FROM DUAL; select add_months(trunc(sysdate,'yyyy'),12)-trunc(sysdate,'yyyy') from dual; --SELECT to_char(add_months(trunc(sysdate,’yyyy’),12),'ddd') FROM dual; select to_char(last_day(add_months(trunc(sysdate,'yyyy'),11)), 'DDD ') from dual; select to_char(add_months(trunc(sysdate,'yyyy'),12)-1,'DDD') from dual; select sum(to_char(last_day(add_months(sysdate,-to_char(sysdate,'mm')+rownum)),'dd')) from emp where rownum<=12; 使用和rownum很相似的一个伪劣level select sum(to_char(last_day(add_months(sysdate,-to_char(sysdate,'mm')+level)),'dd')) from dual connect by level <= 12;
      • 5、周相关
        • select to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyyiw') as week, --oracle求当年的第几周 to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyyww') as week2, --oracle求当年的第几周 to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyy') as year, --oracle求第几年 to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyymm') as month, --oracle求当年的第几月 to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyyddd') as day, --oracle求当年的第几天 to_char(TO_DATE('20110401', 'YYYYMMDD'), 'yyyyq') as quarter -- oracle求当年的第几季度 from dual ww Same day of the week as the first day of the year 按年度1月1日的第一天为每周第一天 iw Same day of the week as the first day of the ISO week, which is Monday 每周一 w Same day of the week as the first day of the month 按月份1日的第一天作为每周第一天 这3个参数均为取每周第一天，区别如下： ww，按每年的1月1日为第一周第一天，1月8日为第二周第一天……依此类推。 2010-1-1为2010年第一周第一天，取一周的第一天，仍然为2010-1-1 SQL> select trunc(date'2010-1-1','w') from dual; TRUNC(DATE'2010-1-1','W') ------------------------- 2010-1-1 2010-1-6为2010年第一周第六天，取一周的第一天，为2010-1-1 SQL> select trunc(date'2010-1-6','w') from dual; TRUNC(DATE'2010-1-6','W') ------------------------- 2010-1-1 2010-1-9为2010年第二周第二天，取这一周的第一天，为2010-1-8 SQL> select trunc(date'2010-1-9','w') from dual; TRUNC(DATE'2010-1-9','W') ------------------------- 2010-1-8 w，与ww功能类似，不过它是按每月的1日为每周第一天，例如1日为当月第一周第一天，8日为第二周第一天……依此类推。 那也就是说，1月份的参数w与ww用trunc函数的结果应该是一致的。 2010-3-1为当月第一周第一天，因此结果为2010-3-1 SQL> select trunc(date'2010-3-1','w') from dual; TRUNC(DATE'2010-3-1','W') ------------------------- 2010-3-1 2010-3-8为当月第二周第一天，第二周第一天为2010-3-8 SQL> select trunc(date'2010-3-8','w') from dual; TRUNC(DATE'2010-3-8','W') ------------------------- 2010-3-8 2010-3-9为当月第二周第二天，第二周第一天为2010-3-8 SQL> select trunc(date'2010-3-9','w') from dual; TRUNC(DATE'2010-3-9','W') ------------------------- 2010-3-8 iw，不受月份与年份的影响，直接取日期参数的周一。 SQL> select trunc(date'2010-3-9','iw') from dual; TRUNC(DATE'2010-3-9','IW') -------------------------- 2010-3-8 --------------取对应时间所在周，以周一所在周为第一周---------------- 法一： select case when to_char(trunc(to_date('20170903','yyyymmdd'),'mm'),'IW') + to_char(to_date('20170903','yyyymmdd'),'W') = to_char(to_date('20170903','yyyymmdd'),'IW') then to_number(to_char(to_date('20170903','yyyymmdd'),'W'))+1 else to_number(to_char(to_date('20170903','yyyymmdd'),'W')) end from dual ; 法二： Select To_Char(Sysdate,'iw') - To_Char(Trunc(Sysdate,'MM'),'iw') +1 From dual;
    • 13、随机数dbms_random
      • 随机数： 0-1之间的： select dbms_random.value() from emp; 1-100之间 select dbms_random.value(1,100) from emp;
    • 14、Listagg
      • 一般用法
        • 用法就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来。 非常方便。 SELECT listagg(ename, ',') within GROUP(ORDER BY ename) AS totoal_ename FROM emp
      • 结合Group By使用
        • SELECT deptno, SUM(sal) AS total_sal, listagg(ename, ',') within GROUP(ORDER BY ename) AS totoal_ename FROM emp GROUP BY deptno;
      • 结合over的使用
        • 同样是聚合函数，还有一个高级用法： 就是over(partition by XXX) 也就是说，在你不实用Group by语句时候，也可以使用LISTAGG函数： SELECT empno, ename, deptno, sal, SUM(sal) over(PARTITION BY deptno) AS total_sal, listagg(ename, ',') within GROUP(ORDER BY ename) over(PARTITION BY deptno) AS total_ename FROM emp WHERE deptno = 10; 总结：LISTAGG()把它当作SUM()函数来使用就可以了。
    • 15、WMSYS.WM_CONCAT
      • 如图，将图1的结果变成图2的结果，使用函数 WMSYS.WM_CONCAT 即可。 查询语句需要配合使用group by select aa, wmsys.wm_concat(t1.name) from (select t.name, to_char(t.createdate, 'yyyy-mm-dd') aa from td_user t where t.td_conference_id = 3218 and t.status = 1 and t.createdate > to_date('2012-10-28', 'yyyy-mm-dd') order by createdate desc) t1 group by t1.aa
  • 正则
    • 正则处理时间范围
      • http://fan-r.iteye.com/blog/416903 1、月份为 1, 3, 5, 7, 8, 10, 12，天数范围为 01 - 31，匹配MM-DD的正则表达式为： (0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]) 2、月份为 4, 6, 9, 11，天数范围为 01-30，匹 配MM-DD的正则表达式为： (0[469]|11)-(0[1-9]|[12][0-9]|30)
    • 正则元数据
      • 正则表达式中常用到的元数据(metacharacter)如下： 1. ^ 表示匹配的字符必须在最前边。 2. $ 与^类似,匹配最末的字符。 3. * 匹配该字符前面的一个字符0次，1次或者多次出现。例如52*oracle 可以匹配 5oracle,52oracle,522oracle,5222oracle等等。 4. + 匹配该字符前面的一个字符1次或者多次出现。例如52+oracle 可以匹配 52oracle,522oracle,5222oracle等等 5.？匹配该字符前面的一个字符0次或1次或者多次出现。例如52oracle 只能匹配5oracle,52oracle等等 6. {n} 匹配一个字符串n次，n为正整数。例如：hel{2}o 所匹配的是hello，也就是出现n次 7. {n,m} 匹配一个字符串至少n次，至多m次。其中n和m都是整数。 8. . 匹配除了null之外的任何单个字符串 9. (pattern) 这个是用来匹配指定模式的一个子表达式 10.x|y 匹配x或者y，其中x和y是一个或者多个字符 11.[abc] 匹配括号中的任意一个字符。例如：[ab]bc可以匹配abc和bbc 12.[a-z] 匹配指定范围内的任意字符串。例如[A-G]hi可以匹配Ahi至Ghi 13.[::]指定一个字符类，可以匹配该类中的任意字符 这里的字符类包括： [[:alphanum:]] 可以匹配字符0-9、A-Z、a-z [:alpha:]可以匹配字符A-Z、a-z [:blank:]可以匹配空格或者tab键 [:digit:]可以匹配数字 0-9 [:gragh:]可以匹配非空字符 [:punct:]可以匹配. , ” ‘等标点符号。 [:upper:]可以匹配字符A-Z [:lower:]可以匹配字符a-z 这里列出的是一些常见的正则表达式中的元数据。更多的正则表达式的内容请参照oracle官网上的正则表达式的相关内容
    • oracle特意为正则表达式设计的几个函数
      • REGEXP_COUNT-regexp_count(x,pattern) 统计x中符合pattern规则的数量
        • 字符串'10,CLARK,MANAGER'，用逗号分割成了三个子串，返回的结果是有几个逗号 SELECT regexp_count('10,CLARK,MANAGER', ',') FROM dual;
      • REGEXP_LIKE-regexp_like(x,pattern) 当x能正确匹配字符串时返回true
        • regexp_like函数的基本构成是： regexp_like(x,pattern[,match_option]) 其基本功能是在x中查找pattern，如果能找到返回true否则返回false,这里的可选参数match_option可以有如下几种形式： 1. ‘c’ 表明进行匹配时区分大小写(这也是默认选项)。 2. ‘i’ 表明在匹配时不区分大小写。 3. ‘n’ 表明允许使用匹配任何字符串的元数据，即’.'。 4. ‘m’将x作为一个包含多行的字符串。 以下是两个关于regexp_like的例子： 这个例子是寻找员工中的80后的员工。 select * from emp where regexp_like(to_char(hiredate,'yyyy'),'^198[0-9]$'); 这个例子是寻找名字是以’M'或者’m'打头的那些员工的名字 select * from emp where regexp_like(ename,'^m','i');
      • REGEXP_INSTR-regexp_instr(x,pattern) 在x中尝试匹配pattern，并返回匹配的位置
        • regexp_instr的原型如下： regexp_instr(x,pattern[,start[,occurrence[,return_option[,match_option]]]]) 这里每个参数分别含义如下： 1. x 待匹配的字符串 2. pattern 待匹配的模式 3. start 开始匹配的位置，如果不指定默认为1 4. occurrence 匹配的次数，如果不指定，默认为1 5. return_option 指定返回值的类型，如果该参数为0，则返回值为匹配位置的第一个字符，如果该值为非0则返回匹配值的最后一个位置加1。 6. match_option 可以用这个参数来修改一些默认的配置设置。这个值与前面所说的regexp_like函数中的match_option参数的意义是一样的。 下面来举几个有关regexp_instr函数的例子： select regexp_instr('i love www.52oracle.com','o[[:alpha:]]{4}e',1,1) as r from dual; 这个例子从’i love www.52oracle.com’这个字符串中匹配一个模式，这个模式是以o开头，以e结尾，在o和e之间有4个字符的字符串。这样自然而然就返回oracle的起始位置14 另一个例子是： select regexp_instr('i love www.52oracle.com','o[[:alpha:]]{2}',1,1,1) as r from dual;这个例子的含义是在’i love www.52oracle.com’这个字符串中匹配另一个模式，这个模式是以o开始，再包含两个字符的一个模式，因此这个模式可以是ove，也可是ora但是我们要求它匹配两次，因此这个SQL语句的结果是ora所在的位置，所在的什么位置呢？因为我们的return_option参数的值是1，即大于0，所以这的结果是16,也就单词oracle中a所在的位置。
      • REGEXP_REPLACE-regexp_replace(x,pattern,replacestring) 在x中尝试匹配pattern,并将其替换成replacestring
        • regexp_replace的意义是找到于给定模式匹配的字符串并用其他的字符串来替代。 其原型是： regexp_replace(x,pattern[,replace_string[,start[,occurence[match_option]]]]) 每个参数的意思分别是： x 待匹配的函数 pattern 正则表达式元字符构成的匹配模式 replace_string 替换字符串 start 开始位置 occurence 匹配次数 match_option 匹配参数，这里的匹配参数和regexp_like是完全一样的，可参考前面的一篇文章。 举例来讲： select regexp_replace('hello everybody,may I have your attention please ','b[[:alpha:]]{3}','one') from dual; 将会返回结果： hello everyone,may I have your attention please
      • REGEXP_SUBSTR-regexp_substr(x,pattern） 返回x中匹配pattern的一个字符串
        • regexp_substr函数的意义找出与给定模式匹配的字符串并返回， 其原型是：regexp_substr(x,pattern[,start[,occurence[match_option]]]) 这里各参数的意义与前面的函数regexp_replace的含义是一样的。 regexp_substr的例子如下： select regexp_substr('I love oracle very much','o[[:alpha:]]{4}e') from dual; 这里将会匹配出结果： oracle 这也是这个函数的返回结果。 例子： With Tmp As (Select '[4567],[re34],[32D5],[JIA],[abc],[00000978864],[非晶铁芯;200,K172-80-75;按图;按图],[L35F5000000],[],[],[],[],[0],[],[],[0],[],[],[0],[0],[0],[-2341.89],[],[],[0],[0],[-2341.89],[0],[0],[0]' str From dual) Select Regexp_Substr(t.str, '\[[^[]*\]', 1, Level) From Tmp t Connect By Level <= Length(t.str) - Length(Replace(t.str, '[')); --结果： 1 [4567] 2 [re34] 3 [32D5] 4 [JIA] 5 [abc] 6 [00000978864] 7 [非晶铁芯;200,K172-80-75;按图;按图] 8 [L35F5000000] 9 [] 10 [] 11 [] 12 [] 13 [0] 14 [] 15 [] 16 [0] 17 [] 18 [] 19 [0] 20 [0] 21 [0] 22 [-2341.89] 23 [] 24 [] 25 [0] 26 [0] 27 [-2341.89] 28 [0] 29 [0] 30 [0]
  • Index
    • 索引创建及说明
      • 1.1 索引的创建语法： CREATE UNIUQE | BITMAP INDEX <schema>.<index_name> ON <schema>.<table_name> (<column_name> | <expression> ASC | DESC, <column_name> | <expression> ASC | DESC,...) TABLESPACE <tablespace_name> STORAGE <storage_settings> LOGGING | NOLOGGING COMPUTE STATISTICS NOCOMPRESS | COMPRESS<nn> NOSORT | REVERSE PARTITION | GLOBAL PARTITION<partition_setting> 相关说明 1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。 2）<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引” 3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高) 4）STORAGE：可进一步设置表空间的存储参数 5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率) 6）COMPUTE STATISTICS：创建新索引时收集统计信息 7）NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值) 8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值 9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区 索引不足： 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
    • 索引分类： Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进程在几分钟得以完成，这样会使您立刻成为一位英雄。下面就将简单的讨论每个索引选项。
      • B树索引(默认类型)
        • 概述
          • 语法：见索引创建及说明 B树索引在Oracle中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引。B树索引最多可以包括32列。 在下图的例子中，B树索引位于雇员表的last_name列上。这个索引的二元高度为3；接下来，Oracle会穿过两个树枝块(branch block)，到达包含有ROWID的树叶块。在每个树枝块中，树枝行包含链中下一个块的ID号。 树叶块包含了索引值、ROWID，以及指向前一个和后一个树叶块的指针。Oracle可以从两个方向遍历这个二叉树。B树索引保存了在索引列上有值的每个数据行的ROWID值。Oracle不会对索引列上包含NULL值的行进行索引。如果索引是多个列的组合索引，而其中列上包含NULL值，这一行就会处于包含NULL值的索引列中，且将被处理为空(视为NULL)。 技巧：索引列的值都存储在索引中。因此，可以建立一个组合(复合)索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了I/O量。
        • 特点
          • 1、适合与大量的增、删、改（OLTP） 2、不能用包含OR操作符的查询； 3、适合高基数的列（唯一值多） 4、典型的树状结构； 5、每个结点都是数据块； 6、大多都是物理上一层、两层或三层不定，逻辑上三层； 7、叶子块数据是排序的，从左向右递增； 8、在分支块和根块中放的是索引的范围；
        • 树的生长
          • 当分裂导致B树索引的层数(Btree Level)增加时，我们称之为树的“生长”。当叶子节点分裂时，在其父节点上需要增加一条记录指向新节点，如果此时父节点上没有足够空间，则父节点也会发生分裂，如果如此递归下去，直到根节点也分裂，那么索引的高度就增加了。<如右图> 2、新写入了一行数据，原来在叶子位置在L4上，但是L4放不下，导致叶子节点分裂，从而出现L5 例如: L4的索引健是1000~10100， 这时写入了一个1049,但是之前的L4已经满了，发生分裂 3、L5原本也应该是挂在B3上，但是B3只能挂2个叶子地址，也满了，那么在B3所在的层级发生分裂，就需要新增一个分支B9（L5先挂载到B9上） 4、同理，B9也无法挂载大B5上，新增B8（而B9先挂载到B8下） 5、B8应该挂载到ROOT下的，可惜ROOT也是满载，只好升级，新加一层， 原来的ROOT变成B6， B8是第三层的，需要新增一个B7与B6平级，然后B6,B7挂载到新的ROOT下。 索引 最终都是叶子节点的index entry写入，如果一个索引中包含的列越多，那么单个叶子上可以存放的数据就越少（索引叶子块的大小固定 < 就是块的大小 > ）。
        • 索引扫描方式
          • 一、INDEX RANGE SCAN
            • cbo 认为 返回的数据>1条会走 range select /*+ index(table_name) */ * from test where object_id>1000; select /*+ index(table_name,index_name) */ * from test where object_id>1000; select /*+ index(t) */ * from test t where object_id>1000; select /*+ index(t,idx) */ * from test t where object_id>1000; 什么样的index range scan 很垃圾 index range scan 查询出数据超多,并且回表. index range scan 要耗费多少io(没有回表的情况): 索引高度-1 + 要扫描的 叶子块数量 index 的叶子中至少要存相当于表中的 4个列 ：key+rowid + 两个相互指向的指针
          • 二、INDEX FULL SCAN
            • 扫描index 的所有叶子块 除了 分页都得死 index full scan 扫描 index高度-2 个 分支块，不会扫描所有的分支块 注意：除了 index fast full scan 其他任何的 索引扫描 方式都只可能扫描一个分支块 index full scan 又回表 不如直接走全表 /*+ full(t) */
          • 三、INDEX SKIP SCAN
            • 索引跳跃扫描。单块读 只可能发生在组合索引上，引导列(组合索引第一列)没有包 含在where条件中，并且引导列基数很低。INDEX SKIP SCAN 一般来说只会返回少量数据，如果返回大量数据，说明该执 行计划可能有问题，也就是说索引建立不对。 等待事件：db file sequential read HINT: INDEX_SS(表名/别名 索引名) 不是组合index 就不用跳了 create index index_name on test(owner,object_id,object_name); select * from test where object_id<100; 一般像这种 就 新建一个 和当前index一样 但是没有当前index引导列的索引 作业：v$sql_plan 抓出走了 index full scan 并且还回表的 sql 抓出 走了 index skip scan 的 sql
            • 相关HINT
          • 四、INDEX FAST FULL SCAN
            • 索引快速全扫描。多块读 。当SQL要查询的数据能够完全从索引中获得，那么 Oracle就不会走全表扫描了，就会走索引快速全 扫描。索引快速全扫描类似全表扫描，它可以多块 读，并且可以并行扫描。 等待事件：db file scattered read HINT：INDEX_FFS(表名/别名 索引名) index fast full scan 是因为 oracle 是行存储数据库的 缺陷 而提出的。 如下： select owner,object_id from test where object_id>50000; owner,object_id ----index fast full scan --这样是index 建反了 所以 iffs 是错的。因为没有必要进行索引全扫描。 object_id,owner ----index range scan 作业：把 错误的 index fast full scan 抓出来
          • 五、INDEX RANGE SCAN DESCENDING
            • 索引降序范围扫描。单块读 索引扫描默认是升序扫描，当有order by desc 这种需求的时候，可能会进行索引降 序范围扫描，这种情况多出现在于分页语 句上。后面的培训案例有分页语句的优化。 等待事件：db file sequential read HINT：INDEX_DESC(表名/别名 索引名)
          • 六、INDEX SKIP SCAN
            • 索引最小/最大值扫描。单块读 通常发生在 select max(xxx) 或者 select min(xxx)并且xxx列上有索引。 等待事件：db file sequential read 我们几乎观察不到，因为只需读 取索引高度相同的index block数。 HINT 无需指定，有索引会自动走INDEX FULL SCAN(MIN/MAX) 等价 index unique scan
      • 位图索引
        • 概述
          • 语法：见索引创建及说明 位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列，但通常它们都只用于少量的列。 例如，您的表可能包含一个称为Sex的列，它有两个可能值：男和女。这个基数只为2，如果用户频繁地根据Sex列的值查询该表，这就是位图索引的基列。当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。如果有多个可用的位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。
        • 内部机制
          • 位图（bitmap）索引是另外一种索引类型，它的组织形式与B树索引相同，也是一棵平衡树。与B树索引的区别在于叶子节点里存放索引条目的方式不同。从前面我们知道，B树索引的叶子节点里，对于表里的每个数据行，如果被索引列的值不为空的，则会为该记录行在叶子节点里维护一个对应的索引条目。 而位图索引则不是这样，其叶子节点里存放的索引条目如上图所示。 假设某个表T里所有的记录在列C1上只具有三个值：01、02和03。在表T的C1列上创建位图索引以后，则叶子节点的内容如上图所示。可以看到，位图索引只有三个索引条目，也就是每个C1列的值对应一个索引条目。位图索引条目上还包含表里第一条记录所对应的ROWID以及最后一条记录所对应的ROWID。索引条目的最后一部分则是由多个bit位所组成的bitmap，每个bit位就对应一条记录。 位图索引的结构 当发出where c1='01'这样的SQL语句时，oracle会去搜索01所在的索引条目，然后扫描该索引条目中的bitmap里所有的bit位。第一个bit位为1，则说明第一条记录上的C1值为01，于是返回第一条记录所在的ROWID（根据该索引条目里记录的start ROWID加上行号得到该记录所在的ROWID）。第二个bit位为0，则说明第二条记录上的C1值不为01，依此类推。另外，如果索引列为空，也会在位图索引里记录，也就是将对应的bit位设置为0即可。 如果索引列上不同值的个数比较少的时候，比如对于性别列（男或女）等，则使用位图索引会比较好，因为它对空间的占用非常少（因为都是用bit位来表示表里的数据行），从而在扫描索引的时候，扫描的索引块的个数也比较少。可以试想一下，如果在列的不同值非常多的列上，比如主键列上，创建位图索引，则产生的索引条目就等于表里记录的条数，同时每个索引条目里的bitmap里，只有一个1，其它都是0。这样还不如B树索引的效率高。 如果被索引的列经常被更新的话，则不适合使用位图索引。因为当更新位图所在的列时，由于要在不同的索引条目之间修改bit位，比如将第一条记录从01变为02，则必须将01所在的索引条目的第一个bit位改为0，再将02所在的索引条目的第一个bit位改为1。因此，在更新索引条目的过程中，会锁定位图索引里多个索引条目。也就是同时只能有一个用户能够更新表T，从而降低了并发性。
        • 特点
          • 1、适合与决策支持系统；做UPDATE代价非常高； 2、非常适合OR操作符的查询； 3、基数比较少的时候才能建位图索引；
        • 示例和技巧
          • 技巧：对于有较低基数的列需要使用位图索引。性别列就是这样一个例子，它有两个可能值：男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快，这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引，所以非常小，因此您可以经常检索表中超过半数的行，并且仍使用位图索引。 当大多数条目不会向位图添加新的值时，位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。 示例 如上图所示来看一个示例表PARTICIPANT，该表包含了来自个人的调查数据。列Age_Code、Income_Level、Education_Level和Marital_Status都包括了各自的位图索引。右图显示了每个直方图中的数据平衡情况，以及对访问每个位图索引的查询的执行路径。图中的执行路径显示了有多少个位图索引被合并，可以看出性能得到了显著的提高。 如上图所示，优化器依次使用4个单独的位图索引，这些索引的列在WHERE子句中被引用。每个位图记录指针(例如0或1)，用于指示表中的哪些行包含位图中的已知值。有了这些信息后，Oracle就执行BITMAP AND操作以查找将从所有4个位图中返回哪些行。该值然后被转换为ROWID值，并且查询继续完成剩余的处理工作。注意，所有4个列都有非常低的基数，使用索引可以非常快速地返回匹配的行。 技巧：在一个查询中合并多个位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。 下面的查询可显示索引类型。 SQL> select index_name, index_type from user_indexes; INDEX_NAME INDEX_TYPE ------------------------------ ---------------------- TT_INDEX NORMAL IX_CUSTADDR_TP NORMAL B树索引作为NORMAL列出；而位图索引的类型值为BITMAP。 技巧：如果要查询位图索引列表，可以在USER _INDEXES视图中查询index_type列。 建议不要在一些联机事务处理(OLTP)应用程序中使用位图索引。B树索引的索引值中包含ROWID，这样Oracle就可以在行级别上锁定索引。位图索引存储为压缩的索引值，其中包含了一定范围的ROWID，因此Oracle必须针对一个给定值锁定所有范围内的ROWID。这种锁定类型可能在某些DML语句中造成死锁。SELECT语句不会受到这种锁定问题的影响。 位图索引的使用限制： 基于规则的优化器不会考虑位图索引。 当执行ALTER TABLE语句并修改包含有位图索引的列时，会使位图索引失效。 位图索引不包含任何列数据，并且不能用于任何类型的完整性检查。 位图索引不能被声明为唯一索引。 位图索引的最大长度为30。 技巧：不要在繁重的OLTP环境中使用位图索引
        • 1111
      • HASH索引
        • 使用HASH索引必须要使用HASH集群。建立一个集群或HASH集群的同时，也就定义了一个集群键。这个键告诉Oracle如何在集群上存储表。在存储数据时，所有与这个集群键相关的行都被存储在一个数据库块上。如果数据都存储在同一个数据库块上，并且将HASH索引作为WHERE子句中的确切匹配，Oracle就可以通过执行一个HASH函数和I/O来访问数据——而通过使用一个二元高度为4的B树索引来访问数据，则需要在检索数据时使用4个I/O。如下图所示，其中的查询是一个等价查询，用于匹配HASH列和确切的值。Oracle可以快速使用该值，基于HASH函数确定行的物理存储位置。 HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的HASH值)。这种冲突是非常消耗资源的。冲突会造成用来存储额外行的缓冲溢出，然后造成额外的I/O。如果不同HASH值的数目已经被低估，您就必须在重建这个集群之后改变这个值。 ALTER CLUSTER命令不能改变HASH键的数目。HASH集群还可能浪费空间。如果无法确定需要多少空间来维护某个集群键上的所有行，就可能造成空间的浪费。如果不能为集群的未来增长分配好附加的空间，HASH集群可能就不是最好的选择。如果应用程序经常在集群表上进行全表扫描，HASH集群可能也不是最好的选择。由于需要为未来的增长分配好集群的剩余空间量，全表扫描可能非常消耗资源。 在实现HASH集群之前一定要小心。您需要全面地观察应用程序，保证在实现这个选项之前已经了解关于表和数据的大量信息。通常，HASH对于一些包含有序值的静态数据非常有效。 技巧：HASH索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。
      • 索引组织表索引
        • 索引组织表会把表的存储结构改成B树结构，以表的主键进行排序。这种特殊的表和其他类型的表一样，可以在表上执行所有的DML和DDL语句。由于表的特殊结构，ROWID并没有被关联到表的行上。 对于一些涉及精确匹配和范围搜索的语句，索引组织表提供了一种基于键的快速数据访问机制。基于主键值的UPDATE和DELETE语句的性能也同样得以提高，这是因为行在物理上有序。由于键列的值在表和索引中都没有重复，存储所需要的空间也随之减少。 如果不会频繁地根据主键列查询数据，则需要在索引组织表中的其他列上创建二级索引。不会频繁根据主键查询表的应用程序不会了解到使用索引组织表的全部优点。对于总是通过对主键的精确匹配或范围扫描进行访问的表，就需要考虑使用索引组织表。 技巧：可以在索引组织表上建立二级索引。
      • 反转键(reverse key)索引
        • 当载入一些有序数据时，索引肯定会碰到与I/O相关的一些瓶颈。在数据载入期间，某部分索引和磁盘肯定会比其他部分使用频繁得多。为了解决这个问题，可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。 为了解决这个问题，Oracle还提供了一种反转键索引的方法。如果数据以反转键索引存储，这些数据的值就会与原先存储的数值相反。这样，数据1234、1235和1236就被存储成4321、5321和6321。结果就是索引会为每次新插入的行更新不同的索引块。 技巧：如果您的磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。 不可以将反转键索引与位图索引或索引组织表结合使用。因为不能对位图索引和索引组织表进行反转键处理。
      • 基于函数的索引
        • 可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。例如，下面的查询就不能使用JOB列上的索引，除非它是基于函数的索引： select * from emp where UPPER(job) = 'MGR'; 下面的查询使用JOB列上的索引，但是它将不会返回JOB列具有Mgr或mgr值的行： select * from emp where job = 'MGR'; 可以创建这样的索引，允许索引访问支持基于函数的列或数据。可以对列表达式UPPER(job)创建索引，而不是直接在JOB列上建立索引，如： create index EMP$UPPER_JOB on emp(UPPER(job)); 尽管基于函数的索引非常有用，但在建立它们之前必须先考虑下面一些问题： 能限制在这个列上使用的函数吗？如果能，能限制所有在这个列上执行的所有函数吗 是否有足够应付额外索引的存储空间？ 在每列上增加的索引数量会对针对该表执行的DML语句的性能带来何种影响？ 基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE和DELETE语句的执行就会花费越多的时间。 注意：对于优化器所使用的基于函数的索引来说，必须把初始参数QUERY _REWRITE _ ENABLED设定为TRUE。 示例： select count(*) from sample where ratio(balance,limit) >.5; Elapsed time: 20.1 minutes create index ratio_idx1 on sample (ratio(balance, limit)); select count(*) from sample where ratio(balance,limit) >.5; Elapsed time: 7 seconds!!!
      • 分区索引(本地和全局索引)
        • 概述
          • 分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。B树和位图索引都可以被分区，而HASH索引不可以被分区。可以有好几种分区方法：表被分区而索引未被分区；表未被分区而索引被分区；表和索引都被分区。不管采用哪种方法，都必须使用基于成本的优化器。分区能够提供更多可以提高性能和可维护性的可能性 有两种类型的分区索引：本地分区索引和全局分区索引。每个类型都有两个子类型，有前缀索引和无前缀索引。表各列上的索引可以有各种类型索引的组合。如果使用了位图索引，就必须是本地索引。把索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。 在使用分区后的表和索引时，Oracle还支持并行查询和并行DML。这样就可以同时执行多个进程，从而加快处理这条语句。
        • 本地分区索引(通常使用的索引)
          • 概述
            • 可以使用与表相同的分区键和范围界限来对本地索引分区。每个本地索引的分区只包含了它所关联的表分区的键和ROWID。本地索引可以是B树或位图索引。如果是B树索引，它可以是唯一或不唯一的索引。 这种类型的索引支持分区独立性，这就意味着对于单独的分区，可以进行增加、截取、删除、分割、脱机等处理，而不用同时删除或重建索引。Oracle自动维护这些本地索引。本地索引分区还可以被单独重建，而其他分区不会受到影响。
          • 有前缀的索引
            • 有前缀的索引包含了来自分区键的键，并把它们作为索引的前导。例如，让我们再次回顾participant表。在创建该表后，使用survey_id和survey_date这两个列进行范围分区，然后在survey_id列上建立一个有前缀的本地索引，如下图所示。这个索引的所有分区都被等价划分，就是说索引的分区都使用表的相同范围界限来创建。 技巧：本地的有前缀索引可以让Oracle快速剔除一些不必要的分区。也就是说没有包含WHERE条件子句中任何值的分区将不会被访问，这样也提高了语句的性能。
          • 无前缀的索引
            • 无前缀的索引并没有把分区键的前导列作为索引的前导列。若使用有同样分区键(survey_id和survey_date)的相同分区表，建立在survey_date列上的索引就是一个本地的无前缀索引，如下图所示。可以在表的任一列上创建本地无前缀索引，但索引的每个分区只包含表的相应分区的键值。 如果要把无前缀的索引设为唯一索引，这个索引就必须包含分区键的子集。在这个例子中，我们必须把包含survey和(或)survey_id的列进行组合(只要survey_id不是索引的第一列，它就是一个有前缀的索引)。 技巧：对于一个唯一的无前缀索引，它必须包含分区键的子集。
        • 全局分区索引
          • 概述
            • 全局分区索引在一个索引分区中包含来自多个表分区的键。一个全局分区索引的分区键是分区表中不同的或指定一个范围的值。在创建全局分区索引时，必须定义分区键的范围和值。全局索引只能是B树索引。Oracle在默认情况下不会维护全局分区索引。如果一个分区被截取、增加、分割、删除等，就必须重建全局分区索引，除非在修改表时指定ALTER TABLE命令的UPDATE GLOBAL INDEXES子句。
          • 有前缀的索引
            • 通常，全局有前缀索引在底层表中没有经过对等分区。没有什么因素能限制索引的对等分区，但Oracle在生成查询计划或执行分区维护操作时，并不会充分利用对等分区。如果索引被对等分区，就必须把它创建为一个本地索引，这样Oracle可以维护这个索引，并使用它来删除不必要的分区，如下图所示。在该图的3个索引分区中，每个分区都包含指向多个表分区中行的索引条目。 技巧：如果一个全局索引将被对等分区，就必须把它创建为一个本地索引，这样Oracle可以维护这个索引，并使用它来删除不必要的分区。
          • 无前缀的索引
            • Oracle不支持无前缀的全局索引。
      • 位图连接索引
        • 位图连接索引是基于两个表的连接的位图索引，在数据仓库环境中使用这种索引改进连接维度表和事实表的查询的性能。创建位图连接索引时，标准方法是连接索引中常用的维度表和事实表。当用户在一次查询中结合查询事实表和维度表时，就不需要执行连接，因为在位图连接索引中已经有可用的连接结果。通过压缩位图连接索引中的ROWID进一步改进性能，并且减少访问数据所需的I/O数量。 创建位图连接索引时，指定涉及的两个表。相应的语法应该遵循如下模式： create bitmap index FACT_DIM_COL_IDX on FACT(DIM.Descr_Col) from FACT, DIM where FACT.JoinCol = DIM.JoinCol; 位图连接的语法比较特别，其中包含FROM子句和WHERE子句，并且引用两个单独的表。索引列通常是维度表中的描述列——就是说，如果维度是CUSTOMER，并且它的主键是CUSTOMER_ID，则通常索引Customer_Name这样的列。如果事实表名为SALES，可以使用如下的命令创建索引： create bitmap index SALES_CUST_NAME_IDX on SALES(CUSTOMER.Customer_Name) from SALES, CUSTOMER where SALES.Customer_ID=CUSTOMER.Customer_ID; 如果用户接下来使用指定Customer_Name列值的WHERE子句查询SALES和CUSTOMER表，优化器就可以使用位图连接索引快速返回匹配连接条件和Customer_Name条件的行。 位图连接索引的使用一般会受到限制： 1）只可以索引维度表中的列。 2）用于连接的列必须是维度表中的主键或唯一约束；如果是复合主键，则必须使用连接中的每一列。 3）不可以对索引组织表创建位图连接索引，并且适用于常规位图索引的限制也适用于位图连接索引。 例子： Select * From emp_t1; Select * From dept_t3; Drop Index bitmap_et_dt_deptno; Create Bitmap Index bitmap_et_dt_deptno On emp_t1(dept_t3.dname) From emp_t1, dept_t3 Where emp_t1.deptno = dept_t3.deptno; Explain Plan For Select /*+ index (e,bitmap_et_dt_deptno) */ d.dname From emp_t1 e, dept_t3 d Where e.deptno = d.deptno And d.dname = 'SALES'; Commit; Select * From Table(dbms_xplan.display); Plan hash value: 3207952492 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 50 | 9 (12)| 00:00:01 | |* 1 | HASH JOIN | | 2 | 50 | 9 (12)| 00:00:01 | |* 2 | TABLE ACCESS FULL | DEPT_T3 | 1 | 22 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS BY INDEX ROWID | EMP_T1 | 6 | 18 | 5 (0)| 00:00:01 | | 4 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | BITMAP_ET_DT_DEPTNO | | | | | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPTNO"="D"."DEPTNO") 2 - filter("D"."DNAME"='SALES') 3 - filter("E"."DEPTNO" IS NOT NULL) 5 - access("E"."SYS_NC00009$"='SALES') Note ----- - dynamic sampling used for this statement (level=2)
    • 索引创建时列的选择
      • 1、应该建索引列的特点： 1）在经常需要搜索的列上，可以加快搜索的速度； 2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。 2、 不应该建索引列的特点： 1），对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 2），对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 3），对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。 4），当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
    • 限制索引
      • 限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题： 1、使用不等于操作符（<>、!=） 下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。 select cust_Id,cust_name from customers where cust_rating <> 'aa'; 把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。 select cust_Id,cust_name from customers where cust_rating < 'aa' or cust_rating > 'aa'; 特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。 2、使用IS NULL 或IS NOT NULL 使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成 NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。 3、使用函数 如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。 下面的查询不会使用索引（只要它不是基于函数的索引） select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81'; 把上面的语句改成下面的语句，这样就可以通过索引进行查找。 select empno,ename,deptno from emp where hiredate<(to_date('01-MAY-81')+0.9999); 4、比较不匹配的数据类型 也是比较难于发现的性能问题之一。 注意下面查询的例子，account_number是一个VARCHAR2类型,在account_number字段上有索引。 下面的语句将执行全表扫描： select bank_name,address,city,state,zip from banks where account_number = 990354; Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引： select bank_name,address,city,state,zip from banks where account_number ='990354'; 特别注意：不匹配的数据类型之间比较会让oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。
    • 集群因子
      • 集群因子有影响只能在返回多条记录的情况下才能起作用 是用来衡量索引扫描的时候通过ROWID 回表，物理读有多大。 我 们知道可以通过dbms_rowid.rowid_block_number(rowid)找到记录对应的block 号。索引中记录了rowid，因此oracle 就可以根据索引中的rowid来判断记录是否是在同一个block 中。举个例子，比如说索引中有a,b,c,d,e五个记录，首先比较a,b 是否在同一个block，如果不在同一个block 那么Clustering Factor +1，然后继续比较b,c 同理，如果b,c 不在同一个block，那么Clustering Factor+1，这样一直进行下去，直到比较了所有的记录。根据算法我们就可以知道clustering factor 的值介于block 数和表行数之间。如果clustering factor 接近block 数，说明表的存储和索引存储排序接近，也就是说表中的记录很有序，这样在做index range scan 的时候能，读取少量的data block 就能得到我们想要的数据，代价比较小。如果clustering factor 接近表记录数，说明表的存储和索引排序差异很大，在做index range scan 的时候，会额外读取多个block，因为表记录分散，代价较高。 索引扫描中受集群因子影响的扫描方式： index range scan index full scan
    • 虚拟索引
      • 引言
        • DBA在日常维护管理数据库进行低性能SQL分析时，有时候需要通过创建索引对SQL进行优化，但有些时候我们创建的索引是否能用到？这个只能创建以后才能看出效果，但是在实际工作中，特别是对大表创建索引对系统性能有很大影响，因此我们不得不避开业务高峰时段，但是有没有一种办法创建索引而不影响性能呢？有，那就是虚拟索引。 虚拟索引不是物理存在的，它并不会创建实际的索引段，只是在数据字典中加了一个索引的记录，使得优化器能够意识到一个索引的存在，从而判断是否使用该索引作为访问路径。作用仅仅是为了DBA作SQL优化时使用，DBA根据虚拟索引的优化效果决定是否创建物理索引。
      • 类型
        • 虚拟索引支持B-TREE索引和BIT位图索引，在CBO模式下ORACLE优化器会考虑虚拟索引，但是在RBO模式下需要添加hint才行。
      • 创建及例子
        • SQL> drop table t purge; 表已删除。 SQL> create table t as select * from dba_objects; 表已创建。 --创建虚拟索引，首先要将_use_nosegment_indexes的隐含参数设置为true SQL> alter session set "_use_nosegment_indexes"=true; 会话已更改。 --虚拟索引的创建语法比较简单，实际上就是普通索引语法后面加一个nosegment关键字 SQL> create index ix_t_id on t(object_id) nosegment; 索引已创建。 SQL> explain plan for select * from t where object_id=1; 已解释。 SQL> set linesize 1000 SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 206018885 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 2484 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 12 | 2484 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IX_T_ID | 273 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 2 - access("OBJECT_ID"=1) Note ----- - dynamic sampling used for this statement (level=2) 已选择18行。 SQL> set autotrace traceonly SQL> select * from t where object_id=1; 未选定行 执行计划 ---------------------------------------------------------- Plan hash value: 206018885 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 2484 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 12 | 2484 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IX_T_ID | 273 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=1) Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 4 recursive calls 0 db block gets 1101 consistent gets 753 physical reads 0 redo size 1184 bytes sent via SQL*Net to client 404 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed SQL> set autotrace off --以下看的是真实执行计划，显然是用不到索引。 SQL> alter session set statistics_level=all; 会话已更改。 SQL> select * from t where object_id=1; 未选定行 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 2qhwh0nzrzx2r, child number 1 ------------------------------------- select * from t where object_id=1 Plan hash value: 1601196873 ------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1037 | |* 1 | TABLE ACCESS FULL| T | 1 | 12 | 0 |00:00:00.01 | 1037 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=1) Note ----- - dynamic sampling used for this statement (level=2) 已选择22行。 --从数据字段中是无法找到这个索引的。 SQL> select index_name,status from user_indexes where table_name='T'; 未选定行
      • 特点
        • 1、虚拟索引无法执行alter index选项 SQL> alter index IX_T_ID rebuild; alter index IX_T_ID rebuild * 第 1 行出现错误: ORA-08114: 无法变更假索引 2、使用回收站特性的时候，虚拟索引必须显示drop，才能创建同名的索引。 复制代码 SQL> create index ind_status on t(status); 索引已创建。 SQL> drop table t; 表已删除。 SQL> flashback table t to before drop; 闪回完成。 SQL> select table_name,index_name,status from user_indexes where table_name='T'; TABLE_NAME INDEX_NAME STATUS ------------------------------ ------------------------------ -------- T BIN$7jAFlUG6b1zgQAB/AQAPyw==$0 VALID SQL> create index ind_object_id on t(object_id); 索引已创建。 SQL> create index inds_status on t(status); create index inds_status on t(status) * 第 1 行出现错误: ORA-01408: 此列列表已索引 复制代码 3、不能创建和虚拟索引同名的实际索引； 4、可以创建和虚拟索引包含相同列但不同名的实际索引； 5、虚拟索引分析并且有效，但是数据字典里查不到结果。
  • 执行计划的查看
    • 1、打开查看执行计划的方法（如查看不了执行计划则做以下操作） 1)、conn / as sysdba 2)、@?\rdbms\admin\utlxplan 3)、@?\sqlplus\admin\plustrce.sql <创建plustrace角色> 4)、grant plustrace to public ; 2、打开跟踪功能： set autotrace on 缩写：SET AUTOT ON 3、关闭跟踪功能： set autotrace off 缩写：SET AUTOT OFF 4、一般的执行计划显示结果 1）、select查询的结果 2）、反馈信息 3）、执行计划 4）、统计信息 5、处理显示结果 （常用 SET AUTOTRACE TRACEONLY） SET AUTOTRACE ON ----------------- 包含执行计划和统计信息,返回多少行 SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式 SET AUTOTRACE ON EXPLAIN ------ 只显示查询结果和执行计划 SET AUTOTRACE ON STATISTICS -- -- 只显示结果和统计信息 SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出 SET AUTOT TRACEONLY EXPLAIN ---- 只显示执行计划 SET AUTOT TRACEONLY STATISTICS – 只显示统计信息
  • 回收站
    • 回收站的内容在表空间中 查看数据库垃圾站：show recyclebin 清空回收站：Purge dba_recyclebin; Purge recyclebin; 收回回收站中的某个表： flashback table t1 to before drop; 可以加rename以防回收的表名已被使用。 flashback是基于回收站的，回收站是每个表空间都有一个，但是system表空间是没有的，所以，在sys或system用户下，回收站是空的。
  • 一些重要概念
    • 高水位
      • 原理
        • 大 家好！我是来自云和恩墨的陈龙，目前主要负责Oracle技术支持工作。在我开始学习Oracle 的时候就听eygle老师说过，要想学好技术，一定要 要多做实验，多做学习记录，理论与实践相结合，才能真正理解吸收那些知识，所以今天我想分享一下对Oracle高水位线与SQL访问性能相关性的研究体 会。谈不上很深入的研究，只是想与大家分享我的Oracle学习过程，希望能与大家交流进步。之所以分享这个学习内容，是因为在我曾经经历的一些优化项目 中，遇到由于高水位导致SQL访问性能下降的问题，这促使我想更深入的去理解：什么是高水位线？ 高水位到底会带来哪些影响？ 应该怎样更好的解决高水位带来的问题？在开始深入分析之前，让我们先来了解一下高水位线HWM。 1HWM的基本原理（概念）在 Oracle中，高水位线（High-warter mark, HWM）被用来形容数据块的使用位置，即说明那些块是已经被使用的，那些没有。在创建一个新的表时，Oracle 就会为这个对象分配一个段。在这个段中，第一个区的第一个块在创建时就会被分配存储一些表头信息。高水位的管理机制在MSSM和ASSM中不同，下面分别 介绍这两种管理机制：手动段空间管理（MSSM）在以往的手动段空间管理中（MSSM），高水位标记HWM, 一个段分成三部分，header block，used block(row data),unused block，其中used block和unused block之间的分界线就是高水位标记HWM，当进行全表扫描的时候，会扫描到HWM下的所有数据块，即使used block中很多数据被删除了，全表扫描还是以HWM为准。当插入insert时，freelist中没有空闲块，HWM会向上移动，但是只会向上移动， 不会自动收缩，即使delete大量数据，导致HWM下有很多空闲块。如图一： 第一个区的第一个块就称为段头(SEGMENT HEADE),段头中就储存了一些信息,基中HWM的信息就存储在此。此时,因为第一个区的第一块用于存储段头的一些信息，虽然没有存储任何实际的记录, 但也算是被使用,此时HWM是位于第2个块当我们不断插入数据到PM_USER后,第1个块已经放不下后面新插入的数据,此时,ORACLE将高水位之上 的块用于存储新增数据。图二 在向表里插入数据后,HWM本身也向上移.也就是说,当我们不断插入数据时,HWM会往不断上移,这样,在HWM之下的,就表示使用过的块,HWM之上的就表示已分配但从未使用过的块。图三 当对表进行导出，导入后，或者move等操作后，HWM就会降低到真实水平上。自动段管理（ASSM）在自动段管理（ASSM）中，利用位图来代替空闲列表，当会话向表插入数据时，数据库只格式一个单独的位图块，而不是像MSSM中那样，会预先格式化一组块。图四 在ASSM表空间中，除了一个HWM外，还有一个低HWM。在MSSM中，HWM推进时，所有的块都会格式化并立即生效，这样Oracle 就可以安全的读取这些块。但是对于ASSM，当HWM推进时，Oracle 并不会立即格式所有的块，只是在第一次使用的时候才会对这些块进行格式化。也就是说，在第一次使用的的时候，即进行insert 操作时，数据会插入到块中的任意水位线，位于低水位线（LHMW）和高水位线（HHMW）之间。因此在这个区域的许多块就不会被格式化。在一个ASSM段 中的每个数据块可能为这些状态：◎ 在HWM之上这些块都是没有格式化，且没有被使用◎在HWM之下这些块会处于这些状态之一：• 已经分配，但是没有被格式化且没有被使用 • 已经格式化且 包含数据 • 已经格式化，但是已经删除数据，块为空。 下面来了解整个过程： 如图五所示，在没进行数据插入前，段中的所有数据块都是没有格式化的且没有被使用。假设这个时候某会话将数据插入时，数据库会把数据写到任何可以利用的空间块中。如图六所示： 数据库也可能会选择HWM和低HWM之间的任何数据块，或者低HWM之下的任何可用的空间的块。注意低HWM位置，因为HWM之下的块只有在被使用的时候，才进行格式化，所以当存在数据扫描时，特别是全表扫描，Oracle 会读取到低HWM的位置。如图七所示： 如果某条会话将数据插入到某表中，但是在当前的HWM之下没有足够的可用空间，那么这个时候Oracle 会推进HWM，重新分配一组新的没有格式化的块。当HWM与低HWM之间的位置被填满时，HWM会继续往前推进，而低HWM会相应的推进到旧的HWM位置 中。以此类推，当数据库不断的插入数据，HWM会持续往前推移，而低HWM会尾随其后，除非重建、或缩小该对象等，否则HWM从不往回退如图八。
      • 检查高水位
        • 查询某张表高水位情况：根据rowid查询，rowid前18位记录到块号。 （对于高水位的查看主要及时两张表：DBA_SEGMENTS、DBA_TABLES） 查询之前可能要先收集表的统计信息： Analyze Table scott.MYBIGT Compute Statistics For Table For All Indexes For All Indexed Columns; select a.used, b.num_total, round((1 - a.used / b.num_total) * 100, 0) percent from (SELECT COUNT(DISTINCT SUBSTR(rowid, 1, 15)) Used FROM mybigt) a, (select blocks num_total from dba_tables where table_name = 'MYBIGT' and owner = 'SCOTT') b;
      • 处理
        • 重建表：注意备份、非繁忙期处理
          • create table t_big_tmp as select * from t_big_tmp; ....设置授权，创建索引等 rename t_big to t_big_bak; rename t_big_tmp to t_big;
        • move表
          • 通过desc table_name 来检查表中是否有LOB 字段， 如果表没有LOB字段， 直接 alter table move; 然后 rebuild index 如果表中包含了LOB字段，如用如下SQL: SQL>alter table owner.table_name move tablespace tablespace_name lob (lob_column) store as lobsegment tablespace tablespace_name; 也可以单独move lob，但是表上的index 同样会失效. 所以在操作结束，需要对索引进行rebuild。 SQL>alter table owner.table_name move lob(lob_column) store as lobsegment tablespace tablespace_name ; 索引的rebuild： 首先用下面的SQL查看表上面有哪类索引: SELECT a.owner, a.index_name, a.index_type, a.partitioned, a.status, b.status p_status, b.composite FROM dba_indexes a LEFT JOIN dba_ind_partitions b ON a.owner = b.index_owner AND a.index_name = b.index_name WHERE a.owner = '&owner'AND a.table_name = '&table_name'; 对于普通索引直接rebuild online nologging parallel, 对于分区索引，必须单独rebuild 每个分区, 对于组合分区索引,必须单独rebuild 每个子分区。 Alter Table scott.mybigt Enable Row Movement; Alter Table scott.mybigt move; Alter Table scott.mybigt Disable Row Movement; Alter Index xxx Rebuild;
        • shrink表
          • 在10g之后，整理碎片消除行迁移的新增功能shrink space SQL>alter table <table_name> shrink space [ <null> | compact | cascade ]; compact: 这个参数当系统的负载比较大时可以用，不降低HWM。如果系统负载较低时，直接用alter table table_name shrink space就一步到位了 cascade：这个参数是在shrink table的时候自动级联索引，相当于rebulid index。 虽然在10g中可以用shrink ，但也有些限制： 1). 对cluster，cluster table，或具有Long，lob类型列的对象 不起作用。 2). 不支持具有function-based indexes 或 bitmap join indexes的表 3). 不支持mapping 表或index-organized表。 4). 不支持compressed 表 Alter Table scott.mybigt Enable Row Movement; Alter Table scott.mybigt Shrink Space Compact; Alter Table scott.mybigt Shrink Space ; Alter Table scott.mybigt Shrink Space Cascade; Alter Table scott.mybigt Disable Row Movement; shrink的三个参数的区别： alter table shrink space compcat; 收缩表，但会保持 high water mark; alter table shrink space; 收缩表，降低 high water mark; alter table shrink space cascade; 收缩表，降低 high water mark，并且相关索引也要收缩
        • Move和Shrink区别
          • MOS 上的说明，ID:577375.1: The shrink algorithm starts from the bottom of the segment and starts moving those rows to the beginning of the segment. Shrink is a combination of delete/insert pair for every row movement and this generates many UNDO and REDO blocks . Move从segment的底部开始，move这些rows到segment的头部。Shrink则是delete/insert相结合，这样会产生非常多的UNDO和REDO。 Move 通过移动数据来来降低HWM，因此需要更多的磁盘空间。 Shrink 通过delete 和 insert， 会产生较多的undo 和redo。 shrink space收缩到数据存储的最小值，alter table move（不带参数）收缩到initial指定值，也可以用alter table test move storage(initial 500k)指定收缩的大小，这样可以达到shrink space效果。 总之，使用Move 效率会高点，但是会导致索引失效。Shrink 会产生undo 和redo，速度相对也慢一点
        • 小结
          • 1．回收高水位操作shrink space可对高水位之下未储存数据的block加以回收，并降低高水位线。既能减少空间使用，又能提高查询效率，而对表内的数据、表上的索引没有影响。 2．回收高水位操作shrink space是表和段级别的操作，能释放表空间文件内的空间，但不能缩小表空间文件的大小。 3．回收操作是DDL操作而非DML操作，不由应用程序完成，需要管理员定期执行。
    • 行迁移行链接
      • 原理及产生原因
        • 看 Oracle架构-数据块结构
      • 查看行链接和行迁移
        • .Oracle的dbms_stats.gather_schema_stats只会收集优化器统计信息,不会检测表的记录是否存在行迁移和行链接. 所以执行完dbms_stats.gather_schema_stats后一查数据字典:USER_TABLES,其中的字段chain_cnt值全为0。要检测表的记录是否存在row chain,需要用到oracle早先一点的命令:ANALYZE TABLE。 除了写存储过程可以检测以外，一个简单的检测方法是： select b.NAME,a.VALUE from v$mystat a,v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'table fetch continued row'
      • 处理
        • 1、使用临时表-chained_rows
          • 操作:（以EMPLOYEES_TEMP表为例，如果涉及到该表有主键，并且有别的表的外键REFERENCE关联到本表，必须要执行步骤2和步骤7，否则不必执行）： 1. 执行$ORACLE_HOME/rdbms/admin目录下的utlchain.sql脚本创建chained_rows表。 2. 禁用所有其它表上关联到此表上的所有限制(假想EMPLOYEES_TEMP表有主键PK_EMPLOYEES_TEMP_ID，假想test表有外键f_EMPLOYEES_TEMP_id关联reference到EMPLOYEES_TEMP表）。 select index_name,index_type,table_name from user_indexes where table_name='EMPLOYEES_TEMP'; select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_EMPLOYEES_TEMP_ID'; alter table test disable constraint f_EMPLOYEES_TEMP_id; 3． 将存在有行迁移的表（用table_name代替）中的产生行迁移的行的rowid放入到chained_rows表中。 4． 将表中的行迁移的row id放入临时表中保存。 5． 删除原来表中存在的行迁移的记录行。 6． 从临时表中取出并重新插入那些被删除了的数据到原来的表中，并删除临时表。 7． 启用所有其它表上关联到此表上的所有限制。 alter table test enable constraint f_EMPLOYEES_TEMP_id;
        • 2、其他处理方式
          • 1、还可以采用move和exp/imp的方式（特别注意move会导致索引失效，需要重建索引） 2、通过修改pctfree 3、对于行链接还可以将数据存放在block更大的表空间中
        • 3、一个例子
          • 行迁移和行链接的清除： 能过REBUILD数据来清除行迁移： create table MM_PM_temp as select * from MM_PM; truncate table MM_PM; insert into MM_PM select * from MM_PM_temp 再重新分析表： analyze table MM_PM compute statistics; 分析过后再查看： select t.table_name, t.num_rows, t.chain_cnt, t.avg_row_len, round((t.chain_cnt / t.num_rows) * 100, 2) as "chained rows %" from user_tables t where t.chain_cnt > 0 如果该表的chain_cnt变为0时，表示原先的chain_cnt全部是行迁移，而不是行链接。如果REBUILD数据后chain_cnt变少，但还大于0，则可以证明，这个表即包含行迁移，又包含真正的行链接。事实证明，行迁移是可以通过REBUILD数据和增加PCTFREE%来清除和减少发生频率的。注意，对于ASSM，PCTUSED，FREELIST，FREELIST GROUPS参数会被忽略。 但对于真正的行链接，只能通过将表移植到大的BLOCSIZE的表空间上
• 高级
• PLSQL
• 优化
• 运维
  • 数据的导入导出
  • 一些参数
  • AWR
  • 10046
  • 10053
  • alert日志
  • 。。。