11g里面有个很有用的新特性,对数据透视图的支持。
简单而言,它可以实现宽表和窄表之间的转化。举一个例子,有一张表记录了全校所有班级所有学生的成绩(A,B,C,D,E),现在想统计每个班级里每个分数级别对应的学生人数。当然,一个SQL就可以实现:
SELECT class, score, count(*) FROM score_tableGROUP BY class, score;
结果的格式如下:
班级 分数 人数一年一班 A 10一年一班 B 16一年一班 C 16一年一班 D 16一年一班 E 16一年二班 A 15一年二班 B 14一年二班 C 15一年二班 D 14一年二班 E 15
不过,很多人更想要下面的格式
班级 A B C D E一年一班 10 16 16 16 16一年二班 15 14 15 14 15
第二种格式显然简单明了多了。
用Oracle的新语法,可以实现如下
SELECT *FROM(SELECT class,score,count(*) cnt FROM score_table GROUP BY class,score)pivot(sum(cnt) FOR score IN ('A','B','C','D','E'));
一个更实用的场合是对ASH(active session history)数据的处理上。
一般而言,我们需要一段时间内Top 10的wait event,并掌握其在每个时间片(例如10 seconds)里的分布。这些信息可以通过下面的SQL进行获取:
SELECT to_char(to_date(trunc(to_char(sample_time,'SSSSS')/10)*10,'SSSSS'),'hh24:mi:ss') start_time , decode(ash.session_state,'ON CPU','ON CPU',ash.event) event , count(1)/10 totalFROM v$active_session_history ashWHERE sample_time > sysdate-1/24GROUP BY trunc(to_char(sample_time,'SSSSS')/10) , decode(ash.session_state,'ON CPU','ON CPU',ash.event);
同样的,我们更习惯将这个结果进行倒置。这同样可以通过pivot来实现:
SELECT * FROM (SELECT to_char(to_date(trunc(to_char(sample_time,'SSSSS')/10)*10,'SSSSS'),'hh24:mi:ss') start_time , decode(ash.session_state,'ON CPU','ON CPU',ash.event) event , count(1)/10 totalFROM v$active_session_history ashWHERE sample_time > sysdate-1/24GROUP BY trunc(to_char(sample_time,'SSSSS')/10) , decode(ash.session_state,'ON CPU','ON CPU',ash.event)) ash pivot (sum(total) FOR event IN ('ON CPU' AS TOP1,'PX Deq: Slave Session Stats' AS TOP2))ORDER BY 1;
下面是一个ASH的例子,系统的工作状态已经一目了然了!
TOP EVENT----- ----------------------------------------------------------------TOP1 cell smart table scanTOP2 ASM file metadata operationTOP3 control file sequential readTOP4 ON CPUTOP5 enq: XL - fault extent mapTOP6 DFS lock handleTOP7 cell single block physical readTOP8 reliable messageTOP9 read by other sessionTOP10 latch: shared poolTIME TOP1 TOP2 TOP3 TOP4 TOP5 TOP6 TOP7 TOP8 TOP9 TOP10-------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----00:30:50 100:31:3000:31:4000:31:5000:32:1000:32:3000:32:50 100:34:1000:34:30 100:34:4000:35:10 100:35:40 100:37:50 16 34 5 9 2 1 14 3 900:38:00 296 95 7 9 3 6 1 600:38:10 478 133 15 4 17 7 100:38:20 543 71 21 6 8 7 500:38:30 531 81 13 2 14 9 1 200:38:40 600 30 17 4 2 1 200:38:50 592 36 16 4 5 10 100:39:00 609 20 12 6 10 400:39:10 620 14 13 4 2 1000:39:20 628 5 10 4 10 100:39:30 248 7 4 4 4
联系客服