SQL – Reformatting to Improve Performance 5

4 12 2009

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.
——————————–


Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 148 other followers

%d bloggers like this: