SQL – Reformatting to Improve Performance 11

December 4, 2009

(Back to the Previous Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/1194b893e71e2e15

After analyzing I have found that this query is taking a long time to
execute

    (
       select count(*) as cntFixed
         from mdl_parameter_tree u
         where u.prmid not in
        (
          select t.prmid
          from mdl_parameter_tree t
         where t.rootnode in
          (
           select b.rootnode
           from subjective_prm_trans a, mdl_parameter_tree b
           where
                a.mdlid = b.mdlid and a.prmid = b.prmid and
                a.endnodeflag = 'N' and a.value between 0.0001 and 1
and
                a.prmid not in (select r.prmid from mdl_parameter_tree
r where trim(lower(r.prmname)) = 'project risk' and r.mdlid=
&var_mdlid ) and
                a.borrid= &var_borrId and a.mdlid= &var_mdlid and
a.user_id= &var_user_id
               ) and t.endnodeflag = 'E' and parametertype = 'S' and
mdlid= &var_mdlid
)         and
        ( u.endnodeflag ='E' and
         u.parametertype = 'S' and
         u.mdlid= &var_mdlid

   )
)
This query is taking 58 seconds to execute.
Below is the explain plan of the above query

--------------------------------------------------------------------------­-------
| Id  | Operation                |  Name                 | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------­-------
|   0 | SELECT STATEMENT         |                       |     1 |   284 |   349 |
|   1 |  SORT AGGREGATE          |                       |     1 |   284 |       |
|   2 |   FILTER                 |                       |       |       |       |
|   3 |    TABLE ACCESS FULL     | MDL_PARAMETER_TREE    |     1 |   284 |     3 |
|   4 |    FILTER                |                       |       |       |       |
|   5 |     NESTED LOOPS         |                       |     1 |   636 |   343 |
|   6 |      MERGE JOIN CARTESIAN|                       |     1 |   481 |   340 |
|   7 |       TABLE ACCESS FULL  | SUBJECTIVE_PRM_TRANS  |     1 |    68 |   337 |
|   8 |       BUFFER SORT        |                       |     1 |   413 |     3 |
|   9 |        TABLE ACCESS FULL | MDL_PARAMETER_TREE    |     1 |   413 |     3 |
|  10 |      TABLE ACCESS FULL   | MDL_PARAMETER_TREE    |    12 |  1860 |     3 |
|  11 |     TABLE ACCESS FULL    | MDL_PARAMETER_TREE    |     1 |   155 |     3 |
--------------------------------------------------------------------------­-------

Note: cpu costing is off, 'PLAN_TABLE' is old version

Please assist me sir to tune the below query

—-
It is a good idea to simplify the SQL statement, if possible.  The following may not be 100% accurate, so test the results.

Your original SQL statement, slightly reformatted:

select
  count(*) as cntFixed
from
  mdl_parameter_tree u
where
  u.prmid not in (
    select
      t.prmid
    from
      mdl_parameter_tree t
    where
      t.rootnode in (
        select
          b.rootnode
        from
          subjective_prm_trans a,
          mdl_parameter_tree b
        where
          a.mdlid = b.mdlid
          and a.prmid = b.prmid
          and a.endnodeflag = 'N'
          and a.value between 0.0001 and 1
          and a.prmid not in (
            select
              r.prmid
            from
              mdl_parameter_tree r
           where
             trim(lower(r.prmname)) = 'project risk'
             and r.mdlid= &var_mdlid )
          and a.borrid= &var_borrId
          and a.mdlid= &var_mdlid
          and a.user_id= &var_user_id)
      and t.endnodeflag = 'E'
      and parametertype = 'S'
      and mdlid= &var_mdlid)
  and u.endnodeflag ='E'
  and u.parametertype = 'S'
  and u.mdlid= &var_mdlid;

Now, removing one of the subqueries, transforming it to <> and OR:

select
  count(*) as cntFixed
from
  mdl_parameter_tree u
where
  u.prmid not in (
    select
      t.prmid
    from
      mdl_parameter_tree t
    where
      t.rootnode in (
        select
          b.rootnode
        from
          subjective_prm_trans a,
          mdl_parameter_tree b
        where
          a.mdlid = b.mdlid
          and a.prmid = b.prmid
          and a.endnodeflag = 'N'
          and a.value between 0.0001 and 1
          and (trim(lower(r.prmname)) <> 'project risk' or r.mdlid <> &var_mdlid)
          and a.borrid= &var_borrId
          and a.mdlid= &var_mdlid
          and a.user_id= &var_user_id)
      and t.endnodeflag = 'E'
      and parametertype = 'S'
      and mdlid= &var_mdlid)
  and u.endnodeflag ='E'
  and u.parametertype = 'S'
  and u.mdlid= &var_mdlid;

Let’s look at what this SQL statement is attempting to accomplish: Count of rows in mdl_parameter_tree that do not have a rootnode in the inner-most subquery.
Let’s rewrite per the specification to use an inline view, rather than a subquery:

select
  count(*) as cntFixed
from
  mdl_parameter_tree u,
  (select
    b.prmid,
    b.rootnode
  from
    subjective_prm_trans a,
    mdl_parameter_tree b
  where
    a.mdlid = b.mdlid
    and a.prmid = b.prmid
    and a.endnodeflag = 'N'
    and a.value between 0.0001 and 1
    and (trim(lower(b.prmname)) <> 'project risk' or b.mdlid <> &var_mdlid)
    and a.borrid= &var_borrId
    and a.mdlid= &var_mdlid
    and a.user_id= &var_user_id) n
where
  u.prmid=n.prmid(+)
  and n.prmid is null;

Once again, verify that this is showing the same number of rows as the original.

If you are still experiencing a problem, create a 10046 trace at level 8 for the query.  On this page I show how to enable a 10046 trace at level 12, which will work just as well as a level 8 trace for you:
http://forums.oracle.com/forums/thread.jspa?messageID=2384639&

This link shows how to decode the contents of a 10046 trace file:
http://forums.oracle.com/forums/thread.jspa?threadID=661124

SQL – Reformatting to Improve Performance 10

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/8176f2571da1da3d

I wanted to tune the below query.Any help would be of great help. Below is the query

--Find Year to Generate Risk Score Starts here
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as year
 from statement
 where borrid= &var_borrId and user_id= &var_user_id and coaid = &var_mdlid
 and to_char(stmtdt, 'yyyy') in
 (
   select os.year from
   (
     select borrid, year, count(*) as cntActuals
     from subjective_prm_trans
     where borrid= &var_borrId and user_id= &var_user_id and mdlid = &var_mdlid and endnodeflag = 'E'
     group by year, borrid
   ) os,
   (
     select j.borrid, j.year, j.cntVariable + k.cntFixed as cntMdlTotals
     from
     (
       select a.borrid, a.year , count(*) as cntVariable
       from subjective_prm_trans a, mdl_Parameter_Tree m
       where
       a.prmid = m.parentid and a.mdlid = m.mdlid and
       a.endnodeflag = 'N' and a.value between 0.0001 and 1 and
       a.prmid not in (select r.prmid from mdl_parameter_tree r
    where
       trim(lower(r.prmname)) = 'project risk' and r.mdlid= &var_mdlid ) and
       a.borrid= &var_borrId and a.mdlid= &var_mdlid and a.user_id= &var_user_id
       group by a.borrid , a.year
     ) j,
     (
       select count(*) as cntFixed
         from mdl_parameter_tree u
         where u.prmid not in
        (
          select t.prmid
          from mdl_parameter_tree t
          where t.rootnode in
          (
           select b.rootnode
           from subjective_prm_trans a, mdl_parameter_tree b
           where
                a.mdlid = b.mdlid and a.prmid = b.prmid and
                a.endnodeflag = 'N' and a.value between 0.0001 and 1
                and a.prmid not in (select r.prmid from mdl_parameter_tree r
                  where trim(lower(r.prmname)) = 'project risk' and r.mdlid= &var_mdlid ) and
                a.borrid= &var_borrId and a.mdlid= &var_mdlid and
                a.user_id= &var_user_id ) and
          t.endnodeflag = 'E' and parametertype = 'S' and mdlid= &var_mdlid
        ) and
         u.endnodeflag ='E' and
         u.parametertype = 'S' and
         u.mdlid= &var_mdlid
     ) k
   ) om
   where
   os.borrid = om.borrid and
   os.year = om.year and
   os.cntActuals  = om.cntMdlTotals
 )
 order by year desc

Well below is the explain plan:

Execution Plan
----------------------------------------------------------
0     SELECT STATEMENT Optimizer=ALL_ROWS (Cost=28 Card=1 Bytes=61 )
1 0    SORT (ORDER BY) (Cost=28 Card=1 Bytes=61)
2 1     HASH JOIN (SEMI) (Cost=26 Card=1 Bytes=61)
3 2      TABLE ACCESS (FULL) OF 'STATEMENT' (Cost=2 Card=1 Bytes=48)
4 2      VIEW OF 'VW_NSO_1' (Cost=23 Card=1 Bytes=13)
5 4       NESTED LOOPS (Cost=23 Card=1 Bytes=91)
6 5        HASH JOIN (Cost=13 Card=1 Bytes=78)
7 6         VIEW (Cost=4 Card=1 Bytes=39)
8 7          SORT (GROUP BY) (Cost=4 Card=1 Bytes=55)
9 8           TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2 Card=1 Bytes=55)
10 6        VIEW (Cost=9 Card=1 Bytes=39)
11 10        SORT (GROUP BY) (Cost=7 Card=1 Bytes=107)
12 11         FILTER
13 12          HASH JOIN (Cost=5 Card=1 Bytes=107)
14 13           TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2 Card=1 Bytes=81)
15 13           TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=142 Bytes=3692)
16 12          TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=1 Bytes=155)
17 5       VIEW
18 17       SORT (AGGREGATE)
19 18        FILTER
20 19         TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=1 Bytes=284)
21 19         FILTER
22 21          NESTED LOOPS (Cost=6 Card=1 Bytes=636)
23 22           MERGE JOIN (CARTESIAN) (Cost=4 Card=1 By tes=481)
24 23            TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2 Card=1 Bytes=68)
25 23            BUFFER (SORT) (Cost=2 Card=1 Bytes=413)
26 25             TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=1 Bytes=413)
27 22           TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=18 Bytes=2790)
28 21          TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=1 Bytes=155)

 

Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
387229 consistent gets
306954 physical reads
0 redo size
432 bytes sent via SQL*Net to client
584 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
114 sorts (memory)
0 sorts (disk)
1 rows processed

Any help would help to resolve the issue

———-
David has already mentioned that your use of “and to_char(stmtdt, ‘yyyy’)” makes it impossible to use a regular index on the stmtdt column, if one exists.

Looking at the plan, you may notice that there is not a single index access, the cardinality numbers are low (possibily indicating that it has been a while since statistics were gathered), there is a merge join Cartesian which can kill performance with large numbers of rows, the formatting without spaces makes it impossible to see how the data was retrieved in order, and it appears that you are using a version of Oracle prior to 10g R1 based on the presence of “SORT (GROUP BY)”. That may be an indication that Oracle is converting or is not converting some/all of the NOT IN subqueries as necessary.

Looking at the query, slightly reformatted:

SELECT
  TO_CHAR(STMTDT, 'YYYY') AS ID, TO_CHAR(STMTDT, 'YYYY') AS YEAR
FROM
  STATEMENT
WHERE
  BORRID= &VAR_BORRID
  AND USER_ID= &VAR_USER_ID
  AND COAID = &VAR_MDLID
  AND TO_CHAR(STMTDT, 'YYYY') IN
  (
   SELECT
     OS.YEAR
   FROM
   (
     SELECT
       BORRID,
       YEAR,
       COUNT(*) AS CNTACTUALS
     FROM
       SUBJECTIVE_PRM_TRANS
     WHERE
       BORRID= &VAR_BORRID
       AND USER_ID= &VAR_USER_ID
       AND MDLID = &VAR_MDLID
       AND ENDNODEFLAG = 'E'
     GROUP BY
       YEAR,
       BORRID) OS,
   (
     SELECT
       J.BORRID,
       J.YEAR,
       J.CNTVARIABLE + K.CNTFIXED AS CNTMDLTOTALS
     FROM
     (
       SELECT
         A.BORRID,
         A.YEAR ,
         COUNT(*) AS CNTVARIABLE
       FROM
         SUBJECTIVE_PRM_TRANS A,
         MDL_PARAMETER_TREE M
       WHERE
         A.PRMID = M.PARENTID
         AND A.MDLID = M.MDLID
         AND A.ENDNODEFLAG = 'N'
         AND A.VALUE BETWEEN 0.0001 AND 1
         AND A.PRMID NOT IN
           (SELECT
             R.PRMID
           FROM
             MDL_PARAMETER_TREE R
           WHERE
             TRIM(LOWER(R.PRMNAME)) = 'PROJECT RISK'
             AND R.MDLID= &VAR_MDLID )
         AND A.BORRID= &VAR_BORRID
         AND A.MDLID= &VAR_MDLID
         AND A.USER_ID= &VAR_USER_ID
       GROUP BY
         A.BORRID ,
         A.YEAR ) J,
     (
       SELECT
         COUNT(*) AS CNTFIXED
       FROM
         MDL_PARAMETER_TREE U
       WHERE
         U.PRMID NOT IN
           (SELECT
             T.PRMID
           FROM
             MDL_PARAMETER_TREE T
           WHERE
             T.ROOTNODE IN
               (SELECT
                 B.ROOTNODE
               FROM
                 SUBJECTIVE_PRM_TRANS A,
                 MDL_PARAMETER_TREE B
               WHERE
                 A.MDLID = B.MDLID
                 AND A.PRMID = B.PRMID
                 AND A.ENDNODEFLAG = 'N' AND A.VALUE BETWEEN 0.0001 AND 1
                 AND A.PRMID NOT IN
                   (SELECT
                     R.PRMID
                   FROM
                     MDL_PARAMETER_TREE R
                   WHERE
                     TRIM(LOWER(R.PRMNAME)) = 'PROJECT RISK'
                    AND R.MDLID= &VAR_MDLID )
             AND A.BORRID= &VAR_BORRID
             AND A.MDLID= &VAR_MDLID
             AND A.USER_ID= &VAR_USER_ID
          )
         AND T.ENDNODEFLAG = 'E'
         AND PARAMETERTYPE = 'S'
         AND MDLID= &VAR_MDLID
        )
       AND U.ENDNODEFLAG ='E'
       AND U.PARAMETERTYPE = 'S'
       AND U.MDLID= &VAR_MDLID
     ) K
   ) OM
   WHERE
     OS.BORRID = OM.BORRID
     AND OS.YEAR = OM.YEAR
     AND OS.CNTACTUALS  = OM.CNTMDLTOTALS
 )
ORDER BY
  YEAR DESC

You seem to be repeating the same inline view SQL in a couple places, you seem to be performing a COUNT in two inline views so that you can perform “AND OS.CNTACTUALS  = OM.CNTMDLTOTALS” – there might be a better way.  Where you have “SELECT OS.YEAR” – you may want to convert this to 2 date columns with Jan 1 of the year in the first date column and Dec 31 of the year in the second – doing that would allow you to change “TO_CHAR(STMTDT, ‘YYYY’) IN” into a statement that does not require TO_CHAR.

It will take someone familiar with the data to tell you if some of the extra work in the inline views may be eliminated.

A follow-up email to the OP:
There are some basic problems that I found in the query and the explain plan that should be fixed before trying David’s suggestions for a materialed view.  I suggested the following:
* There are no indexes used in the query, which may be part or most of the performance problem – verify that indexes exist and statistics are gathered for those indexes.
* The cardinality numbers are low (possibily indicating that it has been a while since statistics were gathered) – make certain that statistics are gathered with DBMS_STATS.
* There is a merge join Cartesian in the plan which can severely hurt performance with large numbers of rows – you may need a hint to fix this.
* The formatting of the plan without spaces makes it impossible to see how the data was retrieved by the plan.
* You seem to be using a version of Oracle prior to 10g R1, and those versions may not convert/change/transform some/all of the NOT IN subqueries as necessary for best performance.
* You seem to be repeating the same inline view SQL in a couple places.
* You seem to be performing a COUNT in two inline views so that you can perform “AND OS.CNTACTUALS  = OM.CNTMDLTOTALS” – there might be a better way.
* Where you have “SELECT OS.YEAR” – you may want to convert this to 2 date columns with Jan 1 of the year in the first date column and Dec 31 of the year in the second – doing that would allow you to change “TO_CHAR(STMTDT, ‘YYYY’) IN” into a statement that does not require TO_CHAR.
* It will take someone familiar with the data to tell you if some of the extra work in the inline views may be eliminated.

SQL – Reformatting to Improve Performance 9

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

A couple months ago  an interesting/huge/confusing SQL statement appeared on the OTN forums that was experiencing performance problems when running on Oracle 8i.  The SQL statement contained several very complex inline view, with inline views nested inside other inline views:

http://forums.oracle.com/forums/thread.jspa?messageID=3802738

I offered the following suggestions to the person:

That is a long SQL statement. Splitting the SQL statement up into multiple views likely will not help performance, but could hurt performance.

A couple comments about the SQL statement:
* “SELECT DISTINCT” repeated in many places, including in an inline view using a GROUP BY clause. Are all of the DISTINCT clauses necessary?

* ORDER BY is frequently used in the inline views: “order by group_name” in the T11 inline view, “order by groupname” in the T6 inline view, “order by groupname” in the T3 inline view, “order by linecode” in the T5 inline view. Remove those ORDER BY clauses.

* “UNION” is used in T3 inline view – is it possible to replace that with a “UNION ALL”?

* IN clauses may be inefficient in Oracle 8i, consider switching to an EXISTS clause or transforming the IN clause to an inline view.

* Odd LIKE syntax in several places: where “groupname like ‘%’ and hub like ‘%'”, “lr.groupname like ‘%'” in T1 inline view, “lr.groupname like ‘%'” in T2 inline view.

* The table autoloadflow_lineresults is accessed many times – is that a large table? Is it possible to consolidate the queries accessing that table?

* The WHERE clauses seem to limit the usefulness of any indexes which may exist.

* It appears that you want the results from the T6 inline view (which join the T1, T2, T3, T4, and T5 inline views) to drive into the T7, T8, T9, T10, and T11 inline views – does the execution plan show that is happening?

SQL – Reformatting to Improve Performance 8

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/deb2e3f18be836c0

I am using Oracle 9i.
I have some reports developed in Reports 6i which run very slow.
When I run the query in TOAD, it’s taking too much time for displaying
result.
Can anybody suggest me how to tune this query ?

SELECT DISTINCT c.AccName, SUBSTR (a.VouNo, 3) VouNo,
                a.VouDate, a.TranType, b.ChequeNo,
                b.ChequeDate,
                DECODE (BalType, 'Debit', Amount, 0) Debit,
                DECODE (BalType, 'Credit', Amount, 0) Credit,
                d.DocNo, d.DocDate, a.Remark
           FROM TranDetails a,
                ChequeDetails b,
                AccMaster c,
                TranDocDetails d
          WHERE c.AccCode <> :p_BankAccCode
            AND a.VouDate BETWEEN :st_date AND :end_date
            AND a.CompanyNo = c.CompanyNo
            AND a.CompanyNo = b.CompanyNo(+)
            AND a.VouNo = b.VouNo(+)
            AND a.VouDate = b.VouDate(+)
            AND a.nu_serial_no = b.nu_serial_no(+)
            AND a.nu_serial_no = d.nu_serial_no(+)
            AND a.AccCode = c.AccCode
            AND a.TranType IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
            AND NVL (a.SecFlag, 'N') = 'Y'
            AND d.VouNo(+) = a.VouNo
            AND d.VouDate(+) = a.VouDate
            AND c.CompanyNo = :p_CompanyNo
            AND d.CompanyNo(+) = :p_CompanyNo
            AND    a.CompanyNo
                || a.VouNo
                || TO_CHAR (a.VouDate, 'dd-mm-yyyy') IN (
                   SELECT    d.CompanyNo
                          || d.VouNo
                          || TO_CHAR (d.VouDate, 'dd-mm-yyyy')
                     FROM TranDetails d
                    WHERE d.AccCode = :p_BankAccCode
                      AND NVL (d.SecFlag, 'N') = 'Y'
                      AND d.CompanyNo = :p_CompanyNo)
       ORDER BY 3, 2

The Oracle version number (for example 9.2.0.4) is important, as it determines what types of transformations Oracle may use to help improve the query performance.  First, a slight change in the formatting to make it easier for me to read (you will need to fix the DECODE statements):

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND TD.COMPANYNO || TD.VOUNO || TO_CHAR (TD.VOUDATE, 'DD-MM-YYYY') IN
    (SELECT
      TD2.COMPANYNO || TD2.VOUNO || TO_CHAR (TD2.VOUDATE, 'DD-MM-YYYY')
    FROM
      TRANDETAILS TD2
    WHERE
      TD2.ACCCODE = :V_BANKACCCODE
      AND NVL (TD2.SECFLAG, 'N') = 'Y'
      AND TD2.COMPANYNO = :V_COMPANYNO)
ORDER BY
  3,
  2;

In the above, you are concatenating three columns into a single value, and then trying to determine if the same concatenated value exists in a table.  A DBMS_XPLAN would probably show many, many full table scans of the TRANSDETAILS table.

If we just list the three columns, and use TRUNC rather than TO_CHAR, we may be able to take advantage of an index on those columns, and avoid the CPU overhead of a data type conversion of a DATE column to a character data type.  You could use an EXISTS syntax instead, which might be more efficient (not shown):

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND (TD.COMPANYNO,TD.VOUNO,TRUNC(TD.VOUDATE)) IN
    (SELECT
      TD2.COMPANYNO,
      TD2.VOUNO,
      TRUNC(TD2.VOUDATE)
    FROM
      TRANDETAILS TD2
    WHERE
      TD2.ACCCODE = :V_BANKACCCODE
      AND NVL (TD2.SECFLAG, 'N') = 'Y'
      AND TD2.COMPANYNO = :V_COMPANYNO)
ORDER BY
  3,
  2;

Let’s transform the subquery into an inline view, which will sometimes help improve performance (usually a very noticeable on Oracle 8i).  Such a transformation may be performed by Oracle automatically:

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD,
  (SELECT DISTINCT
    TD2.COMPANYNO,
    TD2.VOUNO,
    TRUNC(TD2.VOUDATE) VOUDATE
  FROM
    TRANDETAILS TD2
  WHERE
    TD2.ACCCODE = :V_BANKACCCODE
    AND NVL (TD2.SECFLAG, 'N') = 'Y'
    AND TD2.COMPANYNO = :V_COMPANYNO) TD2
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND TD.COMPANYNO=TD2.COMPANYNO
  AND TD.VOUNO=TD2.VOUNO
  AND TRUNC(TD.VOUDATE)=TD2.VOUDATE
ORDER BY
  3,
  2;

Is it really necessary to reference the TRANDETAILS table twice?  Will the following work?:

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND TD.ACCCODE = :V_BANKACCCODE
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TD.COMPANYNO = :V_COMPANYNO
ORDER BY
  3,
  2;

Why do you need to use the DISTINCT clause – is that a sign that you do not have sufficient joins between the tables?  It might be helpful to post a DBMS_XPLAN for the query.

SQL – Reformatting to Improve Performance 7

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/7780f1fd94c03ced

I have been challenged to optimize a stored procedure on Oracle 9i that will return a ref cursor with the counts of each status for each batch from the tables defined below.  I started with a View to join the tables together on the OrderNo field.  Then I wrote a query in the stored procdure that grouped the Batch values together, and then did a count for each Status value in each Batch like this:

SELECT Batch,
COUNT(Batch) Total,
(SELECT  COUNT(Batch)
        FROM MYVIEW
        WHERE Status = 1
                AND Batch = V1.Batch
(SELECT  COUNT(Batch)
        FROM MYVIEW
        WHERE Status = 2
                AND Batch = V1.Batch
(SELECT  COUNT(Batch)
        FROM MYVIEW
        WHERE Status = 3
                AND Batch = V1.Batch
FROM MYVIEW V1
WHERE Status < 4
        GROUP BY Batch;

With 42 unique Batch values and 26,000 OrderNo values my dev server takes over 5 seconds.  I built the query without the view and added the joins in the query and took the same amount of time.  I can’t change the table structures but the view is wide open. This is a conversion project from MS SQL and this same data returns from MS SQL in 0.09 seconds.  I hope Oracle can beat MS

Suporting Data and table structures.

TableOne Definition:

 
Batch - VarChar
OrderNo - VarChar
Various other Columns of Data...

TableTwo Definition:

OrderNo - VarChar
Status - Number
Various other Columns of Data...

View Definition:

Select o.Batch, o.OrderNo, t.Status, ...
FROM TableOne o Left Outer Join TableTwo t
                on o.OrderNo = t.OrderNo;

Sample Data TableOne:

ABC1    123     ...
ABC1  234       ...
ABC1    345     ...
ABC1  456       ...
ABC2    567     ...
ABC2  678       ...
ABC2    789     ...
ABC2  321       ...
ABC3    432     ...
ABC4  543       ...

Sample Data TableTwo:

123     1       ...
234     1       ...
345     2       ...
456     3       ...
567     2       ...
678     1       ...
789     2       ...
321     2       ...
432     3       ...
543     3       ...

Expected Return Ref Cursor:

ABC1    4       2       1       3
ABC2    4       1       3       null
ABC3    1       null    null    1
ABC4    1       null    1       null

Reduce it to a simple SQL statement using DECODE.  If the STATUS is the expected value (1, 2, 3) for the column, return 1, otherwise return NULL.  Then count the non-null return values.  COUNT will only count non-null values:

SELECT
  BATCH,
  COUNT(BATCH) TOTAL,
  COUNT(DECODE(STATUS,1,1,NULL)) TOTAL_1,
  COUNT(DECODE(STATUS,2,1,NULL)) TOTAL_2,
  COUNT(DECODE(STATUS,3,1,NULL)) TOTAL_3
FROM
  MYVIEW V1
WHERE
  STATUS < 4
GROUP BY
  BATCH;

It appears that if COUNT returns 0, that you want NULL to be returned rather than 0.  Once again, use DECODE to convert 0 to NULL and all other values to the original formula:

SELECT
  BATCH,
  COUNT(BATCH) TOTAL,
  DECODE(COUNT(DECODE(STATUS,1,1,NULL)),0,NULL,COUNT(DECODE(STATUS,1,1,NULL))) TOTAL_1,
  DECODE(COUNT(DECODE(STATUS,2,1,NULL)),0,NULL,COUNT(DECODE(STATUS,2,1,NULL))) TOTAL_2,
  DECODE(COUNT(DECODE(STATUS,3,1,NULL)),0,COUNT(DECODE(STATUS,3,1,NULL))) TOTAL_3
FROM
  MYVIEW V1
WHERE
  STATUS < 4
GROUP BY
  BATCH;

The inclusion of STATUS < 4 leads me to believe that you are attempting to reuse a view definition for a different task.  Doing so may lead to performance issues.  It may be a good idea to eliminate the view and directly reference the exact SQL statement of interest.

SQL – Reformatting to Improve Performance 6

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/c28103fe283b7551

I am learning SQL (trial by fire) and have a question regarding a query optimization. Lets say I have three tables ‘project’,  ‘notes’ & ‘reminder’. Key on ‘project’ is ‘proj_id’. The other two tables reference this key as ‘notes’ contains note entries on a given project and ‘reminder’ tracks todo type and due dates.

select count(a.proj_id)
from project a
where a.stat_cd = 'CLOSED'
and  exists
      (
      select b.proj_id
      from reminder b
      where b.rem_type = 'LAST'
      and a.proj_id = b.proj_id
      )
and exists
      (
      select c.proj_id
      from notes c
      where c.note_type = 'QA'
      and a.proj_id = c.proj_id
      )

I am trying to determine the number of projects that have a ‘CLOSED’ status, contain a reminder type of ‘LAST’ and has a note type of ‘QA’

I get the result I am looking for but these are very large tables and it takes over 30 minutes for this to run. Is there a better way to write this query? I’m betting there is. Thank you in advance for your advice.

Give this re-write a try to see if it improves performance – I have essentially moved the exists statements into two inline views (some versions of Oracle may automatically perform such transformations):

SELECT
  COUNT(A.PROJ_ID)
FROM
  PROJECT A,
  (SELECT DISTINCT
    B.PROJ_ID
  FROM
    REMINDER B
  WHERE
    B.REM_TYPE = 'LAST') B,
  (SELECT DISTINCT
    C.PROJ_ID
  FROM
    NOTES C
  WHERE
    C.NOTE_TYPE = 'QA') C
WHERE
  A.STAT_CD = 'CLOSED'
  AND A.PROJ_ID = B.PROJ_ID
  AND A.PROJ_ID = C.PROJ_ID;

Very likely, you will need an index on the PROJ_ID column for each table, especially if the REMINDER and NOTES tables contain columns that are wide.  An index on REMINDER.REM_TYPE might help.  An index on NOTES.NOTE_TYPE also might help.  Take a look at the explain plan (preferrably DBMS_XPLAN) for your query, and compare it to the explain plan for the one above.  Also, make certain that the tables and indexes are analyzed (use DBMS_STATS for Oracle 8i and above).

It is important to keep in mind that the two solutions provided may or may not yield the same results.  This will be evident in those cases where there is more than one row in REMINDER for a PROJ_ID WHERE REM_TYPE = ‘LAST’, and in cases where there is more than one row in NOTES for a PROJ_ID WHERE NOTE_TYPE = ‘QA’.

To work around the above issue, if it may occur, modify AlterEgo’s solution:

select count(a.projectid)

Can be modified as (assuming that PROJECTID is the primary key of the
PROJECT table):

SELECT
  COUNT(DISTINCT A.PROJECTID)

With the above change, you may find that AlterEgo’s SQL statement executes slightly faster than the solution that I provided (this may be Oracle version dependent).

One final note.  When building SQL statements with aliases, it is easier to troubleshoot problems with the SQL statements if the alias name is somehow related to the object name that it represents:
Rather than using:

  PROJECT A,
  REMINDER B,
  NOTES C

I would use:

  PROJECT P,
  REMINDER R,
  NOTES N

SQL – Reformatting to Improve Performance 5

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/97995c4a0e697539

Suppose I have a database table with 20 fields which are lookups to a single table.

configtable(configtable_id, a_field, something_lookup_id,
another_lookup_id, ...)
lookup(lookup_id, value, description, ...)

what is going to be faster to map the rows to an Object which needs the ‘value’ for every field ending in lookup_id

a) View

select c.configtable_id, l1.value as something_lookup, l2.value as
another_lookup
from configtable c,
       lookup l1,
       lookup l2
where c.something_lookup_id = l1.lookup_id
     and c.another_lookup_id = l2.lookup_id

foreach row
   map values to object
end

b) Cache all lookup values and populate

select c.* from configtable
foreach row
   map values to object
   if lookup_id
       find value from hashtable and map value to object
   endif
end

It seems that the latter *might* be better to scale outward better, as you could add application servers to do the caching/mapping and you only select from a single table?

——————————–
It might be a good idea to perform some testing with artifical data to see what happens when the size of the data set increases.  Performance wise, it is generally best to perform as much processing of data as possible on the database server, and return few result rows, compared to returning a large result set and performing the analysis outside the database.

To help you set up a test environment (T1 is your configtable):

CREATE TABLE T1(
  CONFIGTABLE_ID NUMBER(12),
  A_FIELD VARCHAR2(15),
  LOOKUP1 VARCHAR2(15),
  LOOKUP2 VARCHAR2(15),
  LOOKUP3 VARCHAR2(15),
  LOOKUP4 VARCHAR2(15),
  LOOKUP5 VARCHAR2(15),
  LOOKUP6 VARCHAR2(15),
  LOOKUP7 VARCHAR2(15),
  LOOKUP8 VARCHAR2(15),
  LOOKUP9 VARCHAR2(15),
  LOOKUP10 VARCHAR2(15),
  LOOKUP11 VARCHAR2(15),
  LOOKUP12 VARCHAR2(15),
  LOOKUP13 VARCHAR2(15),
  LOOKUP14 VARCHAR2(15),
  LOOKUP15 VARCHAR2(15),
  LOOKUP16 VARCHAR2(15),
  LOOKUP17 VARCHAR2(15),
  LOOKUP18 VARCHAR2(15),
  LOOKUP19 VARCHAR2(15),
  LOOKUP20 VARCHAR2(15),
  PRIMARY KEY(CONFIGTABLE_ID));

T2 is your lookup table:

CREATE TABLE T2(
  LOOKUP_ID VARCHAR2(15),
  VALUE NUMBER(12),
  DESCRIPTION VARCHAR2(30));

Note that I _forgot_ to declare a primary key on T2, so there is no index.

Now, let’s generate 10,000 rows of data in the T1 table, but don’t make the lookup columns too random – only three of the 15 characters in the lookup columns is permitted to be random:

INSERT INTO
  T1
SELECT
  ROWNUM CONFIGTABLE_ID,
  DBMS_RANDOM.STRING('A',15) A_FIELD,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP1,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP2,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP3,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP4,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP5,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP6,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP7,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP8,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP9,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP10,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP11,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP12,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP13,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP14,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP15,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP16,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP17,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP18,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP19,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP20
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

Let’s generate the data for the T2 table (your lookup table) using the distinct values in the 20 lookup columns of T1:

INSERT INTO
  T2
SELECT
  L LOOKUP_ID,
  TRUNC(DBMS_RANDOM.VALUE(1,100000)) VALUE,
  DBMS_RANDOM.STRING('A',25) DESCRIPTION
FROM
(SELECT
  LOOKUP1 L
FROM
  T1
UNION
SELECT
  LOOKUP2 L
FROM
  T1
UNION
SELECT
  LOOKUP3 L
FROM
  T1
UNION
SELECT
  LOOKUP4 L
FROM
  T1
UNION
SELECT
  LOOKUP5 L
FROM
  T1
UNION
SELECT
  LOOKUP6 L
FROM
  T1
UNION
SELECT
  LOOKUP7 L
FROM
  T1
UNION
SELECT
  LOOKUP8 L
FROM
  T1
UNION
SELECT
  LOOKUP9 L
FROM
  T1
UNION
SELECT
  LOOKUP10 L
FROM
  T1
UNION
SELECT
  LOOKUP11 L
FROM
  T1
UNION
SELECT
  LOOKUP12 L
FROM
  T1
UNION
SELECT
  LOOKUP13 L
FROM
  T1
UNION
SELECT
  LOOKUP14 L
FROM
  T1
UNION
SELECT
  LOOKUP15 L
FROM
  T1
UNION
SELECT
  LOOKUP16 L
FROM
  T1
UNION
SELECT
  LOOKUP17 L
FROM
  T1
UNION
SELECT
  LOOKUP18 L
FROM
  T1
UNION
SELECT
  LOOKUP19 L
FROM
  T1
UNION
SELECT
  LOOKUP20 L
FROM
  T1);
COMMIT;

Gather statistics on the two tables:

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER, TABNAME=>'T1',CASCADE=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER, TABNAME=>'T2',CASCADE=>TRUE);

SQL statement test 1, with 14 instances of the T2 lookup table:

SELECT
  C.CONFIGTABLE_ID,
  L1.VALUE L1_VALUE,
  L2.VALUE L2_VALUE,
  L3.VALUE L3_VALUE,
  L4.VALUE L4_VALUE,
  L5.VALUE L5_VALUE,
  L6.VALUE L6_VALUE,
  L7.VALUE L7_VALUE,
  L8.VALUE L8_VALUE,
  L9.VALUE L9_VALUE,
  L10.VALUE L10_VALUE,
  L11.VALUE L11_VALUE,
  L12.VALUE L12_VALUE,
  L13.VALUE L13_VALUE,
  L14.VALUE L14_VALUE
FROM
  T1 C,
  T2 L1,
  T2 L2,
  T2 L3,
  T2 L4,
  T2 L5,
  T2 L6,
  T2 L7,
  T2 L8,
  T2 L9,
  T2 L10,
  T2 L11,
  T2 L12,
  T2 L13,
  T2 L14
WHERE
  C.LOOKUP1=L1.LOOKUP_ID
  AND C.LOOKUP2=L2.LOOKUP_ID
  AND C.LOOKUP3=L3.LOOKUP_ID
  AND C.LOOKUP4=L4.LOOKUP_ID
  AND C.LOOKUP5=L5.LOOKUP_ID
  AND C.LOOKUP6=L6.LOOKUP_ID
  AND C.LOOKUP7=L7.LOOKUP_ID
  AND C.LOOKUP8=L8.LOOKUP_ID
  AND C.LOOKUP9=L9.LOOKUP_ID
  AND C.LOOKUP10=L10.LOOKUP_ID
  AND C.LOOKUP11=L11.LOOKUP_ID
  AND C.LOOKUP12=L12.LOOKUP_ID
  AND C.LOOKUP13=L13.LOOKUP_ID
  AND C.LOOKUP14=L14.LOOKUP_ID;

The DBMS Xplan for the above – took about 11.7 seconds:

---------------------------------------------------------------------------­-----------------------------------------------------------
| Id  | Operation                       | Name | Starts | E-Rows | A- Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------­-----------------------------------------------------------
|*  1 |  HASH JOIN                      |      |      1 |    100K|     100K|00:00:11.68 |   20806 |   5141 |  7744K|  2666K|     1/0/0|
|   2 |   TABLE ACCESS FULL             | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |    149 |       |       |          |
|*  3 |   HASH JOIN                     |      |      1 |    100K|     100K|00:00:10.95 |   19750 |   4992 |  7744K|  2666K|     1/0/0|
|   4 |    TABLE ACCESS FULL            | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|*  5 |    HASH JOIN                    |      |      1 |    100K|     100K|00:00:10.21 |   18694 |   4992 |  7744K|  2666K|     1/0/0|
|   6 |     TABLE ACCESS FULL           | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|*  7 |     HASH JOIN                   |      |      1 |    100K|     100K|00:00:09.57 |   17638 |   4992 |  7744K|  2666K|     1/0/0|
|   8 |      TABLE ACCESS FULL          | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|*  9 |      HASH JOIN                  |      |      1 |    100K|     100K|00:00:08.83 |   16582 |   4992 |  7744K|  2666K|     1/0/0|
|  10 |       TABLE ACCESS FULL         | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 11 |       HASH JOIN                 |      |      1 |    100K|     100K|00:00:08.16 |   15526 |   4992 |  7744K|  2666K|     1/0/0|
|  12 |        TABLE ACCESS FULL        | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 13 |        HASH JOIN                |      |      1 |    100K|     100K|00:00:07.43 |   14470 |   4992 |  7744K|  2666K|     1/0/0|
|  14 |         TABLE ACCESS FULL       | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 15 |         HASH JOIN               |      |      1 |    100K|     100K|00:00:06.78 |   13414 |   4992 |  7744K|  2666K|     1/0/0|
|  16 |          TABLE ACCESS FULL      | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 17 |          HASH JOIN              |      |      1 |    100K|     100K|00:00:06.05 |   12358 |   4992 |  7744K|  2666K|     1/0/0|
|  18 |           TABLE ACCESS FULL     | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 19 |           HASH JOIN             |      |      1 |    100K|     100K|00:00:05.40 |   11302 |   4992 |  7744K|  2666K|     1/0/0|
|  20 |            TABLE ACCESS FULL    | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 21 |            HASH JOIN            |      |      1 |    100K|     100K|00:00:04.65 |   10246 |   4992 |  7744K|  2666K|     1/0/0|
|  22 |             TABLE ACCESS FULL   | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 23 |             HASH JOIN           |      |      1 |    100K|     100K|00:00:04.01 |    9190 |   4992 |  7744K|  2666K|     1/0/0|
|  24 |              TABLE ACCESS FULL  | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 25 |              HASH JOIN          |      |      1 |    100K|     100K|00:00:03.28 |    8134 |   4992 |  7744K|  2666K|     1/0/0|
|  26 |               TABLE ACCESS FULL | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 27 |               HASH JOIN         |      |      1 |    100K|     100K|00:00:02.64 |    7078 |   4992 |  7744K|  2666K|     1/0/0|
|  28 |                TABLE ACCESS FULL| T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|  29 |                TABLE ACCESS FULL| T1   |      1 |    100K|     100K|00:00:01.90 |    6022 |   4992 |       |       |          |
---------------------------------------------------------------------------­-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."LOOKUP14"="L14"."LOOKUP_ID")
   3 - access("C"."LOOKUP13"="L13"."LOOKUP_ID")
   5 - access("C"."LOOKUP12"="L12"."LOOKUP_ID")
   7 - access("C"."LOOKUP11"="L11"."LOOKUP_ID")
   9 - access("C"."LOOKUP10"="L10"."LOOKUP_ID")
  11 - access("C"."LOOKUP9"="L9"."LOOKUP_ID")
  13 - access("C"."LOOKUP8"="L8"."LOOKUP_ID")
  15 - access("C"."LOOKUP7"="L7"."LOOKUP_ID")
  17 - access("C"."LOOKUP6"="L6"."LOOKUP_ID")
  19 - access("C"."LOOKUP5"="L5"."LOOKUP_ID")
  21 - access("C"."LOOKUP4"="L4"."LOOKUP_ID")
  23 - access("C"."LOOKUP3"="L3"."LOOKUP_ID")
  25 - access("C"."LOOKUP2"="L2"."LOOKUP_ID")
  27 - access("C"."LOOKUP1"="L1"."LOOKUP_ID")

Let’s try again, this time with one instance of the T2 lookup table
listed in the SQL statement:

SELECT
  C.CONFIGTABLE_ID,
  MAX(DECODE(C.LOOKUP1,L1.LOOKUP_ID,L1.VALUE,NULL)) L1_VALUE,
  MAX(DECODE(C.LOOKUP2,L1.LOOKUP_ID,L1.VALUE,NULL)) L2_VALUE,
  MAX(DECODE(C.LOOKUP3,L1.LOOKUP_ID,L1.VALUE,NULL)) L3_VALUE,
  MAX(DECODE(C.LOOKUP4,L1.LOOKUP_ID,L1.VALUE,NULL)) L4_VALUE,
  MAX(DECODE(C.LOOKUP5,L1.LOOKUP_ID,L1.VALUE,NULL)) L5_VALUE,
  MAX(DECODE(C.LOOKUP6,L1.LOOKUP_ID,L1.VALUE,NULL)) L6_VALUE,
  MAX(DECODE(C.LOOKUP7,L1.LOOKUP_ID,L1.VALUE,NULL)) L7_VALUE,
  MAX(DECODE(C.LOOKUP8,L1.LOOKUP_ID,L1.VALUE,NULL)) L8_VALUE,
  MAX(DECODE(C.LOOKUP9,L1.LOOKUP_ID,L1.VALUE,NULL)) L9_VALUE,
  MAX(DECODE(C.LOOKUP10,L1.LOOKUP_ID,L1.VALUE,NULL)) L10_VALUE,
  MAX(DECODE(C.LOOKUP11,L1.LOOKUP_ID,L1.VALUE,NULL)) L11_VALUE,
  MAX(DECODE(C.LOOKUP12,L1.LOOKUP_ID,L1.VALUE,NULL)) L12_VALUE,
  MAX(DECODE(C.LOOKUP13,L1.LOOKUP_ID,L1.VALUE,NULL)) L13_VALUE,
  MAX(DECODE(C.LOOKUP14,L1.LOOKUP_ID,L1.VALUE,NULL)) L14_VALUE
FROM
  T1 C,
  T2 L1
WHERE
  C.LOOKUP1=L1.LOOKUP_ID
  OR C.LOOKUP2=L1.LOOKUP_ID
  OR C.LOOKUP3=L1.LOOKUP_ID
  OR C.LOOKUP4=L1.LOOKUP_ID
  OR C.LOOKUP5=L1.LOOKUP_ID
  OR C.LOOKUP6=L1.LOOKUP_ID
  OR C.LOOKUP7=L1.LOOKUP_ID
  OR C.LOOKUP8=L1.LOOKUP_ID
  OR C.LOOKUP9=L1.LOOKUP_ID
  OR C.LOOKUP10=L1.LOOKUP_ID
  OR C.LOOKUP11=L1.LOOKUP_ID
  OR C.LOOKUP12=L1.LOOKUP_ID
  OR C.LOOKUP13=L1.LOOKUP_ID
  OR C.LOOKUP14=L1.LOOKUP_ID
GROUP BY
  C.CONFIGTABLE_ID;

The DBMS Xplan, 21.3 seconds once the GROUP BY completed:

---------------------------------------------------------------------------­---------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A- Time   | Buffers | Reads  | Writes |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------­---------------------------------------------------------
|   1 |  HASH GROUP BY       |      |      1 |  98476 |    100K| 00:00:21.31 |   85162 |  74613 |   4402 |       |       |          |
|   2 |   CONCATENATION      |      |      1 |        |   1399K| 00:00:14.33 |   85162 |  70211 |      0 |       |       |          |
|*  3 |    HASH JOIN         |      |      1 |    100K|    100K| 00:00:00.84 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|   4 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|   5 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|*  6 |    HASH JOIN         |      |      1 |   5000 |  99998 | 00:00:01.00 |    6083 |   5015 |      0 |  7744K|  2666K|     1/0/0|
|   7 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|   8 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5015 |      0 |       |       |          |
|*  9 |    HASH JOIN         |      |      1 |    250 |  99998 | 00:00:00.93 |    6083 |   5015 |      0 |  7744K|  2666K|     1/0/0|
|  10 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  11 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.30 |    5027 |   5015 |      0 |       |       |          |
|* 12 |    HASH JOIN         |      |      1 |     13 |  99997 | 00:00:00.93 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  13 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  14 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|* 15 |    HASH JOIN         |      |      1 |      1 |  99999 | 00:00:00.94 |    6083 |   5013 |      0 |  7744K|  2666K|     1/0/0|
|  16 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  17 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5013 |      0 |       |       |          |
|* 18 |    HASH JOIN         |      |      1 |      1 |  99994 | 00:00:00.97 |    6083 |   5015 |      0 |  7744K|  2666K|     1/0/0|
|  19 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  20 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5015 |      0 |       |       |          |
|* 21 |    HASH JOIN         |      |      1 |      1 |  99993 | 00:00:00.99 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  22 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  23 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|* 24 |    HASH JOIN         |      |      1 |      1 |  99995 | 00:00:00.95 |    6083 |   5014 |      0 |  7744K|  2666K|     1/0/0|
|  25 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  26 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5014 |      0 |       |       |          |
|* 27 |    HASH JOIN         |      |      1 |      1 |  99988 | 00:00:00.97 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  28 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  29 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|* 30 |    HASH JOIN         |      |      1 |      1 |  99994 | 00:00:00.97 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  31 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  32 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.30 |    5027 |   5016 |      0 |       |       |          |
|* 33 |    HASH JOIN         |      |      1 |      1 |  99988 | 00:00:01.09 |    6083 |   5015 |      0 |  7744K|  2666K|     1/0/0|
|  34 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  35 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.30 |    5027 |   5015 |      0 |       |       |          |
|* 36 |    HASH JOIN         |      |      1 |      1 |  99991 | 00:00:00.96 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  37 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  38 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|* 39 |    HASH JOIN         |      |      1 |      1 |  99988 | 00:00:00.93 |    6083 |   5014 |      0 |  7744K|  2666K|     1/0/0|
|  40 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  41 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5014 |      0 |       |       |          |
|* 42 |    HASH JOIN         |      |      1 |      1 |  99997 | 00:00:00.98 |    6083 |   5014 |      0 |  7744K|  2666K|     1/0/0|
|  43 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  44 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5014 |      0 |       |       |          |
---------------------------------------------------------------------------­---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("C"."LOOKUP14"="L1"."LOOKUP_ID")
   6 - access("C"."LOOKUP13"="L1"."LOOKUP_ID")
       filter(LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID"))
   9 - access("C"."LOOKUP12"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  12 - access("C"."LOOKUP11"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  15 - access("C"."LOOKUP10"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  18 - access("C"."LOOKUP9"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  21 - access("C"."LOOKUP8"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  24 - access("C"."LOOKUP7"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  27 - access("C"."LOOKUP6"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  30 - access("C"."LOOKUP5"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  33 - access("C"."LOOKUP4"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP5"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  36 - access("C"."LOOKUP3"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP4"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP5"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  39 - access("C"."LOOKUP2"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP3"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP4"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP5"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  42 - access("C"."LOOKUP1"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP2"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP3"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP4"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP5"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))

OK, so we tried to be clever and it took twice as long.

Third try:

SELECT
  C.CONFIGTABLE_ID,
  MAX(DECODE(C.LOOKUP1,L1.LOOKUP_ID,L1.VALUE,NULL)) L1_VALUE,
  MAX(DECODE(C.LOOKUP2,L1.LOOKUP_ID,L1.VALUE,NULL)) L2_VALUE,
  MAX(DECODE(C.LOOKUP3,L1.LOOKUP_ID,L1.VALUE,NULL)) L3_VALUE,
  MAX(DECODE(C.LOOKUP4,L1.LOOKUP_ID,L1.VALUE,NULL)) L4_VALUE,
  MAX(DECODE(C.LOOKUP5,L1.LOOKUP_ID,L1.VALUE,NULL)) L5_VALUE,
  MAX(DECODE(C.LOOKUP6,L1.LOOKUP_ID,L1.VALUE,NULL)) L6_VALUE,
  MAX(DECODE(C.LOOKUP7,L1.LOOKUP_ID,L1.VALUE,NULL)) L7_VALUE,
  MAX(DECODE(C.LOOKUP8,L1.LOOKUP_ID,L1.VALUE,NULL)) L8_VALUE,
  MAX(DECODE(C.LOOKUP9,L1.LOOKUP_ID,L1.VALUE,NULL)) L9_VALUE,
  MAX(DECODE(C.LOOKUP10,L1.LOOKUP_ID,L1.VALUE,NULL)) L10_VALUE,
  MAX(DECODE(C.LOOKUP11,L1.LOOKUP_ID,L1.VALUE,NULL)) L11_VALUE,
  MAX(DECODE(C.LOOKUP12,L1.LOOKUP_ID,L1.VALUE,NULL)) L12_VALUE,
  MAX(DECODE(C.LOOKUP13,L1.LOOKUP_ID,L1.VALUE,NULL)) L13_VALUE,
  MAX(DECODE(C.LOOKUP14,L1.LOOKUP_ID,L1.VALUE,NULL)) L14_VALUE
FROM
  T1 C,
  T2 L1
WHERE
  DECODE(L1.LOOKUP_ID,C.LOOKUP1,1,
    C.LOOKUP2,1,
    C.LOOKUP3,1,
    C.LOOKUP4,1,
    C.LOOKUP5,1,
    C.LOOKUP6,1,
    C.LOOKUP7,1,
    C.LOOKUP8,1,
    C.LOOKUP9,1,
    C.LOOKUP10,1,
    C.LOOKUP11,1,
    C.LOOKUP12,1,
    C.LOOKUP13,1,
    C.LOOKUP14,1,0)=1
GROUP BY
  C.CONFIGTABLE_ID;

This one took more than two minutes (I killed it at that point).

Let’s try a full Cartesian join just to kill the database server:

SELECT /*+ ORDERED */
  C.CONFIGTABLE_ID,
  MAX(DECODE(C.LOOKUP1,L1.LOOKUP_ID,L1.VALUE,NULL)) L1_VALUE,
  MAX(DECODE(C.LOOKUP2,L1.LOOKUP_ID,L1.VALUE,NULL)) L2_VALUE,
  MAX(DECODE(C.LOOKUP3,L1.LOOKUP_ID,L1.VALUE,NULL)) L3_VALUE,
  MAX(DECODE(C.LOOKUP4,L1.LOOKUP_ID,L1.VALUE,NULL)) L4_VALUE,
  MAX(DECODE(C.LOOKUP5,L1.LOOKUP_ID,L1.VALUE,NULL)) L5_VALUE,
  MAX(DECODE(C.LOOKUP6,L1.LOOKUP_ID,L1.VALUE,NULL)) L6_VALUE,
  MAX(DECODE(C.LOOKUP7,L1.LOOKUP_ID,L1.VALUE,NULL)) L7_VALUE,
  MAX(DECODE(C.LOOKUP8,L1.LOOKUP_ID,L1.VALUE,NULL)) L8_VALUE,
  MAX(DECODE(C.LOOKUP9,L1.LOOKUP_ID,L1.VALUE,NULL)) L9_VALUE,
  MAX(DECODE(C.LOOKUP10,L1.LOOKUP_ID,L1.VALUE,NULL)) L10_VALUE,
  MAX(DECODE(C.LOOKUP11,L1.LOOKUP_ID,L1.VALUE,NULL)) L11_VALUE,
  MAX(DECODE(C.LOOKUP12,L1.LOOKUP_ID,L1.VALUE,NULL)) L12_VALUE,
  MAX(DECODE(C.LOOKUP13,L1.LOOKUP_ID,L1.VALUE,NULL)) L13_VALUE,
  MAX(DECODE(C.LOOKUP14,L1.LOOKUP_ID,L1.VALUE,NULL)) L14_VALUE
FROM
  (SELECT
    C.CONFIGTABLE_ID,
    C.LOOKUP1,
    C.LOOKUP2,
    C.LOOKUP3,
    C.LOOKUP4,
    C.LOOKUP5,
    C.LOOKUP6,
    C.LOOKUP7,
    C.LOOKUP8,
    C.LOOKUP9,
    C.LOOKUP10,
    C.LOOKUP11,
    C.LOOKUP12,
    C.LOOKUP13,
    C.LOOKUP14
  FROM
    T1 C) C,
  T2 L1
GROUP BY
  C.CONFIGTABLE_ID;

This one took more than two minutes (I killed it at that point).

Let’s try the second method again, hiding optimizations from Oracle, prohibiting the CONCAT/UNION ALL optimization and a couple other optimization possibilies:

SELECT /*+ NO_QUERY_TRANSFORMATION */
  C.CONFIGTABLE_ID,
  MAX(DECODE(C.LOOKUP1,L1.LOOKUP_ID,L1.VALUE,NULL)) L1_VALUE,
  MAX(DECODE(C.LOOKUP2,L1.LOOKUP_ID,L1.VALUE,NULL)) L2_VALUE,
  MAX(DECODE(C.LOOKUP3,L1.LOOKUP_ID,L1.VALUE,NULL)) L3_VALUE,
  MAX(DECODE(C.LOOKUP4,L1.LOOKUP_ID,L1.VALUE,NULL)) L4_VALUE,
  MAX(DECODE(C.LOOKUP5,L1.LOOKUP_ID,L1.VALUE,NULL)) L5_VALUE,
  MAX(DECODE(C.LOOKUP6,L1.LOOKUP_ID,L1.VALUE,NULL)) L6_VALUE,
  MAX(DECODE(C.LOOKUP7,L1.LOOKUP_ID,L1.VALUE,NULL)) L7_VALUE,
  MAX(DECODE(C.LOOKUP8,L1.LOOKUP_ID,L1.VALUE,NULL)) L8_VALUE,
  MAX(DECODE(C.LOOKUP9,L1.LOOKUP_ID,L1.VALUE,NULL)) L9_VALUE,
  MAX(DECODE(C.LOOKUP10,L1.LOOKUP_ID,L1.VALUE,NULL)) L10_VALUE,
  MAX(DECODE(C.LOOKUP11,L1.LOOKUP_ID,L1.VALUE,NULL)) L11_VALUE,
  MAX(DECODE(C.LOOKUP12,L1.LOOKUP_ID,L1.VALUE,NULL)) L12_VALUE,
  MAX(DECODE(C.LOOKUP13,L1.LOOKUP_ID,L1.VALUE,NULL)) L13_VALUE,
  MAX(DECODE(C.LOOKUP14,L1.LOOKUP_ID,L1.VALUE,NULL)) L14_VALUE
FROM
  T1 C,
  T2 L1
WHERE
  C.LOOKUP1=L1.LOOKUP_ID
  OR C.LOOKUP2=L1.LOOKUP_ID
  OR C.LOOKUP3=L1.LOOKUP_ID
  OR C.LOOKUP4=L1.LOOKUP_ID
  OR C.LOOKUP5=L1.LOOKUP_ID
  OR C.LOOKUP6=L1.LOOKUP_ID
  OR C.LOOKUP7=L1.LOOKUP_ID
  OR C.LOOKUP8=L1.LOOKUP_ID
  OR C.LOOKUP9=L1.LOOKUP_ID
  OR C.LOOKUP10=L1.LOOKUP_ID
  OR C.LOOKUP11=L1.LOOKUP_ID
  OR C.LOOKUP12=L1.LOOKUP_ID
  OR C.LOOKUP13=L1.LOOKUP_ID
  OR C.LOOKUP14=L1.LOOKUP_ID
GROUP BY
  C.CONFIGTABLE_ID;

This one took more than five minutes (I killed it at that point).

Maybe the fact that we forgot to put an index on the T2 table is causing the performance problem – all of those full tablescans can’t be good:

ALTER TABLE T2 ADD
  PRIMARY KEY (LOOKUP_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T2';

Now we have an index named SYS_C0022342.

Analyze table T2 and its index again:

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER, TABNAME=>'T2', CASCADE=>TRUE);

Execute the first query again, and the same plan was generated.

Force the use of the index on five of the joins, and try a nested loop on one of the joins – took about the same amount of time as the first attempt:

SELECT /*+ INDEX(L1 SYS_C0022342) INDEX(L2 SYS_C0022342) INDEX(L3 SYS_C0022342) INDEX(L4 SYS_C0022342) INDEX(L5 SYS_C0022342) USE_NL(C L1) */
  C.CONFIGTABLE_ID,
  L1.VALUE L1_VALUE,
  L2.VALUE L2_VALUE,
  L3.VALUE L3_VALUE,
  L4.VALUE L4_VALUE,
  L5.VALUE L5_VALUE,
  L6.VALUE L6_VALUE,
  L7.VALUE L7_VALUE,
  L8.VALUE L8_VALUE,
  L9.VALUE L9_VALUE,
  L10.VALUE L10_VALUE,
  L11.VALUE L11_VALUE,
  L12.VALUE L12_VALUE,
  L13.VALUE L13_VALUE,
  L14.VALUE L14_VALUE
FROM
  T1 C,
  T2 L1,
  T2 L2,
  T2 L3,
  T2 L4,
  T2 L5,
  T2 L6,
  T2 L7,
  T2 L8,
  T2 L9,
  T2 L10,
  T2 L11,
  T2 L12,
  T2 L13,
  T2 L14
WHERE
  C.LOOKUP1=L1.LOOKUP_ID
  AND C.LOOKUP2=L2.LOOKUP_ID
  AND C.LOOKUP3=L3.LOOKUP_ID
  AND C.LOOKUP4=L4.LOOKUP_ID
  AND C.LOOKUP5=L5.LOOKUP_ID
  AND C.LOOKUP6=L6.LOOKUP_ID
  AND C.LOOKUP7=L7.LOOKUP_ID
  AND C.LOOKUP8=L8.LOOKUP_ID
  AND C.LOOKUP9=L9.LOOKUP_ID
  AND C.LOOKUP10=L10.LOOKUP_ID
  AND C.LOOKUP11=L11.LOOKUP_ID
  AND C.LOOKUP12=L12.LOOKUP_ID
  AND C.LOOKUP13=L13.LOOKUP_ID
  AND C.LOOKUP14=L14.LOOKUP_ID;

------------------------------------------------------------------------­---------------------------------------------------------------------------­--
| Id  | Operation                                 | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem | 1Mem |  O/1/M   |
---------------------------------------------------- -----------------------­-----------------------------------------------------------------------­--
|*  1 |  HASH JOIN                                |              |      1 |    131K|    100K|00:00:12.98 |     222K|   4967 |  7744K| 2666K|     1/0/0|
|   2 |   TABLE ACCESS FULL                       | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|*  3 |   HASH JOIN                               |              |      1 |    129K|    100K|00:00:12.25 |     221K|   4967 |  7744K| 2666K|     1/0/0|
|   4 |    TABLE ACCESS FULL                      | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|*  5 |    HASH JOIN                              |              |      1 |    126K|    100K|00:00:11.62 |     220K|   4967 |  7744K| 2666K|     1/0/0|
|   6 |     TABLE ACCESS FULL                     | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|*  7 |     HASH JOIN                             |              |      1 |    124K|    100K|00:00:10.99 |     219K|   4967 |  7744K| 2666K|     1/0/0|
|   8 |      TABLE ACCESS FULL                    | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|*  9 |      HASH JOIN                            |              |      1 |    121K|    100K|00:00:10.27 |     218K|   4967 |  7744K| 2666K|     1/0/0|
|  10 |       TABLE ACCESS FULL                   | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 11 |       HASH JOIN                           |              |      1 |    119K|    100K|00:00:09.64 |     217K|   4967 |  7744K| 2666K|     1/0/0|
|  12 |        TABLE ACCESS FULL                  | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 13 |        HASH JOIN                          |              |      1 |    117K|    100K|00:00:09.01 |     216K|   4967 |  7744K| 2666K|     1/0/0|
|  14 |         TABLE ACCESS FULL                 | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 15 |         HASH JOIN                         |              |      1 |    114K|    100K|00:00:08.29 |     215K|   4967 |  7744K| 2666K|     1/0/0|
|  16 |          TABLE ACCESS FULL                | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 17 |          HASH JOIN                        |              |      1 |    112K|    100K|00:00:07.66 |     214K|   4967 |  7744K| 2666K|     1/0/0|
|  18 |           TABLE ACCESS FULL               | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 19 |           HASH JOIN                       |              |      1 |    110K|    100K|00:00:07.03 |     213K|   4967 |  7744K| 2666K|     1/0/0|
|  20 |            TABLE ACCESS BY INDEX ROWID    | T2           |      1 |    140K|    140K|00:00:00.42 |    1520 |      0 |       |      |          |
|  21 |             INDEX FULL SCAN               | SYS_C0022342 |      1 |    140K|    140K|00:00:00.08 |     511 |      0 |       |      |          |
|* 22 |            HASH JOIN                      |              |      1 |    108K|    100K|00:00:05.87 |     211K|   4967 |  7744K| 2666K|     1/0/0|
|  23 |             TABLE ACCESS BY INDEX ROWID   | T2           |      1 |    140K|    140K|00:00:00.42 |    1520 |      0 |       |      |          |
|  24 |              INDEX FULL SCAN              | SYS_C0022342 |      1 |    140K|    140K|00:00:00.08 |     511 |      0 |       |      |          |
|* 25 |             HASH JOIN                     |              |      1 |    106K|    100K|00:00:04.82 |     210K|   4967 |  7744K| 2666K|     1/0/0|
|  26 |              TABLE ACCESS BY INDEX ROWID  | T2           |      1 |    140K|    140K|00:00:00.42 |    1520 |      0 |       |      |          |
|  27 |               INDEX FULL SCAN             | SYS_C0022342 |      1 |    140K|    140K|00:00:00.08 |     511 |      0 |       |      |          |
|* 28 |              HASH JOIN                    |              |      1 |    104K|    100K|00:00:03.76 |     208K|   4967 |  7744K| 2666K|     1/0/0|
|  29 |               TABLE ACCESS BY INDEX ROWID | T2           |      1 |    140K|    140K|00:00:00.42 |    1520 |      0 |       |      |          |
|  30 |                INDEX FULL SCAN            | SYS_C0022342 |      1 |    140K|    140K|00:00:00.08 |     511 |      0 |       |      |          |
|  31 |               NESTED LOOPS                |              |      1 |    101K|    100K|00:00:02.60 |     207K|   4967 |       |      |          |
|  32 |                TABLE ACCESS FULL          | T1           |      1 |    100K|    100K|00:00:01.00 |    6022 |   4967 |       |      |          |
|  33 |                TABLE ACCESS BY INDEX ROWID| T2           |    100K|      1 |    100K|00:00:01.35 |     201K|      0 |       |      |          |
|* 34 |                 INDEX UNIQUE SCAN         | SYS_C0022342 |    100K|      1 |    100K|00:00:00.73 |     101K|      0 |       |      |          |
---------------------------------------------------------------------------­------------------------------------------------------------------------­--
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."LOOKUP14"="L14"."LOOKUP_ID")
   3 - access("C"."LOOKUP13"="L13"."LOOKUP_ID")
   5 - access("C"."LOOKUP12"="L12"."LOOKUP_ID")
   7 - access("C"."LOOKUP11"="L11"."LOOKUP_ID")
   9 - access("C"."LOOKUP10"="L10"."LOOKUP_ID")
  11 - access("C"."LOOKUP9"="L9"."LOOKUP_ID")
  13 - access("C"."LOOKUP8"="L8"."LOOKUP_ID")
  15 - access("C"."LOOKUP7"="L7"."LOOKUP_ID")
  17 - access("C"."LOOKUP6"="L6"."LOOKUP_ID")
  19 - access("C"."LOOKUP5"="L5"."LOOKUP_ID")
  22 - access("C"."LOOKUP4"="L4"."LOOKUP_ID")
  25 - access("C"."LOOKUP3"="L3"."LOOKUP_ID")
  28 - access("C"."LOOKUP2"="L2"."LOOKUP_ID")
  34 - access("C"."LOOKUP1"="L1"."LOOKUP_ID")

The short answer to the above demonstration is that the structure of the SQL statement makes a big difference in the execution time. Hiding information, possibily in views, retricts Oracle’s options when trying to determine the optimal execution plan – and disabling the options has a very negative impact on execution efficiency.  Your experience with your actual data set may be very different.
——————————–