oracle数据查询的规范与优化文档格式.docx
- 文档编号:20858946
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:13
- 大小:32.79KB
oracle数据查询的规范与优化文档格式.docx
《oracle数据查询的规范与优化文档格式.docx》由会员分享,可在线阅读,更多相关《oracle数据查询的规范与优化文档格式.docx(13页珍藏版)》请在冰豆网上搜索。
,job"
岗位"
sal"
月工资"
deptno"
部门号"
☐示例2
selectempnoemployee_number,enameemployee_name,salsalary
2fromemp;
设置别名的方法是在列明后空格,然后使用双引号,在双引号内输入别名,如果是英文别名可以在列名后空格,然后紧跟要英文别名。
☐连接运算符
连接运算符顾名思义是起到连接作用的,在查询输出中为了使得输出结果更易于阅读,更像自然语言的方式,使用连接符可移动到达这个效果。
SELECTename||'
的雇佣日期是:
'
||hiredate
2FROMemp;
使用别名和连接运算符:
||hiredate"
员工雇佣日期查询"
☐DISTINCT运算符
DICTINCT的运算符使得输出的结果中对于某列的值不允许重复,也就是该列的输出值应该是独一无二的。
为了说明它的用法,我们给出实例。
示例:
selectjob
2fromemp;
SELECTDISTINCTjob
2FROMemp;
在查询中自然可以使用算数运算符,即加、减、乘、除四则运算。
我们继续对表EMP操作。
例子10-10在查询中使用运算符。
SELECTename||‘增加10%后的月薪是:
’||sal*(1+0.1)“加薪后的员工工资”FROMemp;
☐乘积连接
笛卡尔积是数据库关系运算中的概念,这里我们不给出严格的关系代数定义,而是只给出解释性的说明,对于维护数据库的DBA来讲更具有可操作性。
笛卡尔积的结果是将两个表中的所有行的一个组合,如果表1有n行,而表2有m行,则笛卡尔积的结果有n*m行。
☐SQL>
selecte.ename,e.sal,e.hiredate,d.dname,d.loc
2fromempe,deptd
3orderbye.hiredate;
☐相等连接
相等连接是使用WHERE字句中两个表或多个表中相应列的值的相等条件来选择数据。
我们以两个表的相等连接为例,首先找到两个表中满足相等条件的记录(表中相应的行),然后再根据SELECT语句选择相应列的值。
例子:
实现相等连接的查询。
SQL>
selecte.ename,d.deptno,d.dname,d.loc
2fromdeptd,empe
3whered.deptno=e.deptno;
☐自然连接
在数据库关系运算中,自然连接是一个重要的连接运算,在ORACLE数据库中自然连接可以通过相等连接实现,但是为了与数据库关系理论结合起来,对于学过数据库原理的读者来说或者更熟悉自然连接这个概念,所以我们先用自然语言描述什么是自然连接,如果有两个表做自然连接,一般这两个表有至少一个相同的属性,首先将相同属性的相同值的行连接起来,然后去掉一个重复的相同属性的值,如果表1有n个属性,而表2有m个属性,二者具有r个相同属性,则自然连接的结果是具有(n+m-r)个属性的记录。
☐举例:
selecte.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,m,e.deptno,d.dname,d.loc
3*wheree.deptno=d.deptno
☐自连接
自连接顾名思义是一个表与自己连接,通过一些限制性的查询满足查询需要。
在EMP表中,我们知道MANAGER对于SALESMAN具有管理权,而我们需要一张表说明每一个销售员与他的经理之间的关系,则可以使用自连接实现这个查询如下例所示。
使用自然连接查询销售员与他的经理之间的关系。
selecte1.ename,e1.job,e2.mgr,e2.ename,e2.job
2fromempe1,empe2
3wheree1.mgr=e2.empno
4ande1.joblike'
SALESMAN'
;
☐不等连接
不等连接和相等连接对应,即两个表连接操作时,限制条件是不等条件,下面我们查询表EMP中每个员工的工资属于那个等级,并且按照岗位排序,此时我们需要和表SALGRADE做不等连接。
☐我们先查询SALGRADE表的内容
select*
2fromsalgrade;
☐下面我们查询EMP表中每个员工的工资等级水平,和表SALGRADE做不等值连接。
如下例所示
EMP表中每个员工的工资等级与表SALGRADE的不等值连接。
selecte.empno,e.ename,e.job,e.hiredate,s.grade
2fromempe,salgrades
3wheree.salbetweens.losalands.hisal
4*orderbye.job
解释:
在上例中使用了BETWEEN……AND使用数值范围来限制查询条件,是不等查询条件。
在上例的结果中每个员工的工资都需要和SAL表中的每个等级的工资范围比较,直到找到对应的等级水平。
☐外连接
为了更直观地说明外连接,我们给出一个例子,通过该例子的分析说明外连接的概念是实现方法。
selectename,job,emp.deptno,dept.deptno,dname,loc
2fromemp,dept
3*whereemp.deptno(+)=dept.deptno
☐在上述输出中,没有员工在DEPTNO为40的部门,但是在表EMP和表DEPT做等值连接时希望显示表DEPT中的这个DEPTNO为40的部门信息,使用外连接就可以实现这类查询。
☐在简单查询中,我们查询了表中相关列的所有记录,但是生产数据库中往往需要查询满足一定条件的记录,如查询岗位JOB为MANAGER的员工信息等,此时需要使用条件查询,条件查询使用关键字WHERE实现
☐条件查询的语句格式为:
SELECT列名[列名……]
FROM表名
WHERE条件语句
☐HAVING子句用来限制分组函数,我们先给出一个使用HAVING字句限制分组函数的例子,然后再给出在HAVING字句中使用单行子查询的例子
☐例子10-17使用HAVING子句限制分组函数。
selectjob,min(sal),avg(sal),max(sal)
2fromemp
3groupbyjob
4havingavg(sal)>
2000;
上述查询中,HAVING字句限制了分组函数AVG(sal)的输出条件,只有满足AVG(sal)>
2000时,才输出结果,目的是查询表emp中,平均工资大于2000,且按照工作岗位JOB分类后的每类岗位的最低工资,平均工资和最高工资。
☐FROM字句后跟的是表名,在一定条件下也可以使用单行子查询,例如查询表EMP中工资大于平均工资的所有员工的信息.
☐例子10-20查询表EMP中工资大于平均工资的所有员工的信息。
selecte.ename,e.sal,e.job,d.av_sal
2fromempe,(selectjob,avg(sal)av_sal
3fromemp
4groupbyjob)d
5wheree.job=d.job
6*ande.sal>
d.av_sal
☐在多行子查询中必须使用多行比较运算符,IN比较符返回子查询的中的每一个值,一旦有与该值相等的数据行则输出这些满足条件的数据行。
☐例子10-21查询那些是所在岗位中工资最低的员工信息。
selectename,job,sal,hiredate
3wheresalin(selectmin(sal)
4fromemp
5groupbyjob);
☐为了说明ANY比较符在多行子查询中的作用,我们先看一个例子,如例子20-24所示,查询表EMP中工资大于获得等于所有岗位的最大平均工资的员工信息。
即先查询每个岗位的平均工资,而查询条件是表这些平均工资的最大值大或相等的员工信息。
selectename,job,sal
3wheresal>
=all(
4selectavg(sal)
5fromemp
6*groupbyjob)
>
=ALL的为大于或者等于最大的含义,而<
=为小于或等于最小的含义
☐ANY是任何一个的意思,所以<
ANY表比多行返回结果中的任何一个值都小,所以<
ANY表示小于最大的,而相反>
ANY表示大于最小的。
selectename,job,sal
any(
6groupbyjob);
☐索引的概念。
索引是Oracle的一个对象,一旦建立索引是否使用由Oracle决定,索引中存储了特定列的排序数据,实现对表的快速访问。
使用索引可以很快查找到建立索引时列的值所在的行,而不必对表实现全表扫描,所以适当的使用索引可以减少磁盘I/O量。
☐在开始我们给出索引的特点总结,这样读者在接下来使用索引时,脑子中就有一个局限,不要认为使用索引就是好事。
☐索引的特点:
✓对于具有只读特性或较少插入、更新或删除操作的大表通常可以提高查询速度。
✓可以对表的一列或多列建立索引。
✓建立索引的数量没有限制。
✓索引需要磁盘存储,由Oracle数据库服务器自动维护。
索引对用户透明,是否使用索引是Oracle决定的。
创建索引的语法格式。
☐CREATE[UNIQUE|BITMAP]INDEX[schema.]index_name
ON[schema.]table_name
(column_name[DESC]ASC][,column_name[DESC]ASC]]………)
[REVERSE]
[TABLESPACEtablespace_name]
[PCTFREEn]
[INITRANSn]
[MAXTRANSn]
[instoragestate]
[LOGGING|NOLOGGING]
[NOSORT]
下面解释各个参数的含义:
✓UNIQUE:
说明该索引是唯一索引。
✓BITMAP:
创建位图索引。
✓DESC|ASC:
说明创建的索引为降序或升序排列。
✓REVERSE:
说明创建反向键索引。
✓TABLESPACE:
说明要创建的索引所存储的表空间。
✓PCTFREE:
索引块中预先保留的空间比列。
✓INITRANS:
每一个索引块中分配的事务数。
✓MAXTRANS:
每一个索引块中分配的最多事务数。
✓instoragestate:
说明索引中区段EXTENT如何分配。
✓LOGGING|NOLOGGING:
说明要记录|不记录索引相关的操作,并保存咋联机重做日志中。
✓NOSORT:
不需要在创建索引时再按键值进行排序。
☐创建索引(index)。
✓建立简单索引:
对EMP表建立索引。
connscott/oracle
createindexemp_ename_idx
2onemp(ename);
该例子创建一个名为emp_ename_idx的索引,在emp表的ename列创建,该索引默认存储在USERS表空间中。
✓查询索引的存储信息。
colindex_namefora20
colindex_typefora10
coltable_namefora20
coltablespace_namefora20
selectindex_name,index_type,table_name,tablespace_name
2*fromuser_indexes;
☐创建索引表空间(index)。
✓创建一个索引表空间示例。
connsystem/oracle
createtablespaceindex_tbs
2datafile'
d:
/index/index_tbs1.dbf'
3size100M
4autoextendon;
✓在表空间index_tbs中创建表的多列索引。
createindexemp_ename_sal_idx
2onemp(ename,sal)
3tablespaceindex_tbs;
✓查询创建的多行索引
selectindex_name,table_name,tablespace_name
2fromuser_indexes
3*whereindex_namelike'
EMP%'
☐查看索引
✓使用数据字典user_ind_columns
colcolumn_namefora40
selectindex_name,table_name,column_name
2fromuser_ind_columns
✓使用数据字典USER_INDEXES查看索引信息
coldroppedfora10
selectindex_name,table_name,table_owner,dropped,tablespace_name
2fromuser_indexes
☐B树索引。
B-树索引结构有三个基本组成部分:
根节点、分支节点和叶子节点,其中根节点位于索引结构的最顶端,而叶子节点位于索引结构的最低端,中间为分之节点
✓叶子节点的存储内容:
实际的值和ROWID
✓叶子节点是双向链表
✓B-树索引从根到叶子都有相等的分支节点
☐位图索引。
位图索引是Oracle10gEnterpriseEdition支持的索引机制。
位图索引使用位图标识被索引的列值,它适用于没有大量更新任务的数据仓库,因为使用位图索引时,每个位图索引项与表中大量的行有关联,当表中有大量数据更新、删除和插入时,位图索引相应地需要做大量更改,而且索引所占用的磁盘空间也会明显增加,并且索引在更新时受影响的索引需要锁定,所以位图索引不适合于有大量更新操作的OLTP系统。
☐位图索引
假设已经在EMP表的JOB列建立了位图索引,其结构如图。
在该索引图中,共用五类JOB,每类JOB对应14个比特位(对应14行记录),其中某行的在该列的值与JOB值对应则使用比特1表示,如JOB=‘CLERK’,第一行在该列对应的值是CLERK,就用比特1表示。
否则用比特0表示,其他JOB类类似。
☐创建位图索引。
createbitmapindexemp_job_bitmap_idx
2onemp(job);
☐查看位图索引
colindex_namefora25
colindex_typefora15
coltable_namefora10
selectindex_name,index_type,table_name,status
2fromuser_indexes
3*whereindex_namelike'
☐反向键索引以及优点。
✓反向键索引是指在创建索引过程中对索引列创建的索引键值的字节反向。
反向键索引适用于一种特殊的情形,如果一个索引值是按照序列值递增的,这样当连续插入大量数据时,所有的记录都将插入B-树索引结构中的最右侧的叶子节点,并且会写入同一叶子节点中,这样难以避免产生争用问题而影响索引性能。
正是为了避免找个问题引入了反向键索引。
使用反向键索引使得每个键值被颠倒顺序,将序列性的键值分散开,使得键值平衡地保存在叶子节点中。
☐反向键索引的键值反向示意图
☐创建反向键索引
createindexemp_sal_reverse_idx
2onemp(sal)reverse;
☐查看反向键索引
selectindex_name,index_type,table_name
3whereindex_namelike'
☐基于函数的索引。
在用户查询数据时,如果查询语句的WHERE字句中有函数存在,Oracle使用函数索引将加快查询速度。
基于函数的索引使用表的列的函数值作为键值建立索引结构。
☐创建基于UPPER函数的函数索引
createindexdept_dname_idx
2ondept(UPPER(dname));
☐上述索引的好处。
当用户需要如下的查询:
SELECTUPPER(DNAME)FROMDEPTWHEREUPPER(DNAME)=’NEWYORK’
时,Oracle就不必对WHERE字句的条件做转化并逐行检索,对于选择的结果也不必使用UPPER函数再做转换的计算,显然此时使用基于函数的索引会极大的提高查询速度,如果该表很大的话,性能的提高是很明显的。
☐本地分区索引。
✓如果一个基表是分布在多个分区上的,即该表是分区表,则对表表建立的索引和表分区一一对应,即每个分区表的索引和该分区表在同一个分区上存储,这样在数据分区和索引分区之间存在对应关系。
这就是本地分区索引的特点。
✓使用本地分区索引使得索引和该索引所对应的数据分区存分布在同一个分区中,使得索引和基表得以均匀分布,如果修改了基表分区,则Oracle自动维护对应的索引分区。
✓创建本地分区索引
createindexproducts_pro_date_idx
2onproducts(pro_date)local
3tablespaceproduct_tbs;
☐监控索引的引用。
虽然创建了索引,但是如何监控索引的使用情况呢,否则就无法判断我们创建的索引的有效性。
对于无效的索引可以删除以释放磁盘空间并较少DML操作带来的修改索引的各种开销。
✓启动对索引EMP_ENAME_BITMAP_IDX的监控。
alterindexEMP_ENAME_BITMAP_IDX
2monitoringusage;
接下来,我们要等待一个周期,在这个过程中等待用户对表EMP的各种操作,对于OLTP(联机事务处理)系统这个周期可能很短,而对于数据仓库则需要更多的时间来监控。
✓模拟OLTP事务
Selectenamelfromemployment;
✓终止监控EMP_ENAME_BITMAP_IDX索引
2nomonitoringusage;
✓使用数据字典视图v$object_usage查看EMP_ENAME_BITMAP_IDX
索引的使用情况。
selectindex_name,table_name,monitoring,used
2fromv$object_usage;
✓查看索引EMP_ENAME_BITMAP_IDX的监控周期
selectindex_name,start_monitoring,end_monitoring
2fromv$object_usage
3whereindex_name='
EMP_ENAME_BITMAP_IDX'
☐重建索引。
索引需要维护,不然如果建立了索引的表中有大量的删除和插入操作,会使得索引很大,因为在删除操作后,删除值所占用的索引空间不能被索引自动重新使用,而插入操作会不断使得索引变大,对于大表和DML操作很频繁的表索引的维护是很重要的,Oralce提供了一个REBUILD指令来重建索引,使得索
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 数据 查询 规范 优化