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