Search notes:

Oracle: Execution plan for an UNPIVOT query

create table tq84_tab(
  col_1    integer,
  col_2    varchar2(5),
  val_foo  number(4,1),
  val_bar  number(4,1),
  val_baz  number(4,1)
);

insert into tq84_tab values ( 1 , 'abc' , 5.1 , 72.6 , 530.6 );
insert into tq84_tab values ( 2 , 'def' , 9.2 , 34.9 , 178.5 );
insert into tq84_tab values ( 3 , 'ghi' , 4.8 , 98.4 , 562.1 );
insert into tq84_tab values ( 4 , 'jkl' , 7.6 , 40.2 , 409.4 );

explain plan for
select
   *
from
  tq84_tab unpivot (
     val for txt in (
         val_foo as 'Foo',
         val_bar as 'Bar',
         val_baz as 'Baz'
     )
  )
;

select * from table(dbms_xplan.display(format=>'basic predicate'));
--
-- ----------------------------------------
-- | Id  | Operation           | Name     |
-- ----------------------------------------
-- |   0 | SELECT STATEMENT    |          |
-- |*  1 |  VIEW               |          |
-- |   2 |   UNPIVOT           |          |
-- |   3 |    TABLE ACCESS FULL| TQ84_TAB |
-- ----------------------------------------
--  
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--  
--    1 - filter("unpivot_view_005"."VAL" IS NOT NULL)

drop table tq84_tab;

See also

Plan operations

Index

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1758199876, '216.73.216.150', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/SQL/select/pivot/un/execution-plan(77): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78