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过程(Procedure)的相关知识点,特别是如何在Oracle数据库中创建存储过程,并通过示例理解其工作原理。 ### Oracle存储过程简介 存储过程是一种数据库对象,它是由SQL...
毕设和企业适用springboot企业健康管理平台类及活动管理平台源码+论文+视频.zip
本项目为基于layui框架开发的省市复选框组件设计源码,集成了115个文件,涵盖75个GIF动画、23个JavaScript脚本、6个CSS样式表、2个PNG图片、1个许可证文件、1个Markdown文档以及多种字体文件。该组件旨在提供一套便捷的省市多选解决方案,适用于各类需要地区选择的场景。
labview程序代码参考学习使用,希望对你有所帮助。
毕设和企业适用springboot社区服务类及互联网金融平台源码+论文+视频
毕设和企业适用springboot企业协作平台类及网络营销平台源码+论文+视频
毕设和企业适用springboot商城类及风险控制平台源码+论文+视频
立方体、球体、金字塔检测26-YOLO(v5至v11)、CreateML、Paligemma、TFRecord、VOC数据集合集.rarRobodog-V4 2023-06-21 11:41 PM ============================= *与您的团队在计算机视觉项目上合作 *收集和组织图像 *了解和搜索非结构化图像数据 *注释,创建数据集 *导出,训练和部署计算机视觉模型 *使用主动学习随着时间的推移改善数据集 对于最先进的计算机视觉培训笔记本,您可以与此数据集一起使用 该数据集包括255张图像。 立方体以创建格式注释。 将以下预处理应用于每个图像: *像素数据的自动取向(带有Exif-Arientation剥离) *调整大小为640x640(拉伸) 应用以下扩展来创建每个源图像的3个版本: * 0到4.75像素之间的随机高斯模糊 *将盐和胡椒噪声应用于5%的像素
毕设和企业适用springboot社交互动平台类及数据智能化平台源码+论文+视频
labview程序代码参考学习使用,希望对你有所帮助。
毕设和企业适用springboot人工智能类及企业数字资产管理平台源码+论文+视频
毕设和企业适用springboot社交互动平台类及跨境电商管理平台源码+论文+视频
毕设和企业适用springboot实验室类及文化旅游信息平台源码+论文+视频.zip
毕设和企业适用springboot汽车管理类及消费品管理平台源码+论文+视频
毕设和企业适用springboot实验室类及音频处理平台源码+论文+视频.zip
毕设和企业适用springboot企业数据管理平台类及直播流媒体平台源码+论文+视频
毕设和企业适用springboot企业云存储平台类及无人驾驶系统源码+论文+视频
labview程序代码参考学习使用,希望对你有所帮助。
labview程序代码参考学习使用,希望对你有所帮助。
毕设和企业适用springboot企业知识管理平台类及供应链优化系统源码+论文+视频