各位大侠好:
我有这样一个需求,就是把 alert 日志做成了外部表,表上只有一个字段‘text’ 对应alert日志中的每一行,以方便搜索ORA-开头的错误,但是ORA-开头的这一行上通常没有日期,日期在上面一两行,详情的trace路径在下面几行。所以就需要显示 ORA-开头这一行的上面两行到下面两行(也就是以这一行为中心的5行)。我用了最笨的方法写成下面这样:
with
a as (select rownum liehao,text from alert_log ),
b as (select a.liehao lie from a where a.text like 'ORA-%')
select * from a where a.liehao in (select b.lie-2 from b)
union
select * from a where a.liehao in (select b.lie-1 from b)
union
select * from a where a.liehao in (select b.lie from b)
union
select * from a where a.liehao in (select b.lie+1 from b)
union
select * from a where a.liehao in (select b.lie+2 from b)
但这样效率不高,union 的每个select 语句都要扫描一下外部表 alert_log
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | www.hkdeng.com | 3335K|
| 1 | TEMP TABLE TRANSFORMATION | | |
| 2 | LOAD AS SELECT | | |
| 3 | VIEW | | 8168 |
| 4 | COUNT | | |
| 5 | EXTERNAL TABLE ACCESS FULL | ALERT_LOG | 8168 |
| 6 | SORT UNIQUE | | 3335K|
| 7 | UNION-ALL | | |
| 8 | HASH JOIN | | 667K|
| 9 | VIEW | | 8168 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6799_DDEA6950 | 8168 |
| 11 | VIEW | | 8168 |
| 12 | COUNT | | |
| 13 | EXTERNAL TABLE ACCESS FULL| ALERT_LOG | 8168 |
| 14 | HASH JOIN | | 667K|
| 15 | VIEW | | 8168 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6799_DDEA6950 | 8168 |
| 17 | VIEW | | 8168 |
| 18 | COUNT | | |
| 19 | EXTERNAL TABLE ACCESS FULL| ALERT_LOG | 8168 |
| 20 | HASH JOIN | | 667K|
| 21 | VIEW | | 8168 |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6799_DDEA6950 | 8168 |
| 23 | VIEW | | 8168 |
| 24 | COUNT | | |
| 25 | EXTERNAL TABLE ACCESS FULL| ALERT_LOG | 8168 |
| 26 | HASH JOIN | | 667K|
| 27 | VIEW | | 8168 |
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6799_DDEA6950 | 8168 |
| 29 | VIEW | | 8168 |
| 30 | COUNT | | |
| 31 | EXTERNAL TABLE ACCESS FULL| ALERT_LOG | 8168 |
| 32 | HASH JOIN | | 667K|
| 33 | VIEW | | 8168 |
| 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6799_DDEA6950 | 8168 |
| 35 | VIEW | | 8168 |
| 36 | COUNT | | |
| 37 | EXTERNAL TABLE ACCESS FULL| ALERT_LOG | 8168 |
--------------------------------------------------------------------------------
各位高手请指教,查出列号后,如何让这个列号的上下2条也加入到这个结果集,而不用多次查询?
我有这样一个需求,就是把 alert 日志做成了外部表,表上只有一个字段‘text’ 对应alert日志中的每一行,以方便搜索ORA-开头的错误,但是ORA-开头的这一行上通常没有日期,日期在上面一两行,详情的trace路径在下面几行。所以就需要显示 ORA-开头这一行的上面两行到下面两行(也就是以这一行为中心的5行)。我用了最笨的方法写成下面这样:
with
a as (select rownum liehao,text from alert_log ),
b as (select a.liehao lie from a where a.text like 'ORA-%')
select * from a where a.liehao in (select b.lie-2 from b)
union
select * from a where a.liehao in (select b.lie-1 from b)
union
select * from a where a.liehao in (select b.lie from b)
union
select * from a where a.liehao in (select b.lie+1 from b)
union
select * from a where a.liehao in (select b.lie+2 from b)
但这样效率不高,union 的每个select 语句都要扫描一下外部表 alert_log
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | www.hkdeng.com | 3335K|
| 1 | TEMP TABLE TRANSFORMATION | | |
| 2 | LOAD AS SELECT | | |
| 3 | VIEW | | 8168 |
| 4 | COUNT | | |
| 5 | EXTERNAL TABLE ACCESS FULL | ALERT_LOG | 8168 |
| 6 | SORT UNIQUE | | 3335K|
| 7 | UNION-ALL | | |
| 8 | HASH JOIN | | 667K|
| 9 | VIEW | | 8168 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6799_DDEA6950 | 8168 |
| 11 | VIEW | | 8168 |
| 12 | COUNT | | |
| 13 | EXTERNAL TABLE ACCESS FULL| ALERT_LOG | 8168 |
| 14 | HASH JOIN | | 667K|
| 15 | VIEW | | 8168 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6799_DDEA6950 | 8168 |
| 17 | VIEW | | 8168 |
| 18 | COUNT | | |
| 19 | EXTERNAL TABLE ACCESS FULL| ALERT_LOG | 8168 |
| 20 | HASH JOIN | | 667K|
| 21 | VIEW | | 8168 |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6799_DDEA6950 | 8168 |
| 23 | VIEW | | 8168 |
| 24 | COUNT | | |
| 25 | EXTERNAL TABLE ACCESS FULL| ALERT_LOG | 8168 |
| 26 | HASH JOIN | | 667K|
| 27 | VIEW | | 8168 |
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6799_DDEA6950 | 8168 |
| 29 | VIEW | | 8168 |
| 30 | COUNT | | |
| 31 | EXTERNAL TABLE ACCESS FULL| ALERT_LOG | 8168 |
| 32 | HASH JOIN | | 667K|
| 33 | VIEW | | 8168 |
| 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6799_DDEA6950 | 8168 |
| 35 | VIEW | | 8168 |
| 36 | COUNT | | |
| 37 | EXTERNAL TABLE ACCESS FULL| ALERT_LOG | 8168 |
--------------------------------------------------------------------------------
各位高手请指教,查出列号后,如何让这个列号的上下2条也加入到这个结果集,而不用多次查询?