原创

Oracle基础知识

1、CBO(Cost-Based Optimizer,查询成本优化器):Oracle基于CBO来产生实际的执行计划。
2、查询语句的逻辑处理顺序:FROM》WHERE》GROUP BY》HAVING》SELECT》ORDER BY;
联结语句处理顺序:交叉连接(笛卡尔积)》内联结》外联结。
3、SQL中逻辑比较的结果可能是TRUE、FALSE、UNDEFINED(未知)。
4、通过子查询更新数据时,比如用t5表的数据去更新t4表的数据,t5表的联结条件必须为主键,以满足t5表关联条件的数据是唯一的。
eg:update (select t4.salarys,t5.salarys ns from t4,t5 where t4.userid=t5.userid) set salarys=ns;
即t5.userid必须是主键,否则报错:ORA-01779: 无法修改与非键值保存表对应的列。
5、实例由系统全局内存区域(System Global Area,SGA)以及一系列的后台进程组成。
6、执行过的SQL语句存放于共享池的库高速缓存区域,所有的数据库对象信息存放于共享池的
数据字典高速缓存区域。
7、锁存器:Oracle为了读取存放在库高速缓存或者其他内存结构中的信息时必须获得的一种锁。
锁存器可以保护库高速缓存避免被两个同时进行的会话修改,或者一个会话正要读取的信息被另一个
会话修改而导致的破坏。锁存器是串行的。硬解析时会用到锁存器,应尽量减少硬解析(比如使用绑定变量)。
8、查询转换(发生在语法、对象和权限检查之后,优化器为了决定最终的执行计划而为不同的计划
计算成本预估之前):
(1)、视图合并
视图合并是一种能将内嵌或者储存式视图展开为能够独立分析或者与查询剩余部分合并为总体执行计划
的独立查询块的转换。改写后的语句基本不包含视图。复杂视图合并使用complexviewmerging参数控制,
默认值为true。alter session set "complexviewmerging"=FALSE.
(2)、子查询解嵌套
子查询解嵌套与视图合并的相似之处在于子查询也是通过一个单独的查询块来表示的。可合并的视图
与可以解嵌套的子查询之间的主要区别在于位置:子查询位于where子句。最典型的解嵌套就是将子查询
转变为表联结。子查询解嵌套由unnestsubquery控制,默认值为true。
(3)、联结消除(表消除)
从查询中清除冗余的表。冗余表:仅在联结谓语中出现的列,并且与这张表的联结不会增加或减少结果集的条数。
(4)、排序消除
最常见的一种情况是,优化器选择在order by子句的列上的索引。
(5)、谓语前推
将谓语从一个内含查询块中应用到不可合并的查询块中。目标是允许索引的使用或者让其他数据集筛选能够在查询中更早地进行。
rownum不仅会禁止谓语推进(NOPUSHPRED),也会禁止视图合并(NOMERGE)。
(6)、使用物化视图重写查询
查询重写发生在当某个查询或者查询的一部分已经被保存为物化视图,转化器重写该查询以使用
预先计算好的物化视图数据而不需要执行当前查询的转换。自动发生的查询重写通过使用
queryrewriteenabled参数启用。
9、当执行一个SQL查询时,返回的由满足查询的数据行组成的响应,实际上是由一系列单独执行的调用完成的。为了完成响应,查询将会完成解析、绑定(包含绑定变量时有该步骤)、执行、提取(FETCH)的步骤。
在查询执行过程中可能会有一个或多个提取调用,每次返回满足查询结果所需的一部分数据行。一次返回的行数称为列大小,可以通过set arraysize n来配置。
10、两种基本的数据访问途径:全扫描或者索引扫描。全扫描(全表扫描或快速全索引扫描)过程中,多个块被读入到一个IO运算中,所请求的块数目可以是1个到dbfilemultiblockreadcount参数所指定的数目范围内的任意个。
全扫描中,当对扫描进行多块读取调用时,Oracle将最多读取到位于表中高水位线的数据块。高水位线标出了表中最后一块有数据写入的数据块。往后的操作中,即使几乎所有数据行都被删除,并且一些块
实际上已经完全变成空的,高水位线还是保持不变。当进行全扫描时,到高水位线为止的所有数据块都将被读取,意味着实际上不需要的空数据块也被读取了。对于频繁加载和清除的表(使用delete而非truncate),可能会发现响应时间变慢。
索引扫描首先扫描索引叶子块以取得特定的rowid,然后利用rowid访问父表取得实际的行数据,这些访问都是通过单块读取来完成的。
11、索引是按升序来排列的。
12、索引扫描方式:索引唯一扫描、索引范围扫描、索引全扫描、索引全扫描(MIN/MAX)、索引跳跃扫描(谓语是非索引引导列)、索引快速全扫描(所有索引块通过多块读取,不能避免排序。在查询列表中所有字段都包含在
索引中并且索引中至少有一列具有非空约束时)。
13、表联结的方法:
嵌套循环连接-嵌套循环联结的运算成本主要包括读取外层表中的每一行并将其与所匹配的内层表中的行联结。
散列联结-只有在相等联结的情况下才能进行。
排序合并联结-在条件为非等式时,排序合并联结通常是最好的选择。
笛卡尔联结-没有指定联结列。
外联结可以与任何链接方法一起使用。
14、SQL是基于集合的,解决问题时先寻找基于集合的方法,只有在必要时才采用过程化的方法。
15、minus用来替换not exists,intersect用来替换exists。
16、集合运算、group by、order by将所有空值(null)作为相等的值来对待。order by默认的排序规则(ASC)是将空指放在最后,如果要放在最前,需在order by子句后面加上nulls first。count(列名)、sum、
avg、min、max会去掉空值,count使用常量时不排除空值。
17、SQLPLUS中解释计划(预期的执行计划,并没有实际的执行,与实际的执行计划有区别)并查看:
set autotrace traceonly explain;
explain plan for
查询语句;
select from table(dbmsxplan.display(format=>'BASIC+COST+PREDICATE'));
解释计划默认存储在表plantable中。解释计划不考虑绑定变量的数据类型,所有的绑定变量都是varchar2类型。
18、当进行比较的两种数据类型不匹配时,Oracle总是尝试将字符串类型转换为与之匹配的非字符串类型。比如表gid字段为varchar2类型,当查询条件谓语为gid=1时,实际执行的计划所示出的谓语是
tonumber(gid)=1。
19、通过v$sqlplan查看实际执行计划运算。
20、获取最近执行的SQL语句:v$sql。
21、执行计划的行数据源执行统计信息存储在v$sqlplanstatistics中,v$sqlplanstatisticsall复合视图包括了v$sqlplan的所有列加上v$sqlplanstatistics中的列以及一些包含内存使用信息
的附加列,dbmsxplan.displaycursor函数输出的相关统计信息在此视图中均以前缀LAST开头。对于每一个运算,A-Rows:返回多少行;Buffers:多少次一致性读取;Reads:多少次物理读取;Starts:每个步骤执行的次数。
dbmsxplan.displaycursor的三个参数:SQLID,CURSORCHILDNO,FORMAT。SQLID,CURSORCHILDNO为null,表明需要取出上一个执行语句的执行计划。eg:select from table(dbmsxplan.displaycursor(null,null,'allstats last'))。
22、SQL监控报告的信息存储在v$sqlmonitor、v$sqlplanmonitor,实时的SQL监控报告通过dbmssqltune.reportsqlmonitor函数通过读取上述视图生成。SQL监控报告占用内存的大小由隐藏参数sqlmonmaxplan控制,监控的执行计划的最大行数由隐藏参数sqlmonmaxplanlines控制。查看最近一条SQL的监控报告(可全部使用默认值):select dbmssqltune.reportsqlmonitor() from dual;
23、可通过查看dbatabstatistics视图的STALESTATS列值来确定统计信息是否过期。如果表中数据的变化超过了10%的阈值,这一列值为YES。可通过dbmsstats.gathertablestats(user,'EMPUSERA',estimatepercent => 100,cascade => true,methodopt => 'FOR ALL COLUMNS SIZE 1');更新统计信息,通过dbmsstats.gathertablestats(user,'EMPUSERA',estimatepercent => 100,cascade => true,methodopt => 'FOR ALL COLUMNS SIZE AUTO');更新直方图信息。

正文到此结束
该篇文章的评论功能已被站长关闭