SELECT A.PJ_ORDER, D.MENU_ID, DECODE(D.MENU_ID, 'PJ000', PJ_NM, MENU_NAME) AS MENU_NAME, PJ_NM, PJ_CD, MENU_LEVEL, B.ROLE_ID, A.PJ_CREAT_TP, B.PJ_ROLE, PROGRAM_ID
FROM ( /*PM*/SELECT /*+ ORDERED USE_NL ( B A ) */
1 AS PJ_ORDER, 'PM' AS PJ_ROLE, 0 AS MENU_LEVEL, A.PJ_CD, A.PJ_NM,
DECODE (A.PJ_CREAT_TP,
'2A', 'SI',
'7S', 'SM',
'3N', '사후',
'관리'
) AS PJ_CREAT_TP_NM,
A.PJ_CREAT_TP
FROM TPS01M B, TPD01M A在这个sql语句中有一些代码不是很懂。
1. DECODE(D.MENU_ID, 'PJ000', PJ_NM, MENU_NAME) AS MENU_NAME这是什么意思?
2. FROM ( /*PM*/SELECT /*+ ORDERED USE_NL ( B A ) */
1 AS PJ_ORDER,还有这段代码
FROM ( /*PM*/SELECT /*+ ORDERED USE_NL ( B A ) */
1 AS PJ_ORDER, 'PM' AS PJ_ROLE, 0 AS MENU_LEVEL, A.PJ_CD, A.PJ_NM,
DECODE (A.PJ_CREAT_TP,
'2A', 'SI',
'7S', 'SM',
'3N', '사후',
'관리'
) AS PJ_CREAT_TP_NM,
A.PJ_CREAT_TP
FROM TPS01M B, TPD01M A在这个sql语句中有一些代码不是很懂。
1. DECODE(D.MENU_ID, 'PJ000', PJ_NM, MENU_NAME) AS MENU_NAME这是什么意思?
2. FROM ( /*PM*/SELECT /*+ ORDERED USE_NL ( B A ) */
1 AS PJ_ORDER,还有这段代码
'7S', 'SM',
'3N', '사후',
'관리')
比较A.PJ_CREAT_TP的值,如果为'2A'则返回'SI',为'7S'返回'SM', 为'3N'返回'사후',都不符合,则返回'관리'
1 AS PJ_ORDER 则是表示定议一个字段PJ_ORDER,其值恒为1
1 AS PJ_ORDER,还有这段代码
/*开头和*/结尾的都是注释,可以忽略
这句就是FROM ( SELECT 1 AS PJ_ORDER
/*+ …… */
-----------------------------------------------------
不是注释,而是SQL语句的优化方法(提示)。2、
DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然,如果未能与任何一个实参序偶匹配成功,则函数也有默认的返回值。区别于SQL的其它函数,DECODE函数还能识别和操作空值。
其具体的语法格式如下:
DECODE(input_value,value,result[,value,result…][,default_result]);
其中:
input_value 试图处理的数值。DECODE函数将该数值与一系列的序偶相比较,以决定最后的返回结果
value 是一组成序偶的数值。如果输入数值与之匹配成功,则相应的结果将被返回。
对应一个空的返回值,可 以使用关键字NULL于之对应
result 是一组成序偶的结果值
default_result 未能与任何一序偶匹配成功时,函数返回的默认值 下面的例子说明了,如何读取用户CHECKUP表SEAPARK中的BLOOD_TEST_FLAG列下的项目,作为DECODE函数的实参支持值。
SELECT checkup_type, DECODE(blood_test_flag, 'Y','Yes', 'N','No', NULL,'None', 'Invalid')
FROM checkup;
这个SQL语句展示了DECODE函数的左右基础功能特征。函数的输入值时BLOOD_TEST_FLAG列的数据,如果该列的值是'Y',那么函数返回'YES;如果该列的值是NULL,那么函数返回'None';如果没有与任何一个序偶匹配成功,则表示该列当前值无效,函数返回'Invali'。
/*+ …… */
-----------------------------------------------------
不是注释,而是SQL语句的优化方法(提示)。 还是不明白,是干什么的?
ordered是指以表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.。
USE_NL(table)是将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表。
作者: 不详 来源: ChinaITLab http://www.csai.cn 2006年03月10日
如何干预执行计划 - - 使用hints提示 基于代价的优化器是很聪明的,在绝大多数情况下它会选择正确的优化器,减轻了DBA的负担。但有时它也聪明反被聪明误,选择了很差的执行计划,使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预,告诉优化器使用我们指定的存取路径或连接类型生成执行计划,从而使语句高效的运行。例如,如果我们认为对于一个特定的语句,执行全表扫描要比执行索引扫描更有效,则我们就可以指示优化器使用全表扫描。在ORACLE中,是通过为语句添加hints(提示)来实现干预优化器优化的目的。 hints是oracle提供的一种机制,用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用hints来实现:
1) 使用的优化器的类型
2) 基于代价的优化器的优化目标,是all_rows还是first_rows。
3) 表的访问路径,是全表扫描,还是索引扫描,还是直接利用rowid。
4) 表之间的连接类型
5) 表之间的连接顺序
6) 语句的并行程度 除了”RULE”提示外,一旦使用的别的提示,语句就会自动的改为使用CBO优化器,此时如果你的数据字典中没有统计数据,就会使用缺省的统计数据。所以建议大家如果使用CBO或HINTS提示,则最好对表和索引进行定期的分析。 如何使用hints: Hints只应用在它们所在sql语句块(statement block,由select、update、delete关键字标识)上,对其它SQL语句或语句的其它部分没有影响。如:对于使用union操作的2个sql语句,如果只在一个sql语句上有hints,则该hints不会影响另一个sql语句。 我们可以使用注释(comment)来为一个语句添加hints,一个语句块只能有一个注释,而且注释只能放在SELECT, UPDATE, or DELETE关键字的后面 使用hints的语法: {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]... 注解:
1) DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字,包含提示的注释只能出现在这些关键字的后面,否则提示无效。
2) “+”号表示该注释是一个hints,该加号必须立即跟在”/*”的后面,中间不能有空格。
3) hint是下面介绍的具体提示之一,如果包含多个提示,则每个提示之间需要用一个或多个空格隔开。
4) text 是其它说明hint的注释性文本 如果你没有正确的指定hints,Oracle将忽略该hints,并且不会给出任何错误。 使用全套的hints: 当使用hints时,在某些情况下,为了确保让优化器产生最优的执行计划,我们可能指定全套的hints。例如,如果有一个复杂的查询,包含多个表连接,如果你只为某个表指定了INDEX提示(指示存取路径在该表上使用索引),优化器需要来决定其它应该使用的访问路径和相应的连接方法。因此,即使你给出了一个INDEX提示,优化器可能觉得没有必要使用该提示。这是由于我们让优化器选择了其它连接方法和存取路径,而基于这些连接方法和存取路径,优化器认为用户给出的INDEX提示无用。为了防止这种情况,我们要使用全套的hints,如:不但指定要使用的索引,而且也指定连接的方法与连接的顺序等。 下面是一个使用全套hints的例子,ORDERED提示指出了连接的顺序,而且为不同的表指定了连接方法:SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b)
USE_NL (glcc glf) USE_MERGE (gp gsb) */
b.application_id, b.set_of_books_id ,
b.personnel_id, p.vendor_id Personnel,
p.segment1 PersonnelNumber, p.vendor_name Name
FROM jl_br_journals j, jl_br_balances b,
gl_code_combinations glcc, fnd_flex_values_vl glf,
gl_periods gp, gl_sets_of_books gsb, po_vendors p
WHERE ... 指示优化器的方法与目标的hints: ALL_ROWS -- 基于代价的优化器,以吞吐量为目标
FIRST_ROWS(n) -- 基于代价的优化器,以响应时间为目标
CHOOSE -- 根据是否有统计信息,选择不同的优化器
RULE -- 使用基于规则的优化器 例子:
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
FROM employees
WHERE department_id = 20; SELECT /*+ CHOOSE */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566; SELECT /*+ RULE */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566; 指示存储路径的hints: FULL /*+ FULL ( table ) */
指定该表使用全表扫描
ROWID /*+ ROWID ( table ) */
指定对该表使用rowid存取方法,该提示用的较少
INDEX /*+ INDEX ( table [index]) */
使用该表上指定的索引对表进行索引扫描
INDEX_FFS /*+ INDEX_FFS ( table [index]) */
使用快速全表扫描
NO_INDEX /*+ NO_INDEX ( table [index]) */
不使用该表上指定的索引进行存取,仍然可以使用其它的索引进行索引扫描 SELECT /*+ FULL(e) */ employee_id, last_name
FROM employees e
WHERE last_name LIKE :b1; SELECT /*+ROWID(employees)*/ *
FROM employees
WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND employee_id = 155; SELECT /*+ INDEX(A sex_index) use sex_index because there are few
male patients */ A.name, A.height, A.weight
FROM patients A
WHERE A.sex = 'm'; SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id
FROM employees
WHERE employee_id > 200; 指示连接顺序的hints:
ORDERED /*+ ORDERED */
按from 字句中表的顺序从左到右的连接
STAR /*+ STAR */
指示优化器使用星型查询 SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
FROM customers c, order_items l, orders o
WHERE c.cust_last_name = :b1
AND o.customer_id = c.customer_id
AND o.order_id = l.order_id; /*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */ 指示连接类型的hints:
USE_NL /*+ USE_NL ( table [,table, ...] ) */
使用嵌套连接
USE_MERGE /*+ USE_MERGE ( table [,table, ...]) */
使用排序- -合并连接
USE_HASH /*+ USE_HASH ( table [,table, ...]) */
使用HASH连接
注意:如果表有alias(别名),则上面的table指的是表的别名,而不是真实的表名 具体的测试实例:
create table A(col1 number(4,0),col2 number(4,0), col4 char(30));
create table B(col1 number(4,0),col3 number(4,0), name_b char(30));
create table C(col2 number(4,0),col3 number(4,0), name_c char(30)); select A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'B'
6 3 SORT (JOIN)
7 6 TABLE ACCESS (FULL) OF 'A'
8 1 SORT (JOIN)
9 8 TABLE ACCESS (FULL) OF 'C' select /*+ ORDERED */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=110)
1 0 HASH JOIN (Cost=5 Card=1 Bytes=110)
2 1 HASH JOIN (Cost=3 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26) select /*+ ORDERED USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=110)
1 0 HASH JOIN (Cost=4 Card=1 Bytes=110)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26) 创建索引:
create index inx_col12A on a(col1,col2);
select A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C' select /*+ ORDERED */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=110)
1 0 HASH JOIN (Cost=5 Card=1 Bytes=110)
2 1 HASH JOIN (Cost=3 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=110)
1 0 HASH JOIN (Cost=4 Card=1 Bytes=110)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26) select /*+ USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10; 我们这个查询的意思是让A、C表做NL连接,并且让A表作为内表,但是从执行计划来看,没有达到我们的目的。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=110)
1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=110)
2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=52)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 SORT (JOIN) (Cost=1 Card=1 Bytes=26)
5 4 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756) 对对象进行分析后:
analyze table a compute statistics;
analyze table b compute statistics;
analyze table c compute statistics;
analyze index inx_col12A compute statistics;
select A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=8 Bytes=336)
1 0 HASH JOIN (Cost=5 Card=8 Bytes=336)
2 1 MERGE JOIN (CARTESIAN) (Cost=3 Card=8 Bytes=64)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
4 2 SORT (JOIN) (Cost=2 Card=4 Bytes=16)
5 4 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020) select /*+ ORDERED */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=5 Card=9 Bytes=378)
2 1 HASH JOIN (Cost=3 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8) select /*+ ORDERED USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=7 Card=9 Bytes=378)
2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8) select /*+ USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=7 Card=9 Bytes=378)
2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8) select /*+ ORDERED USE_NL (A B C) */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=35 Card=9 Bytes=378)
1 0 NESTED LOOPS (Cost=35 Card=9 Bytes=378)
2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8) 对于这个查询我无论如何也没有得到类似下面这样的执行计划:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=35 Card=9 Bytes=378)
1 0 NESTED LOOPS (Cost=35 Card=9 Bytes=378)
2 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
3 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
4 3 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
5 3 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020) 从上面的这些例子我们可以看出:通过给语句添加HINTS,让其按照我们的意愿执行,有时是一件很困难的事情,需要不断的尝试各种不同的hints。对于USE_NL与USE_HASH提示,建议同ORDERED提示一起使用,否则不容易指定那个表为驱动表。