Hi Tom,
I would like to have a query which provides a result set as
follows.Can Inline views be used here?

SQL> select distinct job from emp e1,(select empno,ename from
emp e2...

Query output should be


            DEPTNO
            ------
 
            10                 20                     30   --- Deptno values
            
job                
---
ANALYST    ENAME   EMPNO     ENAME  EMPNO        ENAME EMPNO
            ....     ...     ....   ....         ....  .....
            .....    ...     ....   ....         ..... .....


SALESMAN   ENAME   EMPNO     ENAME  EMPNO        ENAME EMPNO
            ....     ...     ....   ....         ....  .....
            .....    ...     ....   ....         ..... .....




CLERK      ENAME   EMPNO     ENAME  EMPNO        ENAME EMPNO
            ....     ...     ....   ....         ....  .....
            .....    ...     ....   ....         ..... .....




MANAGER  ENAME   EMPNO     ENAME  EMPNO        ENAME EMPNO
            ....     ...     ....   ....         ....  .....
            .....    ...     ....   ....         ..... .....


PRESIDENT ENAME   EMPNO     ENAME  EMPNO        ENAME EMPNO
            ....     ...     ....   ....         ....  .....
            .....    ...     ....   ....         ..... .....




Is it possible to achieve this result set?
Please do reply.
Bye!




 

Hello Tom,
Thanks for your response.Yes.It is a matrix report.People asked me how would I achieve a matrix
report in plain SQL?.Hence I asked for your help.From your hints for the query,I have understood
only the part of the query(Query is below) and the remaining part "and full outer join each to each other.",I don't understand that part.How to query deptno -- 10,20,30 and perform a full join on them so that the deptnoNN appear as row headers?

Could you please help?
Please do reply.
Bye!



SQL> bre on job
SQL> select job,empno,ename,rn from( select e.*,row_number() over(partition by job order by job)
  2  as rn from emp e)
  3  /

JOB            EMPNO ENAME              RN
--------- ---------- ---------- ----------
ANALYST         7788 SCOTT               1
                7902 FORD                2
CLERK           7369 SMITH               1
                7876 ADAMS               2
                7934 MILLER              3
                7900 JAMES               4
MANAGER         7566 JONES               1
                7782 CLARK               2
                7698 BLAKE               3
PRESIDENT       7839 KING                1
SALESMAN        7499 ALLEN               1

JOB            EMPNO ENAME              RN
--------- ---------- ---------- ----------
SALESMAN        7654 MARTIN              2
                7844 TURNER              3
                7521 WARD                4

14 rows selected. 

Dear Tom,
Thanks for your reply.But when I run your query,I got errors as follows.

SQL> with dept_10 as (select job,ename,empno,row_number() over 
(partition by deptno order by ename) rn
                    from emp where deptno = 10),
 dept_20 as (select job,ename,empno,row_number() over
(partition by deptno order by ename) rn
                    from emp where deptno = 20),
 dept_30 as (select job,ename,empno,row_number() over
(partition by deptno order by ename) rn
                    from emp where deptno = 30)
    select coalesce(dept_10.job,dept_20.job,dept_30.job) as job,
           dept_10.ename as Ename,dept_10.empno as Empno,
           dept_20.ename as Ename,dept_20.empno as Empno,
           dept_30.ename as Ename,dept_30.empno as Empno
    from dept_10 full outer join dept_20 on (dept_10.job = dept_20.job and 
dept_10.rn = dept_20.rn) full outer join 
dept_30 on (coalesce(dept_10.job,dept_20.job) =
 dept_30.job and coalesce (dept_10.rn,dept_20.rn) = dept_30.rn)
/

ERROR at line 1:
ORA-00600: internal error code, arguments: [kkqwrm_noref: COLFDNF set], [], [],
[], [], [], [], []

I use Oracle 9i Release 2.What may be the reason for the error?
Please do reply.

 

Hi Tom,

We frequently need to use inline views in our project, and we have run into performance problems with some of our queries. Below is a simulation of what we face -

SQL> create table t1 as select * from user_tables;

Table created.

SQL> create table t2 as select * from user_tables;

Table created.

SQL> create index ik_t1_1 on t1(table_name);

Index created.

SQL> create index ik_t2_1 on t2(table_name);

Index created.

SQL> create table t_big as select * from user_objects;

Table created.

SQL> insert into t_big select * from user_objects
  2  ;

2902 rows created.

SQL> /

2902 rows created.

SQL> /

2902 rows created.

SQL> /

2902 rows created.

SQL> /

2902 rows created.

SQL> /

2902 rows created.

SQL> commit;

Commit complete.

SQL> create index ik_t_big_1 on t_big(object_name);

Index created.

SQL> analyze table t1 compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> analyze table t2 compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> analyze table t_big compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select table_name, num_rows, to_char(last_analyzed, 'DD-Mon-YYYY HH24:mi:ss')
  2  from user_tables
  3  where table_name in ('T1', 'T2', 'T_BIG');

TABLE_NAME                       NUM_ROWS TO_CHAR(LAST_ANALYZED,'DD-
------------------------------ ---------- --------------------------
T1                                    865 23-Sep-2005 16:16:15
T2                                    866 23-Sep-2005 16:16:19
T_BIG                               20314 23-Sep-2005 16:16:24


Now for a simple union all query (no inline view)

SQL> select table_name, tablespace_name
  2  from    t1
  3  where   table_name = 'A'
  4  union all
  5  select t2.table_name, t2.tablespace_name
  6  from    t1, t2
  7  where   t1.table_name = t2.table_name
  8  and     t2.table_name = 'B';

no rows selected

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=2 Bytes=79)
   1    0   UNION-ALL
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=32)
   3    2       INDEX (RANGE SCAN) OF 'IK_T1_1' (NON-UNIQUE) (Cost=1 Card=1)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=32)
   5    4       NESTED LOOPS (Cost=3 Card=1 Bytes=47)
   6    5         INDEX (RANGE SCAN) OF 'IK_T1_1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=15)
   7    5         INDEX (RANGE SCAN) OF 'IK_T2_1' (NON-UNIQUE) (Cost=1 Card=1)

Extending the query above to make it an inline view, and joining T_BIG

SQL> select b.object_name, b.object_id, t.table_name
  2  from  (
  3  select table_name, tablespace_name
  4  from    t1
  5  where   table_name = 'A'
  6  union all
  7  select t2.table_name, t2.tablespace_name
  8  from    t1, t2
  9  where   t1.table_name = t2.table_name
 10  and     t2.table_name = 'B'
 11  ) t, t_big b
 12  where t.table_name = b.object_name;

no rows selected

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=15 Bytes=675)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_BIG' (Cost=8 Card=7 Bytes=210)
   2    1     NESTED LOOPS (Cost=18 Card=15 Bytes=675)
   3    2       VIEW (Cost=2 Card=2 Bytes=30)
   4    3         UNION-ALL
   5    4           INDEX (RANGE SCAN) OF 'IK_T1_1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=15)
   6    4           NESTED LOOPS (Cost=2 Card=1 Bytes=30)
   7    6             INDEX (RANGE SCAN) OF 'IK_T1_1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=15)
   8    6             INDEX (RANGE SCAN) OF 'IK_T2_1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=15)
   9    2       INDEX (RANGE SCAN) OF 'IK_T_BIG_1' (NON-UNIQUE) (Cost=1 Card=7)

is fine - uses the indexes that I expect to be used.

Now, just adding this result set as a sub query, the explain plan is no longer the same.

SQL> select * from
  2  (
  3  select b.object_name, b.object_id, t.table_name
  4  from  (
  5  select table_name, tablespace_name
  6  from    t1
  7  where   table_name = 'A'
  8  union all
  9  select t2.table_name, t2.tablespace_name
 10  from    t1, t2
 11  where   t1.table_name = t2.table_name
 12  and     t2.table_name = 'B'
 13  ) t, t_big b
 14  where t.table_name = b.object_name
 15  );

no rows selected

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=42 Card=12815 Bytes=576675)
   1    0   HASH JOIN (Cost=42 Card=12815 Bytes=576675)
   2    1     VIEW (Cost=7 Card=1731 Bytes=25965)
   3    2       UNION-ALL
   4    3         INDEX (RANGE SCAN) OF 'IK_T1_1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=15)
   5    3         NESTED LOOPS (Cost=2 Card=1 Bytes=30)
   6    5           INDEX (RANGE SCAN) OF 'IK_T1_1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=15)
   7    5           INDEX (RANGE SCAN) OF 'IK_T2_1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=15)
   8    1     TABLE ACCESS (FULL) OF 'T_BIG' (Cost=32 Card=20314 Bytes=609420)

It does a FTS of T_BIG

Further, I observe, this only happens when there is a UNION ALL query in the inline view

Commenting out the second sub query -

SQL> select * from
  2  (
  3  select b.object_name, b.object_id, t.table_name
  4  from  (
  5  select table_name, tablespace_name
  6  from    t1
  7  where   table_name = 'A'
  8  -- union all
  9  -- select t2.table_name, t2.tablespace_name
 10  -- from    t1, t2
 11  -- where   t1.table_name = t2.table_name
 12  -- and     t2.table_name = 'B'
 13  ) t, t_big b
 14  where t.table_name = b.object_name
 15  );

no rows selected

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=45)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_BIG' (Cost=9 Card=1 Bytes=30)
   2    1     NESTED LOOPS (Cost=10 Card=1 Bytes=45)
   3    2       INDEX (RANGE SCAN) OF 'IK_T1_1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=15)
   4    2       INDEX (RANGE SCAN) OF 'IK_T_BIG_1' (NON-UNIQUE) (Cost=1 Card=1)


Using a UNION instead of UNION ALL

SQL> select * from
  2  (
  3  select b.object_name, b.object_id, t.table_name
  4  from  (
  5  select table_name, tablespace_name
  6  from    t1
  7  where   table_name = 'A'
  8  union
  9  select t2.table_name, t2.tablespace_name
 10  from    t1, t2
 11  where   t1.table_name = t2.table_name
 12  and     t2.table_name = 'B'
 13  ) t, t_big b
 14  where t.table_name = b.object_name
 15  );

no rows selected

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=16 Bytes=752)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_BIG' (Cost=9 Card=8 Bytes=240)
   2    1     NESTED LOOPS (Cost=26 Card=16 Bytes=752)
   3    2       VIEW (Cost=9 Card=2 Bytes=34)
   4    3         SORT (UNIQUE) (Cost=9 Card=2 Bytes=79)
   5    4           UNION-ALL
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=32)
   7    6               INDEX (RANGE SCAN) OF 'IK_T1_1' (NON-UNIQUE) (Cost=1 Card=1)
   8    5             TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=32)
   9    8               NESTED LOOPS (Cost=3 Card=1 Bytes=47)
  10    9                 INDEX (RANGE SCAN) OF 'IK_T1_1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=15)
  11    9                 INDEX (RANGE SCAN) OF 'IK_T2_1' (NON-UNIQUE) (Cost=1 Card=1)
  12    2       INDEX (RANGE SCAN) OF 'IK_T_BIG_1' (NON-UNIQUE) (Cost=1 Card=8)

So, the problem arises when I use an inline view with UNION ALL and then provide an outer query over the result set. 

In my actual query, the inline view is a set of 5 queries with a UNION ALL (they all return different results), and the outer most query does GROUP BY ROLLUP. Also the T_BIG is actually a transaction table with a lot of rows.

Can you please help me understand this behaviour. Is there anything wrong in the query itself? Is there a better way to write the query.

Thanks. 

CREATE TABLE "JOB_MASTER" 
  ( 
    "JOB_IDR"    NUMBER(15,0), 
    "JOB_NO"     VARCHAR2(25 BYTE) NOT NULL ENABLE, 
    "OFFICE_COD" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
    "JOB_DATE" DATE NOT NULL ENABLE, 
    "CLIENT_COD"         NUMBER(10,0) NOT NULL ENABLE, 
    "COMPANY_COD"        NUMBER(10,0) NOT NULL ENABLE, 
    "BRAND_COD"          NUMBER(10,0), 
    "JOB_TYPE_COD"       VARCHAR2(25 BYTE), 
    "CURRENCY_COD"       VARCHAR2(10 BYTE) NOT NULL ENABLE, 
    "CLIENT_APPROVED"    VARCHAR2(1 BYTE), 
    "CLIENT_PO_RECEIVED" VARCHAR2(1 BYTE), 
    "CLIENT_PO_NO"       VARCHAR2(25 BYTE), 
    "CLIENT_PO_DATE" DATE, 
    "CLOSED_BY" NUMBER(10,0), 
    "CLOSED_ON" DATE, 
    "STATUS"    VARCHAR2(10 BYTE) NOT NULL ENABLE, 
    "CR_STATUS" VARCHAR2(10 BYTE) 
  ) 
  
CREATE TABLE "JOB_STATUS" 
  ( 
    "STATUS_IDR" NUMBER(20,0) NOT NULL ENABLE, 
    "JOB_COD"    NUMBER(15,0) NOT NULL ENABLE, 
    "STATUS"     VARCHAR2(50 BYTE) NOT NULL ENABLE, 
    "STATUS_DATE" DATE NOT NULL ENABLE, 
    "REMARKS" VARCHAR2(2000 BYTE) 
  )   

SELECT JOB_IDR, 
    JOB_NO, 
    OFFICE_COD, 
    JOB_DATE, 
    CLIENT_COD, 
    COMPANY_COD, 
    BRAND_COD, 
    JOB_TYPE_COD, 
    CURRENCY_COD, 
    MAX(OPENED_ON) OPENED_ON, 
    MAX(APPROVED_ON) APPROVED_ON, 
    MAX(APPROVED_REMARKS) APPROVED_REMARKS , 
    MAX(ASSIGNED_ON) ASSIGNED_ON , 
    MAX(ASSIGNED_REMARKS) ASSIGNED_REMARKS , 
    MAX(DELIVERED_ON) DELIVERED_ON , 
    MAX(DELIVER_REMARKS) DELIVER_REMARKS, 
    MAX(CLOSED_ON) CLOSED_ON , 
    MAX(CLOSED_REMARKS) CLOSED_REMARKS 
  FROM 
    (SELECT JOB_MASTER.JOB_IDR, 
      JOB_MASTER.JOB_NO, 
      JOB_MASTER.OFFICE_COD, 
      JOB_MASTER.JOB_DATE, 
      JOB_MASTER.CLIENT_COD, 
      JOB_MASTER.COMPANY_COD, 
      JOB_MASTER.BRAND_COD, 
      JOB_MASTER.JOB_TYPE_COD, 
      JOB_MASTER.CURRENCY_COD, 
      DECODE(JOB_STATUS.status,'OPEN',STATUS_DATE,NULL) OPENED_ON, 
      DECODE(JOB_STATUS.status,'APPROVED',STATUS_DATE,NULL) APPROVED_ON, 
      DECODE(JOB_STATUS.status,'APPROVED',REMARKS,NULL) APPROVED_REMARKS, 
      DECODE(JOB_STATUS.status,'ASSIGNED',STATUS_DATE,NULL) ASSIGNED_ON, 
      DECODE(JOB_STATUS.status,'ASSIGNED',REMARKS,NULL) ASSIGNED_REMARKS, 
      DECODE(JOB_STATUS.status,'DELIVERED',STATUS_DATE,NULL) DELIVERED_ON, 
      DECODE(JOB_STATUS.status,'DELIVERED',REMARKS,NULL) DELIVER_REMARKS, 
      DECODE(JOB_STATUS.status,'CLOSED',STATUS_DATE,NULL) CLOSED_ON, 
      DECODE(JOB_STATUS.status,'CLOSED',REMARKS,NULL) CLOSED_REMARKS 
    FROM JOB_MASTER JOB_MASTER 
    LEFT OUTER JOIN JOB_STATUS 
    ON JOB_MASTER.job_idr=JOB_STATUS.JOB_COD 
    ) 
  GROUP BY JOB_IDR, 
    JOB_NO, 
    OFFICE_COD, 
    JOB_DATE, 
    CLIENT_COD, 
    COMPANY_COD, 
    BRAND_COD, 
    JOB_TYPE_COD, 
    CURRENCY_COD 

  SELECT JOB_IDR, 
    JOB_NO, 
    OFFICE_COD, 
    JOB_DATE, 
    CLIENT_COD, 
    COMPANY_COD, 
    BRAND_COD, 
    JOB_TYPE_COD, 
    CURRENCY_COD, 
    (SELECT STATUS_DATE 
    FROM JOB_STATUS 
    WHERE JOB_COD = J.JOB_IDR 
    AND STATUS    = 'OPEN' 
    ) OPENED_ON, 
    (SELECT STATUS_DATE 
    FROM JOB_STATUS 
    WHERE JOB_COD = J.JOB_IDR 
    AND STATUS    = 'APPROVED' 
    ) APPROVED_ON, 
    (SELECT REMARKS 
    FROM JOB_STATUS 
    WHERE JOB_COD = J.JOB_IDR 
    AND STATUS    = 'APPROVED' 
    ) APPROVED_REMARKS, 
    (SELECT STATUS_DATE 
    FROM JOB_STATUS 
    WHERE JOB_COD = J.JOB_IDR 
    AND STATUS    = 'ASSIGNED' 
    ) ASSIGNED_ON, 
    (SELECT REMARKS 
    FROM JOB_STATUS 
    WHERE JOB_COD = J.JOB_IDR 
    AND STATUS    = 'ASSIGNED' 
    ) ASSIGNED_REMARKS, 
    (SELECT STATUS_DATE 
    FROM JOB_STATUS 
    WHERE JOB_COD = J.JOB_IDR 
    AND STATUS    = 'DELIVERED' 
    ) DELIVERED_ON, 
     (SELECT REMARKS 
    FROM JOB_STATUS 
    WHERE JOB_COD = J.JOB_IDR 
    AND STATUS    = 'DELIVERED' 
    ) DELIVER_REMARKS, 
    (SELECT STATUS_DATE 
    FROM JOB_STATUS 
    WHERE JOB_COD = J.JOB_IDR 
    AND STATUS    = 'CLOSED' 
    ) CLOSED_ON, 
    (SELECT REMARKS 
    FROM JOB_STATUS 
    WHERE JOB_COD = J.JOB_IDR 
    AND STATUS    = 'CLOSED' 
    ) CLOSED_REMARKS 
  FROM JOB_MASTER J ;