| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: which is more scalable - view or raw table access+offload to application server
On Mar 6, 8:10 pm, "Timasmith" <timasm..._at_hotmail.com> wrote:
> 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),
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
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=>'ME', TABNAME=>'T1',
CASCADE=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ME', 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,
T2 L1, T2 L2, T2 L3, T2 L4, T2 L5, T2 L6, T2 L7, T2 L8, T2 L9, T2 L10, T2 L11,
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_VALUEFROM
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
| 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")
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")
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_VALUEFROM
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
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_VALUEFROM
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
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_VALUEFROM
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
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=>'ME', 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,
T2 L1, T2 L2, T2 L3, T2 L4, T2 L5, T2 L6, T2 L7, T2 L8, T2 L9, T2 L10, T2 L11,
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.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Wed Mar 07 2007 - 15:38:00 CST
 |
 |