This discussion is archived
11 Replies Latest reply: Oct 31, 2012 2:43 AM by Marco Gralike RSS

Pivoting a result set

udayaraj Newbie
Currently Being Moderated
Hello All

Please see below
raj@cms> select banner from v$version;

BANNER
--------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

Elapsed: 00:00:00.02
raj@cms> With Sample_Data as
  2     (
  3     select
  4             xmltype('<keyCategory.Block>
  5                             <keyCategory>
  6                                     <keyCategoryNum>27</keyCategoryNum>
  7                                     <keyCategoryLabel>Asia</keyCategoryLabel>
  8                                     <keyCategoryTopic>X527</keyCategoryTopic>
  9                             </keyCategory>
 10                             <keyCategory>
 11                                     <keyCategoryNum>40</keyCategoryNum>
 12                                     <keyCategoryLabel>Europe</keyCategoryLabel>
 13                                     <keyCategoryTopic>X540</keyCategoryTopic>
 14                             </keyCategory>
 15                             <keyCategory>
 16                                     <keyCategoryNum>41</keyCategoryNum>
 17                                     <keyCategoryLabel>Oceania</keyCategoryLabel>
 18                                     <keyCategoryTopic>X541</keyCategoryTopic>
 19                             </keyCategory>
 20                     </keyCategory.Block>') keyCategory_doc
 21     from dual
 22     union all
 23     select
 24             xmltype('<keyCategory.Block>
 25                             <keyCategory>
 26                                     <keyCategoryNum>28</keyCategoryNum>
 27                                     <keyCategoryLabel>Africa</keyCategoryLabel>
 28                                     <keyCategoryTopic>X628</keyCategoryTopic>
 29                             </keyCategory>
 30                             <keyCategory>
 31                                     <keyCategoryNum>50</keyCategoryNum>
 32                                     <keyCategoryLabel>America</keyCategoryLabel>
 33                                     <keyCategoryTopic>X650</keyCategoryTopic>
 34                             </keyCategory>
 35                     </keyCategory.Block>') keyCategory_doc
 36     from dual
 37     union all
 38     select
 39             xmltype('<keyCategory.Block>
 40                             <keyCategory>
 41                                     <keyCategoryNum>10</keyCategoryNum>
 42                                     <keyCategoryLabel>Middle East</keyCategoryLabel>
 43                                     <keyCategoryTopic>X100</keyCategoryTopic>
 44                             </keyCategory>
 45                     </keyCategory.Block>') keyCategory_doc
 46     from dual
 47     )
 48  select
 49     x1.*
 50  from
 51     sample_data t,
 52     xmlTable
 53     (
 54     '/keyCategory.Block/keyCategory'
 55     passing t.keyCategory_doc
 56     columns
 57             seq for ordinality,
 58             num   varchar2(100) path '/keyCategory/keyCategoryNum',
 59             label varchar2(100) path '/keyCategory/keyCategoryLabel'
 60    ) x1 ;

       SEQ NUM LABEL
---------- --- --------------------
         1 27  Asia
         2 40  Europe
         3 41  Oceania
         1 28  Africa
         2 50  America
         1 10  Middle East

6 rows selected.

Elapsed: 00:00:00.10

Execution Plan
----------------------------------------------------------
Plan hash value: 613170418

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        | 24504 |  3613K|    78   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |                        | 24504 |  3613K|    78   (0)| 00:00:01 |
|   2 |   VIEW                               |                        |     3 |   102 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL                         |                        |       |       |            |       |
|   4 |     FAST DUAL                        |                        |     1 |       |     2   (0)| 00:00:01 |
|   5 |     FAST DUAL                        |                        |     1 |       |     2   (0)| 00:00:01 |
|   6 |     FAST DUAL                        |                        |     1 |       |     2   (0)| 00:00:01 |
|   7 |   VIEW                               |                        |  8168 |   933K|    24   (0)| 00:00:01 |
|   8 |    COUNT                             |                        |       |       |            |       |
|   9 |     COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |       |
---------------------------------------------------------------------------------------------------------------
The result needs to be pivoted like below.
NUM NUM NUM LABEL              LABEL               LABEL
--- --- --- ------------------ ---------------------------- 
27  40  41  Asia               Europe               Oceania
28  50      Africa             America
10          Middle East
The table contains more than a million records, pivoting using a group by clause will have performance issues. Would it be possible to do this with Xquery or any other method?

Thanking you all in advance

With Regards
Raj
  • 1. Re: Pivoting a result set
    odie_63 Guru
    Currently Being Moderated
    Would it be possible to do this with Xquery or any other method?
    Yes, but only if you know the number of resulting columns at parse time.

    It's most unlikely that XQuery outperforms the classic GROUP-BY method though.
    Your version doesn't allow much optimization, unless you're using Object-Relational storage?

    SQL> select x.*
      2  from tmp_xml t
      3     , xmltable('/keyCategory.Block' passing t.object_value
      4         columns num1   number       path 'keyCategory[1]/keyCategoryNum'
      5               , label1 varchar2(30) path 'keyCategory[1]/keyCategoryLabel'
      6               , num2   number       path 'keyCategory[2]/keyCategoryNum'
      7               , label2 varchar2(30) path 'keyCategory[2]/keyCategoryLabel'
      8               , num3   number       path 'keyCategory[3]/keyCategoryNum'
      9               , label3 varchar2(30) path 'keyCategory[3]/keyCategoryLabel'
     10       ) x
     11  ;
     
          NUM1 LABEL1                               NUM2 LABEL2                               NUM3 LABEL3
    ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
            27 Asia                                   40 Europe                                 41 Oceania
            28 Africa                                 50 America                                   
            10 Middle East                                                                         
     
    Edited by: odie_63 on 26 oct. 2012 09:56 - added example
  • 2. Re: Pivoting a result set
    mdrake Expert
    Currently Being Moderated
    This is a use case where Object Relational storage should get to the same performance as relational
    SQL> set echo on
    SQL> spool testcase.log
    SQL> --
    SQL> connect sys/oracle as sysdba
    Connected.
    SQL> --
    SQL> set define on
    SQL> set timing on
    SQL> --
    SQL> def XMLDIR = &1
    SQL> --
    SQL> def USERNAME = OTNTEST
    SQL> --
    SQL> def PASSWORD = &USERNAME
    SQL> --
    SQL> def USER_TABLESPACE = USERS
    SQL> --
    SQL> def TEMP_TABLESPACE = TEMP
    SQL> --
    SQL> drop user &USERNAME cascade
      2  /
    old   1: drop user &USERNAME cascade
    new   1: drop user OTNTEST cascade
    
    User dropped.
    
    Elapsed: 00:00:00.40
    SQL> grant create any directory, drop any directory, connect, resource, alter session, create view, unlimited tablespace to &USERNAME identified by &PASSWORD
      2  /
    old   1: grant create any directory, drop any directory, connect, resource, alter session, create view, unlimited tablespace to &USERNAME identified by &PASSWOR
    D
    new   1: grant create any directory, drop any directory, connect, resource, alter session, create view, unlimited tablespace to OTNTEST identified by OTNTEST
    
    Grant succeeded.
    
    Elapsed: 00:00:00.02
    SQL> alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespace &TEMP_TABLESPACE
      2  /
    old   1: alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespace &TEMP_TABLESPACE
    new   1: alter user OTNTEST default tablespace USERS temporary tablespace TEMP
    
    User altered.
    
    Elapsed: 00:00:00.00
    SQL> set long 100000 pages 0 lines 256 trimspool on timing on
    SQL> --
    SQL> connect &USERNAME/&PASSWORD
    Connected.
    SQL> --
    SQL> var XMLSCHEMA CLOB
    SQL> --
    SQL> begin
      2    :XMLSCHEMA :=
      3  '<?xml version="1.0" encoding="UTF-8"?>
      4  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" elementFormDefault="qualified" attributeFormDefault="unquali
    fied">
      5     <xs:element name="keyCategory.Block" type="keyCategory.BlockType"/>
      6     <xs:complexType name="keyCategory.BlockType" xdb:SQLType="KEY_CATEGORY_BLOCK_T" xdb:maintainDOM="false">
      7             <xs:sequence>
      8                     <xs:element name="keyCategory" type="keyCategoryType" maxOccurs="unbounded"/>
      9             </xs:sequence>
     10     </xs:complexType>
     11     <xs:complexType name="keyCategoryType" xdb:SQLType="KEY_CATEGORY_T" xdb:maintainDOM="false">
     12             <xs:sequence>
     13                     <xs:element name="keyCategoryNum" type="xs:int"/>
     14                     <xs:element name="keyCategoryLabel" type="xs:string"/>
     15                     <xs:element name="keyCategoryTopic" type="xs:string"/>
     16             </xs:sequence>
     17     </xs:complexType>
     18  </xs:schema>';
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.00
    SQL> begin
      2    DBMS_XMLSCHEMA.registerSchema(
      3       SCHEMAURL => 'http://xmlns.example.com/xsd/testcase.xsd',
      4       SCHEMADOC => XMLTYPE(:XMLSCHEMA),
      5       LOCAL => TRUE,
      6       GENTYPES => TRUE,
      7       GENTABLES => TRUE
      8    );
      9  end;
     10  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.67
    SQL> create table SAMPLE_DATA (
      2    PK              VARCHAR2(2),
      3    keyCategory_doc XMLTYPE
      4  )
      5  XMLTYPE KEYCATEGORY_DOC
      6  STORE AS OBJECT RELATIONAL
      7  XMLSCHEMA "http://xmlns.example.com/xsd/testcase.xsd" ELEMENT "keyCategory.Block"
      8  VARRAY keyCategory_doc.XMLDATA."keyCategory" store as table KEY_CATEGORY_NT(
      9    (PRIMARY KEY ("NESTED_TABLE_ID", "ARRAY_INDEX"))
     10  )
     11  /
    
    Table created.
    
    Elapsed: 00:00:00.07
    SQL> desc SAMPLE_DATA
     Name                                                                                                                                       Null?    Type
     -------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---
    ------------------------------------------------------------------------------------------------
     PK                                                                                                                                                  VARCHAR2(2)
    
     KEYCATEGORY_DOC                                                                                                                                     SYS.XMLTYPE
    (XMLSchema "http://xmlns.example.com/xsd/testcase.xsd" Element "keyCategory.Block") STOR
                                                                                                                                                         AGE Object-
    relational TYPE "KEY_CATEGORY_BLOCK_T"
    
    SQL> --
    SQL> INSERT INTO SAMPLE_DATA values ('A',
      2               xmltype('<keyCategory.Block>
      3                               <keyCategory>
      4                                       <keyCategoryNum>27</keyCategoryNum>
      5                                       <keyCategoryLabel>Asia</keyCategoryLabel>
      6                                       <keyCategoryTopic>X527</keyCategoryTopic>
      7                               </keyCategory>
      8                               <keyCategory>
      9                                       <keyCategoryNum>40</keyCategoryNum>
     10                                       <keyCategoryLabel>Europe</keyCategoryLabel>
     11                                       <keyCategoryTopic>X540</keyCategoryTopic>
     12                               </keyCategory>
     13                               <keyCategory>
     14                                       <keyCategoryNum>41</keyCategoryNum>
     15                                       <keyCategoryLabel>Oceania</keyCategoryLabel>
     16                                       <keyCategoryTopic>X541</keyCategoryTopic>
     17                               </keyCategory>
     18                       </keyCategory.Block>')
     19  )
     20  /
    
    1 row created.
    
    Elapsed: 00:00:00.01
    SQL> INSERT INTO SAMPLE_DATA values ('B',
      2               xmltype('<keyCategory.Block>
      3                               <keyCategory>
      4                                       <keyCategoryNum>28</keyCategoryNum>
      5                                       <keyCategoryLabel>Africa</keyCategoryLabel>
      6                                       <keyCategoryTopic>X628</keyCategoryTopic>
      7                               </keyCategory>
      8                               <keyCategory>
      9                                       <keyCategoryNum>50</keyCategoryNum>
     10                                       <keyCategoryLabel>America</keyCategoryLabel>
     11                                       <keyCategoryTopic>X650</keyCategoryTopic>
     12                               </keyCategory>
     13                       </keyCategory.Block>')
     14  )
     15  /
    
    1 row created.
    
    Elapsed: 00:00:00.00
    SQL> INSERT INTO SAMPLE_DATA values ('C',
      2               xmltype('<keyCategory.Block>
      3                               <keyCategory>
      4                                       <keyCategoryNum>10</keyCategoryNum>
      5                                       <keyCategoryLabel>Middle East</keyCategoryLabel>
      6                                       <keyCategoryTopic>X100</keyCategoryTopic>
      7                               </keyCategory>
      8                       </keyCategory.Block>')
      9  )
     10  /
    
    1 row created.
    
    Elapsed: 00:00:00.00
    SQL> set autotrace on explain lines 256 heading on
    SQL> --
    SQL> select t.rowid, x1.*
      2    from SAMPLE_DATA t,
      3         xmlTable(
      4           '/keyCategory.Block/keyCategory'
      5           passing t.keyCategory_doc
      6           columns
      7                seq for ordinality,
      8                num   varchar2(100) path '/keyCategory/keyCategoryNum',
      9                label varchar2(100) path '/keyCategory/keyCategoryLabel'
     10         ) x1
     11  /
    AAAaIMAAEAAB7ncAAA          1 27   Asia
    AAAaIMAAEAAB7ncAAA          2 40   Europe
    AAAaIMAAEAAB7ncAAA          3 41   Oceania
    AAAaIMAAEAAB7ncAAB          1 28   Africa
    AAAaIMAAEAAB7ncAAB          2 50   America
    AAAaIMAAEAAB7ncAAC          1 10   Middle East
    
    6 rows selected.
    
    Elapsed: 00:00:00.02
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1846565380
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |                 |     6 |   834 |    12   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS        |                 |     6 |   834 |    12   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL  | SAMPLE_DATA     |     3 |    66 |     3   (0)| 00:00:01 |
    |   3 |   VIEW               |                 |     2 |   234 |     3   (0)| 00:00:01 |
    |   4 |    COUNT             |                 |       |       |            |          |
    |*  5 |     TABLE ACCESS FULL| KEY_CATEGORY_NT |     2 |  4050 |     3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - filter("NESTED_TABLE_ID"="T"."SYS_NC0000800009$")
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL> create VIEW v1
      2  as
      3  select t.rowid DOCID, x1.*
      4    from SAMPLE_DATA t,
      5         xmlTable(
      6           '/keyCategory.Block/keyCategory'
      7           passing t.keyCategory_doc
      8           columns
      9                seq for ordinality,
     10                num    path '/keyCategory/keyCategoryNum',
     11                label  path '/keyCategory/keyCategoryLabel'
     12         ) x1
     13  /
    
    View created.
    
    Elapsed: 00:00:00.03
    SQL> DESC V1
     Name                                                                                                                                       Null?    Type
     -------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---
    ------------------------------------------------------------------------------------------------
     DOCID                                                                                                                                               ROWID
     SEQ                                                                                                                                                 NUMBER
     NUM                                                                                                                                                 NUMBER(10)
     LABEL                                                                                                                                               VARCHAR2(40
    00)
    
    SQL> --
    SQL> column NUM format 999
    SQL> column LABEL format A20
    SQL> --
    SQL> set heading on pages 20
    SQL> select V1.NUM, V2.NUM, V3.NUM, V1.LABEL, V2.LABEL, V3.LABEL
      2    from SAMPLE_DATA s
      3    LEFT JOIN V1 v1
      4      ON s.ROWID = v1.DOCID
      5     and v1.seq = 1
      6    LEFT JOIN V1 v2
      7      ON s.ROWID = v2.DOCID
      8     and v2.seq = 2
      9    LEFT JOIN V1 v3
     10      ON s.ROWID = v3.DOCID
     11     and v3.seq = 3
     12  /
    
     NUM  NUM  NUM LABEL                LABEL                LABEL
    ---- ---- ---- -------------------- -------------------- --------------------
      27   40   41 Asia                 Europe               Oceania
      28   50      Africa               America
      10           Middle East
    
    Elapsed: 00:00:00.07
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 238015595
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |                 |    24 |   142K|    41   (5)| 00:00:01 |
    |*  1 |  HASH JOIN RIGHT OUTER   |                 |    24 |   142K|    41   (5)| 00:00:01 |
    |   2 |   VIEW                   | V1              |     6 | 12162 |    12   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS          |                 |     6 | 12300 |    12   (0)| 00:00:01 |
    |   4 |     TABLE ACCESS FULL    | SAMPLE_DATA     |     3 |    66 |     3   (0)| 00:00:01 |
    |*  5 |     VIEW                 |                 |     2 |  4056 |     3   (0)| 00:00:01 |
    |   6 |      COUNT               |                 |       |       |            |          |
    |*  7 |       TABLE ACCESS FULL  | KEY_CATEGORY_NT |     2 |  4050 |     3   (0)| 00:00:01 |
    |*  8 |   HASH JOIN OUTER        |                 |    12 | 48792 |    28   (4)| 00:00:01 |
    |*  9 |    HASH JOIN OUTER       |                 |     6 | 12234 |    16   (7)| 00:00:01 |
    |  10 |     TABLE ACCESS FULL    | SAMPLE_DATA     |     3 |    36 |     3   (0)| 00:00:01 |
    |  11 |     VIEW                 | V1              |     6 | 12162 |    12   (0)| 00:00:01 |
    |  12 |      NESTED LOOPS        |                 |     6 | 12300 |    12   (0)| 00:00:01 |
    |  13 |       TABLE ACCESS FULL  | SAMPLE_DATA     |     3 |    66 |     3   (0)| 00:00:01 |
    |* 14 |       VIEW               |                 |     2 |  4056 |     3   (0)| 00:00:01 |
    |  15 |        COUNT             |                 |       |       |            |          |
    |* 16 |         TABLE ACCESS FULL| KEY_CATEGORY_NT |     2 |  4050 |     3   (0)| 00:00:01 |
    |  17 |    VIEW                  | V1              |     6 | 12162 |    12   (0)| 00:00:01 |
    |  18 |     NESTED LOOPS         |                 |     6 | 12300 |    12   (0)| 00:00:01 |
    |  19 |      TABLE ACCESS FULL   | SAMPLE_DATA     |     3 |    66 |     3   (0)| 00:00:01 |
    |* 20 |      VIEW                |                 |     2 |  4056 |     3   (0)| 00:00:01 |
    |  21 |       COUNT              |                 |       |       |            |          |
    |* 22 |        TABLE ACCESS FULL | KEY_CATEGORY_NT |     2 |  4050 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("S".ROWID="V3"."DOCID"(+))
       5 - filter("X1"."SEQ"=3)
       7 - filter("NESTED_TABLE_ID"="T"."SYS_NC0000800009$")
       8 - access("S".ROWID="V2"."DOCID"(+))
       9 - access("S".ROWID="V1"."DOCID"(+))
      14 - filter("X1"."SEQ"=1)
      16 - filter("NESTED_TABLE_ID"="T"."SYS_NC0000800009$")
      20 - filter("X1"."SEQ"=2)
      22 - filter("NESTED_TABLE_ID"="T"."SYS_NC0000800009$")
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL>
    Was PIVOT introduced in 10.2.x ?

    Edited by: mdrake on Oct 26, 2012 12:50 PM
  • 3. Re: Pivoting a result set
    mdrake Expert
    Currently Being Moderated
    Odie's looks cleaner (At least in 11.2.0.3.0)
    SQL> select x.*
      2    from SAMPLE_DATA t
      3       , xmltable('/keyCategory.Block' passing t.keyCategory_doc
      4           columns num1   number       path 'keyCategory[1]/keyCategoryNum'
      5                 , label1 varchar2(30) path 'keyCategory[1]/keyCategoryLabel'
      6                 , num2   number       path 'keyCategory[2]/keyCategoryNum'
      7                 , label2 varchar2(30) path 'keyCategory[2]/keyCategoryLabel'
      8                 , num3   number       path 'keyCategory[3]/keyCategoryNum'
      9                 , label3 varchar2(30) path 'keyCategory[3]/keyCategoryLabel'
     10        ) x
     11  /
    
          NUM1 LABEL1                               NUM2 LABEL2                               NUM3 LABEL3
    ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
            27 Asia                                   40 Europe                                 41 Oceania
            28 Africa                                 50 America
            10 Middle East
    
    Elapsed: 00:00:00.02
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1250090377
    
    -----------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                 |     3 |    30 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| KEY_CATEGORY_NT |     1 |  4180 |     1   (0)| 00:00:01 |
    |*  2 |   INDEX UNIQUE SCAN         | SYS_C0028060    |     1 |       |     1   (0)| 00:00:01 |
    |   3 |  TABLE ACCESS BY INDEX ROWID| KEY_CATEGORY_NT |     1 |  4180 |     1   (0)| 00:00:01 |
    |*  4 |   INDEX UNIQUE SCAN         | SYS_C0028060    |     1 |       |     1   (0)| 00:00:01 |
    |   5 |  TABLE ACCESS BY INDEX ROWID| KEY_CATEGORY_NT |     1 |  4180 |     1   (0)| 00:00:01 |
    |*  6 |   INDEX UNIQUE SCAN         | SYS_C0028060    |     1 |       |     1   (0)| 00:00:01 |
    |   7 |  TABLE ACCESS BY INDEX ROWID| KEY_CATEGORY_NT |     1 |  4180 |     1   (0)| 00:00:01 |
    |*  8 |   INDEX UNIQUE SCAN         | SYS_C0028060    |     1 |       |     1   (0)| 00:00:01 |
    |   9 |  TABLE ACCESS BY INDEX ROWID| KEY_CATEGORY_NT |     1 |  4180 |     1   (0)| 00:00:01 |
    |* 10 |   INDEX UNIQUE SCAN         | SYS_C0028060    |     1 |       |     1   (0)| 00:00:01 |
    |  11 |  TABLE ACCESS BY INDEX ROWID| KEY_CATEGORY_NT |     1 |  4180 |     1   (0)| 00:00:01 |
    |* 12 |   INDEX UNIQUE SCAN         | SYS_C0028060    |     1 |       |     1   (0)| 00:00:01 |
    |  13 |  TABLE ACCESS FULL          | SAMPLE_DATA     |     3 |    30 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("P"."NESTED_TABLE_ID"=:B1 AND "P"."SYS_NC_ARRAY_INDEX$"=0001)
       4 - access("P"."NESTED_TABLE_ID"=:B1 AND "P"."SYS_NC_ARRAY_INDEX$"=0001)
       6 - access("P"."NESTED_TABLE_ID"=:B1 AND "P"."SYS_NC_ARRAY_INDEX$"=0002)
       8 - access("P"."NESTED_TABLE_ID"=:B1 AND "P"."SYS_NC_ARRAY_INDEX$"=0002)
      10 - access("P"."NESTED_TABLE_ID"=:B1 AND "P"."SYS_NC_ARRAY_INDEX$"=0003)
      12 - access("P"."NESTED_TABLE_ID"=:B1 AND "P"."SYS_NC_ARRAY_INDEX$"=0003)
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL>
  • 4. Re: Pivoting a result set
    odie_63 Guru
    Currently Being Moderated
    Was PIVOT introduced in 10.2.x ?
    No, in 11.1.

    The usual approach in prior versions looks like :
    SQL> select max(case when x.seq = 1 then x.num end) as num1
      2       , max(case when x.seq = 2 then x.num end) as num2
      3       , max(case when x.seq = 3 then x.num end) as num3
      4       , max(case when x.seq = 1 then x.label end) as label1
      5       , max(case when x.seq = 2 then x.label end) as label2
      6       , max(case when x.seq = 3 then x.label end) as label3
      7  from sample_data t,
      8       xmltable(
      9         '/keyCategory.Block/keyCategory'
     10         passing t.keyCategory_doc
     11         columns
     12           seq   for ordinality
     13         , num   number       path 'keyCategoryNum'
     14         , label varchar2(20) path 'keyCategoryLabel'
     15       ) x
     16  group by t.pk
     17  ;
    
          NUM1       NUM2       NUM3 LABEL1               LABEL2               LABEL3
    ---------- ---------- ---------- -------------------- -------------------- --------------------
            27         40         41 Asia                 Europe               Oceania
            28         50            Africa               America
            10                       Middle East
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3963476672
    
    -----------------------------------------------------------------------------------------
    | Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                 |     3 |   171 |    13   (8)| 00:00:01 |
    |   1 |  HASH GROUP BY        |                 |     3 |   171 |    13   (8)| 00:00:01 |
    |   2 |   NESTED LOOPS        |                 |     6 |   342 |    12   (0)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL  | SAMPLE_DATA     |     3 |    57 |     3   (0)| 00:00:01 |
    |   4 |    VIEW               |                 |     2 |    76 |     3   (0)| 00:00:01 |
    |   5 |     COUNT             |                 |       |       |            |          |
    |*  6 |      TABLE ACCESS FULL| KEY_CATEGORY_NT |     2 |  4050 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       6 - filter("NESTED_TABLE_ID"="T"."SYS_NC0000800009$")
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    ... which is pretty much what PIVOT does behind the scenes.
    Notice the projection information :
    SQL> explain plan for
      2  with pivot_set as (
      3    select t.pk, x.seq, x.num, x.label
      4    from sample_data t
      5       , xmltable(
      6           '/keyCategory.Block/keyCategory'
      7           passing t.keyCategory_doc
      8           columns
      9             seq   for ordinality
     10           , num   number       path 'keyCategoryNum'
     11           , label varchar2(20) path 'keyCategoryLabel'
     12         ) x
     13  )
     14  select *
     15  from pivot_set
     16  pivot (
     17    max(num) as num, max(label) as label
     18    for seq in (1,2,3)
     19  );
    
    Explained.
    
    SQL> set pages 100
    SQL> select * from table(dbms_xplan.display(format => 'TYPICAL PROJECTION'));
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 2595693309
    
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |                 |     3 |   171 |    10  (10)| 00:00:01 |
    |   1 |  HASH GROUP BY PIVOT            |                 |     3 |   171 |    10  (10)| 00:00:01 |
    |   2 |   NESTED LOOPS                  |                 |     6 |   342 |     9   (0)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL            | SAMPLE_DATA     |     3 |    57 |     3   (0)| 00:00:01 |
    |   4 |    VIEW                         |                 |     2 |    76 |     2   (0)| 00:00:01 |
    |   5 |     COUNT                       |                 |       |       |            |          |
    |   6 |      TABLE ACCESS BY INDEX ROWID| KEY_CATEGORY_NT |     2 |    56 |     2   (0)| 00:00:01 |
    |*  7 |       INDEX RANGE SCAN          | SYS_C008344     |     2 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       7 - access("NESTED_TABLE_ID"="T"."SYS_NC0000800009$")
    
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
    
       1 - (#keys=1) "T"."PK"[VARCHAR2,2], MAX(CASE  WHEN ("X"."SEQ"=1) THEN "X"."LABEL" END
           )[20], MAX(CASE  WHEN ("X"."SEQ"=2) THEN "X"."LABEL" END )[20], MAX(CASE  WHEN
           ("X"."SEQ"=3) THEN "X"."LABEL" END )[20], MAX(CASE  WHEN ("X"."SEQ"=1) THEN "X"."NUM" END
           )[22], MAX(CASE  WHEN ("X"."SEQ"=2) THEN "X"."NUM" END )[22], MAX(CASE  WHEN ("X"."SEQ"=3)
           THEN "X"."NUM" END )[22]
       2 - (#keys=0) "T"."PK"[VARCHAR2,2], "X"."SEQ"[NUMBER,22], "X"."NUM"[NUMBER,22],
           "X"."LABEL"[VARCHAR2,20]
       3 - "T"."PK"[VARCHAR2,2], "T"."SYS_NC0000800009$"[RAW,16]
       4 - "X"."SEQ"[NUMBER,22], "X"."NUM"[NUMBER,22], "X"."LABEL"[VARCHAR2,20]
       5 - "keyCategoryNum"[NUMBER,22], "keyCategoryLabel"[VARCHAR2,4000], ROWNUM[4]
       6 - "keyCategoryNum"[NUMBER,22], "keyCategoryLabel"[VARCHAR2,4000]
       7 - "SYS_ALIAS_2".ROWID[ROWID,10]
    
    35 rows selected.
    Edited by: odie_63 on 27 oct. 2012 19:35
  • 5. Re: Pivoting a result set
    udayaraj Newbie
    Currently Being Moderated
    Hello odie_63 and mdrake

    Thank you very much for kindly helping me with clear examples.
    We are using Object relational storage. Sorry that I didn't mention this first up.

    We were using the Group by pivot till now and the answers provided by both of you work fine.
    This is part of a pagination query. The response times are very good now except while sorting on the xml columns.

    Please see below

    The registered XML Schema(keyCategory.xsd) is given below
    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" elementFormDefault="qualified" xdb:storeVarrayAsTable="true">
         <xs:element name="keyCategory.Block">
              <xs:complexType xdb:maintainDOM="false">
                   <xs:sequence>
                        <xs:element name="keyCategory" maxOccurs="unbounded">
                             <xs:complexType xdb:maintainDOM="false">
                                  <xs:sequence>
                                       <xs:element name="keyCategoryNum" type="xs:string"/>
                                       <xs:element name="keyCategoryLabel" type="xs:string"/>
                                       <xs:element name="keyCategoryTopic" type="xs:string"/>
                                  </xs:sequence>
                             </xs:complexType>
                        </xs:element>
                   </xs:sequence>
              </xs:complexType>
         </xs:element>
    </xs:schema>
    The code used to register the schema is given below
    declare
          result boolean;
        begin
          result := dbms_xdb.createResource('/keydata/xsd/keyCategory.xsd', bfilename('XMLDIR','keyCategory.xsd'),nls_charset_id('AL32UTF8'));
        end;
    /
    
    begin
          dbms_xmlschema.registerSchema
          (
            schemaURL => '/keydata/xsd/keyCategory.xsd',
            schemaDoc => xdbURIType('/keydata/xsd/keyCategory.xsd').getClob(),
            local     => TRUE,
            genTypes  => TRUE,
            genBean   => FALSE,
            genTables => FALSE
         );
       end;
    / 
    The table definition is
    create table NEWS_META
         (
         JVID                    VARCHAR2(255) NOT NULL,
         CONTENT_TYPE_ID         NUMBER(4,0)   NOT NULL,
         NEWS_NBR                VARCHAR2(32)  NOT NULL,
         ISSUE_DTM          DATE,
         KEY_CATEGORY_PATH       SYS.XMLTYPE,
         CONSTRAINT NEWS_META_PK PRIMARY KEY (JVID)
    )
    TABLESPACE KEYDATA0001
    XMLTYPE COLUMN KEY_CATEGORY_PATH STORE AS OBJECT RELATIONAL
             XMLSCHEMA "/keydata/xsd/keyCategory.xsd"
             ELEMENT "keyCategory.Block"
              VARRAY KEY_CATEGORY_PATH."XMLDATA"."keyCategory" STORE AS TABLE KEYCATEGORY_TAB
                  (
                     ( primary key (nested_table_id, array_index)
                     ) organization index overflow
                 )
    /
    Created a view
     
    raj@cms> create or replace view
      2  NEWS_META_VIEW1
      3     (jvid,
      4     issue_dtm,
      5     key_category_num1,
      6     key_category_num2,
      7     key_category_num3,
      8     key_category_label1,
      9     key_category_label2,
     10     key_category_label3)
     11  as
     12             select
     13                     news_meta.jvid,news_meta.issue_dtm,
     14                     kcp.num1, kcp.num2, kcp.num3, key_Category_label1, kcp.key_Category_label2 , kcp.key_Category_label3
     15                     from
     16                     news_meta,
     17                             xmlTable
     18                             (
     19                                     '/keyCategory.Block'
     20                                     passing news_meta.KEY_CATEGORY_PATH
     21                                     columns
     22                                               num1                  varchar2(100)   path 'keyCategory[1]/keyCategoryNum'
     23                                          , num2                  varchar2(100)   path 'keyCategory[2]/keyCategoryNum'
     24                                          , num3                  varchar2(100)   path 'keyCategory[3]/keyCategoryNum'
     25                                          , key_Category_label1   varchar2(100)   path 'keyCategory[1]/keyCategoryLabel'
     26                                          , key_Category_label2   varchar2(100)   path 'keyCategory[2]/keyCategoryLabel'
     27                                          , key_Category_label3   varchar2(100)   path 'keyCategory[3]/keyCategoryLabel'
     28                             ) kcp
     29  /
    
    View created.
    
    Elapsed: 00:00:00.04
    Created an index on ISSUE_DTM column so that it could be used for sorting
    raj@cms> create index
      2     IDX_NEWS_META_ISSUE_DTM
      3  on
      4     NEWS_META(ISSUE_DTM,0);
    
    Index created.
    
    Elapsed: 00:00:01.17
    raj@cms> begin
      2  DBMS_STATS.GATHER_TABLE_STATS
      3     (
      4     ownname => user ,
      5     tabname => 'NEWS_META',
      6     cascade => true,
      7     method_opt=>'for all indexed columns size AUTO'
      8     );
      9  end;
     10  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:17.53
    Executed the Pagination query sorted by ISSUE_DTM
    raj@cms> set autotrace traceonly
    raj@cms>
    raj@cms> exec :l_maxrow := 20
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.01
    raj@cms> exec :l_minrow := 1
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.01
    
    raj@cms> select
      2     JVID, ISSUE_DTM, KEY_CATEGORY1, KEY_CATEGORY2, KEY_CATEGORY3
      3  from
      4          (
      5          select
      6                  a.*, rownum r
      7          from (
      8             SELECT /*+ FIRST_ROWS(20) */
      9                     JM.JVID,  NV.ISSUE_DTM, NV.KEY_CATEGORY_LABEL1 KEY_CATEGORY1, NV.KEY_CATEGORY_LABEL2 KEY_CATEGORY2,
     10                     NV.KEY_CATEGORY_LABEL3 KEY_CATEGORY3
     11             FROM
     12                     JV_MASTER JM,  NEWS_META_VIEW1 NV
     13             WHERE
     14                     JM.ACTIVE_FLG = 'Y'  AND
     15                     JM.JVID = NV.JVID
     16             Order by
     17                     ISSUE_DTM DESC
     18             ) a
     19             where
     20                     rownum <= :l_maxrow
     21     )
     22  where r >= :l_minrow
     23  
    
    20 rows selected.
    
    Elapsed: 00:00:00.06
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3444472946
    
    -----------------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |                         |    21 |  6447 |    10   (0)| 00:00:01 |
    |*  1 |  VIEW                           |                         |    21 |  6447 |    10   (0)| 00:00:01 |
    |*  2 |   COUNT STOPKEY                 |                         |       |       |            |       |
    |   3 |    VIEW                         |                         |    21 |  6174 |    10   (0)| 00:00:01 |
    |   4 |     NESTED LOOPS                |                         |    21 |  3444 |    10   (0)| 00:00:01 |
    |*  5 |      TABLE ACCESS BY INDEX ROWID| NEWS_META               | 19306 |  2545K|     9   (0)| 00:00:01 |
    |   6 |       INDEX FULL SCAN DESCENDING| IDX_NEWS_META_ISSUE_DTM |    40 |       |     1   (0)| 00:00:01 |
    |*  7 |      TABLE ACCESS BY INDEX ROWID| JV_MASTER               |    21 |   609 |     1   (0)| 00:00:01 |
    |*  8 |       INDEX UNIQUE SCAN         | JV_MASTER_PK            |     1 |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("R">=TO_NUMBER(:L_MINROW))
       2 - filter(ROWNUM<=TO_NUMBER(:L_MAXROW))
       5 - filter(SYS_OP_NOEXPAND("NEWS_META"."SYS_NC00012$") IS NOT NULL AND
                  "NEWS_META"."CONTENT_TYPE_ID"=601)
       7 - filter("JM"."ACTIVE_FLG"='Y')
       8 - access("JM"."JVID"="NEWS_META"."JVID")
    
    
    Statistics
    ----------------------------------------------------------
             60  recursive calls
              0  db block gets
            144  consistent gets
              0  physical reads
              0  redo size
           2357  bytes sent via SQL*Net to client
            487  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             20  rows processed
    Now sorting with the XML Column Key_Category_Label
    raj@cms>   select
      2         JVID, ISSUE_DTM, KEY_CATEGORY1, KEY_CATEGORY2, KEY_CATEGORY3
      3      from
      4              (
      5              select
      6                      a.*, rownum r
      7              from (
      8                 SELECT /*+ FIRST_ROWS(20) */
      9                         JM.JVID,  NV.ISSUE_DTM, NV.KEY_CATEGORY_LABEL1 KEY_CATEGORY1, NV.KEY_CATEGORY_LABEL2 KEY_CATEGORY2,
     10                         NV.KEY_CATEGORY_LABEL3 KEY_CATEGORY3
     11                 FROM
     12                         JV_MASTER JM,  NEWS_META_VIEW1 NV
     13                 WHERE
     14                         JM.ACTIVE_FLG = 'Y'  AND
     15                         JM.JVID = NV.JVID
     16                 Order by
     17                         KEY_CATEGORY_LABEL1 DESC
     18                 ) a
     19                 where
     20                         rownum <= :l_maxrow
     21         )
     22     where r >= :l_minrow
     23 /
    
    20 rows selected.
    
    Elapsed: 00:00:55.10
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3713631540
    
    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |
    ---------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |              |    96M|    27G|       |  3515K  (1)| 11:43:02 |
    |*  1 |  VIEW                            |              |    96M|    27G|       |  3515K  (1)| 11:43:02 |
    |*  2 |   COUNT STOPKEY                  |              |       |       |       |            |       |
    |   3 |    VIEW                          |              |    96M|    26G|       |  3515K  (1)| 11:43:02 |
    |*  4 |     SORT ORDER BY STOPKEY        |              |    96M|    14G|    15G|  3515K  (1)| 11:43:02 |
    |   5 |      NESTED LOOPS                |              |    96M|    14G|       |  8852   (1)| 00:01:47 |
    |*  6 |       TABLE ACCESS FULL          | NEWS_META    | 19306 |  2545K|       |  4023   (1)| 00:00:49 |
    |*  7 |       TABLE ACCESS BY INDEX ROWID| JV_MASTER    |  5022 |   142K|       |     1   (0)| 00:00:01 |
    |*  8 |        INDEX UNIQUE SCAN         | JV_MASTER_PK |     1 |       |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("R">=TO_NUMBER(:L_MINROW))
       2 - filter(ROWNUM<=TO_NUMBER(:L_MAXROW))
       4 - filter(ROWNUM<=TO_NUMBER(:L_MAXROW))
       6 - filter(SYS_OP_NOEXPAND("NEWS_META"."SYS_NC00012$") IS NOT NULL AND
                  "NEWS_META"."CONTENT_TYPE_ID"=601)
       7 - filter("JM"."ACTIVE_FLG"='Y')
       8 - access("JM"."JVID"="NEWS_META"."JVID")
    
    
    Statistics
    ----------------------------------------------------------
        1789799  recursive calls
              0  db block gets
        3632147  consistent gets
              0  physical reads
              0  redo size
           2139  bytes sent via SQL*Net to client
            487  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
             20  rows processed
    I tried creating an index on the label column
    raj@cms> DESC KEYCATEGORY_TAB
     Name                                                                                      Null?    Type
     ----------------------------------------------------------------------------------------- -------- ----------------------------
     keyCategoryNum                                                                                     VARCHAR2(4000 CHAR)
     keyCategoryLabel                                                                                   VARCHAR2(4000 CHAR)
     keyCategoryTopic                                                                                   VARCHAR2(4000 CHAR)
    
    
    
    raj@cms> create index KEYCATEGORY_TAB_IDX1 on KEYCATEGORY_TAB("keyCategoryLabel", 0)
      2  /
    
    Index created.
    
    Elapsed: 00:00:12.96
    raj@cms> begin
      2  DBMS_STATS.GATHER_TABLE_STATS
      3     (
      4     ownname => user ,
      5     tabname => 'NEWS_META',
      6     cascade => true,
      7     method_opt=>'for all indexed columns size AUTO'
      8     );
      9  end;
     10  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:16.03
    
    raj@cms> select
      2          JVID, ISSUE_DTM, KEY_CATEGORY1, KEY_CATEGORY2, KEY_CATEGORY3
      3       from
      4               (
      5               select
      6                       a.*, rownum r
      7               from (
      8                  SELECT /*+ FIRST_ROWS(20) */
      9                          JM.JVID,  NV.ISSUE_DTM, NV.KEY_CATEGORY_LABEL1 KEY_CATEGORY1, NV.KEY_CATEGORY_LABEL2 KEY_CATEGORY2,
     10                          NV.KEY_CATEGORY_LABEL3 KEY_CATEGORY3
     11                  FROM
     12                          JV_MASTER JM,  NEWS_META_VIEW1 NV
     13                  WHERE
     14                          JM.ACTIVE_FLG = 'Y'  AND
     15                          JM.JVID = NV.JVID
     16                  Order by
     17                          KEY_CATEGORY_LABEL1 DESC
     18                  ) a
     19                  where
     20                          rownum <= :l_maxrow
     21          )
     22      where r >= :l_minrow
     23  /
    
    20 rows selected.
    
    Elapsed: 00:00:54.15
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3713631540
    
    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |
    ---------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |              |    98M|    28G|       |  3581K  (1)| 11:56:16 |
    |*  1 |  VIEW                            |              |    98M|    28G|       |  3581K  (1)| 11:56:16 |
    |*  2 |   COUNT STOPKEY                  |              |       |       |       |            |       |
    |   3 |    VIEW                          |              |    98M|    27G|       |  3581K  (1)| 11:56:16 |
    |*  4 |     SORT ORDER BY STOPKEY        |              |    98M|    15G|    16G|  3581K  (1)| 11:56:16 |
    |   5 |      NESTED LOOPS                |              |    98M|    15G|       |  8852   (1)| 00:01:47 |
    |*  6 |       TABLE ACCESS FULL          | NEWS_META    | 19306 |  2545K|       |  4023   (1)| 00:00:49 |
    |*  7 |       TABLE ACCESS BY INDEX ROWID| JV_MASTER    |  5116 |   144K|       |     1   (0)| 00:00:01 |
    |*  8 |        INDEX UNIQUE SCAN         | JV_MASTER_PK |     1 |       |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("R">=TO_NUMBER(:L_MINROW))
       2 - filter(ROWNUM<=TO_NUMBER(:L_MAXROW))
       4 - filter(ROWNUM<=TO_NUMBER(:L_MAXROW))
       6 - filter(SYS_OP_NOEXPAND("NEWS_META"."SYS_NC00012$") IS NOT NULL AND
                  "NEWS_META"."CONTENT_TYPE_ID"=601)
       7 - filter("JM"."ACTIVE_FLG"='Y')
       8 - access("JM"."JVID"="NEWS_META"."JVID")
    
    
    Statistics
    ----------------------------------------------------------
        1789805  recursive calls
              0  db block gets
        3632220  consistent gets
              0  physical reads
              0  redo size
           2139  bytes sent via SQL*Net to client
            487  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
             20  rows processed
    How to get the index on the label column to be used while sorting?

    Thank you very much once again in advance

    Kind Regards
    Raj
  • 6. Re: Pivoting a result set
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    Me being curious... why the extra ", 0" in the create index statement?
    raj@cms> create index
      2     IDX_NEWS_META_ISSUE_DTM
      3  on
      4     NEWS_META(ISSUE_DTM,0);
  • 7. Re: Pivoting a result set
    Stew Ashton Expert
    Currently Being Moderated
    Marco Gralike wrote:
    Me being curious... why the extra ", 0" in the create index statement?
    Possibly to guarantee that the index has an entry for every row in the table, even if the column value is NULL.
  • 8. Re: Pivoting a result set
    udayaraj Newbie
    Currently Being Moderated
    Thanks Stew. You are right.

    Regards
    Raj
  • 9. Re: Pivoting a result set
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    Still don't get it

    NULL != 0

    Why are you trying to "mislead" the optimizer by building indexes this way? The column in the table contains NULL, right?

    Edited by: Marco Gralike on Oct 30, 2012 2:19 PM
  • 10. Re: Pivoting a result set
    udayaraj Newbie
    Currently Being Moderated
    Yes, the column which is indexed is defined as NULL. Since this column is being used to sort the result set, an entry in the index for every row in the table will enable the optimizer to use this index while sorting even if this column value has null.

    Please see below
    raj@cms> 
    raj@cms> set lines 141
    raj@cms> 
    raj@cms> set autotrace traceonly
    raj@cms> 
    raj@cms> desc news_meta_View1
     Name                                                                          Null?    Type
     ---------------------------------------------- -------- -----------------------------------------------------
     NEWS_ROWID                                              ROWID
     JVID                                           NOT NULL VARCHAR2(255)
     ISSUE_DTM                                               DATE
     KEY_CATEGORY_NUM1                                       VARCHAR2(100)
     KEY_CATEGORY_NUM2                                       VARCHAR2(100)
     KEY_CATEGORY_NUM3                                       VARCHAR2(100)
     KEY_CATEGORY_LABEL1                                     VARCHAR2(100)
     KEY_CATEGORY_LABEL2                                     VARCHAR2(100)
     KEY_CATEGORY_LABEL3                                     VARCHAR2(100)
    
    raj@cms> 
    raj@cms> desc news_meta
     Name                                           Null?    Type
     ---------------------------------------------- -------- -----------------------------------------------------
     JVID                                           NOT NULL VARCHAR2(255)
     CONTENT_TYPE_ID                                NOT NULL NUMBER(4)
     NEWS_NBR                                       NOT NULL VARCHAR2(32)
     ISSUE_DTM                                               DATE
     KEY_CATEGORY_PATH                                       SYS.XMLTYPE(XMLSchema "/keydata/xsd/keyCategory.xsd" 
                                        Element "keyCategory.Block") STORAGE Object-relatio
                                        nal TYPE "keyCategory.Block697_T"
    
    raj@cms> 
    raj@cms> drop index IDX_NEWS_META_ISSUE_DTM
      2  /
    
    Index dropped.
    
    Elapsed: 00:00:00.02
    raj@cms> 
    raj@cms> create index
      2       IDX_NEWS_META_ISSUE_DTM
      3    on
      4       NEWS_META(ISSUE_DTM)
      5  /
    
    Index created.
    
    Elapsed: 00:00:01.16
    raj@cms> 
    raj@cms> begin
      2  DBMS_STATS.GATHER_TABLE_STATS
      3     (
      4     ownname => user ,
      5     tabname => 'NEWS_META',
      6     cascade => true,
      7     method_opt=>'for all indexed columns size AUTO'
      8     );
      9  end;
     10  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:12.14
    raj@cms> 
    raj@cms> variable l_maxrow number
    raj@cms> variable l_minrow number
    raj@cms> 
    raj@cms> exec :l_maxrow := 20
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.01
    raj@cms> exec :l_minrow := 1
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.01
    raj@cms> 
    raj@cms> 
    raj@cms> 
    raj@cms> select
      2        JVID, ISSUE_DTM, KEY_CATEGORY1, KEY_CATEGORY2, KEY_CATEGORY3
      3     from
      4             (
      5             select
      6                     a.*, rownum r
      7             from (
      8                SELECT /*+ FIRST_ROWS(20) */
      9                        JM.JVID,  NV.ISSUE_DTM, NV.KEY_CATEGORY_LABEL1 KEY_CATEGORY1, NV.KEY_CATEGORY_LABEL2 KEY_CATEGORY2,
     10                        NV.KEY_CATEGORY_LABEL3 KEY_CATEGORY3
     11                FROM
     12                        JV_MASTER JM,  NEWS_META_VIEW1 NV
     13                WHERE
     14                        JM.ACTIVE_FLG = 'Y'  AND
     15                        JM.JVID = NV.JVID
     16                Order by
     17                        ISSUE_DTM DESC
     18                ) a
     19                where
     20                        rownum <= :l_maxrow
     21        )
     22     where r >= :l_minrow
     23  /
    
    20 rows selected.
    
    Elapsed: 00:00:54.56
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3713631540
    
    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |              |   101M|    29G|       |  3680K  (1)| 12:16:06 |
    |*  1 |  VIEW                            |              |   101M|    29G|       |  3680K  (1)| 12:16:06 |
    |*  2 |   COUNT STOPKEY                  |              |       |       |       |            |          |
    |   3 |    VIEW                          |              |   101M|    27G|       |  3680K  (1)| 12:16:06 |
    |*  4 |     SORT ORDER BY STOPKEY        |              |   101M|    15G|    16G|  3680K  (1)| 12:16:06 |
    |   5 |      NESTED LOOPS                |              |   101M|    15G|       |  8852   (1)| 00:01:47 |
    |*  6 |       TABLE ACCESS FULL          | NEWS_META    | 19306 |  2545K|       |  4023   (1)| 00:00:49 |
    |*  7 |       TABLE ACCESS BY INDEX ROWID| JV_MASTER    |  5259 |   148K|       |     1   (0)| 00:00:01 |
    |*  8 |        INDEX UNIQUE SCAN         | JV_MASTER_PK |     1 |       |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("R">=TO_NUMBER(:L_MINROW))
       2 - filter(ROWNUM<=TO_NUMBER(:L_MAXROW))
       4 - filter(ROWNUM<=TO_NUMBER(:L_MAXROW))
       6 - filter(SYS_OP_NOEXPAND("NEWS_META"."SYS_NC00012$") IS NOT NULL AND
                  "NEWS_META"."CONTENT_TYPE_ID"=601)
       7 - filter("JM"."ACTIVE_FLG"='Y')
       8 - access("JM"."JVID"="NEWS_META"."JVID")
    
    
    Statistics
    ----------------------------------------------------------
        1789799  recursive calls
              0  db block gets
        3632210  consistent gets
              0  physical reads
              0  redo size
           2357  bytes sent via SQL*Net to client
            487  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
             20  rows processed
    
    raj@cms> 
    raj@cms> drop index IDX_NEWS_META_ISSUE_DTM
      2  /
    
    Index dropped.
    
    Elapsed: 00:00:00.02
    raj@cms> 
    raj@cms> create index
      2       IDX_NEWS_META_ISSUE_DTM
      3    on
      4       NEWS_META(ISSUE_DTM,0)
      5  /
    
    Index created.
    
    Elapsed: 00:00:01.21
    raj@cms> 
    raj@cms> begin
      2  DBMS_STATS.GATHER_TABLE_STATS
      3     (
      4     ownname => user ,
      5     tabname => 'NEWS_META',
      6     cascade => true,
      7     method_opt=>'for all indexed columns size AUTO'
      8     );
      9  end;
     10  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:12.58
    raj@cms> select
      2        JVID, ISSUE_DTM, KEY_CATEGORY1, KEY_CATEGORY2, KEY_CATEGORY3
      3     from
      4             (
      5             select
      6                     a.*, rownum r
      7             from (
      8                SELECT /*+ FIRST_ROWS(20) */
      9                        JM.JVID,  NV.ISSUE_DTM, NV.KEY_CATEGORY_LABEL1 KEY_CATEGORY1, NV.KEY_CATEGORY_LABEL2 KEY_CATEGORY2,
     10                        NV.KEY_CATEGORY_LABEL3 KEY_CATEGORY3
     11                FROM
     12                        JV_MASTER JM,  NEWS_META_VIEW1 NV
     13                WHERE
     14                        JM.ACTIVE_FLG = 'Y'  AND
     15                        JM.JVID = NV.JVID
     16                Order by
     17                        ISSUE_DTM DESC
     18                ) a
     19                where
     20                        rownum <= :l_maxrow
     21        )
     22     where r >= :l_minrow
     23  /
    
    20 rows selected.
    
    Elapsed: 00:00:00.07
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3444472946
    
    -----------------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |                         |    20 |  6140 |    10   (0)| 00:00:01 |
    |*  1 |  VIEW                           |                         |    20 |  6140 |    10   (0)| 00:00:01 |
    |*  2 |   COUNT STOPKEY                 |                         |       |       |            |          |
    |   3 |    VIEW                         |                         |    20 |  5880 |    10   (0)| 00:00:01 |
    |   4 |     NESTED LOOPS                |                         |    20 |  3280 |    10   (0)| 00:00:01 |
    |*  5 |      TABLE ACCESS BY INDEX ROWID| NEWS_META               | 19306 |  2545K|     9   (0)| 00:00:01 |
    |   6 |       INDEX FULL SCAN DESCENDING| IDX_NEWS_META_ISSUE_DTM |    40 |       |     1   (0)| 00:00:01 |
    |*  7 |      TABLE ACCESS BY INDEX ROWID| JV_MASTER               |    20 |   580 |     1   (0)| 00:00:01 |
    |*  8 |       INDEX UNIQUE SCAN         | JV_MASTER_PK            |     1 |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("R">=TO_NUMBER(:L_MINROW))
       2 - filter(ROWNUM<=TO_NUMBER(:L_MAXROW))
       5 - filter(SYS_OP_NOEXPAND("NEWS_META"."SYS_NC00012$") IS NOT NULL AND
                  "NEWS_META"."CONTENT_TYPE_ID"=601)
       7 - filter("JM"."ACTIVE_FLG"='Y')
       8 - access("JM"."JVID"="NEWS_META"."JVID")
    
    
    Statistics
    ----------------------------------------------------------
             68  recursive calls
              0  db block gets
            239  consistent gets
              1  physical reads
              0  redo size
           2357  bytes sent via SQL*Net to client
            487  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             20  rows processed
    
    raj@cms> 
    Similarly, tried to create an index on the XML Column KEY_CATEGORY_LABEL, so that while sorting, this index is used to sort and retrieve the result set. But it doesn't seem to work.

    Thanks and Regards
    Raj
  • 11. Re: Pivoting a result set
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    Some food for thought: http://richardfoote.wordpress.com/2008/01/23/indexing-nulls-empty-spaces/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points