Oracle documentation indicates that the pivot operation is optimized, which to me implies that the optimizer has some special code available that can improve its performance. In the testing I did (admittedly not exhaustive), I saw performance improvements in execution time of the pivot over the case method that was slightly significant.
What was really interesting in these tests was that the cost of the two statements was equivalent except that the execution plan for the case method used a hash group by operation and the pivot plan used the new sort group by pivot operation.
rajesh@11GR2>
rajesh@11GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.06
rajesh@11GR2> create table t
2 nologging
3 as select * from all_objects;
Table created.
Elapsed: 00:00:06.01
rajesh@11GR2>
rajesh@11GR2> begin
2 for i in 1..6
3 loop
4 insert /*+ append */ into t select * from t;
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:28.60
rajesh@11GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
rajesh@11GR2> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.67
rajesh@11GR2> select count(*) from T;
COUNT(*)
----------
4577600
Elapsed: 00:00:15.62
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2>
********************************************************************************
select * from (
select owner,object_type,object_id
from t
)
pivot
(
count(object_id)
for object_type in ('PROCEDURE','DESTINATION','LIBRARY','TRIGGER','JAVA SOURCE','SEQUENCE','TABLE PARTITION','INDEX PARTITION','JOB CLASS',
'PROGRAM','EVALUATION CONTEXT','JAVA DATA','INDEX','OPERATOR','TYPE','CONTEXT','RULE SET','JAVA CLASS','EDITION','SYNONYM',
'PACKAGE BODY','WINDOW','RULE','DIRECTORY','TABLE','PACKAGE','VIEW','TYPE BODY','SCHEDULE','JAVA RESOURCE','XML SCHEMA',
'MATERIALIZED VIEW','CLUSTER','FUNCTION','CONSUMER GROUP','JOB','SCHEDULER GROUP','INDEXTYPE')
)
order by owner
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.73 16.26 65159 65170 0 30
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.73 16.26 65159 65170 0 30
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
30 VIEW (cr=65170 pr=65159 pw=0 time=0 us cost=18054 size=412377 card=807)
30 TRANSPOSE (cr=65170 pr=65159 pw=0 time=0 us)
236 SORT GROUP BY PIVOT (cr=65170 pr=65159 pw=0 time=117 us cost=18054 size=12105 card=807)
4577600 TABLE ACCESS FULL T (cr=65170 pr=65159 pw=0 time=21653452 us cost=17827 size=68664000 card=4577600)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
direct path read 506 0.24 11.91
asynch descriptor resize 1 0.00 0.00
SQL*Net message from client 2 0.01 0.01
********************************************************************************
select owner,
max(case when object_type = 'PROCEDURE' then cnt else null end) as PROCEDURE,
max(case when object_type = 'DESTINATION' then cnt else null end) as DESTINATION,
max(case when object_type = 'LIBRARY' then cnt else null end) as LIBRARY,
max(case when object_type = 'TRIGGER' then cnt else null end) as TRIGGER_cnt,
max(case when object_type = 'JAVA SOURCE' then cnt else null end) as JAVA_SOURCE,
max(case when object_type = 'SEQUENCE' then cnt else null end) as SEQUENC_cntE,
max(case when object_type = 'TABLE PARTITION' then cnt else null end) as TABLE_PARTITION,
max(case when object_type = 'INDEX PARTITION' then cnt else null end) as INDEX_PARTITION,
max(case when object_type = 'JOB CLASS' then cnt else null end) as JOB_CLASS,
max(case when object_type = 'PROGRAM' then cnt else null end) as PROGRAM_cnt,
max(case when object_type = 'EVALUATION CONTEXT' then cnt else null end) as EVALUATION_CONTEXT,
max(case when object_type = 'JAVA DATA' then cnt else null end) as JAVA_DATA,
max(case when object_type = 'INDEX' then cnt else null end) as INDEX_cnt,
max(case when object_type = 'OPERATOR' then cnt else null end) as OPERATOR_cnt,
max(case when object_type = 'TYPE' then cnt else null end) as TYPE,
max(case when object_type = 'CONTEXT' then cnt else null end) as CONTEXT_cnt,
max(case when object_type = 'RULE SET' then cnt else null end) as RULE_SET,
max(case when object_type = 'JAVA CLASS' then cnt else null end) as JAVA_CLASS,
max(case when object_type = 'EDITION' then cnt else null end) as EDITION,
max(case when object_type = 'PACKAGE BODY' then cnt else null end) as PACKAGE_BODY,
max(case when object_type = 'WINDOW' then cnt else null end) as WINDOW,
max(case when object_type = 'RULE' then cnt else null end) as RULE,
max(case when object_type = 'DIRECTORY' then cnt else null end) as DIRECTORY_cnt,
max(case when object_type = 'TABLE' then cnt else null end) as TABLE_cnt,
max(case when object_type = 'PACKAGE' then cnt else null end) as PACKAGE_cnt,
max(case when object_type = 'VIEW' then cnt else null end) as VIEW_cnt,
max(case when object_type = 'TYPE BODY' then cnt else null end) as TYPE_BODY,
max(case when object_type = 'SCHEDULE' then cnt else null end) as SCHEDULE,
max(case when object_type = 'JAVA RESOURCE' then cnt else null end) as JAVA_RESOURCE,
max(case when object_type = 'XML SCHEMA' then cnt else null end) as XML_SCHEMA,
max(case when object_type = 'MATERIALIZED VIEW' then cnt else null end) as MATERIALIZED_VIEW,
max(case when object_type = 'CLUSTER' then cnt else null end) as CLUSTER_cnt,
max(case when object_type = 'FUNCTION' then cnt else null end) as FUNCTION_cnt,
max(case when object_type = 'CONSUMER GROUP' then cnt else null end) as CONSUMER_GROUP,
max(case when object_type = 'JOB' then cnt else null end) as JOB_cnt,
max(case when object_type = 'SCHEDULER GROUP' then cnt else null end) as SCHEDULER_GROUP,
max(case when object_type = 'INDEXTYPE' then cnt else null end) as INDEXTYPE_cnt
from ( select owner,object_type,count(*) as cnt
from t
group by owner,object_type)
group by owner
order by owner
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.04 0 0 0 0
Fetch 2 2.07 16.47 65159 65170 0 30
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.07 16.52 65159 65170 0 30
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
30 SORT GROUP BY (cr=65170 pr=65159 pw=0 time=0 us cost=18054 size=900 card=30)
236 VIEW (cr=65170 pr=65159 pw=0 time=352 us cost=18054 size=24210 card=807)
236 HASH GROUP BY (cr=65170 pr=65159 pw=0 time=235 us cost=18054 size=12105 card=807)
4577600 TABLE ACCESS FULL T (cr=65170 pr=65159 pw=0 time=22080070 us cost=17827 size=68664000 card=4577600)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.00 0.00
direct path read 505 0.39 13.76
asynch descriptor resize 2 0.00 0.00
********************************************************************************
No comments:
Post a Comment