利用分析函数改写范围判断自关联查询

最近碰到一个单条SQL运行效率不佳导致数据库整体运行负载较高的问题。

分析、定位数据库的主要负载是这条语句引起的过程相对简单,通过AWR报告就可以比较容易的完成定位,这里就不赘述了。

现在直接看一下这个导致性能问题的SQL语句,其对应的SQL REPORT统计如下:

Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 363,741 363,740.78 8 .42
CPU Time (ms) 362,770 362,770.00 8 .81
Executions 1    
Buffer Gets 756 756.00 0.00
Disk Reads 0 0.00 0.00
Parse Calls 1 1.00 0.01
Rows 50,825 50,825.00  
User I/O Wait Time (ms) 0  
Cluster Wait Time (ms) 0    
Application Wait Time (ms) 0    
Concurrency Wait Time (ms) 0    
Invalidations 0    
Version Count 1    
Sharable Mem(KB) 28    

 

从SQL的性能指标上看,其单次执行需要6分钟左右,处理5万多条记录,逻辑度只有756,主要消耗时间在CPU上。而这里就存在疑点,逻辑读如此之低,而CPU时间花费又如此之高,那么这些CPU都消耗在哪里呢?当然这个问通过SQL的统计信息中是找不到答案的,我们下面关注SQL的执行计划:

 

Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
0 SELECT STATEMENT       1226 (100)  
1    SORT ORDER BY   49379 3375K 3888K 1226 (2) 00:00:05
2      HASH JOIN ANTI   49379 3375K 2272K 401 (3) 00:00:02
3        TABLE ACCESS FULL T_NUM 49379 1687K   88 (4) 00:00:01
4        TABLE ACCESS FULL T_NUM 49379 1687K   88 (4) 00:00:01

 

从执行计划看,Oracle选择了HASH JOIN ANTI,JOIN的两张表都是T_NUM,且都采用了全表扫描,并未选择索引。仅靠执行计划也只等得到上面的结论,至于为什么不选择索引,以及为什么执行时间过长,还需要进一步的分析。

将原SQL进行简单脱密改写后, SQL文本类似如下:

 

SELECT BEGIN, END, ROWID, LENGTH(BEGIN)

FROM T_NUM A

WHERE NOT EXISTS (

SELECT 1

FROM T_NUM B

WHERE B.BEGIN <= A.BEGIN

AND B.END >= A.END

AND B.ROWID != A.ROWID

AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN));

 

如果分析SQL语句,会发现这是一个自关联语句,在BEGIN字段长度相等的前提下,想要找到哪些不存在BEGIN比当前记录BEGIN小且END比当前记录END大的记录。

简单一点说,表中的记录表示的是由BEGIN开始到END截至的范围,那么当前想要获取的结果是找出哪些没有范围所包含的范围。需要注意的是,对于当前的SQL逻辑,如果存在两条范围完全相同的记录,那么最终这两条记录都会被舍弃。

业务的逻辑并不是特别复杂,但是要解决一条记录与其他记录进行比较,多半采用的方法是自关联,而在这个自关联中,既有大于等于又有小于等于,还有不等于,仅有的一个等于的关联条件,来自范围段BEGIN的长度的比较。

显而易见的是,如果是范围段本身的比较,其选择度一般还是不错的,但是如果只是比较其长度,那么无疑容易产生大量的重复,比如在这个例子中:

 

SQL> select length(begin), count(*) from t_num group by length(begin) order by 2 desc;

 

LENGTH(BEGIN)   COUNT(*)

————- ———-

12      22096

11       9011

13       8999

14       8186

16         49

9         45

8         41

7         27

 

大量重复的数据出现在长度为11到14的范围上,在这种情况下,仅有的一个等值判断条件LENGTH(BEGIN)是非常低效的,这时一条记录根据这个等值条件会关联到近万条记录,设置关联到两万多条记录,显然大量的实践消耗在低效的连接过程中。

再来看一下具体的SQL语句,会发现几乎没有办法建立索引,因为LENGTH(BEGIN)的选择度非常查,而其他的条件都是不等查询,选择度也不会好,即使建立索引,强制执行选择索引,效率也不会好。

那么如果想要继续优化这个SQL,就只剩下一个办法,那就是SQL的改写。对于自关联查询而言,最佳的改写方法是利用分析函数,其强大的行级处理能力,可以在一次扫描过程中获得一条记录与其他记录的关系,从而消除了自关联的必要性。

SQL改写结果如下:

 

SELECT BEGIN, OLDEND END, LENGTH(BEGIN)

FROM (

SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,

ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN

FROM

(

SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END

FROM T_NUM

)

)

WHERE RN = 1

AND CN = 1;

 

简单的说,内层的分析函数MAX用来根据BEGIN从小到大,END从大到小的条件,确定每个范围对应的最大的END的值。而外层的两个分析函数,COUNT用来去掉完全重复的记录,而ROW_NUMBER用来获取范围最大的记录(也就是没有被其他记录的范围所涵盖)。

改写后,这个SQL避免对自关联,也就不存在关联条件重复值过高的性能隐患了。在模拟环境中,性能对比如下:

SQL> SELECT BEGIN, END, ROWID, LENGTH(BEGIN)

2  FROM T_NUM A

3  WHERE NOT EXISTS (

4     SELECT 1

5     FROM T_NUM B

6     WHERE B.BEGIN <= A.BEGIN

7     AND B.END >= A.END

8     AND B.ROWID != A.ROWID

9     AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN))

10  ;

 

48344 rows selected.

 

Elapsed: 00:00:57.68

 

Execution Plan

———————————————————-

Plan hash value: 2540751655

 

————————————————————————————

| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

————————————————————————————

|   0 | SELECT STATEMENT   |       | 48454 |  1703K|       |   275   (1)| 00:00:04 |

|*  1 |  HASH JOIN ANTI    |       | 48454 |  1703K|  1424K|   275   (1)| 00:00:04 |

|   2 |   TABLE ACCESS FULL| T_NUM | 48454 |   851K|       |    68   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T_NUM | 48454 |   851K|       |    68   (0)| 00:00:01 |

————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

1 – access(LENGTH(TO_CHAR(“B”.”BEGIN”))=LENGTH(TO_CHAR(“A”.”BEGIN”)))

filter(“B”.”BEGIN”<=”A”.”BEGIN” AND “B”.”END”>=”A”.”END” AND

“B”.ROWID<>”A”.ROWID)

 

 

Statistics

———————————————————-

0  recursive calls

0  db block gets

404  consistent gets

0  physical reads

0  redo size

2315794  bytes sent via SQL*Net to client

35966  bytes received via SQL*Net from client

3224  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

48344  rows processed

 

SQL> SELECT BEGIN, OLDEND END, LENGTH(BEGIN)

2  FROM (

3     SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,

4             ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN

5     FROM

6     (

7             SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END

8             FROM T_NUM

9     )

10  )

11  WHERE RN = 1

12  AND CN = 1;

 

48344 rows selected.

 

Elapsed: 00:00:00.72

 

Execution Plan

———————————————————-

Plan hash value: 1546715670

 

——————————————————————————————

| Id  | Operation                | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT         |       | 48454 |  2460K|       |   800   (1)| 00:00:10 |

|*  1 |  VIEW                    |       | 48454 |  2460K|       |   800   (1)| 00:00:10 |

|*  2 |   WINDOW SORT PUSHED RANK|       | 48454 |  1845K|  2480K|   800   (1)| 00:00:10 |

|   3 |    WINDOW BUFFER         |       | 48454 |  1845K|       |   800   (1)| 00:00:10 |

|   4 |     VIEW                 |       | 48454 |  1845K|       |   311   (1)| 00:00:04 |

|   5 |      WINDOW SORT         |       | 48454 |   662K|  1152K|   311   (1)| 00:00:04 |

|   6 |       TABLE ACCESS FULL  | T_NUM | 48454 |   662K|       |    68   (0)| 00:00:01 |

——————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

1 – filter(“RN”=1 AND “CN”=1)

2 – filter(ROW_NUMBER() OVER ( PARTITION BY LENGTH(TO_CHAR(“BEGIN”)),”END”

ORDER BY “BEGIN”)<=1)

 

 

Statistics

———————————————————-

0  recursive calls

0  db block gets

202  consistent gets

0  physical reads

0  redo size

1493879  bytes sent via SQL*Net to client

35966  bytes received via SQL*Net from client

3224  SQL*Net roundtrips to/from client

3  sorts (memory)

0  sorts (disk)

48344  rows processed

 

原SQL运行时间接近1分钟,而改写后的SQL语句只需要0.72秒,执行时间变为原本的1/80,逻辑读减少一半。

 

Posted in ORACLE | Tagged , | Leave a comment

UPDATE语句是否带有WHERE语句对更新重启动的影响

最近看到一个比较有意思的例子,是和更新重启动有关。

更新重启动是Oracle中一个隐藏的知识点,当Oracle在读取数据时,会构造读一致性,当读取的内容发生了修改,Oracle会通过UNDO信息来还原数据的前镜像,把数据还原到查询发生的时刻,通过构造一致性的结果来实现读取数据的一致性和隔离性。

Oracle实现的读一致性也被称为多版本读一致性,每个会话都会构造自己的一致性查询版本。但是对于写操作而言,这种方式是不可能的,因为最终的数据只有一份,如要要修改数据,就只能修改唯一的这份数据,所以对读操作,访问的是一致性版本,而对于写操作,修改的永远是当前版本。

既然写操作只能修改当前版本,当写操作执行的过程中,发现要修改的记录发生了变化,破坏了更新发起时刻的一致性,这时就触发了更新重启动,也就是说更新操作会放弃之前的修改,然后重新发起一次新的更新操作。

ITPUB上有一个有意思的案例,展示的就是更新重启动现象,链接如下:http://www.itpub.net/forum.php?mod=viewthread&tid=2102897

这里来重现一下更新重启动,建立一张简单测试用表,在会话一插入初始数据并进行更新操作:

SQL> SET SQLP ‘SQL1> ‘
SQL1> CREATE TABLE T_UPDATE (ID NUMBER);

Table created.

SQL1> INSERT INTO T_UPDATE SELECT ROWNUM FROM DUAL CONNECT BY LEVEL < 4;

3 rows created.

SQL1> COMMIT;

Commit complete.

SQL1> SELECT * FROM T_UPDATE;

ID
———-
1
2
3

SQL1> UPDATE T_UPDATE SET ID = 4 WHERE ID > 2;

1 row updated.

在会话2发起UPDATE语句,更新ID大于0的记录,这时UPDATE操作由于会话1更新了ID为3的记录且未提交,会处于挂起状态:

SQL> SET SQLP ‘SQL2> ‘
SQL2> UPDATE T_UPDATE SET ID = ID + 0.1 WHERE ID > 0;

然后在会话3插入一条新的记录,并提交:

SQL> SET SQLP ‘SQL3> ‘
SQL3> INSERT INTO T_UPDATE VALUES (0.1);

1 row created.

SQL3> COMMIT;

Commit complete.

这时回到会话1,进行提交:

SQL1> COMMIT;

Commit complete.

检查会话2,发现更新重启动发生,UPDATE操作更新了4条记录:

4 rows updated.

SQL2> SELECT * FROM T_UPDATE;

ID
———-
1.1
2.1
4.1
.2

会话2中不仅更新了之前存在的三条记录,连更新语句发起之后插入并提交的第四条记录也一起进行了更新,这说明更新重启动发生在第四条记录插入之后。

这个案例很好的展示了更新重启动现象,不过这个帖子并没有完,除了这个更新重启动的案例外,还展示了一个另外一个案例,操作和这个案例仅有很小的差别,但是结果却大不相同。

首先把数据恢复到初始状态,在会话2回滚之前的更新:

SQL2> ROLLBACK;

Rollback complete.

在会话1,恢复初始的数据情况,然后开始更新数据:

SQL1> DELETE T_UPDATE WHERE ID < 1;

1 row deleted.

SQL1> UPDATE T_UPDATE SET ID = 3 WHERE ID = 4;

1 row updated.

SQL1> COMMIT;

Commit complete.

SQL1> SELECT * FROM T_UPDATE;

ID
———-
1
2
3

SQL1> UPDATE T_UPDATE SET ID = 4 WHERE ID > 2;

1 row updated.

在会话2,对表中所有的记录的ID执行UPDATE操作,和上一个案例的唯一差别是,这里没有使用WHERE语句:

SQL2> UPDATE T_UPDATE SET ID = ID + 0.1;

同样在会话3插入数据并提交:

SQL3> INSERT INTO T_UPDATE VALUES (0.1);

1 row created.

SQL3> COMMIT;

Commit complete.

回到会话1,进行提交:

SQL1> COMMIT;

 

Commit complete.

发现会话2仅更新了3条记录:

 

3 rows updated.

 

SQL2> SELECT * FROM T_UPDATE;

ID
———-
1.1
2.1
4.1
.1

可以清晰的看到,最后一条插入的记录并未被更新,说明更新重启动并未被触发。

为什么带有WHERE条件的更新操作触发了更新重启动,而不带WHERE条件的更新未触发重启动呢,我是这样理解的:当UPDATE操作包含了WHERE条件,那么这个查询的结果要满足WHERE定义的查询一致性,当更新发现一致性不满足的情况下,就会触发更新重启动。而对于不包含WHERE条件或包含WHERE条件但是该条件与表查询无关的情况,这时Oracle的目标只是将表中所有的数据进行一次更新,并不需要考虑一致性的问题。因此,这个不加WHERE条件的UPDATE,感觉其实现方式上类似这种通过ORA_ROWSCN限定来实现:

SQL2> ROLLBACK;

 

Rollback complete.

回到会话1恢复数据,并检查ORA_ROWSCN的值:

SQL1> DELETE T_UPDATE WHERE ID < 1;

1 row deleted.

SQL1> UPDATE T_UPDATE SET ID = 3 WHERE ID = 4;

1 row updated.

SQL1> COMMIT;

Commit complete.

SQL1> SELECT ID, ORA_ROWSCN FROM T_UPDATE;
ID ORA_ROWSCN
———- ———-
1  231512964
2  231512964
3  231512964

SQL1> UPDATE T_UPDATE SET ID = 4 WHERE ID > 2;

1 row updated.

在会话2,发起包含制定ORA_ROWSCN限制条件的更新:

SQL2> UPDATE T_UPDATE SET ID = ID + 0.1 WHERE ORA_ROWSCN = 231512964;

会话3插入并提交:

SQL3> INSERT INTO T_UPDATE VALUES (0.1);

1 row created.

SQL3> COMMIT;

Commit complete.

回到会话1,进行提交:

SQL1> COMMIT;

Commit complete.

检查会话2:

3 rows updated.

SQL2> SELECT * FROM T_UPDATE;

ID
———-
1.1
2.1
4.1
.1

采用ORA_ROWSCN的效果与直接UPDATE不带任何WHERE条件是一样的,说明更新只关注UPDATE语句发出时刻的数据,不再考虑整体更新结果的一致性问题。

关于更新重启动的更详细的描述,建议参考ASKTOM上的回复:https://asktom.oracle.com/pls/apex/asktom.search?tag=write-consistency

 

 

Posted in ORACLE | Tagged , , | Leave a comment

SQL解决刑侦推理问题

前些天看到一个朋友在朋友圈里面贴了个图,上面是2018年刑侦科推理试题,看了一下题目,这些题目都是彼此依赖,很难找到一个题目作为入手点可以进一步分析,因此可能需要用纸笔配合大量的假设和试错才能继续完成。

原题如下:


    不过这种问题其实是SQL擅长的领域,于是一时手痒,写了一个SQL语句:

SQL> WITH T AS

  2  (SELECT ASCII(‘A’) R FROM DUAL

  3  UNION ALL

  4  SELECT ASCII(‘B’) FROM DUAL

  5  UNION ALL

  6  SELECT ASCII(‘C’) FROM DUAL

  7  UNION ALL

  8  SELECT ASCII(‘D’) FROM DUAL),

  9  RESULT AS

 10  (SELECT T1.R R1, T2.R R2, T3.R R3, T4.R R4, T5.R R5, T6.R R6, T7.R R7, T8.R R8, T9.R R9, T10.R R10,

 11     DECODE(T1.R, ASCII(‘A’), 1, 0) + DECODE(T2.R, ASCII(‘A’), 1, 0) + DECODE(T3.R, ASCII(‘A’), 1, 0) + DECODE(T4.R, ASCII(‘A’), 1, 0)

 12             + DECODE(T5.R, ASCII(‘A’), 1, 0) + DECODE(T6.R, ASCII(‘A’), 1, 0) + DECODE(T7.R, ASCII(‘A’), 1, 0)

 13             + DECODE(T8.R, ASCII(‘A’), 1, 0) + DECODE(T9.R, ASCII(‘A’), 1, 0) + DECODE(T10.R, ASCII(‘A’), 1, 0) A,

 14     DECODE(T1.R, ASCII(‘B’), 1, 0) + DECODE(T2.R, ASCII(‘B’), 1, 0) + DECODE(T3.R, ASCII(‘B’), 1, 0) + DECODE(T4.R, ASCII(‘B’), 1, 0)

 15             + DECODE(T5.R, ASCII(‘B’), 1, 0) + DECODE(T6.R, ASCII(‘B’), 1, 0) + DECODE(T7.R, ASCII(‘B’), 1, 0)

 16             + DECODE(T8.R, ASCII(‘B’), 1, 0) + DECODE(T9.R, ASCII(‘B’), 1, 0) + DECODE(T10.R, ASCII(‘B’), 1, 0) B,

 17     DECODE(T1.R, ‘C’, 1, 0) + DECODE(T2.R, ASCII(‘C’), 1, 0) + DECODE(T3.R, ASCII(‘C’), 1, 0) + DECODE(T4.R, ASCII(‘C’), 1, 0)

 18             + DECODE(T5.R, ASCII(‘C’), 1, 0) + DECODE(T6.R, ASCII(‘C’), 1, 0) + DECODE(T7.R, ASCII(‘C’), 1, 0)

 19             + DECODE(T8.R, ASCII(‘C’), 1, 0) + DECODE(T9.R, ASCII(‘C’), 1, 0) + DECODE(T10.R, ASCII(‘C’), 1, 0) C,

 20     DECODE(T1.R, ASCII(‘D’), 1, 0) + DECODE(T2.R, ASCII(‘D’), 1, 0) + DECODE(T3.R, ASCII(‘D’), 1, 0) + DECODE(T4.R, ASCII(‘D’), 1, 0)

 21             + DECODE(T5.R, ASCII(‘D’), 1, 0) + DECODE(T6.R, ASCII(‘D’), 1, 0) + DECODE(T7.R, ASCII(‘D’), 1, 0)

 22             + DECODE(T8.R, ASCII(‘D’), 1, 0) + DECODE(T9.R, ASCII(‘D’), 1, 0) + DECODE(T10.R, ASCII(‘D’), 1, 0) D

 23  FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7, T T8, T T9, T T10

 24  WHERE DECODE(CHR(T2.R), ‘A’, ‘C’, ‘B’, ‘D’, ‘C’, ‘A’, ‘B’) = CHR(T5.R)

 25  AND (CASE CHR(T3.R) WHEN ‘A’ THEN LEAST(T2.R, T4.R, T6.R) – GREATEST(T2.R, T4.R, T6.R)

 26     WHEN ‘B’ THEN LEAST(T2.R, T3.R, T4.R) – GREATEST(T2.R, T3.R, T4.R)

 27     WHEN ‘C’ THEN LEAST(T3.R, T4.R, T6.R) – GREATEST(T3.R, T4.R, T6.R)

 28     ELSE LEAST(T2.R, T3.R, T6.R) – GREATEST(T2.R, T3.R, T6.R) END) = 0

 29  AND (CASE CHR(T3.R) WHEN ‘A’ THEN T2.R – T3.R

 30     WHEN ‘B’ THEN T2.R – T6.R

 31     WHEN ‘C’ THEN T3.R – T2.R

 32     ELSE T2.R – T4.R END) != 0

 33  AND (CASE CHR(T4.R) WHEN ‘A’ THEN T1.R – T5.R

 34     WHEN ‘B’ THEN T2.R – T7.R

 35     WHEN ‘C’ THEN T1.R – T9.R

 36     ELSE T6.R – T10.R END) = 0

 37  AND (CASE CHR(T5.R) WHEN ‘A’ THEN T8.R – ASCII(‘A’)

 38     WHEN ‘B’ THEN T4.R – ASCII(‘B’)

 39     WHEN ‘C’ THEN T9.R – ASCII(‘C’)

 40     ELSE T10.R – ASCII(‘D’) END) = 0

 41  AND (CASE CHR(T6.R) WHEN ‘A’ THEN LEAST(T2.R, T4.R, T8.R) – GREATEST(T2.R, T4.R, T8.R)

 42     WHEN ‘B’ THEN LEAST(T1.R, T6.R, T8.R) – GREATEST(T1.R, T6.R, T8.R)

 43     WHEN ‘C’ THEN LEAST(T3.R, T10.R, T8.R) – GREATEST(T3.R, T10.R, T8.R)

 44     ELSE LEAST(T5.R, T9.R, T8.R) – GREATEST(T5.R, T9.R, T8.R) END) = 0

 45  AND (CASE CHR(T8.R) WHEN ‘A’ THEN ABS(T7.R – T1.R)

 46     WHEN ‘B’ THEN ABS(T5.R – T1.R)

 47     WHEN ‘C’ THEN ABS(T2.R – T1.R)

 48     ELSE ABS(T10.R – T1.R) END) != 1

 49  AND CASE CHR(T9.R) WHEN ‘A’ THEN T1.R – T6.R + T5.R – T6.R ELSE 1 END != 0

 50  AND CASE CHR(T9.R) WHEN ‘A’ THEN (T1.R – T6.R) * (T5.R – T6.R) ELSE 0 END = 0

 51  AND CASE CHR(T9.R) WHEN ‘B’ THEN T1.R – T6.R + T5.R – T10.R ELSE 1 END != 0

 52  AND CASE CHR(T9.R) WHEN ‘B’ THEN (T1.R – T6.R) * (T5.R – T10.R) ELSE 0 END = 0

 53  AND CASE CHR(T9.R) WHEN ‘C’ THEN T1.R – T6.R + T5.R – T2.R ELSE 1 END != 0

 54  AND CASE CHR(T9.R) WHEN ‘C’ THEN (T1.R – T6.R) * (T5.R – T2.R) ELSE 0 END = 0

 55  AND CASE CHR(T9.R) WHEN ‘D’ THEN T1.R – T6.R + T5.R – T9.R ELSE 1 END != 0

 56  AND CASE CHR(T9.R) WHEN ‘D’ THEN (T1.R – T6.R) * (T5.R – T9.R) ELSE 0 END = 0)

 57  SELECT CHR(R1) R1, CHR(R2) R2, CHR(R3) R3, CHR(R4) R4, CHR(R5) R5,

 58     CHR(R6) R6, CHR(R7) R7, CHR(R8) R8, CHR(R9) R9, CHR(R10) R10

 59  FROM RESULT

 60  WHERE (CASE CHR(R7) WHEN ‘A’ THEN C

 61     WHEN ‘B’ THEN B

 62     WHEN ‘C’ THEN A

 63     ELSE D END) = LEAST(A, B, C, D)

 64  AND (CASE CHR(R10) WHEN ‘A’ THEN 3

 65     WHEN ‘B’ THEN 2

 66     WHEN ‘C’ THEN 4

 67     ELSE 1 END) = (GREATEST(A, B, C, D) – LEAST(A, B, C, D));

 

R1      R2      R3      R4      R5      R6      R7      R8      R9      R10

——- ——- ——- ——- ——- ——- ——- ——- ——- ——-

B       C       A       C       A       C       D       A       B       A

 

主要是通过WITH构造ABCD四个选项,然后把十道题的所有选项可能性穷举生成,然后按照题意通过WHERE语句来进行条件限制,除了第三题和第九题都是一道题对应一个WHERE 语句,另外第七题和第十题无法在第一层查询中获取,需要在第二层中过滤。

虽然用了一点技巧,但是总的来说没啥技术含量,这里就不逐一解释了。

Posted in ORACLE | Tagged , , | Leave a comment

20180517 Oracle Code Singapore

接到Oracle的邀请参加了2018517日在新加坡举办的Oracle Code会议。我的演讲主题是How to write an efficient SQL

开发相关的技术变化非常之快,甚至让人有应接不暇的感觉,不过开发技术中也有少量技术一直历久弥新,SQL就是之一,自诞生以来40多年一直发挥着重要的作用。

调查显示,SQL是目前第二大编程语言,有50%的开发者都在使用SQL。虽然使用非常广泛,但是SQL的质量水平却并不令人满意。

          根据经验80%的数据库问题是由于SQL引起的,而80%SQL问题来自于20%SQL语句,在一些高并发高负载的系统中,由于一条SQL的性能问题导致数据库整体出现异常的情况屡见不鲜,这也是我本次选择这个主题的原因,希望帮助更多的开发人员可以书写出高效的SQL语句。         

 

         本次主题包括四方面的内容:合理的使用新特性;数据集整体处理;设计SQL执行计划;严格过滤数据。

          

         合理的使用新特性,可以避免重复访问数据,合并简化执行操作过程,缓存中执行间结果,减少自关联,高效灵活的处理一些复杂问题。因此,充分理解新特性的功能以及其适用场景,是书写高效SQL语句的基础。

         

         SQL本身是描述性语言,大部分情况下使用SQL的时候并不需要特别关注每行数据如何去处理,将数据整理处理作为思路,会发现SQL的性能会更好,而且很多时候SQL的写法也会更加简洁。

  

         好的性能是设计出来的,因此如果想写出高效的SQL语句,要从一开始就考虑好这个SQL的执行计划,驱动表是谁,采用何种JOIN方式连接到被驱动表。设计思路是一方面,另一方面是保证执行计划符合设计思路,这时候就需要用到提示的功能。熟悉提示的功能,可以更好的控制SQL的执行路径,绕过bug或性能问题,强制SQL按照设计思路去执行。

  

         过滤不必要的数据对于提升SQL的性能非常重要,对于一个计算10000以内质数的SQL,未经优化前需要112秒的执行时间,消耗17万逻辑读。

          

         通过严格的数据过滤,在运行的第一步消除不必要的数据,最终优化后的SQL只需要0.05秒,消耗461的逻辑读,执行时间和逻辑读都有几百倍的提升。

  

         想要具有书写高效SQL的能力,除了之前介绍的四点之外,还要下面三个因素:多些多练:熟能生巧;深思熟虑:算法为王;坚持不懈:优化无止境。

         最后附上本次演讲的PPT:How to write an efficient SQL

Posted in NEWS | Tagged , | Leave a comment

非空字段空值的产生

上一篇讨论了非空字段中如果存在空值对于查询的影响,这里描述一下导致问题的原因。
非空字段空值对查询的影响:http://yangtingkun.net/?p=1481
书接上文,其实CBO的判断本身是没有问题的,问题在于,为什么一个空值会存在非空约束的字段中。
重新看一下问题:
SQL> select * from t_def;
ID NAME TYPE
---------- ------------------------------ --------
1 a
SQL> select * from t_def where type is null;
no rows selected
SQL> select * from t_def where type is not null;
ID NAME TYPE
---------- ------------------------------ --------
1 a
SQL> select dump(type) from t_def;
DUMP(TYPE)
--------------------------------------------
NULL
SQL> select nvl(type, 'is null') from t_def;
NVL(TYPE
--------
is null
SQL> select dbms_metadata.get_ddl('TABLE', 'T_DEF') from dual;
DBMS_METADATA.GET_DDL('TABLE','T_DEF')
--------------------------------------------------------------------------------
CREATE TABLE "TEST"."T_DEF"
( "ID" NUMBER,
"NAME" VARCHAR2(8) DEFAULT 'a',
"TYPE" VARCHAR2(8) DEFAULT '' NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

之前提到,由于TYPE列具有非空约束,导致CBO给出的执行计划返回了错误的结果,但是问题的根源在于,为Oracle会允许空值插入到非空约束字段中:
SQL> insert into t_def (id, name) values (1, 'a');
insert into t_def (id, name) values (1, 'a')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."T_DEF"."TYPE")

那么是什么情况导致了错误的数据绕过了Oracle的检查呢。检查表的定义,发现一个特别之处,TYPE列的默认值本身就是NULL,是不是这个导致了Oracle的数据问题呢:
SQL> CREATE TABLE T_TEST (ID NUMBER, NAME VARCHAR2(30) DEFAULT '' NOT NULL);
表已创建。
SQL> INSERT INTO T_TEST (ID) VALUES (1);
INSERT INTO T_TEST (ID) VALUES (1)
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("TEST"."T_TEST"."NAME")

显然问题没有那么简单,虽然默认值人为设置为NULL并不常见,但是对于哪些具有NOT NULL约束且没有指定默认值的列,都相当于默认值为NULL。显然不太可能是常规问题导致的bug,Oracle经过这么多年这么多版本的磨练,应该不会在11g还出现这种问题,而且这个问题还是第一次碰到。综上所述,推断问题可能是11g新特性所引入的bug。
分析到这里,问题的答案也呼之欲出了,没错,导致问题的就是11g新增的快速添加非空默认值的功能,这个诡异的问题可以通过下面的三步简单的重新:
SQL> create table t_def (id number, name varchar2(30) default '' not null);
Table created.
SQL> insert into t_def values (1, 'a');
1 row created.
SQL> alter table t_def add type varchar2(8) default '' not null;
Table altered.
SQL> select * from t_def;
ID NAME TYPE
---------- ------------------------------ --------
1 a

Oracle确实允许NOT NULL列的默认值为NULL,如果不指定默认值那么就相当于默认值为NULL,但是对于11g新增的新特性而言,DEFAULT为NULL是要禁止的,否则就会导致现有记录的NOT NULL字段出现NULL值。
而且由于指定的DEFAULT是NULL,ECOL$中居然没有记录任何信息:
SQL> select * from sys.ecol$;
no rows selected

看来任何新特性都难以避免BUG的产生,没想到一个增加非空默认值的新特性也会引发BUG。

Posted in BUG, ORACLE | Tagged , , | Leave a comment

非空字段空值对查询的影响

BLOG有三年没有更新了,其实这篇文档也是三年前写的,不过当时没有贴出来,今天看到有人问起,于是分享出来。

这是客户数据库中碰到的一个有意思的问题。
数据库版本为11.2.0.3,客户的DSG同步软件在同步某张表时出现了错误,报错无非插入空值ORA-1400,本来这是一个很简单的问题,但是诊断发现问题比较有意思:
SQL> select * from t_def;
ID NAME TYPE
---------- ------------------------------ --------
1 a
SQL> select * from t_def where type is null;
no rows selected
SQL> select * from t_def where type is not null;
ID NAME TYPE
---------- ------------------------------ --------
1 a
SQL> select dump(type) from t_def;
DUMP(TYPE)
--------------------------------------------
NULL
SQL> select nvl(type, 'is null') from t_def;
NVL(TYPE
--------
is null

第一个查询不能反映任何问题,而第二个查询和第三个查询明确的告诉我们,TYPE列的值不是NULL。但是第四个查询和第五个查询又确定告之我们,TYPE列是NULL。到底TYPE列的值是NULL还是NOT NULL呢,这是一个问题。
Oracle给了我们两个不同的答案,但是可以坚信的是,一条记录的字段不可能同时具有这两种属性,要不然是NULL值,要不然就是NOT NULL,它不可能既是NULL又是NOT NULL,这不科学。什么?薛定谔的猫?谁说的?我的刀呢?!
电子的波粒二象性问题本质是波动和粒子性不想人们一开始认为的那样是完全对立的,事实上在电子的尺度上,本身二者就是共存的,所以你用检查波动的方法去观察电子,电子就以波的形式出现,而以粒子方式去观察电子,电子就以粒子的方式呈现。因此,波粒二象性本质并不是对立的。
薛定谔的猫的本质也是相同的,当然还要涉及到意识、观测、坍塌等一系列复杂的问题。建议不了解量子力学的同学们直接跳过以上两段内容。而如果你对量子力学有所研究,你就知道我完全是做为一个门外汉在胡扯,请无视上面两段内容。
好了,扯淡完毕,回到Oracle的问题上,我个人不相信量子尺度上的不确定性会影响到Oracle的查询结果,因此我们需要找到问题出在哪里。
简单分析一下,通过WHERE条件判断得到的字段空值与否与通过函数得到的结果是相反的。在宏观的维度上看,那么总有一个结果是错误的。既然Oracle本身已经不可信了,那么我们来看看数据本身到底是如何的。
SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from t_def;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 173109
SQL> alter system dump datafile 4 block 173109;
System altered.

下面看一下数据块中的内容:
Block header dump: 0x0102a435
Object id on Block? Y
seg/obj: 0x145df csc: 0x00.23b6097 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x102a430 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.01b.00001afc 0x00c026de.11be.10 --U- 1 fsc 0x0000.023b6098
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0102a435
data_block_dump,data header at 0x7fe7fdc97264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7fe7fdc97264
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f90
avsp=0x1f7b
tosp=0x1f7b
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f90
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 61
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 173109 maxblk 173109

显然,数据块中只有两个列的值,也就是真实的情况是TYPE列是NULL。在WHERE条件和函数的PK中,函数取得了最终的胜利。
但是对我们而言,问题才刚刚开始,为什么明明是NULL值,但是通过IS NULL和IS NOT NULL的查询条件进行过滤,得到的是与真实条件相反的结果。
Oracle的执行语句不会这么笨,连最基本的查询条件都判断错吧。事实上,导致查询结果相左的一个原因恰恰是Oracle的CBO太智能了,我们看一下表结构和执行计划就真相大白了:
SQL> select dbms_metadata.get_ddl('TABLE', 'T_DEF') from dual;
DBMS_METADATA.GET_DDL('TABLE','T_DEF')
--------------------------------------------------------------------------------
CREATE TABLE "TEST"."T_DEF"
( "ID" NUMBER,
"NAME" VARCHAR2(8) DEFAULT 'a',
"TYPE" VARCHAR2(8) DEFAULT '' NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL> set autot on exp
SQL> select * from t_def where type is null;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 177263571
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T_DEF | 1 | 5 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):,F
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
SQL> select * from t_def where type is not null;
ID NAME TYPE
---------- -------- --------
1 a
Execution Plan
----------------------------------------------------------
Plan hash value: 1057129282
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T_DEF | 1 | 5 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

现在我们可以放心大胆的宣布,这个问题和猫没有关系。从表的定义可以看到,TYPE列的属性是NOT NULL的,而目前记录中TYPE的值是NULL,这就是问题的关键。
从10g开始,CBO变得更聪明,但一个查询条件明显的违反表中的约束条件时,CBO会在执行计划的最上层增加一个FILTER,而FILTER的条件是恒为假的条件NULL IS NOT NULL,导致的结果是Oracle根本不需要真正执行这个语句,就直接返回0条记录,因为表中定义的限制条件是TYPE非空,Oracle并不会去执行这个查询语句,而是直接返回了0条记录。
而对于TYPE IS NOT NULL的查询而言,由于查询条件满足约束的条件,因此Oracle在全表扫描后省略掉了原本应该做的TYPE IS NOT NULL的过滤,而直接将结果返回给用户,造成了TYPE IS NOT NULL条件返回的结果是NULL的情况。
简单的说,导致这个问题的原因是由于错误的数据存储于表中,而这导致了CBO在判断时出现了错误,导致和预期相反的结果返回。

Posted in BUG, ORACLE | Tagged , , , | 2 Comments

20130725 上海OOW第四日

今天终于有空听了一下技术主题。
展台的奖品已经基本上发完了,而且今天是最后一天,人相对少一些,因此上午抽出一些时间去听了一个技术的专题。
这个主题主要介绍了12c性能方面的特性。开篇Oracle先介绍了在各个场景下TPCC的记录都是Oracle保持的,并展示了那张引来无数关注的TPMC测试结果:SUN平台性能超过HP平台的7倍。
主题中的另一个亮点是介绍了Oracle自己用来承载邮件、日历、联系人、聊天、文档以及网络会议的Oracle集群,由9台X2-2组成,一共2376核,2PB数据量,7T内层和48T闪存。这个应该是实际上最大的Oracle集群部署在奥斯汀,通过DG技术将备用环境部署在犹他数据中心。这个环境中充分利用了Oracle各种软件功能:RAC、Streams、Active Data Guard、Secure Backup、RMAN、Flashback Database、ASM、Partition等。数据库利用SecureFiles的压缩功能节省了2倍的空间。
最后终于介绍了12c中一些与性能有关的特性:内存中并行执行、自适应执行计划、通过WITH将PL/SQL内嵌到SQL中、内存中的全局临时表、内存中的LOB查询和更新、并行加载和移动LOB、基于内存队列的快速审计、自动数据优化、高级压缩等。

Posted in NEWS | Tagged , , | Leave a comment

20130724 上海OOW第三日

又在OOW的展览大厅打了一回酱油,和Jackie Han、Kamus一起介绍ACE Program。
今天上午本打算一早去听技术的专题,没想到有客户数据库出现了异常,远端诊断和分析用了不少时间,到了OOW现场时,KeyNotes已经结束了。又赶上公司的展台事情比较多,因此一直在展台帮忙。
OOW的午饭从1点开始,而我们的专题从1:20开始,怕吃饭的人多来不及,于是打算等专题结束后再去,没有想到后来提问的人还很多,专题一直持续到了2点以后。跑到楼上发现午饭也结束了。
公司的展台仍然是络绎不绝,连Oracle数据库研发的老大Andy都来到了我们的展台,可惜当时正在给一个咨询问题的朋友讲解12c的架构,完全没有注意到。知道最后看到Eygle的微博才发现,也算是后知后觉了。我们的12c架构图非常受欢迎,从北京带来的近900张图,在今天下午就已经发送一空了。看来明天上午已经没啥礼品可送了。
晚上Oracle的答谢晚宴,感觉和旧金山的金银岛音乐节异曲同工,都是我很不感冒的。转了一圈就闪人了。

Posted in NEWS | Tagged , , | Leave a comment

20130723 上海OOW第二日

今天在OOW的ACOUG专场分享了Think Different的主题。
今天一天在会场非常的充实。到了会场后先去听了今天的KeyNotes,其中Steve Miranda的云计算的主题以及Andy Mendelsohn的12c数据库对云计算的支持,都是本次大会的重点话题。Oracle 12c中的C指的就是CLOUD,而12c最大的亮点PLUGABLE DB就是Oracle在DB层对云架构的支持。
如果说去年OOW大会只是给出了12c PLUGABLE DATABASE的概念,那么今年OOW上Oracle已经想好要如何用PDB来支持云服务了,甚至我怀疑Oracle来云服务的收费细则都已经定义好了,青铜、白银和黄金三档,每档对应的架构和服务都已经定义清楚,而在各个档之间进行升级也同样进行说明。看来Oracle已经准备利用12c来打造数据库服务云了。只不过以Oracle以往收费的标准,不知道一些不想自己搭建环境、维护数据库的中小企业,能否接受正版云服务的价格。
中午OOW的午饭一如既往的不给力,这种面包、三明治的配置更适合老外,估计大部分国内参会者,这三天午饭会比较郁闷。
下午全程参加了展览大厅的ACOUG活动,首先是Eygle两个优化的主题,然后是Kamus三个关于升级的主题,最后是我的两个主题,一个是ODA,另一个是Think Different。可惜的是,每个主题的时间只有二十分钟,而我们任何一个主题正常时间都应该超过1小时。因此,最终Eygle只讲了一个主题就超过了两个主题的时间,而Kamus最后紧赶慢赶终于完成了三个主题,但是时间方面超了不少。根据他们两个的前车之鉴,我直接选择了现场绝大部分人都想听的Think Different,而舍弃了ODA的主题。其实舍弃ODA也比较可惜,在OOW的现场展示了ODA的最新版本,而我在中午的时候还把最新版的ODA的内容添加到了PPT里面。
剩下一些零碎的时间基本上都在公司的展台帮忙,除了解答问题,当背景合影外,还碰到了不少新、老朋友。

Posted in NEWS | Tagged , , | Leave a comment

20130722 上海OOW开幕

期待已久的ORACLE OPEN WORLD终于开幕了。
有了以往OOW的经验,没有一大早就跑去排队注册,而是到了下午才去签到注册地方,几分钟搞定了注册的过程。
今天一早听说马克赫德的飞机由于机械故障,导致中途返航,导致他将无法现场出席本次上海OOW大会,他的演讲将改为远程的方式。我在去年10月不但现场听过他的演讲,而且现场看过Larry的演讲,因此倒也没有什么遗憾,而对于大部分技术人员而言,在现场的时候也会盯着大屏幕,是不是现场真人演讲也没有本质的区别。估计影响最大的应该是媒体的朋友,缺少现场采访对象,可能会使本次的OOW之行大打折扣。
下午并没有去听KeyNotes,而是跑到了公司展台帮忙。公司的几个同事基本已经把展品布置好了,我们为大家准备了一些礼物,尤其是一张Oracle 12c的架构图,经过我们多次设计修改,是公司很多人集体智慧的结果,希望可以对技术爱好者有所帮助。

Posted in NEWS | Tagged , , | Leave a comment