工作笔记_昨日全量+当天增量-CSDN博客

本文链接：https://blog.csdn.net/u012852188/article/details/81004734

收集表的统计信息：

DECLARE

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(USER,’F_IS_AGT_M’);

END;

Chown：用来更改某个目录或文件的用户名和用户组的；

Chmod：用来修改某个目录或文件的访问权限。

启监听：lsnrctl start;;

用MINUS比较两个表时的字段时要把两个表的主键列出来，这样才会根据主键找到两个表对应的唯一一条数据，然后对两个表要比对的字段进行比对；

更新多个字段update a set
   a = b.a
   ,b=b.b
   ,c=b.c
from t a,t b
where (a.条件1) and (b.条件2)

`查看oracle某一用户下所有对象：

Select count(*) from all_objects where owner=’EIBCBI’;

·使用to_date必须要字符串和’YYYYMMDD’严格对应，比如to_date(‘2012-12-12’,’’yyyy-mm-dd)，字符串什么格式，yyyymmdd就要什么格式，否则匹配不上，to_date后再进行to_char时会报错；

·查一个表影响到的脚本：

Select * from user_dependents t

Where t.dependents.name = ‘表名’；

·停止job：

Select * from dba_jobs找到job号，然后在sql window执行

Begin

Dbms_job.remove(2684);

Commit;

End;

·向一个表插入多次值：select * from table_1 for updata然后点解锁按键向表里插入数据或者右击表名，点击edit data，向表里插入数据，插入时选中那一列的列名，然后增加一行，再把数据粘贴到此表；

·压缩命令：gzip 文件名解压命令：gzip -d 文件名

·找某一日期往后的时间序列：

selectto_char(to_date('20091202','yyyymmdd')+level-1,'yyyymmdd')

from dual

connect by level<= 4000；

·使用一个脚本通过输入另一个脚本的参数和时间序列根据时间序列对另一个脚本进行调用执行：

create or replace procedure sss_20161222 is

begin

for i in (selectto_char(to_date('20091202','yyyymmdd')+level-1,'yyyymmdd') d

from dual

connect by level<= 365

) loop

sss_20161216(v_bbq=> i.d,

v_agmt_num => '21502013014151');

endloop;

end sss_20161222;

·Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序;

可以在最后一个结果集中指定Order by子句改变排序方式。

·DML语言，比如update，delete，insert等修改表中数据的需要commit;

Insert into a select * from b，要求a表已经创建出和b表一样的表结构; create table a as select * from b;

使用imp导入之前要给用户赋予MP_FULL_DATABASE权限的权限，否则会出现imp00013的报错.

left join一个表时要根据left join 要使用的关联字段来找leftjoin表放的位置，可以放在关联字段所在left join的所在嵌套层里。

会计科目是按照会计对象的经济内容性质的不同而进行的分类标志，是对会计要素的具体内容进行分类核算的项目名称。

Aix、liux系统下查找jdk路径使用whichjava命令，比如/usr/java5/jre/bin/java，其中java_homewei为/usr/java5;

jdk跟eclipse、myeclipse、netbeans等软件一样是java语言的开发软件，只不过jdk没有像他们那样的可视化开发界面，需要在cmd下面进行运行才行。
至于jre当然是java语言的运行环境，也就是虚拟机，jdk、eclipse、myeclipse、netbeans等软件需要jre才能开发运行java语言；

部署wherephere下的war包需要在部署前进入war包里修改两个配置文件，第一个是修改web-inf里的web.xml，加入工作目录，第二个是修改web-inf/classes/meta-inf/esensso/ssoconfig-slave.xml,将第二行的sysid改成war包的文根名，将第三行第四行改成具体的ip以及端口名；

部署war包要修改的参数文件（配置时参照其它war包的参数文件）：

在tomcat/conf/server.xml里69行左右配tomcat的端口号；

在tomcat/webapps/“war包名称”/WEB-INF/web.xml里第三行<web-app>和<filter>之间添加工作目录；

在配置好的工作目录下新建conf文件夹，在文件夹下新建jdbc.conf，配置连接数据库的相关信息；

跑脚本的时候select * from log_etl_sub 和select * fromlog_etl_main在后台监控脚本的执行情况；

在命令执行语句开头加上Nohup 在最后加上&可以在后台执行这个命令，不怕正在使用的电脑关机。

164连接227执行ssh传输文件时显示connection refused，下载AIX5.3系统ssh安装包

并在227安装及启动服务后可用

断开连接数据库的用户：

以sysdba登录数据库，select sid,serial# from v$session where username=’user_name’

找到后执行alter system kill session ‘sid,serial’;

Plsql developer 连接数据库：打开tnsnames.ora配置。

使用TOOLS-SESSIONS工具来监测数据库用户对数据库的操作情况，使用ACTIVE SESSIONS查看正在活动的进程。

对一个脚本连续执行多次后需清理缓存：

打开command window，执行 ALTER SYSTEM FLUSH SHARED POOL;

执行好几个SQL语句用BEGIN END,在BEGIN END 之间插入要执行的多条SQL语句即可。

跑PACKAGE:打开PACKAGE BODIES,找到此PACKAGE，找到PACKAGE中的MAIN函数，右击-TEST，给参数跑。

2016.3.30

当’’INSERT INTO TABLEA SELECT * FROMTABLEB”不可执行时，采用以下方法：

定义一个变量tt使其等于以上SQL语句，再EXECUTE IMMEDIATE tt;

动态语句可以使用EXECUTE使用（DML中的TRUNCATE），静态语句不可以（DDL中的DELETE）;

注意转义字符。比如在 ’’ 里面含有 ’’ 等字符，里边的 ’’ 要变成 ’’’’;

3.31

A表比B表多几个字段，若从A表抽取B表的某几个字段，抽取时需要把A表多出的几个字段也加上，再在这几个字段前加NULL;

DIP中的表有可能某些字段查不出来，因为没有开放权限；

P_DIP_ETL报错的原因是其编写定制的规定为某一个日期只能执行一次，超过一次时需手工到LOG_ETL_END里删除日期：

DELETE FROMLOG_TEL_END WHERE WORK_DATE=’20160111’;

从数据库导出对象：

用此工具

使用PL/SQL登录，打开TOOL-Export User,全选所有对象，设置导出目录与导出文件名

从DIP抽取数据过程：从DIP抽过来数据先给BI,BI给I后I对数据进行补录，然后再传给BI。

在PL/SQL Developer中执行脚本的时候对脚本中各部分运行时间进行监测：右击脚本，选择TEST，在打开的窗口点击中上方的Create Profiler Report，然后输入脚本参数，执行脚本，脚本执行完毕后点击 Create Profiler Report键上面的Profiler键，查看脚本中各部分执行的时间。

分析函数

本文讲述Oracle分析函数用法，首先建库：

Sql代码

create table earnings -- 打工赚钱表

(

earnmonth varchar2(6), -- 打工月份

area varchar2(20), -- 打工地区

sno varchar2(10), -- 打工者编号

sname varchar2(20), -- 打工者姓名

times int, -- 本月打工次数

singleincome number(10,2), -- 每次赚多少钱

personincome number(10,2) -- 当月总收入

)

然后插入实验数据：

Sql代码

insert into earnings values('200912','北平','511601','大魁',11,30,11*30);

insert into earnings values('200912','北平','511602','大凯',8,25,8*25);

insert into earnings values('200912','北平','511603','小东',30,6.25,30*6.25);

insert into earnings values('200912','北平','511604','大亮',16,8.25,16*8.25);

insert into earnings values('200912','北平','511605','贱敬',30,11,30*11);

insert into earnings values('200912','金陵','511301','小玉',15,12.25,15*12.25);

insert into earnings values('200912','金陵','511302','小凡',27,16.67,27*16.67);

insert into earnings values('200912','金陵','511303','小妮',7,33.33,7*33.33);

insert into earnings values('200912','金陵','511304','小俐',0,18,0);

insert into earnings values('200912','金陵','511305','雪儿',11,9.88,11*9.88);

insert into earnings values('201001','北平','511601','大魁',0,30,0);

insert into earnings values('201001','北平','511602','大凯',14,25,14*25);

insert into earnings values('201001','北平','511603','小东',19,6.25,19*6.25);

insert into earnings values('201001','北平','511604','大亮',7,8.25,7*8.25);

insert into earnings values('201001','北平','511605','贱敬',21,11,21*11);

insert into earnings values('201001','金陵','511301','小玉',6,12.25,6*12.25);

insert into earnings values('201001','金陵','511302','小凡',17,16.67,17*16.67);

insert into earnings values('201001','金陵','511303','小妮',27,33.33,27*33.33);

insert into earnings values('201001','金陵','511304','小俐',16,18,16*18);

insert into earnings values('201001','金陵','511305','雪儿',11,9.88,11*9.88);

然后看看刚刚建好的库：

Sql代码

select * from earnings;

(1)sum函数，统计总合
按照月份，统计每個地区的总收入

Sql代码

select earnmonth, area, sum(personincome)

from earnings

group by earnmonth,area;

查看结果如下：

(2)rollup函数
按照月份，地区统计收入

Sql代码

select earnmonth, area, sum(personincome)

from earnings

group by rollup(earnmonth,area);

查看结果如下：

(3)cube函数
按照月份，地区进行收入总汇总

Sql代码

select earnmonth, area, sum(personincome)

from earnings

group by cube(earnmonth,area)

order by earnmonth,area nulls last;

结果如下：

小结：sum是统计求和的函数。
group by 是分组函数，按照earnmonth和area先后次序分组。
以上三例都是先按照earnmonth分组，在earnmonth内部再按area分组，并在area组内统计personincome总合。
group by 后面什么也不接就是直接分组。
group by 后面接 rollup 是在纯粹的 group by 分组上再加上对earnmonth的汇总统计。
group by 后面接 cube 是对earnmonth汇总统计基础上对area再统计。
另外那个 nulls last 是把空值放在最后。

rollup和cube区别：
如果是ROLLUP(A, B, C)的话，GROUP BY顺序
(A、B、C)
(A、B)
(A)
最后对全表进行GROUP BY操作。

如果是GROUP BY CUBE(A, B, C)，GROUP BY顺序
(A、B、C)
(A、B)
(A、C)
(A)，
(B、C)
(B)
(C)，
最后对全表进行GROUP BY操作。

(4)grouping函数
在以上例子中，是用rollup和cube函数都会对结果集产生null，这时候可用grouping函数来确认
该记录是由哪个字段得出来的
grouping函数用法，带一个参数，参数为字段名，结果是根据该字段得出来的就返回1，反之返回0

Sql代码

select decode(grouping(earnmonth),1,'所有月份',earnmonth) 月份,

decode(grouping(area),1,'全部地区',area) 地区, sum(personincome) 总金额

from earnings

group by cube(earnmonth,area)

order by earnmonth,area nulls last;

查看结果如下：

(5)rank() over开窗函数
按照月份、地区，求打工收入排序

Sql代码

select earnmonth 月份,area 地区,sname 打工者, personincome 收入,

rank() over (partition by earnmonth,area order by personincome desc) 排名

from earnings;

查看结果：

(6)dense_rank() over开窗函数
按照月份、地区，求打工收入排序2

Sql代码

select earnmonth 月份,area 地区,sname 打工者, personincome 收入,

dense_rank() over (partition by earnmonth,area order by personincome desc) 排名

from earnings;

结果如下：

(7)row_number() over开窗函数
按照月份、地区，求打工收入排序3

Sql代码

select earnmonth 月份,area 地区,sname 打工者, personincome 收入,

row_number() over (partition by earnmonth,area order by personincome desc) 排名

from earnings;

结果如下：

通过(5)(6)(7)发现rank,dense_rank,row_number的区别：
结果集中如果出现两个相同的数据，那么rank会进行跳跃式的排名，
比如两个第二，那么没有第三接下来就是第四；
但是dense_rank不会跳跃式的排名，两个第二接下来还是第三；
row_number最牛，即使两个数据相同，排名也不一样。

(8)sum累计求和
根据月份求出各个打工者收入总和，按照收入由少到多排序

Sql代码

select earnmonth 月份,area 地区,sname 打工者,

sum(personincome) over (partition by earnmonth,area order by personincome) 总收入

from earnings;

查看结果如下：

(9)max，min，avg和sum函数综合运用
按照月份和地区求打工收入最高值，最低值，平均值和总额

Sql代码

select distinct earnmonth 月份, area 地区,

max(personincome) over(partition by earnmonth,area) 最高值,

min(personincome) over(partition by earnmonth,area) 最低值,

avg(personincome) over(partition by earnmonth,area) 平均值,

sum(personincome) over(partition by earnmonth,area) 总额

from earnings;

结果如下：

(10)lag和lead函数
求出每个打工者上个月和下个月有没有赚钱(personincome大于零即为赚钱)

Sql代码

select earnmonth 本月,sname 打工者,

lag(decode(nvl(personincome,0),0,'没赚','赚了'),1,0) over(partition by sname order by earnmonth) 上月,

lead(decode(nvl(personincome,0),0,'没赚','赚了'),1,0) over(partition by sname order by earnmonth) 下月

from earnings;

说明：Lag和Lead函数可以在一次查询中取出某个字段的前N行和后N行的数据（可以是其他字段的数据，比如根据字段甲查询上一行或下两行的字段乙），原来没有分析函数的时候采用子查询方法，但是比较麻烦，惭愧，我用子查询有的还查不出来呢。

语法如下：

lag(value_expression [,offset] [,default])over ([query_partition_clase] order_by_clause)；
lead(value_expression [,offset] [,default]) over ([query_partition_clase]order_by_clause)；
其中：
value_expression：可以是一个字段或一个内建函数。
offset是正整数，默认为1，指往前或往后几点记录．因组内第一个条记录没有之前的行，最后一行没有之后的行，
default就是用于处理这样的信息，默认为空。你

再讲讲所谓的开窗函数，依本人遇见，开窗函数就是over([query_partition_clase] order_by_clause)。比如说，我采用sum求和，rank排序等等，但是我根据什么来呢？over提供一个窗口，可以根据什么什么分组，就用partitionby，然后在组内根据什么什么进行内部排序，就用 order by。

这就是我理解的开窗函数。好了本文先写到这，以后再有什么心得体会再来补充。

拉链表

一、概念

拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。

在历史表中对客户的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题：

（NAME）人名（START-DATE）开始日期（END-DT）结束日期（STAT）状态

client         19000101               19070901         H在家
client          19070901                19130901          A小学
client          19130901                19160901          B初中
client          19160901                19190901          C高中
client          19190901                19230901          D大学
client          19230901                19601231          E公司
client          19601231                29991231          H退休在家

上面的每一条记录都是不算末尾的，比如到19070901，client已经在A，而不是H了。所以除最后一条记录因为状态到目前都未改变的，其余的记录实际上在END-DT那天，都不在是该条记录END-DT那天的状态。这种现象可以理解为算头不算尾。

二、算法

1、采集当日全量数据到ND(NewDay)表；

2、可从历史表中取出昨日全量数据存储到OD(OldDay)表;

3、(ND-OD)就是当日新增和变化的数据，也就是当天的增量，用W_I表示；

4、(OD-ND)为状态到此结束需要封链的数据，用W_U表示；

5、将W_I表的内容全部插入到历史表中，这些是新增记录，start_date为当天，而end_date为max值；

6、对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天，也就是关链操作；

拉链表实际上是一个数据的有效更新处理方法。在定义了对于该方法支持的几个字段后可以对数据进行处理。

讲解一个加了几个字段的的一种处理方法

模拟场景

1、定义两个临时表，一个为当日全量数据，另一个为需要新增或更新的数据；

CREATE TABLEA_day_full;

CREATE TABLE B;

2、获取当日全量数据

INSERTINTO A SELECT (a,b,c,cur_date, max_date) FROMSOURCE_Table

3、抽取新增或有变化的数据，从A临时表到B临时表；

INSERTINTO B SELECTfieldname FROM A WHERE NOT IN (selectfieldname from A_HIS where end_date='max_date');

4、更新历史表的失效记录的end_date为max值

UPDATEA1 FROM a_his A1, B A2 SETEnd_Date='current_date' WHERE A1.xx=A2.xx AND A1.End_Date='max_date';

5、将新增或者有变化的数据插入目标表*/

INSERT INTOA_HIS SELECT * FROM B;

本系统拉链表：

1.从交易明细表把本月数据取到一个临时表tmp；

2.从f_cr_loan_card把上月最后一天的数据取到tmp表,当做本月的期初数（如果本月为一月，就取上期余额非0的数据，如果本月为非一月，就取上期的所有值）

3.将f_cr_loan_card把上月最后一天的数据的日期转化为本月第一天

4.将tmp表的数据的end_date改为null，将tmp表的数据插入到tmp1

5.将tmp1的数据插入到f_cr_loan_card

动态SQL

使用动态SQL是在编写PL/SQL过程时经常使用的方法之一。很多情况下，比如根据业务的需要，如果输入不同查询条件，则生成不同的执行SQL查询语句，对于这种情况需要使用动态SQL来完成。再比如，对于分页的情况，对于不同的表，必定存在不同的字段，因此使用静态SQL则只能针对某几个特定的表来形成分页。而使用动态的SQL，则可以对不同的表，不同的字段进行不同的分页。这些情况的处理通常都是用动态SQL来完成。本文讲述了动态SQL的日常用法。

一、动态SQL和静态SQL

1.静态SQL

静态SQL通常用于完成可以确定的任务。比如传递部门号调用存储过程，返回该部门的所有雇员及薪水信息，则该语句为

SELECT ename，sal INTO lv_ename,lv_sal FROM scott.emp WHERE deptno=&dno;

对于上述类似的DML语句在第一次运行时进行编译，而后续再次调用，则不再编译该过程。即一次编译，多次调用，使用的相同的执行

计划。此种方式被称之为使用的是静态的SQL。

2.动态SQL

动态SQL通常是用来根据不同的需求完成不同的任务。比如分页查询，对于表emp分页，需要使用字段雇员姓名，薪水，雇用日期，且按

薪水降序生成报表，每页显示行数据。而对于表sales，需要使用字段雇员名称，客户名称，销售数量，销售日期，且按销售日期升序

排列。以上两种情况，可以创建存储过程来对其进行分页，通过定义变量，根据输入不同的表名，字段名，排序方法来生成不同的SQL

语句。对于输入不同的参数，SQL在每次运行时需要事先对其编译。即多次调用则需要多次编译，此称之为动态SQL。

动态SQL语句通常存放在字符串变量中，且SQL语句可以包含占位符(使用冒号开头)。

也可以直接将动态SQL紧跟在EXECUTE IMMEDIATE语句之后，如EXECUTE IMMEDIATE 'alter table emp enable row movement'

3.两者的异同

静态SQL为直接嵌入到PL/SQL中的代码，而动态SQL在运行时，根据不同的情况产生不同的SQL语句。

静态SQL为在执行前编译，一次编译，多次运行。动态SQL同样在执行前编译，但每次执行需要重新编译。

静态SQL可以使用相同的执行计划，对于确定的任务而言，静态SQL更具有高效性。但缺乏灵活性

动态SQL使用了不同的执行计划，效率不如静态SQL，但能够解决复杂的问题。

动态SQL容易产生SQL注入，为数据库安全带来隐患。

4.动态SQL语句的几种方法

a.使用EXECUTE IMMEDIATE语句

包括DDL语句，DCL语句，DML语句以及单行的SELECT 语句。该方法不能用于处理多行查询语句。

b.使用OPEN-FOR，FETCH和CLOSE语句

对于处理动态多行的查询操作，可以使用OPEN-FOR语句打开游标，使用FETCH语句循环提取数据，最终使用CLOSE语句关闭游标。

c.使用批量动态SQL

即在动态SQL中使用BULK子句，或使用游标变量时在fetch中使用BULK ，或在FORALL语句中使用BULK子句来实现。

d.使用系统提供的PL/SQL包DBMS_SQL来实现动态SQL，关于该方式请参考后续博文。

二、动态SQL的语法

下面是动态SQL常用的语法之一

EXECUTEIMMEDIATE dynamic_SQL_string

[INTOdefined_variable1, defined_variable2, ...]

[USING[IN | OUT | IN OUT] bind_argument1, bind_argument2,

...][{RETURNING| RETURN} field1, field2, ... INTO bind_argument1,

bind_argument2,...]

1.语法描述

dynamic_SQL_string：存放指定的SQL语句或PL/SQL块的字符串变量

defined_variable1：用于存放单行查询结果，使用时必须使用INTO关键字，类似于使用SELECT ename INTO v_name FROM scott.emp；

只不过在动态SQL时，将INTOdefined_variable1移出到dynamic_SQL_string语句之外。

bind_argument1：用于给动态SQL语句传入或传出参数，使用时必须使用USING关键字，IN表示传入的参数，OUT表示传出的参数，

IN OUT则既可以传入，也可传出。

RETURNING| RETURN 子句也是存放SQL动态返回值的变量。

2.使用要点

a.EXECUTEIMMEDIATE执行DML时，不会提交该DML事务，需要使用显示提交(COMMIT)或作为EXECUTE IMMEDIATE自身的一部分。

b.EXECUTEIMMEDIATE执行DDL,DCL时会自动提交其执行的事务。

c.对于多行结果集的查询，需要使用游标变量或批量动态SQL，或者使用临时表来实现。

d.当执行SQL时，其尾部不需要使用分号，当执行PL/SQL 代码时，其尾部需要使用分号。

f.动态SQL中的占位符以冒号开头，紧跟任意字母或数字表示。

三、动态SQL的使用(DDL，DCL，DML以及单行结果集)

1.使用EXECUTE IMMEDIATE处理DDL操作

下面是一个简单的DDL操作，将其封装在存储过程之中，通过传入表名来进行调用。

CREATE OR REPLACE PROCEDURE trunc_table(table_nameVARCHAR2) --创建存储过程trunc_table

sql_statementVARCHAR2(100);

BEGIN

sql_statement := 'TRUNCATETABLE' || table_name; --为变量进行赋值，用于生成动态SQL语句

EXECUTE IMMEDIATEsql_statement; --使用EXECUTE IMMEDIATE执行动态SQL语句

END;

flasher@ORCL> create table tb2 --从scott.emp生产表tb2

2 as select empno,ename,sal,deptno from scott.emp;

flasher@ORCL> select count(1) from tb2;

COUNT(1)

----------

flasher@ORCL> exec trunc_table('tb2'); --调用存储过程来对表tb2进行truncate

flasher@ORCL> select count(1) from tb2; --表tb2被清空

COUNT(1)

----------

flasher@ORCL> insert into tb2 --重新为表tb2生成记录

2 select empno,ename,sal,deptno from scott.emp;

flasher@ORCL> commit;

2.使用EXECUTE IMMEDIATE处理DCL操作

下面使用sys帐户创建存储过程grant_sys_priv用于给用户授予权限

sys@ORCL> connsys/redhat@orcl as sysdba

CREATE OR REPLACE PROCEDURE grant_sys_priv(privVARCHAR2, username VARCHAR2)

sql_statVARCHAR2(100);

BEGIN

sql_stat := 'GRANT' || priv || ' TO ' || username;

EXECUTE IMMEDIATEsql_stat;

END;

sys@ORCL> exec grant_sys_priv('connect','usr1');

3.使用EXECUTE IMMEDIATE处理DML操作

在使用EXECUTE IMMEDIATE处理DML操作时，分为几种情况，即不带输入参数，带输入参数，既有输入也有输出参数或返回参数等不同情

况，下面分别对其描述。

a.没有参数传入传出的DML语句

下面的示例中，使用动态SQL删除一条记录，且未使用参数传入。

flasher@ORCL> select * from tb2 where empno=7900; --删除前

EMPNOENAME SAL DEPTNO

-------------------- ---------- ----------

7900JAMES 950 30

flasher@ORCL> DECLARE sql_statVARCHAR2(100);

2 BEGIN

3 sql_stat:='DELETEFROM flasher.tb2 WHERE empno=7900'; --使用动态SQL来删除记录

4 EXECUTE IMMEDIATEsql_stat;

5 END;

6 /

flasher@ORCL> SELECT * FROM tb2 where empno=7900; --验证删除情况

no rowsselected

b.有参数传入的DML语句(使用USING子句)

对于使用了参数传入的动态SQL，需要使用USING子句来指明传入的参数。在下面的示例中，为表tb2插入一条记录，在DML语句中使

用了四个占位符(占位符用以冒号开头，紧跟任意字母或数字表示)。因此在使用EXECUTE IMMEDIATE使用USING子句为其指定其参数。

DECLARE --声明变量

sql_statVARCHAR2(100);

lv_empnotb2.empno%TYPE := 7900;

lv_enametb2.ename%TYPE := 'JAMES';

lv_sal tb2.sal%TYPE := 950;

BEGIN

sql_stat := 'INSERTINTO tb2VALUES(:1,:2,:3,:4)'; --DML语句中使用了占位符

EXECUTE IMMEDIATEsql_stat USING lv_empno, lv_ename,lv_sal,30; --为占位符指定参数或值

COMMIT;

END;

flasher@ORCL> select * from tb2 where empno=7900; --验证插入后的结果

EMPNOENAME SAL DEPTNO

-------------------- ---------- ----------

7900JAMES 950 30

c.处理包含returning子句的DML语句

下面的示例中，对表tb2进行更新，使用了两个占位符，一个是:percent，一个是:eno，因此在使用EXECUTE IMMEDIATE执行动态

DML时，需要使用USING子句且带两个输入参数。其次，动态DML中使用了RETURNING sal INTO :salary，因此EXECUTE IMMEDIATE后

也必须使用RETURNING INTO varialbe_name。

DECLARE

salaryNUMBER(6, 2);

sql_statVARCHAR2(100);

BEGIN

sql_stat := 'UPDATEtb2 SET sal = sal * (1 + :percent / 100)' --更新sal列，使用占位符:percent

|| 'WHERE empno = :eno RETURNING sal INTO :salary'; --使用了占位符:eno，:salary，以及RETURNING子句

EXECUTE IMMEDIATEsql_stat USING &1, &2RETURNING INTO salary; --必须使用USING及RETURNING子句

COMMIT;

dbms_output.put_line('Newsalary: ' || salary);

END;

Enter value for 1: 10

Enter value for 2: 7900

old 7: EXECUTE IMMEDIATEsql_stat USING &1, &2 RETURNING INTO salary;

new 7: EXECUTE IMMEDIATEsql_stat USING 10, 7900 RETURNING INTO salary;

Newsalary: 1045

d.处理包含检索值的单行查询

下面的示例中，使用SELECT 查询获得单行结果集，使用了占位符:name，因此也需要使用USING子句为其传递参数

DECLARE

sql_statVARCHAR2(100);

emp_recordtb2%ROWTYPE;

BEGIN

sql_stat := 'SELECT* FROM tb2 WHERE ename = UPPER(:name)'; --动态SQL语句为单行DQL语句

EXECUTE IMMEDIATEsql_stat INTO emp_recordUSING '&name'; --使用USING子句为其传递参数

DBMS_OUTPUT.PUT_LINE('Thesalary is ' || emp_record.sal || ' for '||emp_record.ename);

END;

Enter value for 1: james

old 6: EXECUTE IMMEDIATEsql_stat INTO emp_record USING '&1';

new 6: EXECUTE IMMEDIATEsql_stat INTO emp_record USING 'james';

Thesalary is 1045 for JAMES

四、动态SQL的使用(处理多行结果集的查询语句)

1.使用游标变量来循环提取数据，其主要流程为

定义游标变量

TYPE cursortype IS REF CURSOR;

cursor_variablecursortype;

打开游标变量

OPEN cursor_variable FOR dynamic_string

[USINGbind_argument[,bind_argument]...]

循环提取数据

FETCH cursor_variable INTO {var1[,var2]...| record_variable};

EXIT WHEN cursor_variable%NOTFOUND

关闭游标变量

CLOSE cursor_variable;

2.使用游标变量处理查询多行结果集

下面的示例中，首先定义了一个游标类型，接下来定义游标变量，以及存放结果集的变量，动态查询语句将获得多个结果集。

OPEN cursorname FOR SELECT ... 时，其SELECT 语句使用了字符串变量(动态SQL)，其后紧跟USING子句。

DECLARE --游标，变量的声明

TYPE emp_cur_type IS REF CURSOR;

emp_cv emp_cur_type;

emp_record tb2%ROWTYPE;

sql_stat VARCHAR2(100);

v_dno NUMBER := &dno;

BEGIN

sql_stat := 'SELECT* FROM tb2 WHERE deptno = :dno'; --动态多行结果集查询语句

OPEN emp_cv FOR sql_statUSINGv_dno; --OPEN 时使用动态查询语句以及USING子句来传递参数

LOOP

FETCH emp_cv INTO emp_record; --从结果集中提取记录

EXIT WHEN emp_cv%NOTFOUND;

dbms_output.put_line('Employeename：' || emp_record.ename || ', Salary：' || emp_record.sal);

END LOOP;

CLOSE emp_cv;

END;

Employee name：Henry, Salary：

Employee name：JONES, Salary：

Employee name：ADAMS, Salary：

Employee name：FORD, Salary：

五、动态SQL的使用(FORALL及BULK子句的使用)

1.动态SQL中使用BULK子句的语法

EXECUTEIMMEDIATEdynamic_string --dynamic_string用于存放动态SQL字符串

[BULKCOLLECT INTO define_variable[,define_variable...]] --存放查询结果的集合变量

[USINGbind_argument[,argument...]] --使用参数传递给动态SQL

[{RETURNING|RETURN} --返回子句

BULKCOLLECT INTO return_variable[,return_variable...]]; --存放返回结果的集合变量

使用bulk collect into子句处理动态SQL中的多行查询可以加快处理速度，从而提高应用程序的性能。当使用bulk子句时，集合类型可

以是PL/SQL所支持的索引表、嵌套表和VARRY，但集合元素必须使用SQL数据类型。常用的三种语句支持BULK子句，分别为EXECUTE

IMMEDIATE， FETCH 和FORALL。

2.使用EXECUTE IMMEDIATE 结合BULK子句处理DML语句返回子句

下面的例子，首先定义了两个索引表类型以及其变量，接下来使用动态SQL语句来更新tb2的薪水，使用EXECUTE IMMEDIATE配合BULK

COLLECTINTO 来处理结果集。

DECLARE

TYPE ename_table_type IS TABLE OF tb2.ename%TYPE INDEX BY BINARY_INTEGER; --定义类型用于存放结果集

TYPE sal_table_type IS TABLE OF tb2.sal%TYPE INDEX BY BINARY_INTEGER;

ename_tableename_table_type;

sal_tablesal_table_type;

sql_statVARCHAR2(120);

v_percentNUMBER :=&percent;

v_dno NUMBER :=&dno;

BEGIN

sql_stat := 'UPDATEtb2 SET sal = sal * (1 + :percent /100)' --动态DML语句

|| 'WHERE deptno = :dno'

|| 'RETURNING ename, sal INTO :name,:salary'; --使用了RETURNING子句，有返回值

EXECUTE IMMEDIATEsql_stat USING v_percent, v_dno --执行动态SQL语句

RETURNING BULK COLLECT INTO ename_table, sal_table; --使用BULK COLLECT INTO到集合变量

FOR i IN 1..ename_table.COUNT --使用FOR循环读取集合变量的结果

LOOP

DBMS_OUTPUT.PUT_LINE('Employee' || ename_table(i) || ' Salary is:' || sal_table(i));

END LOOP;

END;

EmployeeHenry Salary is: 1694

EmployeeJONES Salary is: 3841.75

EmployeeADAMS Salary is: 1573

EmployeeFORD Salary is: 3872

3.使用EXECUTE IMMEDIATE 结合BULK子句处理多行查询

下面示例中，与前一个示例相同，只不过其动态SQL有查询语句组成，且返回多个结果集，同样使用了BULKCOLLECT INTO来传递结果。

DECLARE

TYPE ename_table_type IS TABLE OF tb2.ename%TYPE INDEX BY BINARY_INTEGER; --定义类型用于存放结果集

TYPE sal_table_type IS TABLE OF tb2.sal%TYPE INDEX BY BINARY_INTEGER;

ename_tableename_table_type;

sal_tablesal_table_type;

sql_statVARCHAR2(100);

BEGIN

sql_stat := 'SELECTename,sal FROM tb2 WHERE deptno =:dno'; --动态DQL语句，未使用RETURNING子句

EXECUTE IMMEDIATEsql_stat BULK COLLECT INTO ename_table,sal_tableUSING &dno; --使用BULK COLLECT INTO

FOR i IN 1..ename_table.COUNT

LOOP

DBMS_OUTPUT.PUT_LINE('Employee' || ename_table(i) || ' Salary is:' || sal_table(i));

END LOOP;

END;

EmployeeHenry Salary is: 1694

EmployeeJONES Salary is: 3841.75

EmployeeADAMS Salary is: 1573

EmployeeFORD Salary is: 4259.2

4.使用FETCH子句结合BULK子句处理多行结果集

下面的示例中首先定义了游标类型，游标变量以及复合类型，复合变量，接下来从动态SQL中OPEN游标，然后使用FETCH将结果存放到复

合变量中。即使用OPEN，FETCH代替了EXECUTE IMMEDIATE来完成动态SQL的执行。

DECLARE

TYPE empcurtype IS REF CURSOR; --定义游标类型及游标变量

emp_cvempcurtype;

TYPE ename_table_type IS TABLE OF tb2.ename%TYPE INDEX BY BINARY_INTEGER; --定义结果集类型及变量

ename_tableename_table_type;

sql_stat VARCHAR2(120);

BEGIN

sql_stat := 'SELECTename FROM tb2 WHERE deptno = :dno'; --动态SQL字符串

OPEN emp_cv FOR sql_stat --从动态SQL中打开游标

USING &dno;

FETCH emp_cv BULK COLLECT --使用BULK COLLECT INTO提取结果集

INTO ename_table;

FOR i IN 1 .. ename_table.COUNT LOOP

DBMS_OUTPUT.PUT_LINE('EmployeeName is ' || ename_table(i));

END LOOP;

CLOSE emp_cv;

END;

Employee Name is Henry

Employee Name is JONES

Employee Name is ADAMS

Employee Name is FORD

5.使用FORALL语句中使用BULK子句

下面是FORALL子句的语法

FORALLindex IN lower bound..upperbound --FORALL循环计数

EXECUTEIMMEDIATE dynamic_string --结合EXECUTE IMMEDIATE来执行动态SQL语句

USINGbind_argument | bind_argument(index) --绑定输入参数

[bind_argument| bind_argument(index)]...

[{RETURNING| RETURN} BULK COLLECT INTO bind_argument[,bind_argument...]]; --绑定返回结果集

FORALL子句允许为动态SQL输入变量，但FORALL子句仅支持DML(INSERT,DELETE,UPDATE)语句，不支持动态的SELECT语句。

下面的示例中，首先声明了两个复合类型以及复合变量，接下来为复合变量ename_table赋值，以形成动态SQL语句。紧接着使用FORALL

子句结合EXECUTE IMMEDIATE 来提取结果集。

DECLARE --定义复合类型及变量

TYPE ename_table_type IS TABLE OF tb2.ename%TYPE;

TYPE sal_table_type IS TABLE OF tb2.sal%TYPE;

ename_tableename_table_type;

sal_tablesal_table_type;

sql_statVARCHAR2(100);

BEGIN

ename_table := ename_table_type('BLAKE', 'FORD', 'MILLER'); --为复合类型赋值

sql_stat := 'UPDATEtb2 SET sal = sal * 1.1 WHERE ename = :1' --定义动态SQL语句

|| 'RETURNING sal INTO :2';

FORALLi IN 1..ename_table.COUNT --为FORALL 设定起始值

EXECUTE IMMEDIATEsql_stat USING ename_table(i) --使用EXECUTE IMMEDIATE 结合RETURNING BULK COLLECT INTO获取结果集

RETURNING BULK COLLECT INTO sal_table;

FOR j IN 1..ename_table.COUNT

LOOP

DBMS_OUTPUT.PUT_LINE('Thenew salary is ' || sal_table(j) || ' for' ||ename_table(j)) ;

END LOOP;

END;

Thenew salary is 3135 for BLAKE

Thenew salary is 4259.2 for FORD

Thenew salary is 1760 for MILLER

Merge into

Merge into table1

Using table2 on(table1.num = table2.num)

When matched then

Update

Set table1.bbq =table2.bbq

Where table1.g = 222

When not matched then

Insert

Values(110,1,g,b)

Where table2.g=11;

行列转换函数

pivot 行转列

测试数据 (id，类型名称，销售数量)，案例：根据水果的类型查询出一条数据显示出每种类型的销售数量。

create table demo(id int,name varchar(20),nums int); ---- 创建表

insert into demo values(1, '苹果', 1000);

insert into demo values(2, '苹果', 2000);

insert into demo values(3, '苹果', 4000);

insert into demo values(4, '橘子', 5000);

insert into demo values(5, '橘子', 3000);

insert into demo values(6, '葡萄', 3500);

insert into demo values(7, '芒果', 4200);

insert into demo values(8, '芒果', 5500);

Select * from (select name,nums from demo)pivot(sum(num)for name in())

分组查询（当然这是不符合查询一条数据的要求的）

1	select name, sum(nums) nums from demo group by name

行转列查询

1	select * from (select name, nums from demo) pivot (sum(nums) for name in ('苹果' 苹果, '橘子', '葡萄', '芒果'));

注意： pivot（聚合函数 for 列名 in（类型）），其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 select distinct code from customers

当然也可以不使用pivot函数，等同于下列语句，只是代码比较长，容易理解

unpivot 列转行

顾名思义就是将多列转换成1列中去
案例：现在有一个水果表，记录了4个季度的销售数量，现在要将每种水果的每个季度的销售情况用多行数据展示。

创建表和数据

create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);

insert into Fruit values(1,'苹果',1000,2000,3300,5000);

insert into Fruit values(2,'橘子',3000,3000,3200,1500);

insert into Fruit values(3,'香蕉',2500,3500,2200,2500);

insert into Fruit values(4,'葡萄',1500,2500,1200,3500);

select * from Fruit

列转行查询

1	select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )

注意： unpivot没有聚合函数，xiaoshou、jidu字段也是临时的变量

列转行：

使用over(partition byt.u_id)用法

SELECT NAME,wmsys.wm_concat(course||score) FROM kecheng group BY NAME

列转行（没有现成的函数调用，直接用union把每条查出来的数据关联起来即可）：

1、集合查询

实现的SQL语句：

select id,name,'语文' course,chinesescore from lie2hang

union

select id,name,'数学' course,math scorefrom lie2hang

union

select id,name,'英语' course,englishscore from lie2hang

union

select id,name,'历史' course,historyscore from lie2hang

union

select id,name,'化学' course,chemistryscore from lie2hang;

Vm_concat函数：

oracle wm_concat(column)函数使我们经常会使用到的，使用oracle wm_concat(column)函数实现字段合并。

原数据显示：

U_ID GOODS NUM

--------- -------- ----

1 苹果 2

2 梨子 5

1 西瓜 4

3 葡萄 1

3 香蕉 1

1 橘子 3

create table shopping(u_id int,goods varchar2(100),num int);

insert into shopping values(1,'苹果',2);

insert into shopping values(2,'梨子',5);

insert into shopping values(1,'西瓜',4);

insert into shopping values(3,'葡萄',1);

insert into shopping values(3,'香蕉',1);

insert into shopping values(1,'橘子',3);

commit;

想要的结果1:

#########################################

U_ID GOODS_SUM

-------------- -----------------

1 苹果,西瓜,橘子

2 梨子

3 葡萄,香蕉

#########################################

colgoods_sum format a20;

select u_id, wm_concat(goods)goods_sum from shopping group by u_id;

想要的结果2:

#########################################

U_ID GOODS_SUM

-------- --------------------------------

1 苹果(2斤),西瓜(4斤),橘子(3斤)

2 梨子(5斤)

3 葡萄(1斤),香蕉(1斤)

#########################################

select u_id, wm_concat(goods || '(' || num|| '斤)' ) goods_sum from shopping group by u_id ;

LISTAGG

2，测试数据

SQL>

SQL> select empno,ename,deptno fromscott.emp;

EMPNO ENAME DEPTNO

----- ---------- ------

7369 SMITH 20

7499 ALLEN 30

7521 WARD 30

7566 JONES 20

7654 MARTIN 30

7698 BLAKE 30

7782 CLARK 10

7788 SCOTT 20

7839 KING 10

7844 TURNER 30

7876 ADAMS 20

7900 JAMES 30

7902 FORD 20

7934 MILLER 10

14 rows selected

3，作为聚集函数

SQL> SELECT deptno,

2 LISTAGG(ename, ',') WITHIN GROUP(ORDER BYename) AS employees

3 FROM scott.emp

4 GROUP BY deptno;

DEPTNO EMPLOYEES

--------------------------------------------------------------------------------------

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>

--更换排序列

SQL> SELECT deptno,

2 LISTAGG(ename, ',') WITHIN GROUP(ORDER BYhiredate) AS employees

3 FROM scott.emp

4 GROUP BY deptno;

DEPTNO EMPLOYEES

------ --------------------------------------------------------------------------------

10 CLARK,KING,MILLER

20 SMITH,JONES,FORD,SCOTT,ADAMS

30 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES

--order by必须存在

SQL> SELECT deptno,

2 LISTAGG(ename, ',') WITHIN GROUP() ASemployees

3 FROM scott.emp

4 GROUP BY deptno;

SELECT deptno,

LISTAGG(ename, ',') WITHIN GROUP() ASemployees

FROM scott.emp

GROUP BY deptno

ORA-30491: ORDER BY 子句缺失

SQL> SELECT deptno,

2 LISTAGG(ename, ',') WITHIN GROUP(order bynull) AS employees

3 FROM scott.emp

4 GROUP BY deptno;

DEPTNO EMPLOYEES

--------------------------------------------------------------------------------------

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

==〉按字母顺序排列

4，LISTAGG作为分析函数使用

SQL> SELECT empno,

2 ename,

3 deptno,

4 LISTAGG(ename, ',') WITHIN GROUP(ORDER BYename) over(partition by deptno) AS employees

5 FROM scott.emp;

EMPNO ENAME DEPTNO EMPLOYEES

----- ---------- --------------------------------------------------------------------------------------

7782 CLARK 10 CLARK,KING,MILLER

7839 KING 10 CLARK,KING,MILLER

7934 MILLER 10 CLARK,KING,MILLER

7876 ADAMS 20 ADAMS,FORD,JONES,SCOTT,SMITH

7902 FORD 20 ADAMS,FORD,JONES,SCOTT,SMITH

7566 JONES 20 ADAMS,FORD,JONES,SCOTT,SMITH

7788 SCOTT 20 ADAMS,FORD,JONES,SCOTT,SMITH

7369 SMITH 20 ADAMS,FORD,JONES,SCOTT,SMITH

7499 ALLEN 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

7698 BLAKE 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

7900 JAMES 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

7654 MARTIN 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

7844 TURNER 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

7521 WARD 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

14 rows selected

5,其他实现方法参考

--model

SQL> SELECT deptno, vals

2 FROM (SELECT deptno, RTRIM(vals, ',') ASvals, rn

3 FROM scott.emp MODEL PARTITION BY(deptno)DIMENSION BY(ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) AS rn)MEASURES(CAST(ename AS VARCHAR2(4000)) AS vals) RULES(vals [ ANY ] ORDER BY rnDESC = vals [ CV() ] || ',' || vals [ CV() + 1 ]))

4 WHERE rn = 1

5 ORDER BY deptno;

DEPTNO VALS

------------------------------------------------------------------------------------------

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

--表函数：WMSYS.WM_CONCAT，10G已经提供该函数

SQL>

SQL> SELECT deptno, WMSYS.WM_CONCAT(ename)AS vals --<-- WM_CONCAT ~= STRAGG

2 FROM scott.emp

3 GROUP BY deptno;

DEPTNO VALS

--------------------------------------------------------------------------------------

10 CLARK,MILLER,KING

20 SMITH,FORD,ADAMS,SCOTT,JONES

30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

索引

三、唯一索引

1、何时创建：当某列任意两行的值都不相同

2、当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立

3、语法：CREATE UNIQUE INDEX index ON table (column);

4、演示

四、组合索引

1、何时创建：当两个或多个列经常一起出现在where条件中时，则在这些列上同时创建组合索引

2、组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面

3、演示(组合列，单独列)

五、位图索引

1、何时创建：

列中有非常多的重复的值时候。例如某列保存了“性别”信息。

Where 条件中包含了很多OR操作符。

较少的update操作，因为要相应的跟新所有的bitmap

2、结构：位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。

3、优点：位图以一种压缩格式存放，因此占用的磁盘空间比标准索引要小得多

4、语法：CREATE BITMAP INDEX index ON table (column[, column]...);

5、掩饰：

create table bitmaptable as select * fromindextable where owner in('SYS','PUBLIC');

分析，查找，建立索引，查找

六、基于函数的索引

1、何时创建：在WHERE条件语句中包含函数或者表达式时

2、函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。

3、语法：CREATE INDEX index ON table (FUNCTION(column));

4、演示

必须要分析表，并且query_rewrite_enabled=TRUE

或者使用提示/*+INDEX(ic_index)*/

七、反向键索引

目的：比如索引值是一个自动增长的列：

多个用户对集中在少数块上的索引行进行修改，容易引起资源的争用，比如对数据块的等待。此时建立反向索引。

性能问题：

语法：

重建为标准索引：反之不行

八、键压缩索引

比如表landscp的数据如下：

site feature job

Britten Park, Rose Bed 1, Prune

Britten Park, Rose Bed 1, Mulch

Britten Park, Rose Bed 1,Spray

Britten Park, Shrub Bed 1, Mulch

Britten Park, Shrub Bed 1, Weed

Britten Park, Shrub Bed 1, Hoe

……

查询时，以上3列均在where条件中同时出现，所以建立基于以上3列的组合索引。但是发现重复值很多，所以考虑压缩特性。

Create index zip_idx

on landscp(site, feature, job)

compress 2;

将索引项分成前缀(prefix)和后缀(postfix)两部分。前两项被放置到前缀部分。

Prefix 0: Britten Park, Rose Bed 1

Prefix 1: Britten Park, Shrub Bed 1

实际所以的结构为：

0 Prune

0 Mulch

0 Spray

1 Mulch

1 Weed

1 Hoe

特点：组合索引的前缀部分具有非选择性时，考虑使用压缩。减少I/O,增加性能。

九、索引组织表(IOT)

将表中的数据按照索引的结构存储在索引中，提高查询速度。

牺牲插入更新的性能，换取查询性能。通常用于数据仓库，提供大量的查询，极少的插入修改工作。

必须指定主键。插入数据时，会根据主键列进行B树索引排序，写入磁盘。

十、分区索引

簇:

A cluster is a group of tables that sharethe same data blocks because they share common columns and are often used together.

Decode、case when 、nvl：

DECODE:

DECODE(字段或字段的运算，值1，值2，值3）

这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式;

CASE WHEN:

--简单Case函数

CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '其他' END

--Case搜索函数

CASE

WHEN sex = '1' THEN '男'

WHEN sex = '2' THEN '女'

ELSE '其他' END

SELECT

CASE WHEN salary <= 500 THEN '1'

WHEN salary > 500 AND salary <=600 THEN '2'

WHEN salary > 600 AND salary <=800 THEN '3'

WHEN salary > 800 AND salary <= 1000THEN '4'

ELSE NULL END salary_class

UPDATE Personnel

SET salary =

CASE WHEN salary >= 5000 THEN salary * 0.9

WHEN salary >= 2000 AND salary <4600 THEN salary * 1.15

ELSE salary END

NVL:

NVL函数的格式如下：NVL(expr1,expr2)

含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

例如：

SQL> select ename,NVL(comm, -1) fromemp;

数据导入导出数据库的几种方式与区别:

1.pl/sqldeveloper中tools - exporttables/import tables 和tools - export-user objects;

2.Exp/imp命令导出导入；

3.数据泵导出导入；

区别：

第一种方式界面化操作、操作简便、适用于小数据量的数据导入导出，当导入导出大数据量时会导致developer的界面卡死；

第二种方式适用于比第一种方式更大数据量的导出导入，相对于数据泵的方式，命令更加简单；

第三种方式效率更高，功能更全，但命令更加复杂，操作更加复杂；

数据泵是服务器端工具，exp是客户端工具，所以数据泵操作更加安全可靠，不怕session中断；

delete，truncate,drop的区别与特点

第一：相同点：
truncate和不带where子句的delete，以及drop 都会删除表内的数据
第二：不同点：
1. truncate和delete只删除数据不删除表的结构(定义)
    drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index);依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
2. delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的trigger执行的时候将被触发。DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
truncate、drop 是数据库定义语言(ddl),操作立即生效，原数据不放到rollbacksegment中，不能回滚，操作不触发 trigger。
3.delete 语句不影响表所占用的extent，高水线(high watermark)保持原位置不动；
   显然drop 语句将表所占用的空间全部释放；   truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage;truncate 会将高水线复位(回到最开始)。
4.速度：一般来说: drop> truncate > delete
5.安全性：小心使用 drop 和 truncate，尤其没有备份的时候.，否则哭都来不及。
6.使用上：想删除部分数据行用delete,注意带上where子句. 回滚段要足够大。
                 想删除表，当然用drop。
                 想保留表而将所有数据删除，如果和事务无关,用truncate即可；如果和事务有关,或者想触发trigger，还是用delete。
                 如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

Union、union all、minus:

Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则（按第一个字段进行排序）的排序；

Union All，对两个结果集进行并集操作，包括重复行，不进行排序；

Intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

Minus，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

可以在最后一个结果集中指定Order by子句改变排序方式。

Connect by

CONNECT BY 为查找的条件。

基本语法

select * from table [start with condition1]

connect by[prior] id=parentid

select t.*,level, CONNECT_BY_ROOT(id)

from tab_test t

start with t.id = 0 connect by prior t.id =t.fid;

一般用来查找存在父子关系的数据，也就是树形结构的数据；其返还的数据也能够明确的区分出每一层的数据。

start with condition1 是用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。

connect by [prior] id=parentid 这部分是用来指明oracle在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理查找第三层第四层…等等都是按这样去匹配。

prior还有一种用法：

select * from table [start with condition1]

connect by id= [prior] parentid

这种用法就表示从下往上查找数据，可以理解为从叶子节点往上查找父级几点，用第一层数据的parentid去跟表记录里面的id进行匹配，匹配成功那么查找出来的就是第二层数据；上面的那种就是从父级节点往下查找叶子节点。

其他特性

level关键字，代表树形结构中的层级编号；第一层是数字1，第二层数字2，依次递增。

CONNECT_BY_ROOT方法，能够获取第一层集结点结果集中的任意字段的值；例CONNECT_BY_ROOT（字段名）。

select ename,job,LEVEL from scott.empemp start with ename in ('KING') CONNECT BY PRIOR EMPNO = MGR ANDLEVEL<=3;

with子查询、rowid,rownum,dual

Rowid：ROWID 是一个类似于rownum的伪列，用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。而对于聚簇表，由于聚簇特性，不同表上的记录由于存储在相同的簇上，因此会拥有相同的ROWID。数据库的大多数操作都是通过ROWID来完成的，而且使用ROWID来进行单记录定位速度是最快的。

ROWID的特性组成及用途
  1、特性
      相对唯一性(聚簇表上不唯一)
      一旦确定，不可随意更改
      使用10个字节存储(扩展rowid)，显示为18位的字符串
      特殊情况下，ROWID会发生变化(如下列情形)
         表的导入导出操作
         alter table tab_name move
         alter table tab_name shrinkspace
         flashback table tab_name
         拆分分区表
         分区表上更新一个值后记录被移入到新分区
         合并两个分区
  2、组成(扩展ROWID)
     数据库对象的对象编号
     数据库对象所在文件的文件编号
     数据库对象上块的编号
     块上的行编号(起始值为0)
  3、用途
     快速定位单行记录
      展示行在表上如何存储
      表上的一行的唯一标识符
      用作数据类型column_name rowid

Rownum:

ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1，而有其他大于1的值。

任何时候想把 rownum = 1 这条记录抛弃是不对的，它在结果集中是不可或缺的，少了rownum=1 就像空中楼阁一般不能存在，所以你的 rownum 条件要包含到 1

但如果就是想要用 rownum > 10 这种条件的话话就要用嵌套语句,把 rownum 先生成，然后对他进行查询。

select *
from (selet rownum as rn，t1.* from a where ...)
where rn >10

Dual:

Oracle中的dual表是一个单行单列的虚拟表。
3. Dual表是oracle与数据字典一起自动创建的一个表，这个表只有1列：DUMMY，数据类型为VERCHAR2(1)，dual表中只有一个数据'X', Oracle有内部逻辑保证dual表中永远只有一条数据。

4. Dual表主要用来选择系统变量或求一个表达式的值。

with子查询：

with table as 相当于建个临时表，将一个语句中某些中间结果放在临时表空间的SQL语句，可以将查询中的子查询命名，放到SELECT语句的最前面，此语法从Oracle 9i开始新增。

(1)、基本语法格式：

with temptablename as (select ....)

select ... from temptablename

(2)、多个临时表之间用『，』分开，格式如下：

with

a as (select did,arg(salary) 平均工资 from work group by did),

b as (select emp.*,w.salary from emp left join work w on emp.eid = w.eid)

select * from a,b where wd.did =em.did and wd.平均工资>em.salary;

(3)、建立临时表的时候，默认使用检索出来的字段名作为临时表字段名，也可以自己定义临时表里面的字段名，比如：

with t_a (column1) as (select count(*) a from t_1),

t_b (column2) as (select count(*) b from t_2),

t_c (column3) as (select count(*) c from t_3)

select * from t_a, t_b, t_c where column1 = column2 and column2 = column3

注意点：

(1)、此种语法创建的临时表跟create temporarytable as (select ... from ... where ...) 创建的临时表不一样，后者是会话结束就自动被消除，前者是检索查询完成以后就被消除。

(2)、建立多个临时表的时候，后者是可以访问前面已经建好的临时表的。

(3)、从功能上讲，跟子查询一样的效果。但是执行计划不同，当有多个子查询的时候，特别是相同子查询，一般用with写这部分，因为子查询结果存在内存临时表中，执行效率当然也就会高很多。

统计信息

1. 什么是统计信息
统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。比如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。
统计信息是存放在数据字段表中的，如tab$。一般我们从数据字段视图中察看统计信息状况，如DBA_TABLES,DBA_INDEXES,DBA_TAB_COL_STATISTICS,DBA_TAB_HISTOGRAMS 等。
列举下DBA_TABLES,DBA_INDEXES视图中表示统计信息的一些字段。这些字段只有搜集过统计信息之后才有值，否则是空的。这些字段中last_analyzed 字段表示上次统计信息搜集的时间，大家可以根据这个字段，快速的了解最近一次统计信息搜集的时间。

2. 如何搜集统计信息
统计信息搜集也是有多种方法，推荐大家使用DBMS_STATS表来进行统计信息搜集及进行一般的统计信息维护工作。
DBMS-STATS 包，主要提供了搜集，删除，导出，导入，修改统计信息的方法，分别对应于gather系列，delete系列，export 系列，import系列，set系列的子过程。一般可能主要是使用统计信息的搜集，以及导出导入这样的功能。具体来说，主要会使用到如下几个子过程：
GATHER_INDEX_STATS Procedure
Gathers index statistics.

GATHER_TABLE_STATS Procedure
Gathers table and column (and index) statistics.

CREATE_STAT_TABLE Procedure
Creates a table with name stattab in ownname's schema which is capable ofholding statistics.

EXPORT_TABLE_STATS Procedure
Retrieves statistics for a particular table and stores them in the user stattable.

EXPORT_SCHEMA_STATS Procedure
Retrieves statistics for all objects in the schema identified by ownname andstores them in the user stat table identified by stattab.

IMPORT_INDEX_STATS Procedure
Retrieves statistics for a particular index from the user stat table identifiedby stattab and stores them in the dictionary.

IMPORT_TABLE_STATS Procedure
Retrieves statistics for a particular table from the user stat table identifiedby stattab and stores them in the dictionary.

IMPORT_SCHEMA_STATS Procedure
Retrieves statistics for all objects in the schema identified by ownname fromthe user stat table and stores them in the dictionary.

ORACLE优化器的优化方式有两大类，即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。

A、 RBO方式：优化器在分析SQL语句时，更据数据库中表和索引等定义信息，遵循的是Oracle内部预定的一些规则。比如我们常见的：当一个where子句中的一列有索引时去走索引而不走全表扫描。

B、 CBO方式：依词义可知,它是看语句的代价(Cost)了。基于代价的查询，数据库根据搜集的表和索引的数据的统计信息（统计信息通过analyze 命令或者使用dbms_stats包来搜集）综合来决定选取一个数据库认为最优的执行计划（实际上不一定最优）。统计信息给出表的大小、有多少行、每行的长度等信息。

注意：这些统计信息起初在库内是没有的，是根据 analyze 命令或者dbms_stats包来定期搜集后才出现的，所以很多的时侯过期统计信息会令优化器做出一个错误的执行计划，因些我们应及时更新这些信息。为了使用基于成本的优化器(CBO) , 你必须经常运行analyze或dbms_stats命令,以增加数据库中的对象统计信息(object statistics)的准确性。

在Oracle8及以后的版本,Oracle强列推荐用CBO的方式。

1. 如何查看对象统计信息(objectstatistics)

对CBO模式，对象统计信息至关重要。如何查看对象统计信息(object statistics)？

Oracle中关于表的统计信息是在数据字典中的，可以下SQL查询到，eg：

SELECTtable_name,num_rows, blocks, empty_blocks AS empty, avg_space, chain_cnt,avg_row_len

FROMdba_tables

WHERE owner = ‘ONT’

AND table_name= ‘OE_ORDER_LINES_ALL’;

TABLE_NAMENUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN

OE_ORDER_LINES_ALL5344 505 5 0 0 441

可以看到数据字典中统计到的该表有5344笔记录，我们下SQL验证一下：

selectcount(*) from apps.OE_ORDER_LINES_ALL;

发现返回是16518笔记录，可见这个表的统计信息是比较陈旧的，真实数据与统计到的数据有较大的差别。在这种情况下，如果某个View用到此Table，且系统使用CBO的方式，则可能导致Oracle的optimizer给出效率低下的执行计划。

此时可以用ANALYZE去重新统计OE_ORDER_LINES_ALL这个表，可以下SQL：

ANALYZE TABLEONT.OE_ORDER_LINES_ALL COMPUTE STATISTICS;

再次Query数据字典：

TABLE_NAMENUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN

OE_ORDER_LINES_ALL16518 1530 1035 865 257 643

发现此时的信息已是最新的了。有了比较正确的统计信息，optimizer才能给出高效的执行计划。

2. 并发请求：统计数据收集模式(FNDGSCST)/ Gather Schema Statistics

Oracle ERP中有几个与Gather有关的标准Request：

Gather AllColumn Statistics –FND_STATS.GATHER_ALL_COLUMN_STATS()

Gather ColumnStatistics –FND_STATS.GATHER_COLUMN_STATS()

Gather SchemaStatistics –FND_STATS.GATHER_SCHEMA_STATS()

Gather TableStatistics –FND_STATS.GATHER_TABLE_STATS()

查看FND_STATS 这个Package的写法，其实它就是在调用Oracle DB中Standard的Package dbms_stats 中的某些Function。

Oracle DB中常用的Gather有以下一些，DBA也可以直接在Database级别上定期Run这些Function，以便能让Oracle统计到最新的数据库状况：

dbms_stats.gather_database_stats();

dbms_stats.gather_schema_stats();

dbms_stats.gather_table_stats();

dbms_stats.gather_index_stats();

Oracle CBO需要系统定期分析统计表/索引。只有这样CBO才能使用正确的SQL访问路径，提高查询效率。因此在Instance Level的optimizer_mode= choose ，定期运行ANALYZE 或dbms_stats是非常重要的，尤其是当上次统计后，数据量已发生较大变化之后。

注意：统计操作是很耗资源的动作，要在系统Loading小的时候进行。

一、什么是统计信息

统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。例如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。

统计信息是存放在数据字典表中的，如tab$，一般可通过察看某些视图来获取统计信息状况，如DBA_TABLES,DBA_INDEXES,DBA_TAB_COL_STATISTICS,DBA_TAB_HISTOGRAMS等。在这些视图中包含表示统计信息的一些字段，这些字段只有搜集过统计信息之后才有值，否则是空的。例如，last_analyzed 字段表示上次统计信息搜集的时间，可以根据这个字段，快速的了解最近一次统计信息搜集的时间。

二、收集统计信息的方法

使用gather_stats_job自动收集是在创建数据库时自动创建的，并由调度程序进行管理。他会收集数据库中优化程序统计信息缺失或已过时的所有对象的统计信息。

使用dbms_stats 程序包手动收集收集的是系统统计信息。

通过设置数据库初始化参数进行收集。

通过从另一个数据库导入统计信息进行收集。

三、Oracle自动收集统计信息的原理

统计信息对于Oracle数据库来说至关重要，尤其是在使用CBO（基于成本的优化器）模式的时候，统计信息包括表的使用块数、空闲块数、平均行长度、统计信息收集时间等。在Oracle9i数据库中，两种优化器模式RBO和CBO并存，在默认情况下，optimizer_mode参数的值是choose，choose不是优化器模式，它表示在分析数据库中的语句时，如果在对象上有统计信息，就是用CBO方式生成执行计划，如果对象上没有统计信息，是使用RBO模式。

从总体上来说，CB的准确度高于RBO，但是它要求要有统计信息和统计信息必须准确，否则Oracle可能会做出错误的判断。所以在Oracle9i数据库中，我们会自己来规划在什么样的时间采用什么样的策略来收集统计信息。也就是说，Oracle9i的统计信息收集工作必须通过手工方式来实现。

到了Oracle10g，默认情况下，optimizer_mode=all_rows，也就是采用了CBO的方式，为了保证执行计划的准确，在周一到周五（晚22:00-次日6:00），通过一个job（gather_stat_job）自动收集对象的统计信息。这种自动收集统计信息的方式并不是收集所有对象的统计信息，而是收集没有统计信息的对象和统计信息过旧的对象。

AutomaticStatistics Gathering是由Scheduler调度GATHER_STATS_JOB作业来完成的，在GATHER_STATS_JOB作业中则调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程。GATHER_DATABASE_STATS_JOB_PROC是一个内部的存储过程，基本上跟DBMS_STATS.GATHER_DATABASE_STATS的功能一样，但在其内部有优先顺序的考虑，更新量（变化量）越多的表将会越优先收集统计信息。为对象收集统计信息的条件是，之前从来没有收集过的或者是更新的（包括insert,update,delete,truncate）记录数超过当前总记录数10%的表（在Oracle11g中则提供了SET_TABLE_PREFS函数修改10%这个阈值）。记录数的更改量由Oracle数据库自动监控，在初始化参数statistics_level设置为TYPICAL或者ALL时，自动监控即会生效。

3.1 调整当更新量达（变化量）达到多少时开始统计信息收集任务

BEGIN

DBMS_STATS.SET_TABLE_PREFS ( ownname=>'XXXXX', tabname =>'T1', pname =>'STALE_PERCENT', pvalue =>'5');

END;

3.2 调整自动收集统计信息的执行时间

selectt1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windowst1,dba_scheduler_wingroup_members t2

wheret1.window_name=t2.window_name and t2.window_group_name in('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

WINDOW_NAME REPEAT_INTERVAL DURATION

--------------------------------------------------------------------------------------------------------------

MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00

TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00

WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0;bysecond=0 +000 04:00:00

THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00

FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00

SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00

SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00

# WINDOW_NAME：任务名

#REPEAT_INTERVAL：任务重复间隔时间

# DURATION：持续时间

# 1.停止任务

BEGIN

DBMS_SCHEDULER.DISABLE(

name=>'"SYS"."FRIDAY_WINDOW"',

force=>TRUE);

END;

# 2.修改任务的持续时间，单位是分钟

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name=>'"SYS"."FRIDAY_WINDOW"',

attribute=>'DURATION',

value=>numtodsinterval(180,'minute'));

END;

# 3.开始执行时间，BYHOUR=2,表示2点开始执行

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name=>'"SYS"."FRIDAY_WINDOW"',

attribute=>'REPEAT_INTERVAL',

value=>'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');

END;

# 4.开启任务

BEGIN

DBMS_SCHEDULER.ENABLE(

name=>'"SYS"."FRIDAY_WINDOW"');

END;

3.3 禁用统计信息自动收集

BEGIN

DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

END;

四、DBMS_STATS包

DBMS_STATS包，主要提供了搜集（gather），删除（delete），导出（export），导入（import），修改（set）统计信息的方法。

dbms_stats与analyze的区别：

dbms_stats是Oracle9i及后续版本中用于收集统计信息的包，虽然analyze命令也一直可以使用，但是现在已经不推荐使用analyze命令来收集统计信息，而是使用dbms_stats。两者之间有很大的不同，dbms_stats能正确收集分区表的统计信息，也就是说能够收集global statistic，而analyze只能收集最低层次对象的统计信息，然后推导和汇总出高一级对象的统计信息，如果分区表只会收集分区统计信息，然后再汇总出所有分区的统计信息，得到表一级的统计信息。

4.1 什么是golbalstatistic

golbalstatistic是指直接从对象本身收集到的统计信息，而不是从下一级对象“推导”和“汇总”出来的统计信息，golbal statistic对于优化器来说非常重要，一个SQL，除非其查询条件限制了数据只在分区上，否则大多数情况下需要golbal statistic才能得到正确的执行计划。有的统计值可以从下一级对象进行汇总后得到，如表的总行数，可以通过各分区的行数相加得到。但有的统计值不能通过下一级对象得到，比如列上的唯一值数量（distinct value）以及密度值（density）。

4.2 使用DBMS_STATS.GATHER_DATABASE_STATS收集整个数据库的统计信息

BEGIN

dbms_stats.gather_database_stats(estimate_percent =>dbms_stats.AUTO_SAMPLE_SIZE,

method_opt => 'for allindexed columns',

options => 'GATHER AUTO',

cascade => TRUE);

END;

参数说明：

1.estimate_percent:采样的百分比，使用dbms_stats.auto_sample_size选项允许Oracle自动估算要采样的一个segment的最佳百分比。

2.method_opt选项适合在表和索引数据发生变化时刷新统计数据：

for table:只统计表

for allindexed columns:只统计有索引的表列

for allindexes:只分析统计相关索引

for allcolumns：分析所有的列

dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。method_opt参数也适合用于判断哪些列需要直方图（histograms）。某些情况下，索引内的各个值的分布会影响CBO是使用一个索引还是执行一次全表扫描的决策。例如，假如在where子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。

如果有一个高度倾斜的索引（某些值的行数不对称），就可创建Oracle直方图统计。但在现实世界中，出现这种情况的机率相当小。使用CBO时，最常见的错误之一就是在CBO统计中不必要地引入直方图。为了智能地生成直方图，Oracle为dbms_stats准备了method_opt参数。在method_opt子句中，还有一些重要的选项，包括skewonly，repeat和auto：

method_opt=>'forall columns size skewonly'

method_opt=>'forall columns size repeat'

method_opt=>'forall columns size auto'

(1).skewonly选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。如果dbms_stat发现一个索引的各个列分布得不均匀，那么就会为该索引创建直方图，帮助基于成本的SQL优化器决定是进行索引访问，还是进行全表扫描访问。

(2).repeat选项在重新分析任务所消耗的资源就会少一些。使用repeat选项时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分析统计数据时，应该采取这种方式。

(3).auto选项根据数据分布以及应用程序访问列的方式来创建直方图。

3.options控制Oracle统计信息的刷新方式:

gather:重新分析整个架构

gather empty:只分析目前还没有统计的表

gather stale:只重新分析修改量超过10%的表（包括插入、更新和删除）

gather auto:重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。使用gather auto类似于组合使用gather stale和gather empty

4.3 使用DBMS_STATS.GATHER_SCHEMA_STATS收集整个用户下对象的统计信息

execdbms_stats.gather_schema_stats(

ownname =>'SCOTT',

options =>'GATHER AUTO',

estimate_percent=> dbms_stats.auto_sample_size,

method_opt=> 'for all columns size repeat',

degree =>15

)

4.4 使用DBMS_STATS.GATHER_TABLE_STATS收集表、列、索引的统计信息

dbms_stats.gather_table_stats(

ownerVARCHAR2,

tablenameVARCHAR2,

partnameVARCHAR2,

estimate_percentNUMBER,

block_sampleBOOLEAN,

method_optVARCHAR2,

degree NUMBER,

granularityVARCHAR2,

cascadeBOOLEAN,

stattabVARCHAR2,

statidVARCHAR2,

statownVARCHAR2,

no_invalidateBOOLEAN,

force BOOLEAN

)

参数说明：

1.owner:要分析表的所有者

2.tablename：要分析的表的表名

3.partname：分区名

4.estimate_percent:采样行的百分比，从0.000001-100，null为全部分析，不采样。常量DBMS_STATS.AUTO_SAMPLE_SIZE是默认值，由Oracle决定最佳采样率。

5.block_sample:是否用块采样代替行采样。

6.method_opt：决定histograms信息是怎样被统计的，method_opt的取值如下：

for allcolumns：统计所有的histograms

for allindexed columns：统计所有index列的histograms

for all hiddencoloumns：统计hidden列的histograms

for columns<list> SIZE <N> | REPEAT | AUTO | SKEWONLY 统计指定列的histograms，N的取值范围是0-254

7.degree:设置统计信息收集的并行度，默认值为null。

8.cascade：收集索引的统计信息，默认为false

9.stattab：指定存储统计信息的表。

10.statid：如果多个表的统计信息存储在一个stattab中时，statid用作分区条件。

11.statown：存储统计信息表的所有着。

如果不指定上述三个参数，则统计信息会被更新到数据字典。

12.force:即使表锁住了也收集统计信息。

4.5 统计信息的导出导入删除操作

# 1.创建统计信息历史保留表

execdbms_stats.create_stat_table(

ownname =>'',

stattab =>''

)

# 2.导出整个scheme的统计信息

execdbms_stats.export_schema_stats(

ownname =>'',

stattab =>''

)

# 3.分析scheme

Execdbms_stats.gather_schema_stats(

ownname =>'',

options =>'GATHER AUTO',

estimate_percent=> dbms_stats.auto_sample_size,

method_opt=> 'for all indexed columns ',

degree => 6

)

# 4.分析表

execdbms_stats.gather_table_stats(

ownname =>'',

tabname=>'',

estimate_percent=> 10,

method_opt=>'for all indexed columns'

)

# 5.分析索引

execdbms_stats.gather_index_stats(

ownname =>'',

indname =>'',

estimate_percent=> 10,

degree => 6

)

# 6.如果发现执行计划走错，删除表的统计信息

execdbms_stats.delete_table_stats(

ownname =>'',

tabname =>''

)

# 7.导入表的历史统计信息

execdbms_stats.import_table_stats(

ownname =>'',

tabname =>'',

stattab =>'')

4.6 锁住统计信息

将一个表的统计信息锁住，以防止错误的统计信息将此正确的信息覆盖掉时需要用到LOCK_TABLE_STATS包：

DBMS_STATS.LOCK_TABLE_STATS(

ownname VARCHAR2,

tabname VARCHAR2

);

分区

分区提供以下优点：

（1）由于将数据分散到各个分区中，减少了数据损坏的可能性；

（2）可以对单独的分区进行备份和恢复；

（3）可以将分区映射到不同的物理磁盘上，来分散IO；

（4）提高可管理性、可用性和性能。

Oracle 10g提供了以下几种分区类型：

（1）范围分区（range）；

（2）哈希分区（hash）；

（3）列表分区（list）；

（4）范围－哈希复合分区（range-hash）；

（5）范围－列表复合分区（range-list）。

Range分区：

　　Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。

如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。

在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。

如：

create table pdba (id number, time date)partition by range (time)

(

partition p1 values less than(to_date('2010-10-1', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2010-11-1','yyyy-mm-dd')),

partition p3 values less than(to_date('2010-12-1', 'yyyy-mm-dd')),

partition p4 values less than (maxvalue)

)

Hash分区：

　　对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。

如：

create table test

(

transaction_id number primary key,

item_id number(8) not null

)

partition by hash(transaction_id)

(

partition part_01 tablespace tablespace01,

partition part_02 tablespace tablespace02,

partition part_03 tablespace tablespace03

);

在这里，我们指定了每个分区的表空间。

List分区：

　　List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。

　　在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。

在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。

如：

create table custaddr
(

id varchar2(15 byte) not null,

areacode varchar2(4 byte)
)

partition by list (areacode)
( partition t_list025 values ('025'),
partition t_list372 values ('372') ,
partition t_list510 values ('510'),

partition p_other values (default)

)

Sql解析过程

解析有两种：硬解析和软解析
在Oracle中SQL语句的解析步骤如下：
1、语法检测。判断一条SQL语句的语法是否符合SQL的规范；
2、语义检查。语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确；

3、检查共享池中是否有相同的语句存在。假如执行的SQL语句已经在共享池中存在同样的副本，那么该
SQL语句将会被软解析，也就是可以重用已解析过的语句的执行计划和优化方案，可以忽略语句解析过程
中最耗费资源的步骤，这也是我们为什么一直强调避免硬解析的原因。这个步骤又可以分为两个步骤：

（1、）验证SQL语句是否完全一致。在这个步骤中，Oracle将会对传递进来的SQL语句使用HASH函数运算
得出HASH值，再与共享池中现有语句的HASH值进行比较看是否一一对应。

（2、）验证SQL语句执行环境是否相同。

通过如上三个步骤检查以后，如果SQL语句是一致的，那么就会重用原有SQL语句的执行计划和优化方案，也就是我们通常所说的软解析。如果SQL语句没有找到同样的副本，那么就需要进行硬解析了。

4、 Oracle根据提交的SQL语句再查询相应的数据对象是否有统计信息。如果有统计信息的话，那么CBO将
会使用这些统计信息产生所有可能的执行计划（可能多达成千上万个）和相应的Cost，最终选择Cost最低
的那个执行计划。如果查询的数据对象无统计信息，则按RBO的默认规则选择相应的执行计划。这个步骤
也是解析中最耗费资源的，因此我们应该极力避免硬解析的产生。至此，解析的步骤已经全部完成，
Oracle将会根据解析产生的执行计划执行SQL语句和提取相应的数据。

sql语句的执行步骤：

1）语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。

2）语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。

3）视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。

4）表达式转换，将复杂的 SQL 表达式转换为较简单的等效连接表达式。

5）选择优化器，不同的优化器一般产生不同的“执行计划”

6）选择连接方式， ORACLE 有三种连接方式，对多表连接 ORACLE 可选择适当的连接方式。

7）选择连接顺序，对多表连接 ORACLE选择哪一对表先连接，选择这两表中哪个表做为源数据表。

8）选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。

9）运行“执行计划”

执行计划：

一：什么是Oracle执行计划？

执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述

二：怎样查看Oracle执行计划？

因为我一直用的PLSQL远程连接的公司数据库，所以这里以PLSQL为例：

①：配置执行计划需要显示的项：

工具 —> 首选项—> 窗口类型 —> 计划窗口 —> 根据需要配置要显示在执行计划中的列

执行计划配置

执行计划的常用列字段解释：

基数（Rows）：Oracle估计的当前操作的返回结果集行数

字节（Bytes）：执行该步骤后返回的字节数

耗费（COST）、CPU耗费：Oracle估计的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好（该值可能与实际有出入）

时间（Time）：Oracle估计的当前操作所需的时间

②：打开执行计划：

在SQL窗口执行完一条select语句后按 F5 即可查看刚刚执行的这条查询语句的执行计划

执行计划查看

注：在PLSQL中使用SQL命令查看执行计划的话，某些SQL*PLUS命令PLSQL无法支持，比如SET AUTOTRACE ON

执行计划sql查看

三：看懂Oracle执行计划

看懂执行计划

①：执行顺序：

根据Operation缩进来判断，缩进最多的最先执行；（缩进相同时，最上面的最先执行）

例：上图中 INDEX RANGE SCAN 和 INDEX UNIQUE SCAN 两个动作缩进最多，最上面的 INDEX RANGE SCAN 先执行；

同一级如果某个动作没有子ID就最先执行

同一级的动作执行时遵循最上最右先执行的原则

例：上图中 TABLE ACCESS BY GLOBAL INDEX ROWID 和 TABLE ACCESS BY INDEX ROWID两个动作缩进都在同一级，则位于上面的 TABLEACCESS BY GLOBAL INDEX ROWID 这个动作先执行；这个动作又包含一个子动作 INDEX RANGE SCAN，则位于右边的子动作 INDEX RANGE SCAN 先执行；

图示中的SQL执行顺序即为：

INDEX RANGESCAN —> TABLEACCESS BY GLOBAL INDEX ROWID —> INDEX UNIQUE SCAN —> TABLEACCESS BY INDEX ROWID —> NESTED LOOPS OUTER —> SORTGROUP BY —> SELECT STATEMENT, GOAL = ALL_ROWS

（注：PLSQL提供了查看执行顺序的功能按钮(上图中的红框部分) ）

②：对图中动作的一些说明：

1. 上图中 TABLE ACCESS BY … 即描述的是该动作执行时表访问（或者说Oracle访问数据）的方式；

表访问的几种方式：（非全部）

TABLE ACCESSFULL（全表扫描）

TABLE ACCESS BYROWID（通过ROWID的表存取）

TABLE ACCESS BYINDEX SCAN（索引扫描）

（1） TABLE ACCESS FULL（全表扫描）：

Oracle会读取表中所有的行，并检查每一行是否满足SQL语句中的 Where 限制条件；

全表扫描时可以使用多块读（即一次I/O读取多块数据块）操作，提升吞吐量；

使用建议：数据量太大的表不建议使用全表扫描，除非本身需要取出的数据较多，占到表数据总量的 5% ~ 10% 或以上

（2） TABLE ACCESS BY ROWID（通过ROWID的表存取） :

先说一下什么是ROWID？

rowid

ROWID是由Oracle自动加在表中每行最后的一列伪列，既然是伪列，就说明表中并不会物理存储ROWID的值；

你可以像使用其它列一样使用它，只是不能对该列的值进行增、删、改操作；

一旦一行数据插入后，则其对应的ROWID在该行的生命周期内是唯一的，即使发生行迁移，该行的ROWID值也不变。

让我们再回到 TABLE ACCESS BY ROWID 来：

行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID可以快速定位到目标数据上，这也是Oracle中存取单行数据最快的方法；

（3） TABLE ACCESS BY INDEX SCAN（索引扫描）：

在索引块中，既存储每个索引的键值，也存储具有该键值的行的ROWID。

一个数字列上建索引后该索引可能的概念结构如下图：

index

所以索引扫描其实分为两步：

Ⅰ：扫描索引得到对应的ROWID

Ⅱ：通过ROWID定位到具体的行读取数据

----------------索引扫描延伸-------------------

索引扫描又分五种：

INDEX UNIQUESCAN（索引唯一扫描）

INDEX RANGE SCAN（索引范围扫描）

INDEX FULL SCAN（索引全扫描）

INDEX FAST FULLSCAN（索引快速扫描）

INDEX SKIP SCAN（索引跳跃扫描）

a) INDEX UNIQUESCAN（索引唯一扫描）：

针对唯一性索引（UNIQUE INDEX）的扫描，每次至多只返回一条记录；

表中某字段存在 UNIQUE、PRIMARY KEY 约束时，Oracle常实现唯一性扫描；

b) INDEX RANGESCAN（索引范围扫描）：

使用一个索引存取多行数据；

发生索引范围扫描的三种情况：

在唯一索引列上使用了范围操作符（如：> < <> >= <= between）

在组合索引上，只使用部分列进行查询（查询时必须包含前导列，否则会走全表扫描）

对非唯一索引列上进行的任何查询

c) INDEX FULLSCAN（索引全扫描）：

进行全索引扫描时，查询出的数据都必须从索引中可以直接得到（注意全索引扫描只有在CBO模式下才有效）

-----------------------延伸阅读：Oracle优化器简述-----------------------

Oracle中的优化器是SQL分析和执行的优化工具，它负责生成、制定SQL的执行计划。

Oracle的优化器有两种：

RBO（Rule-Based Optimization）基于规则的优化器

CBO（Cost-Based Optimization）基于代价的优化器

RBO：

RBO有严格的使用规则，只要按照这套规则去写SQL语句，无论数据表中的内容怎样，也不会影响到你的执行计划；

换句话说，RBO对数据“不敏感”，它要求SQL编写人员必须要了解各项细则；

RBO一直沿用至ORACLE 9i，从ORACLE 10g开始，RBO已经彻底被抛弃。

CBO：

CBO是一种比RBO更加合理、可靠的优化器，在ORACLE 10g中完全取代RBO；

CBO通过计算各种可能的执行计划的“代价”，即COST，从中选用COST最低的执行方案作为实际运行方案；

它依赖数据库对象的统计信息，统计信息的准确与否会影响CBO做出最优的选择，也就是对数据“敏感”。

---------------------------------------------------------------------

d) INDEX FASTFULL SCAN（索引快速扫描）:

扫描索引中的所有的数据块，与 INDEX FULL SCAN 类似，但是一个显著的区别是它不对查询出的数据进行排序（即数据不是以排序顺序被返回）

e) INDEX SKIPSCAN（索引跳跃扫描）：

Oracle 9i后提供，有时候复合索引的前导列（索引包含的第一列）没有在查询语句中出现，oralce也会使用该复合索引，这时候就使用的INDEX SKIP SCAN;

什么时候会触发 INDEX SKIP SCAN 呢？

前提条件：表有一个复合索引，且在查询时有除了前导列（索引中第一列）外的其他列作为条件，并且优化器模式为CBO时

当Oracle发现前导列的唯一值个数很少时，会将每个唯一值都作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询；

例如：

假设表emp有ename（雇员名称）、job（职位名）、sex（性别）三个字段，并且建立了如 create indexidx_emp on emp (sex, ename, job) 的复合索引；

因为性别只有 '男' 和 '女' 两个值，所以为了提高索引的利用率，Oracle可将这个复合索引拆成 ('男', ename, job)，('女', ename, job) 这两个复合索引；

当查询 select * from emp where job = 'Programmer' 时，该查询发出后：

Oracle先进入sex为'男'的入口，这时候使用到了 ('男', ename, job) 这条复合索引，查找 job ='Programmer' 的条目；

再进入sex为'女'的入口，这时候使用到了 ('女', ename, job) 这条复合索引，查找 job ='Programmer' 的条目；

最后合并查询到的来自两个入口的结果集。

----------------------------------------------

2. 上图中的 NESTED LOOPS …描述的是表连接方式；

JOIN 关键字用于将两张表作连接，一次只能连接两张表，JOIN 操作的各步骤一般是串行的（在读取做连接的两张表的数据时可以并行读取）；

表（rowsource）之间的连接顺序对于查询效率有很大的影响，对首先存取的表（驱动表）先应用某些限制条件（Where过滤条件）以得到一个较小的row source，可以使得连接效率提高。

-------------------------延伸阅读：驱动表（Driving Table）与匹配表（Probed Table）-------------------------

驱动表（Driving Table）：

表连接时首先存取的表，又称外层表（Outer Table），这个概念用于 NESTED LOOPS（嵌套循环）与 HASHJOIN（哈希连接）中；

如果驱动表返回较多的行数据，则对所有的后续操作有负面影响，故一般选择小表（应用Where限制条件后返回较少行数的表）作为驱动表。

匹配表（Probed Table）：

又称为内层表（Inner Table），从驱动表获取一行具体数据后，会到该表中寻找符合连接条件的行。故该表一般为大表（应用Where限制条件后返回较多行数的表）。

---------------------------------------------------------------------------------------------------------

表连接的几种方式：

SORT MERGE JOIN（排序-合并连接）

NESTED LOOPS（嵌套循环）

HASH JOIN（哈希连接）

CARTESIANPRODUCT（笛卡尔积）

注：这里将首先存取的表称作 row source 1，将之后参与连接的表称作 row source 2；

（1） SORT MERGE JOIN（排序-合并连接）：

假设有查询：select a.name, b.name from table_A a join table_B b on (a.id= b.id)

内部连接过程：

a) 生成 row source 1 需要的数据，按照连接操作关联列（如示例中的a.id）对这些数据进行排序

b) 生成 row source 2 需要的数据，按照与 a) 中对应的连接操作关联列（b.id）对数据进行排序

c) 两边已排序的行放在一起执行合并操作（对两边的数据集进行扫描并判断是否连接）

延伸：

如果示例中的连接操作关联列 a.id，b.id 之前就已经被排过序了的话，连接速度便可大大提高，因为排序是很费时间和资源的操作，尤其对于有大量数据的表。

故可以考虑在 a.id，b.id 上建立索引让其能预先排好序。不过遗憾的是，由于返回的结果集中包括所有字段，所以通常的执行计划中，即使连接列存在索引，也不会进入到执行计划中，除非进行一些特定列处理（如仅仅只查询有索引的列等）。

排序-合并连接的表无驱动顺序，谁在前面都可以；

排序-合并连接适用的连接条件有： < <= = > >= ，不适用的连接条件有： <> like

（2） NESTED LOOPS（嵌套循环）：

内部连接过程：

a) 取出 row source 1 的 row 1（第一行数据），遍历 row source 2 的所有行并检查是否有匹配的，取出匹配的行放入结果集中

b) 取出 row source 1 的 row 2（第二行数据），遍历 row source 2 的所有行并检查是否有匹配的，取出匹配的行放入结果集中

c) ……

若 rowsource 1 （即驱动表）中返回了 N 行数据，则 row source 2 也相应的会被全表遍历 N 次。

因为 rowsource 1 的每一行都会去匹配 row source 2 的所有行，所以当 row source 1 返回的行数尽可能少并且能高效访问 row source 2（如建立适当的索引）时，效率较高。

延伸：

嵌套循环的表有驱动顺序，注意选择合适的驱动表。

嵌套循环连接有一个其他连接方式没有的好处是：可以先返回已经连接的行，而不必等所有的连接操作处理完才返回数据，这样可以实现快速相应。

应尽可能使用限制条件（Where过滤条件）使驱动表（row source 1）返回的行数尽可能少，同时在匹配表（row source 2）的连接操作关联列上建立唯一索引（UNIQUE INDEX）或是选择性较好的非唯一索引，此时嵌套循环连接的执行效率会变得很高。若驱动表返回的行数较多，即使匹配表连接操作关联列上存在索引，连接效率也不会很高。

（3）HASH JOIN（哈希连接） :

哈希连接只适用于等值连接（即连接条件为 = ）

HASH JOIN对两个表做连接时并不一定是都进行全表扫描，其并不限制表访问方式；

内部连接过程简述：

a) 取出 row source 1（驱动表，在HASH JOIN中又称为Build Table）的数据集，然后将其构建成内存中的一个 Hash Table（Hash函数的Hash KEY就是连接操作关联列），创建Hash位图（bitmap）

b) 取出 row source 2（匹配表）的数据集，对其中的每一条数据的连接操作关联列使用相同的Hash函数并找到对应的 a) 里的数据在 Hash Table 中的位置，在该位置上检查能否找到匹配的数据

----------------延伸阅读：Hash Table相关----------------

来自Wiki的解释：

In computing, ahash table (hash map) is a data structure used to implement an associativearray, a structure that can map keys to values. A hash table uses a hashfunction to compute an index into an array of buckets or slots, from which thedesired value can be found.

散列（hash）技术：在记录的存储位置和记录具有的关键字key之间建立一个对应关系 f ，使得输入key后，可以得到对应的存储位置 f(key)，这个对应关系 f 就是散列（哈希）函数；

采用散列技术将记录存储在一块连续的存储空间中，这块连续的存储空间就是散列表（哈希表）；

不同的key经同一散列函数散列后得到的散列值理论上应该不同，但是实际中有可能相同，相同时即是发生了散列（哈希）冲突，解决散列冲突的办法有很多，比如HashMap中就是用链地址法来解决哈希冲突；

哈希表是一种面向查找的数据结构，在输入给定值后查找给定值对应的记录在表中的位置以获取特定记录这个过程的速度很快。

--------------------------------------------------------

HASH JOIN的三种模式：

OPTIMAL HASHJOIN

ONEPASS HASHJOIN

MULTIPASS HASHJOIN

1) OPTIMAL HASHJOIN：

OPTIMAL 模式是从驱动表（也称Build Table）上获取的结果集比较小，可以把根据结果集构建的整个HashTable都建立在用户可以使用的内存区域里。

optimal_hash_join

连接过程简述：

Ⅰ：首先对Build Table内各行数据的连接操作关联列使用Hash函数，把Build Table的结果集构建成内存中的Hash Table。如图所示，可以把Hash Table看作内存中的一块大的方形区域，里面有很多的小格子，Build Table里的数据就分散分布在这些小格子中，而这些小格子就是Hash Bucket（见上面Wiki的定义）。

Ⅱ：开始读取匹配表（Probed Table）的数据，对其中每行数据的连接操作关联列都使用同上的Hash函数，定位Build Table里使用Hash函数后具有相同值数据所在的Hash Bucket。

Ⅲ：定位到具体的Hash Bucket后，先检查Bucket里是否有数据，没有的话就马上丢掉匹配表（Probed Table）的这一行。如果里面有数据，则继续检查里面的数据（驱动表的数据）是否和匹配表的数据相匹配。

2): ONEPASS HASHJOIN :

从驱动表（也称Build Table）上获取的结果集较大，无法将根据结果集构建的Hash Table全部放入内存中时，会使用 ONEPASS 模式。

one_pass_hash_join

连接过程简述：

Ⅰ：对Build Table内各行数据的连接操作关联列使用Hash函数，根据Build Table的结果集构建Hash Table后，由于内存无法放下所有的Hash Table内容，将导致有的Hash Bucket放在内存里，有的Hash Bucket放在磁盘上，无论放在内存里还是磁盘里，Oracle都使用一个Bitmap结构来反映这些Hash Bucket的状态（包括其位置和是否有数据）。

Ⅱ：读取匹配表数据并对每行的连接操作关联列使用同上的Hash函数，定位Bitmap上Build Table里使用Hash函数后具有相同值数据所在的Bucket。如果该Bucket为空，则丢弃匹配表的这条数据。如果不为空，则需要看该Bucket是在内存里还是在磁盘上。

如果在内存中，就直接访问这个Bucket并检查其中的数据是否匹配，有匹配的话就返回这条查询结果。

如果在磁盘上，就先把这条待匹配数据放到一边，将其先暂存在内存里，等以后积累了一定量的这样的待匹配数据后，再批量的把这些数据写入到磁盘上（上图中的 Dump probe partitions to disk）。

Ⅲ：当把匹配表完整的扫描了一遍后，可能已经返回了一部分匹配的数据了。接下来还有Hash Table中一部分在磁盘上的Hash Bucket数据以及匹配表中部分被写入到磁盘上的待匹配数据未处理，现在Oracle会把磁盘上的这两部分数据重新匹配一次，然后返回最终的查询结果。

3): MULTIPASSHASH JOIN：

当内存特别小或者相对而言Hash Table的数据特别大时，会使用 MULTIPASS 模式。MULTIPASS会多次读取磁盘数据，应尽量避免使用该模式。

3. 上图中的… OUTER 描述的是表连接类型；

表连接的两种类型：

INNER JOIN（内连接）

OUTER JOIN（外连接）

示例数据说明：

现有A、B两表，A表信息如下：

table_A

B表信息如下：

table_B

下面的例子都用A、B两表来演示。

（1） INNER JOIN（内连接）：

只返回两表中相匹配的记录。

INNER JOIN 又分为两种：

等值连接（连接条件为 = ）

非等值连接（连接条件为非 = ，如 > >= < <= 等）

等值连接用的最多，下面以等值连接举例：

内连接的两种写法：

Ⅰ： selecta.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a inner join B b on(a.id = b.id)

Ⅱ： selecta.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a join B b on (a.id =b.id)

连接时只返回满足连接条件（a.id = b.id）的记录：

inner_join

（2） OUTER JOIN（外连接）：

OUTER JOIN 分为三种：

LEFT OUTER JOIN（可简写为 LEFT JOIN，左外连接）

RIGHT OUTER JOIN（ RIGHT JOIN，右外连接）

FULL OUTER JOIN（ FULL JOIN，全外连接）

a) LEFT JOIN（左连接）：

返回的结果不仅包含符合连接条件的记录，还包含左边表中的全部记录。（若返回的左表中某行记录在右表中没有匹配项，则右表中的返回列均为空值）

两种写法：

Ⅰ：selecta.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a left outer join B bon (a.id = b.id)

Ⅱ：selecta.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a left join B b on(a.id = b.id)

返回结果：

left_join

b) RIGHT JOIN（右连接）：

返回的结果不仅包含符合连接条件的记录，还包含右边表中的全部记录。（若返回的右表中某行记录在左表中没有匹配项，则左表中的返回列均为空值）

两种写法：

Ⅰ：selecta.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a right outer join Bb on (a.id = b.id)

Ⅱ：selecta.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a right join B b on(a.id = b.id)

返回结果：

right_join

c) FULL JOIN（全连接）：

返回左右两表的全部记录。(左右两边不匹配的项都以空值代替)

两种写法：

Ⅰ：selecta.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a full outer join B bon (a.id = b.id)

Ⅱ：selecta.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a full join B b on(a.id = b.id)

返回结果：

full_join

---------------------延伸阅读： (+) 操作符-------------------

(+) 操作符是Oracle特有的表示法，用来表示外连接（只能表示左外、右外连接），需要配合Where语句使用。

特别注意：(+) 操作符在左表的连接条件上表示右连接，在右表的连接条件上表示左连接。

如：

Ⅰ：selecta.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a, B b where a.id =b.id(+)

查询结果：

右边( )

实际与左连接 select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAMEfrom A a left join B b on (a.id = b.id) 效果等价

Ⅱ：selecta.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a, B b where a.id(+)= b.id

查询结果：

左边( )

实际与右连接 select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAMEfrom A a right join B b on (a.id = b.id) 效果等价

HINT

ORACLE的HINT详解

hints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用hints来实现：

1) 使用的优化器的类型

2) 基于代价的优化器的优化目标，是all_rows还是first_rows。

3) 表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。

4) 表之间的连接类型

5) 表之间的连接顺序

6) 语句的并行程度

2、HINT可以基于以下规则产生作用

表连接的顺序、表连接的方法、访问路径、并行度

3、HINT应用范围

dml语句

查询语句

4、语法

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text][hint[text]]... */

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text][hint[text]]...

如果语（句）法不对，则ORACLE会自动忽略所写的HINT，不报错

1. /*+ALL_ROWS*/

表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.

例如:

SELECT /*+ALL_ROWS*/ EMP_NO,EMP_NAM,DAT_INFROM BSEMPMS WHERE EMP_NO='SCOTT';

2. /*+FIRST_ROWS*/

表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.

例如:

SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROMBSEMPMS WHERE EMP_NO='SCOTT';

3. /*+CHOOSE*/

表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;

表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;

例如:

SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROMBSEMPMS WHERE EMP_NO='SCOTT';

4. /*+RULE*/

表明对语句块选择基于规则的优化方法.

例如:

SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROMBSEMPMS WHERE EMP_NO='SCOTT';

5. /*+FULL(TABLE)*/

表明对表选择全局扫描的方法.

例如:

SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROMBSEMPMS A WHERE EMP_NO='SCOTT';

6. /*+ROWID(TABLE)*/

提示明确表明对指定表根据ROWID进行访问.

例如:

SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMSWHERE ROWID>='AAAAAAAAAAAAAA'

AND EMP_NO='SCOTT';

7. /*+CLUSTER(TABLE)*/

提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.

例如:

SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NOFROM BSEMPMS,BSDPTMS

WHERE DPT_NO='TEC304' ANDBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

8. /*+INDEX(TABLE INDEX_NAME)*/

表明对表选择索引的扫描方法.

例如:

SELECT /*+INDEX(BSEMPMS SEX_INDEX) USESEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/

表明对表选择索引升序的扫描方法.

例如:

SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */FROM BSEMPMS WHERE DPT_NO='SCOTT';

10. /*+INDEX_COMBINE*/

为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.

例如:

SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMIHIREDATE_BMI)*/ * FROM BSEMPMS

WHERE SAL<5000000 AND HIREDATE

11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/

提示明确命令优化器使用索引作为访问路径.

例如:

SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMIHIREDATE_BMI)*/ SAL,HIREDATE

FROM BSEMPMS WHERE SAL<60000;

12. /*+INDEX_DESC(TABLE INDEX_NAME)*/

表明对表选择索引降序的扫描方法.

例如:

SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */FROM BSEMPMS WHERE DPT_NO='SCOTT';

13. /*+INDEX_FFS(TABLE INDEX_NAME)*/

对指定的表执行快速全索引扫描,而不是全表扫描的办法.

例如:

SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ *FROM BSEMPMS WHERE DPT_NO='TEC305';

14. /*+ADD_EQUAL TABLEINDEX_NAM1,INDEX_NAM2,...*/

提示明确进行执行规划的选择,将几个单列索引的扫描合起来.

例如:

SELECT /*+INDEX_FFS(BSEMPMSIN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' ANDDPT_NO='TDC306';

15. /*+USE_CONCAT*/

对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.

例如:

SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHEREDPT_NO='TDC506' AND SEX='M';

16. /*+NO_EXPAND*/

对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.

例如:

SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHEREDPT_NO='TDC506' AND SEX='M';

17. /*+NOWRITE*/

禁止对查询块的查询重写操作.

18. /*+REWRITE*/

可以将视图作为参数.

19. /*+MERGE(TABLE)*/

能够对视图的各个查询进行相应的合并.

例如:

SELECT /*+MERGE(V) */A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO

,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BYDPT_NO) V WHERE A.DPT_NO=V.DPT_NO

AND A.SAL>V.AVG_SAL;

20. /*+NO_MERGE(TABLE)*/

对于有可合并的视图不再合并.

例如:

SELECT /*+NO_MERGE(V) */A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SALFROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO ANDA.SAL>V.AVG_SAL;

21. /*+ORDERED*/

根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.

例如:

SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROMTABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

22. /*+USE_NL(TABLE)*/

将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

例如:

SELECT /*+ORDERED USE_NL(BSEMPMS)*/BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

23. /*+USE_MERGE(TABLE)*/

将指定的表与其他行源通过合并排序连接方式连接起来.

例如:

SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROMBSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

24. /*+USE_HASH(TABLE)*/

将指定的表与其他行源通过哈希连接方式连接起来.

例如:

SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROMBSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

25. /*+DRIVING_SITE(TABLE)*/

强制与ORACLE所选择的位置不同的表进行查询执行.

例如:

SELECT /*+DRIVING_SITE(DEPT)*/ * FROMBSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

26. /*+LEADING(TABLE)*/

将指定的表作为连接次序中的首表.

27. /*+CACHE(TABLE)*/

当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端

例如:

SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */EMP_NAM FROM BSEMPMS;

28. /*+NOCACHE(TABLE)*/

当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端

例如:

SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */EMP_NAM FROM BSEMPMS;

29. /*+APPEND*/

直接插入到表的最后,可以提高速度.

insert /*+append*/ into test1 select * fromtest4 ;

30. /*+NOAPPEND*/

通过在插入语句生存期内停止并行模式来启动常规插入.

insert /*+noappend*/ into test1 select * fromtest4 ;

31. NO_INDEX: 指定不使用哪些索引

/*+ NO_INDEX ( table [index [index]...] )*/

select /*+ no_index(emp ind_emp_salind_emp_deptno)*/ * from emp where deptno=200 and sal>300;

32. parallel

select /*+ parallel(emp,4)*/ * from emp wheredeptno=200 and sal>300;

另：每个SELECT/INSERT/UPDATE/DELETE命令后只能有一个/*+ */，但提示内容可以有多个，可以用逗号分开，空格也可以。

如：/*+ ordered index() use_nl() */

---------

类似如下的一条语句：insert into xxxx select /*+parallel(a) */ * from xxx a;数据量大约在75G左右，这位兄弟从上午跑到下午还没跑完，过来问我咋回事，说平常2hrs能跑完的东西跑了好几个小时还撒动静。查看系统性能也比较正常，cpu，io都不繁忙，平均READ速度在80M/s左右（勉强凑合），但平均写速度只有10M不到。等待事件里面大量的‘‘PX Deq Credit: send blkd’，这里能看出并行出了问题，从而最后得知是并行用法有问题，修改之后20分钟完成了该操作。正确的做法应该是：

alter sessionenable dml parallel；

insert/*+parallel(xxxx,4) */ into xxxx select /*+parallel(a) */ * from xxx a;

因为oracle默认并不会打开PDML，对DML语句必须手工启用。另外不得不说的是，并行不是一个可扩展的特性，只有在数据仓库或作为DBA等少数人的工具在批量数据操作时利于充分利用资源，而在OLTP环境下使用并行需要非常谨慎。事实上PDML还是有比较多的限制的，例如不支持触发器，引用约束，高级复制和分布式事务等特性，同时也会带来额外的空间占用，PDDL同样是如此。

---------

selectcount(*)

From wid_serv_prod_mon_1100 a

where a.acct_month = 201010

and a.partition_id = 10

and serv_state not in ('2HB', '2HL', '2HJ','2HP', '2HF')

and online_flag in (0)

and incr_product_id in (2000020)

and product_id in (2020966, 2020972,2100297, 2021116)

and billing_mode_id = 1

and exp_date > to_date('201010','yyyymm')

and not exists (select /*+no_index (bIDX_W_CDR_MON_SERV_ID_1100)*/

from wid_cdr_mon_1100 b

where b.acct_month = 201010

and b.ANA_EVENT_TYPE_4 in

('10201010201', '10202010201','10203010201', '10203010202', '10203030201', '10203030202', '10204010201','10204010202', '10204030201')

and a.serv_id = b.serv_id)

Group by

group by 一般和聚合函数一起使用才有意义,比如 count sum avg等,使用groupby的两个要素:
(1) 出现在select后面的字段要么是是聚合函数中的,要么就是group by 中的.
(2) 要筛选结果可以先使用where 再用group by 或者先用group by 再用having