1. minus(差集)与intersect(交集)
minus指令是运用在两个SQL语句上。它先找出第一个SQL语句所产生的结果,然后看这些结果有没有在第二个SQL语句的结果中,如果有的话,那这一笔资料就被去除,而不会在最后的结果中出现;
如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内,那这笔资料就被抛弃。
intersect指令是运用在两个SQL语句上,如果两个SQL语句的记录完全相同则显示相应记录,否则将不在结果中出现。
-------------------------------------------------------------------------------------------
2. 表空间(tablespace)、段(segment)、区段(extent)、块(block)的关系
1)表空间:是Oracle中的一个逻辑存储容器,位于存储层次体系的顶层,包含一个或多个数据文件。
2)段:占用存储空间的数据为对象,如表、索引、回滚段等;段由一个或多个区段组成。
3)区段:是文件中一个逻辑上连续分配的空间;区段由块组成。
4)块:是Oracle中最小的空间分配单位;数据行、索引条目或临时排序结果就存储在块中;Oracle中常见的块大小:2K、4K、8K、16K(最大不能超过32K)。
5)它们之间的关系:数据库由一个或多个表空间组成,表空间由一个或多个数据文件组成,表空间包含段,段由一个或多个区段组成,区段则由连续的块组成。
-------------------------------------------------------------------------------------------
3. ETL(Extraction-Transformation-Loading)名称解释
ETL:抽取(Extraction)、转换(Transformation)、载入(Loading) ETL负责将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成,最后加载到数据仓库或数据集市中,成为联机分析处理、数据挖掘的基础。
oracle中的裸设备指: 裸设备就是绕过文件系统直接访问的储存空间.
-------------------------------------------------------------------------------------------
4. 数据库分析技术语句
分析表: analyze table UNIT_PRE_CLAIM compute statistics;
分析索引:analyze index PK_UNIT_PRE_CLAIM_RELA_ID compute statistics;
分析列: analyze table zl_yhjbqk compute statistics for columns hbs_bh;
分析索引列:analyze table zl_yhjbqk compute statistics for all indexed columns;
-------------------------------------------------------------------------------------------
5. ORACLE的优化器共有3种:
a. RULE (基于规则的优化器)
。总是使用索引
。总是从驱动表开始(from子句最右边的表)
。只有在不可避免的情况下,才使用全表扫描
b. COST (基于成本的优化器)
。需要表、索引的统计资料
Analyze table customer compute statistics;
Analyze table customer estimate statistics sample 5000 rows;
。表中设置并行度、表分区
c. CHOOSE (选择性)
设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS. 你当然也在SQL句级或是会话(session)级对其进行覆盖.
为了使用基于成本的优化器(CBO, Cost-Based Optimizer), 你必须经常运行analyze命令,以增加数据库中的对象统计信息(object statistics)的准确性.如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关,如果table已经被analyze过, 优化器模式将自动成为CBO, 反之,数据库将采用RULE形式的优化器. 缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan), 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.注意: 在排序合并连接中不使用索引,如果查询返回两个表中大部分的数据快,那么CBO会认为全表扫描比索引扫描执行更快。
-------------------------------------------------------------------------------------------
6. Hint的常用用法(注意/*和+之间不能有空格)
1)/*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
2)/*+FIRST_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
3)/*+CHOOSE*/ 表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;
如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;
4)/*+ RULE*/ 表明对语句块选择基于规则的优化方法.
5)/*+ROWID(TABLE)*/ 提示明确表明对指定表根据ROWID进行访问.
例如: SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
6)/*+INDEX(TABLE INDEX_NAME)*/ 表明对表选择索引的扫描方法.
7)/*+INDEX_JOIN(TABLE INDEX_NAME)*/ 提示明确命令优化器使用索引作为访问路径.
8)/*+APPEND*/ 直接插入到表的最后,可以提高速度.
例如: insert /*+append*/ into test1 select * from test4;
如用hint指定使用某个索引
select /*+ index(表名) */ col1 from 表名;
select /*+ index(表名 索引名) */ col1 from 表名;
select /*+ index(a 索引名) */ col1 from 表名 a;
其中
TABLE_NAME是必须要写的,且如果在查询中使用了表的别名,在hint也要用表的别名来代替表名;
INDEX_NAME可以不必写,Oracle会根据统计值选一个索引;
-------------------------------------------------------------------------------------------
7. 位图索引
位图索引储存主要用来节省空间,减少ORACLE对数据块的访问,它采用位图偏移方式来与表的行ID号对应,采用位图索引一般是重复值太多的表字段。位图索引在实际密集型OLTP(数据事务处理)中用得比较少,因为OLTP会对表进行大量的删除、修改、新建操作,ORACLE每次进行操作都会对要操作的数据块加锁,所以多人操作很容易产生数据块锁等待甚至死锁现象。在OLAP(数据分析处理)中应用位图有优势,因为OLAP中大部分是对数据库的查询操作,而且一般采用数据仓库技术,所以大量数据采用位图索引节省空间比较明显。
-------------------------------------------------------------------------------------------
8. 外部连接说明(左、右连接)
在where条件子句的等式谓词放置一个(+)来实现,例如:该语句返回所有emp表的记录;
select a.ename,b.comm from emp a,bonus b where a.ename=b.ename(+);
在9i以前可以这么写:
select a.id,a.name,b.address from a,b where a.id=b.id(+) --左联
select a.id,a.name,b.address from a,b where a.id(+)=b.id --右联
外联
Select a.Id, a.Name, b.Address From a, b Where a.Id = b.Id(+)
Union
Select b.Id, '' Name, b.Address From b Where Not Exists (Select * From a Where a.Id = b.Id);
-------------------------------------------------------------------------------------------
9. Oracle为什么不使用索引
1)看采用了哪种类型的连接方式。ORACLE的共有Sort Merge Join(SMJ)、Hash Join(HJ)和Nested Loop Join(NL)。在两张表连接,且内表的目标列上建有索引时,只有Nested Loop才能有效地利用到该索引。SMJ即使相关列上建有索引,最多只能因索引的存在,避免数据排序过程。HJ由于须做HASH运算,索引的存在对数据查询速度几乎没有影响。
2)看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引,表dept的列deptno上无索引,WHERE语句有emp.deptno=dept.deptno条件。在做NL连接时,emp做为外表,先被访问,由于连接机制原因,外表的数据访问方式是全表扫描,emp.deptno上的索引显然是用不上,最多在其上做索引全扫描或索引快速全扫描。
3)索引列如果是函数的参数。则索引在查询时用不上。
4)是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较,ORACLE会自动将字符型用to_number()函数进行转换,从而导致上一种现象的发生。
5)是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析,可用SQL语句analyze table xxxx compute statistics for all indexes; ORACLE掌握了充分反映实际的统计数据,才有可能做出正确的选择。
6)如果索引列值是空值,在SQL语句中那些要返回NULL值的操作,将不会用到索引,如COUNT(*),而是用全表扫描。这是因为索引中存储值不能为全空。
7)看是否有用到并行查询(PQO)。并行查询将不会用到索引。
8)如果从以上几个方面都查不出原因的话,我们只好用采用在语句中加hint的方式强制ORACLE使用最优的“执行计划”。hint采用注释的方式,有行注释和段注释两种方式。如我们想要用到A表的IND_COL1索引的话,可采用以下方式:“SELECT /*+ INDEX(A IND_COL1)*/ * FROM A WHERE COL1 = XXX;"
-------------------------------------------------------------------------------------------
10. 如何屏蔽索引
语句的执行计划中有不良索引时,可以人为地屏蔽该索引,方法:
数值型:在索引字段上加0, 例如 select * from emp where emp_no+0 = v_emp_no;
字符型:在索引字段上加'',例如 select * from tg_cdr01 where msisdn||'' =v_msisdn;
怎么避免使用特定索引:
在很多时候,Oracle会错误的使用索引而导致效率的明显下降,我们可以使用一点点技巧而避免,如表test,有字段a,b,c,d,在a,b,c上建立联合索引inx_a(a,b,c),在b上单独建立了一个索引Inx_b(b)。
在正常情况下,where a=? and b=? and c=?会用到索引inx_a,where b=?会用到索引inx_b
但是,where a=? and b=? and c=? group by b会用到哪个索引呢?在分析数据不正确(很长时间没有分析)或根本没有分析数据的情况下,oracle往往会使用索引inx_b。通过执行计划的分析,这个索引的使用,将大大耗费查询时间。当然,我们可以通过如下的技巧避免使用inx_b,而使用inx_a。
where a=? and b=? and c=? group by b||'' --如果b是字符
where a=? and b=? and c=? group by b+0 --如果b是数字
通过这样简单的改变,往往可以是查询时间提交很多倍. 当然,我们也可以使用no_index提示,相信很多人没有用过,也是一个不错的方法:
select /*+ no_index(t,inx_b) */ * from test t
where a=? and b=? and c=? group by b
-------------------------------------------------------------------------------------------
11. 如获得完整的时间格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(to_date('2008-06-24','yyyy-mm-dd'),'day') from dual; --可以转换成中文星期几
-------------------------------------------------------------------------------------------
12. 随机抽取前N条记录的问题
select * from (select * from tablename order by sys_guid()) where rownum < N;
抽取从N行到M行的记录,如从20行到30行的记录
select * from (select rownum id,t.* from table where ……
and rownum <= 30) where id > 20;
怎么查询(抽取)重复记录
select * from table t1 where t1.rowed !=(select max(rowed) from table t2 where t1.id=t2.id)
如果想删除重复记录,可以把第一个语句的select替换为delete
Delete From serv_jz_sz a
Where a.Rowid <> (Select Max(Rowid) From serv_jz_sz b Where a.Acc_Nbr = b.Acc_Nbr);
-------------------------------------------------------------------------------------------
13. 怎么样在ORACLE中定时运行存储过程
可以利用dbms_job包来定时运行作业,如执行存储过程,一个简单的例子,提交一个作业:
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;', SYSDATE, 'SYSDATE + 1');
commit;
END;
之后,就可以用以下语句查询已经提交的作业 select * from user_jobs;
-------------------------------------------------------------------------------------------
14. 如果存在就更新,不存在就插入数据,可以用一个语句实现吗?
记住,一定要用MERGE INTO来实现哦,具体可参考我赋的例子!
类似的还有,实现一条记录根据条件多表插入。可以通过Insert all语句完成,仅仅是一个语句,如:
INSERT ALL
WHEN (id=1) THEN
INTO table_1 (id, name) values(id,name)
WHEN (id=2) THEN
INTO table_2 (id, name) values(id,name)
ELSE
INTO table_other (id, name) values(id, name)
SELECT id,name FROM a;
如果没有条件的话,则完成每个表的插入,如
INSERT ALL
INTO table_1 (id, name) values(id,name)
INTO table_2 (id, name) values(id,name)
INTO table_other (id, name) values(id, name)
SELECT id,name FROM a;
-------------------------------------------------------------------------------------------
15. 固定列数的行列转换
student subject grade
-------- ------- -----
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
转换为:
语文 数学 英语
student1 80 70 60
student2 90 80 100
…… …… …… …… ……
语句如下:
Select Student,
Sum(Decode(Subject, '语文', Grade, Null)) "语文",
Sum(Decode(Subject, '数学', Grade, Null)) "数学",
Sum(Decode(Subject, '英语', Grade, Null)) "英语"
From Table
Group By Student
-------------------------------------------------------------------------------------------
16. 怎么样实现分组取前N条记录 (如获取每个部门薪水前三名的员工)
Select *
From (Select Depno, Ename, Sal,
Row_Number() Over(Partition By Depno Order By Sal Desc) Rn
From Emp)
Where Rn <= 3
分享到:
相关推荐
通过"Oracle Database 12c PL/SQL开发指南"中的实例源代码,读者可以亲手实践上述各种概念和技术,从而更深入地理解和掌握PL/SQL的使用。这些源代码通常包含了各种实际场景的应用,例如业务逻辑的实现、数据处理的...
标题中提到的“PL/SQL开发文档1”,说明本文档是关于PL/SQL开发的入门级指导材料,面向希望学习使用PL/SQL进行数据库程序单元开发的用户。文档很可能是Oracle公司内部或其授权的培训机构用于教学目的的官方材料。 ...
在实际工作中,这样的工具通常会提供友好的图形用户界面(GUI),方便用户编写和执行PL/SQL代码,同时可能还包含调试、代码提示、自动完成、数据库对象浏览器等功能,极大地提升了数据库管理员和开发人员的工作效率...
PL/SQL Developer是一款专为PL/SQL编程和Oracle数据库管理设计的强大开发工具。这款软件提供了全面的功能,使得数据库开发者和管理员能够高效地进行各种数据库操作,包括编写、调试、执行和管理PL/SQL代码,以及...
Oracle 10g PL/SQL 是Oracle数据库系统中用于创建和管理存储过程、函数、触发器等数据库对象的编程语言。本教程旨在为初学者提供一个全面的学习平台,同时也为经验丰富的开发者提供参考资料。PL/SQL是Oracle特有的...
这份"PL/SQL最新中文手册"对于任何想要深入理解并有效利用PL/SQL进行Oracle数据库开发的人来说,都是宝贵的资源。它将帮助读者不仅掌握语法,还能理解最佳实践和性能优化策略。通过深入学习和实践,开发者可以编写出...
在"Oracle PL/SQL实例精解 数据库建立代码"中,我们将深入探讨如何在Oracle数据库中使用PL/SQL来构建一个名为"student"的模式,这包括创建数据库对象如表、索引,以及填充样本数据。 首先,"student"模式可能包含一...
### PL/SQL 开发手册:Oracle 初学者的全面指南 #### 一、PL/SQL:Oracle 的核心编程语言 PL/SQL(Procedural Language for SQL)是Oracle数据库的标准编程语言,它结合了SQL的强大数据操作能力和过程化语言的控制...
PL/SQL Developer是一款由Allround Automations公司开发的专业Oracle数据库开发工具,专为编写、调试、测试和管理PL/SQL代码而设计。标题中的“pl/sql developer11.0”指的是该软件的第11个主要版本。在本文中,我们...
这本书“Oracle PL/SQL实例编程”显然旨在深入讲解如何利用PL/SQL进行实际开发工作,通过实例来教授读者掌握这一技能。下面我们将深入探讨PL/SQL的关键知识点。 1. **基础语法**:PL/SQL是过程化SQL,它包含声明...
Oracle PL/SQL是一种强大的编程语言,它结合了SQL的数据处理能力与PL的程序设计特性,是Oracle数据库系统中用于创建存储过程、函数、触发器和包的主要工具。在这个"Oracle PL/SQL实战(待续)"的主题中,我们将深入...
作者的写作手法源自于在哥伦比亚大学教授pl/sql编程技术的经验,深度探索pl/sql编程技术,融合自己的最佳实践。使用本书,读者可以快速掌握pl/sql编程基本知识,并建立工程化的概念,是市面上难得的pl/sql教程。...
PL/SQL(Procedural Language/Structured Query Language)是Oracle专为数据库操作设计的一种过程化编程语言,结合了SQL的强大查询功能与高级编程语言的控制结构,使得数据库管理员和开发人员能够更高效、安全地管理...
6. **兼容性**:PL/SQL可以与多种开发工具和编程语言(如Java、C#)协同工作,增强了其适用范围。 #### 三、PL/SQL的结构与组成部分 PL/SQL程序的基本结构由三个部分组成: 1. **声明部分(DECLARE)**:定义变量、...
总的来说,64位的PL/SQL Developer是Oracle开发人员的一个强大工具,它可以提供更高效的工作流程,减少因内存限制带来的问题,同时保持易于使用的界面和丰富的功能集。如果你在处理大型数据库或者需要更高的性能,...
在"Oracle PL/SQL 实例精解(第4版涵盖Oracle 11g)+源码脚本"中,读者可以深入理解PL/SQL的各种概念和实践技巧。 本书详细讲解了Oracle 11g版本中的PL/SQL语言,这涵盖了从基础语法到高级特性的广泛范围。PL/SQL是...
PL/SQL Developer是一款流行的Oracle数据库开发工具,它提供了编写、调试、测试和优化PL/SQL代码的功能。而VCS插件的引入,使得开发者无需离开这个熟悉的环境,就能完成版本控制的相关任务,如版本对比、历史查看、...
1. **PL/SQL编程支持**:PL/SQL Developer内置了强大的PL/SQL编辑器,支持语法高亮、自动完成、错误检查等功能,使开发人员能够高效编写PL/SQL代码。此外,还具备代码折叠、查找替换、书签设置等实用功能,提高了...
### Oracle PL/SQL程序设计(第5版)(上下册)知识点概述 ...本书不仅覆盖了PL/SQL的基础知识,还包括了大量的实战经验和高级主题,对于想要深入了解和掌握Oracle数据库的读者来说是非常宝贵的资源。
PL/SQL Developer是一款专为Oracle数据库设计的集成开发环境,它极大地简化了PL/SQL语言的编写、调试和管理任务。远程连接Oracle数据库是PL/SQL Developer的一项关键功能,允许用户在本地计算机上操作和管理远程...