11g에서 나온 PIVOT 구문입니다.
아래 sample 처럼 동일 column 값에 대해 pivot 기능이 적용되어 display 됩니다.
SQL> select job,deptno,avg(sal) from emp group by job,deptno;
JOB DEPTNO AVG(SAL)
--------------------------- ---------- ----------
PRESIDENT 10 5000
MANAGER 30 2850
CLERK 30 950
CLERK 10 1300
MANAGER 20 2975
ANALYST 20 6499.5
CLERK 20 950
SALESMAN 30 1400
MANAGER 10 2450
SQL> select * from (select job,deptno,sal from emp)
pivot (avg(sal) for deptno in (10,20,30)) order by job;
JOB 10 20 30
--------------------------- ---------- ---------- ----------
ANALYST 6499.5
CLERK 1300 950 950
MANAGER 2450 2975 2850
PRESIDENT 5000
SALESMAN 1400
* 아래 SQL은 노드별 user call 값을 출력해본 간단한 SQL 입니다.
select * from
(
select to_char(end_interval_time,'HH24:MI:SS') end_interval_time
,a.instance_number,value from dba_hist_sysstat a, dba_hist_snapshot b
where stat_name in ('user calls')
and a.snap_id = b.snap_id
and a.instance_number = b.instance_number
)
pivot (avg(value) for instance_number in (1 as "Node1",2 as "Node2",3 as "Node3"))
order by 1
/
END_INTERV Node1 Node2 Node3
---------- -------------------- ---------- ----------
00:10:31 1,432,826,125 1278172133 1281672038
00:10:34 1,254,265,411 1153949372 1154503152
00:10:39 233,495,867 223300171 222547544
00:20:01 1,255,123,846 1154019492 1154577807
00:20:02 2,260,343,067 2050291466 2048154492
00:20:08 719,183,716 664321484 663506525
00:20:13 478,942,298 446272763 444528572
* V$SYSSTAT의 특정 statistic 값을 query하는 SQL
select *
from (select to_char(sysdate,'MMDDHH24MISS') dat, a.name,a.value
from v$sysstat a
where statistic# in (146,9,67,62,72,6,446,447,0,451,5,4)
)
pivot (sum(value) for name in ('logons cumulative','user rollbacks','user commits','user calls','session logical reads','physical reads','db block changes', 'physical writes','redo size','parse count (total)','parse count (hard)','execute count'))
order by dat
/
* PIVOT을 decode로 바꿔본 SQL (11g 이전 버전)
select to_char(sysdate,'MMDDHH24MISS') dat,
sum(decode(name,'logons cumulative',value)) "logons cumulative",
sum(decode(name,'user rollbacks',value)) "user rollbacks",
sum(decode(name,'user commits',value)) "user commits",
sum(decode(name,'user calls',value)) "user calls",
sum(decode(name,'session logical reads',value)) "session logical reads",
sum(decode(name,'physical reads',value)) "physical reads",
sum(decode(name,'db block changes',value)) "db block changes",
sum(decode(name,'physical writes',value)) "physical writes",
sum(decode(name,'redo size',value)) "redo size",
sum(decode(name,'parse count (total)',value)) "parse count (total)",
sum(decode(name,'parse count (hard)',value)) "parse count (hard)",
sum(decode(name,'execute count',value)) "execute count"
from v$sysstat
where statistic# in (146,9,67,62,72,6,446,447,0,451,5,4)
group by to_char(sysdate,'MMDDHH24MISS')
/
(왠지 좀 무지해 보이는데.. 더 좋은 방법이 있으면 알려주세요 *^^*)
* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!
'Oracle Database' 카테고리의 다른 글
| TABLE COMPRESS (0) | 2010.06.04 |
|---|---|
| Oracle 10g new feature - 여태 몰랐던.. (0) | 2010.05.20 |
| 회사에서 Oracle DBA에게 요구하는 기술들 (0) | 2010.04.29 |
| Oracle index 생성 전에 index에 대한 size 예상하는 방법. (0) | 2010.04.28 |
| DISTRIBUTED QUERY ANALYSIS (0) | 2010.04.22 |