[20140829]在vim中使用align.txt
--经常上一些网站,发现一些帖子贴出的sql的执行计划出现参差不齐的情况,很难看懂.
--有一次我通过QQ解决问题,对方贴出执行计划,也是这种情况,本来这些执行计划就很复杂,加上不对齐,确实影响工作与解决问题的心情.
--我想起在vim下格式化sql语句时,需要安装align插件,应该使用它能够解决问题.
--下载安装align插件很简单。
1.ALign 插件:
http://www.vim.org/scripts/script.php?script_id=294
http://www.vim.org/scripts/download_script.php?src_id=19633
安装很简单:
vim Align.vba.gz
:so %
:q
--注意要删除旧的AlignMaps.vim文件，不要map会冲突！新的改名为AlignMapsPlugin.vim。
2.查看相关文档:
:help align
3.文档写的很复杂,而我的需求很简单,就是显示执行计划要美观.通过例子来讲解:
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cj135jhhjjzsd, child number 0
-------------------------------------
select dept.dname,emp.* from dept,emp where dept.deptno=emp.deptno and
dept.deptno=10
Plan hash value: 568005898
---------------------------------------------------------------------------------------------------------------
|Id|Operation|Name|Starts|E-Rows|Cost(%CPU)|A-Rows|A-Time|Buffers|
---------------------------------------------------------------------------------------------------------------
|0|SELECT STATEMENT||1||5(100)|3|00:00:00.01|9|
|1|NESTED LOOPS||1|3|5(0)|3|00:00:00.01|9|
|2|TABLE ACCESSBY INDEX ROWID|DEPT|1|1|2(0)|1|00:00:00.01|2|
|*3|INDEX UNIQUE SCAN|PK_DEPT|1|1|1(0)|1|00:00:00.01|1|
|*4|TABLE ACCESS FULL|EMP|1|3|3(0)|3|00:00:00.01|7|
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPT"."DEPTNO"=10)
4 - filter("EMP"."DEPTNO"=10)
a.操作如下,移动id那一行.ma,做标记a.
b.移动到*4那一行.
c.定义格式化参数:
:AlignCtrl lrllrrrrrrrrrrr
--说明实际上控制格式化显示样式,很容易猜测,l表示left靠左边显示,r表示right靠右边显示,另外注意执行计划开始就是分隔符号|
--*2 实际上是第2个字段.这里就是r,表示这列靠右边显示.还有:表示后面的列不格式化.
d.格式化:
:'a,.Align |
--结合我重新映射删除空格操作很快的. 或者开始操作前删除仅仅有横线那一行。
--链接:http://blog.itpub.net/267265/viewspace-1244321/
4.效果如下:
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost(%CPU) | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5(100) | 3 | 00:00:00.01 | 9 |
| 1 | NESTED LOOPS | | 1 | 3 | 5(0) | 3 | 00:00:00.01 | 9 |
| 2 | TABLE ACCESSBY INDEX ROWID | DEPT | 1 | 1 | 2(0) | 1 | 00:00:00.01 | 2 |
| *3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1(0) | 1 | 00:00:00.01 | 1 |
| *4 | TABLE ACCESS FULL | EMP | 1 | 3 | 3(0) | 3 | 00:00:00.01 | 7 |
---------------------------------------------------------------------------------------------------------------
--这样就美观了.以下是实际的显示效果：
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 (100)| 3 |00:00:00.01 | 9 |
| 1 | NESTED LOOPS | | 1 | 3 | 5 (0)| 3 |00:00:00.01 | 9 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 2 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 1 |
|* 4 | TABLE ACCESS FULL | EMP | 1 | 3 | 3 (0)| 3 |00:00:00.01 | 7 |
---------------------------------------------------------------------------------------------------------------
--对比主要差别在与operation没有缩进。其他基本一样。
5.最后补充AlignCtrl中的:
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1240 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
:AlignCtrl r:
:'a,.Align " "
--显示如下,这样仅仅对第一个空格前的列格式化.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1240 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
6.还有许多功能,但是上面的这些对我已经足够了.做一个记录,需要了解更多:
:help align