Has anyone else had the misfortune to come accross an Entity-Arrtibute-Value type data model? some bright spark has implimented a particularly nasty variation in a database in the company I've just joined, as a special twist each row holds an start date and end date for which the value of the attribute is valid the table structure is as follows.
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECTID NOT NULL NUMBER
TYPEENTITY NOT NULL VARCHAR2(255)
PARAMNAME NOT NULL VARCHAR2(255)
PARAMVALUE NOT NULL VARCHAR2(255)
STARTDATE NOT NULL DATE
ENDDATE DATE
UPDOBJECTID NUMBER
To get one record out the developers need to perform 22 self joins (natrally these are all outer joins performance is so bad that the data is now pre-joined in a materialised view. creating an explain plan for the select statement gives the output below. From what I can tell the design is so bad the optimiser has had a breakdown. Does anyone have any experiance of dealing with EAV designs that could help while I explain (slowly using short words) to the developers that they are going to redesign the application properly. Just about the only thing I can think of is to partition the data on TYPEENTITY and PARAMNAME which may give the optimiser better satistics on the cardinality of each join, I'm going to build a test database to try this out but would be interested in other ideas on how to make this less crap. On the bright side the buffer cache hit ratio looks really good.
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2887 | | 31P(100)|999:59:59 |
| 1 | SORT GROUP BY NOSORT | | 1 | 2887 | | 31P(100)|999:59:59 |
| 2 | MERGE JOIN OUTER | | 18E| 15E| | 31P(100)|999:59:59 |
| 3 | MERGE JOIN OUTER | | 18E| 15E| | 3008T(100)|999:59:59 |
| 4 | MERGE JOIN OUTER | | 18E| 15E| | 317T(100)|999:59:59 |
| 5 | MERGE JOIN OUTER | | 4381P| 15E| | 29T(100)|999:59:59 |
| 6 | MERGE JOIN OUTER | | 417P| 15E| | 2798G(100)|999:59:59 |
| 7 | MERGE JOIN OUTER | | 22P| 15E| | 148G(100)|999:59:59 |
| 8 | MERGE JOIN OUTER | | 1420T| 2628P| | 9530M(100)|999:59:59 |
| 9 | MERGE JOIN OUTER | | 83T| 145P| | 563M(100)|999:59:59 |
| 10 | MERGE JOIN OUTER | | 3837G| 6333T| | 25M(100)| 86:27:44 |
| 11 | MERGE JOIN OUTER | | 848G| 1297T| | 5888K (97)| 19:37:45 |
| 12 | MERGE JOIN OUTER | | 42G| 60T| | 482K (60)| 01:36:29 |
| 13 | MERGE JOIN OUTER | | 42G| 54T| | 482K (60)| 01:36:29 |
| 14 | MERGE JOIN OUTER | | 4887M| 5822G| | 227K (15)| 00:45:36 |
| 15 | MERGE JOIN OUTER | | 180M| 192G| | 196K (2)| 00:39:14 |
| 16 | MERGE JOIN OUTER | | 11M| 10G| | 194K (1)| 00:38:58 |
| 17 | MERGE JOIN OUTER | | 706K| 590M| | 194K (1)| 00:38:54 |
| 18 | MERGE JOIN OUTER | | 75664 | 53M| | 194K (1)| 00:38:52 |
| 19 | MERGE JOIN OUTER | | 7860 | 4674K| | 194K (1)| 00:38:50 |
| 20 | MERGE JOIN OUTER | | 1146 | 531K| | 193K (1)| 00:38:48 |
| 21 | NESTED LOOPS OUTER | | 1146 | 381K| | 193K (1)| 00:38:48 |
| 22 | MERGE JOIN OUTER | | 1146 | 222K| | 482 (2)| 00:00:06 |
| 23 | MERGE JOIN | | 71 | 4615 | | 262 (2)| 00:00:04 |
| 24 | SORT JOIN | | 5004 | 70056 | | 86 (2)| 00:00:02 |
| 25 | VIEW | VW_SQ_22 | 5004 | 70056 | | 85 (0)| 00:00:02 |
| 26 | HASH GROUP BY | | 5004 | 185K| | 85 (0)| 00:00:02 |
|* 27 | INDEX RANGE SCAN | PK_PARAM | 6871 | 254K| | 85 (0)| 00:00:02 |
|* 28 | SORT JOIN | | 6871 | 342K| 936K| 175 (1)| 00:00:03 |
|* 29 | INDEX RANGE SCAN | PK_PARAM | 6871 | 342K| | 85 (0)| 00:00:02 |
|* 30 | SORT JOIN | | 110 | 14740 | | 220 (1)| 00:00:03 |
| 31 | VIEW | | 110 | 14740 | | 219 (1)| 00:00:03 |
|* 32 | HASH JOIN | | 110 | 8030 | | 219 (1)| 00:00:03 |
| 33 | VIEW | VW_SQ_2 | 5952 | 127K| | 109 (0)| 00:00:02 |
| 34 | HASH GROUP BY | | 5952 | 220K| | 109 (0)| 00:00:02 |
|* 35 | INDEX RANGE SCAN| PK_PARAM | 8924 | 331K| | 109 (0)| 00:00:02 |
|* 36 | INDEX RANGE SCAN | PK_PARAM | 8924 | 444K| | 109 (0)| 00:00:02 |
|* 37 | VIEW | | 1 | 142 | | 169 (1)| 00:00:03 |
|* 38 | HASH JOIN | | 71 | 5183 | | 169 (1)| 00:00:03 |
| 39 | VIEW | VW_SQ_1 | 4985 | 107K| | 84 (0)| 00:00:02 |
| 40 | SORT GROUP BY | | 4985 | 184K| | 84 (0)| 00:00:02 |
|* 41 | INDEX RANGE SCAN | PK_PARAM | 6833 | 253K| | 84 (0)| 00:00:02 |
|* 42 | INDEX RANGE SCAN | PK_PARAM | 6833 | 340K| | 84 (0)| 00:00:02 |
|* 43 | SORT JOIN | | 1 | 134 | | 8 (25)| 00:00:01 |
| 44 | VIEW | | 1 | 134 | | 7 (15)| 00:00:01 |
|* 45 | HASH JOIN | | 1 | 73 | | 7 (15)| 00:00:01 |
| 46 | VIEW | VW_SQ_3 | 74 | 1628 | | 3 (0)| 00:00:01 |
| 47 | HASH GROUP BY | | 74 | 2812 | | 3 (0)| 00:00:01 |
|* 48 | INDEX RANGE SCAN | PK_PARAM | 75 | 2850 | | 3 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | PK_PARAM | 75 | 3825 | | 3 (0)| 00:00:01 |
|* 50 | SORT JOIN | | 47 | 6298 | | 136 (2)| 00:00:02 |
| 51 | VIEW | | 47 | 6298 | | 135 (1)| 00:00:02 |
|* 52 | HASH JOIN | | 47 | 3431 | | 135 (1)| 00:00:02 |
| 53 | VIEW | VW_SQ_4 | 4205 | 92510 | | 67 (0)| 00:00:01 |
| 54 | HASH GROUP BY | | 4205 | 156K| | 67 (0)| 00:00:01 |
|* 55 | INDEX RANGE SCAN | PK_PARAM | 5414 | 200K| | 67 (0)| 00:00:01 |
|* 56 | INDEX RANGE SCAN | PK_PARAM | 5414 | 269K| | 67 (0)| 00:00:01 |
|* 57 | SORT JOIN | | 66 | 8844 | | 164 (2)| 00:00:02 |
| 58 | VIEW | | 66 | 8844 | | 163 (1)| 00:00:02 |
|* 59 | HASH JOIN | | 66 | 4818 | | 163 (1)| 00:00:02 |
| 60 | VIEW | VW_SQ_5 | 4849 | 104K| | 81 (0)| 00:00:01 |
| 61 | HASH GROUP BY | | 4849 | 179K| | 81 (0)| 00:00:01 |
|* 62 | INDEX RANGE SCAN | PK_PARAM | 6572 | 243K| | 81 (0)| 00:00:01 |
|* 63 | INDEX RANGE SCAN | PK_PARAM | 6572 | 327K| | 81 (0)| 00:00:01 |
|* 64 | SORT JOIN | | 64 | 8576 | | 160 (2)| 00:00:02 |
| 65 | VIEW | | 64 | 8576 | | 159 (1)| 00:00:02 |
|* 66 | HASH JOIN | | 64 | 4672 | | 159 (1)| 00:00:02 |
| 67 | VIEW | VW_SQ_6 | 4770 | 102K| | 79 (0)| 00:00:01 |
| 68 | HASH GROUP BY | | 4770 | 177K| | 79 (0)| 00:00:01 |
|* 69 | INDEX RANGE SCAN | PK_PARAM | 6423 | 238K| | 79 (0)| 00:00:01 |
|* 70 | INDEX RANGE SCAN | PK_PARAM | 6423 | 319K| | 79 (0)| 00:00:01 |
|* 71 | SORT JOIN | | 111 | 14874 | | 222 (1)| 00:00:03 |
| 72 | VIEW | | 111 | 14874 | | 221 (1)| 00:00:03 |
|* 73 | HASH JOIN | | 111 | 8103 | | 221 (1)| 00:00:03 |
| 74 | VIEW | VW_SQ_7 | 5967 | 128K| | 110 (0)| 00:00:02 |
| 75 | HASH GROUP BY | | 5967 | 221K| | 110 (0)| 00:00:02 |
|* 76 | INDEX RANGE SCAN | PK_PARAM | 8962 | 332K| | 110 (0)| 00:00:02 |
|* 77 | INDEX RANGE SCAN | PK_PARAM | 8962 | 446K| | 110 (0)| 00:00:02 |
|* 78 | SORT JOIN | | 108 | 14472 | | 218 (1)| 00:00:03 |
| 79 | VIEW | | 108 | 14472 | | 217 (1)| 00:00:03 |
|* 80 | HASH JOIN | | 108 | 7884 | | 217 (1)| 00:00:03 |
| 81 | VIEW | VW_SQ_8 | 5905 | 126K| | 108 (0)| 00:00:02 |
| 82 | HASH GROUP BY | | 5905 | 219K| | 108 (0)| 00:00:02 |
|* 83 | INDEX RANGE SCAN | PK_PARAM | 8812 | 327K| | 108 (0)| 00:00:02 |
|* 84 | INDEX RANGE SCAN | PK_PARAM | 8812 | 438K| | 108 (0)| 00:00:02 |
|* 85 | SORT JOIN | | 186 | 24924 | | 304 (1)| 00:00:04 |
| 86 | VIEW | | 186 | 24924 | | 303 (1)| 00:00:04 |
|* 87 | HASH JOIN | | 186 | 13578 | | 303 (1)| 00:00:04 |
| 88 | VIEW | VW_SQ_9 | 7185 | 154K| | 151 (0)| 00:00:02 |
| 89 | HASH GROUP BY | | 7185 | 266K| | 151 (0)| 00:00:02 |
|* 90 | INDEX RANGE SCAN | PK_PARAM | 12434 | 461K| | 151 (0)| 00:00:02 |
|* 91 | INDEX RANGE SCAN | PK_PARAM | 12434 | 619K| | 151 (0)| 00:00:02 |
|* 92 | SORT JOIN | | 60 | 8040 | | 156 (2)| 00:00:02 |
| 93 | VIEW | | 60 | 8040 | | 155 (1)| 00:00:02 |
|* 94 | HASH JOIN | | 60 | 4380 | | 155 (1)| 00:00:02 |
| 95 | VIEW | VW_SQ_10 | 4649 | 99K| | 77 (0)| 00:00:01 |
| 96 | HASH GROUP BY | | 4649 | 172K| | 77 (0)| 00:00:01 |
|* 97 | INDEX RANGE SCAN | PK_PARAM | 6199 | 230K| | 77 (0)| 00:00:01 |
|* 98 | INDEX RANGE SCAN | PK_PARAM | 6199 | 308K| | 77 (0)| 00:00:01 |
|* 99 | SORT JOIN | | 1 | 134 | | 8 (25)| 00:00:01 |
| 100 | VIEW | | 1 | 134 | | 7 (15)| 00:00:01 |
|*101 | HASH JOIN | | 1 | 73 | | 7 (15)| 00:00:01 |
|*102 | INDEX RANGE SCAN | PK_PARAM | 19 | 969 | | 3 (0)| 00:00:01 |
| 103 | VIEW | VW_SQ_11 | 19 | 418 | | 3 (0)| 00:00:01 |
| 104 | HASH GROUP BY | | 19 | 722 | | 3 (0)| 00:00:01 |
|*105 | INDEX RANGE SCAN | PK_PARAM | 19 | 722 | | 3 (0)| 00:00:01 |
|*106 | SORT JOIN | | 136 | 18224 | | 250 (1)| 00:00:04 |
| 107 | VIEW | | 136 | 18224 | | 249 (1)| 00:00:03 |
|*108 | HASH JOIN | | 136 | 9928 | | 249 (1)| 00:00:03 |
| 109 | VIEW | VW_SQ_12 | 6434 | 138K| | 124 (0)| 00:00:02 |
| 110 | HASH GROUP BY | | 6434 | 238K| | 124 (0)| 00:00:02 |
|*111 | INDEX RANGE SCAN | PK_PARAM | 10157 | 376K| | 124 (0)| 00:00:02 |
|*112 | INDEX RANGE SCAN | PK_PARAM | 10157 | 505K| | 124 (0)| 00:00:02 |
|*113 | SORT JOIN | | 31 | 4154 | | 108 (2)| 00:00:02 |
| 114 | VIEW | | 31 | 4154 | | 107 (1)| 00:00:02 |
|*115 | HASH JOIN | | 31 | 2263 | | 107 (1)| 00:00:02 |
| 116 | VIEW | VW_SQ_13 | 3483 | 76626 | | 53 (0)| 00:00:01 |
| 117 | HASH GROUP BY | | 3483 | 129K| | 53 (0)| 00:00:01 |
|*118 | INDEX RANGE SCAN | PK_PARAM | 4257 | 157K| | 53 (0)| 00:00:01 |
|*119 | INDEX RANGE SCAN | PK_PARAM | 4257 | 212K| | 53 (0)| 00:00:01 |
|*120 | SORT JOIN | | 150 | 20100 | | 266 (1)| 00:00:04 |
| 121 | VIEW | | 150 | 20100 | | 265 (1)| 00:00:04 |
|*122 | HASH JOIN | | 150 | 10950 | | 265 (1)| 00:00:04 |
| 123 | VIEW | VW_SQ_14 | 6674 | 143K| | 132 (0)| 00:00:02 |
| 124 | HASH GROUP BY | | 6674 | 247K| | 132 (0)| 00:00:02 |
|*125 | INDEX RANGE SCAN | PK_PARAM | 10829 | 401K| | 132 (0)| 00:00:02 |
|*126 | INDEX RANGE SCAN | PK_PARAM | 10829 | 539K| | 132 (0)| 00:00:02 |
|*127 | SORT JOIN | | 116 | 15544 | | 226 (1)| 00:00:03 |
| 128 | VIEW | | 116 | 15544 | | 225 (1)| 00:00:03 |
|*129 | HASH JOIN | | 116 | 8468 | | 225 (1)| 00:00:03 |
| 130 | VIEW | VW_SQ_15 | 6059 | 130K| | 112 (0)| 00:00:02 |
| 131 | HASH GROUP BY | | 6059 | 224K| | 112 (0)| 00:00:02 |
|*132 | INDEX RANGE SCAN | PK_PARAM | 9186 | 340K| | 112 (0)| 00:00:02 |
|*133 | INDEX RANGE SCAN | PK_PARAM | 9186 | 457K| | 112 (0)| 00:00:02 |
|*134 | SORT JOIN | | 107 | 14338 | | 216 (1)| 00:00:03 |
| 135 | VIEW | | 107 | 14338 | | 215 (1)| 00:00:03 |
|*136 | HASH JOIN | | 107 | 7811 | | 215 (1)| 00:00:03 |
| 137 | VIEW | VW_SQ_16 | 5889 | 126K| | 107 (0)| 00:00:02 |
| 138 | HASH GROUP BY | | 5889 | 218K| | 107 (0)| 00:00:02 |
|*139 | INDEX RANGE SCAN | PK_PARAM | 8775 | 325K| | 107 (0)| 00:00:02 |
|*140 | INDEX RANGE SCAN | PK_PARAM | 8775 | 437K| | 107 (0)| 00:00:02 |
|*141 | SORT JOIN | | 129 | 17286 | | 242 (1)| 00:00:03 |
| 142 | VIEW | | 129 | 17286 | | 241 (1)| 00:00:03 |
|*143 | HASH JOIN | | 129 | 9417 | | 241 (1)| 00:00:03 |
| 144 | VIEW | VW_SQ_17 | 6309 | 135K| | 120 (0)| 00:00:02 |
| 145 | HASH GROUP BY | | 6309 | 234K| | 120 (0)| 00:00:02 |
|*146 | INDEX RANGE SCAN | PK_PARAM | 9821 | 364K| | 120 (0)| 00:00:02 |
|*147 | INDEX RANGE SCAN | PK_PARAM | 9821 | 489K| | 120 (0)| 00:00:02 |
|*148 | SORT JOIN | | 72 | 9648 | | 172 (2)| 00:00:03 |
| 149 | VIEW | | 72 | 9648 | | 171 (1)| 00:00:03 |
|*150 | HASH JOIN | | 72 | 5256 | | 171 (1)| 00:00:03 |
| 151 | VIEW | VW_SQ_18 | 5023 | 107K| | 85 (0)| 00:00:02 |
| 152 | HASH GROUP BY | | 5023 | 186K| | 85 (0)| 00:00:02 |
|*153 | INDEX RANGE SCAN | PK_PARAM | 6908 | 256K| | 85 (0)| 00:00:02 |
|*154 | INDEX RANGE SCAN | PK_PARAM | 6908 | 344K| | 85 (0)| 00:00:02 |
|*155 | SORT JOIN | | 74 | 9916 | | 174 (2)| 00:00:03 |
| 156 | VIEW | | 74 | 9916 | | 173 (1)| 00:00:03 |
|*157 | HASH JOIN | | 74 | 5402 | | 173 (1)| 00:00:03 |
| 158 | VIEW | VW_SQ_20 | 5079 | 109K| | 86 (0)| 00:00:02 |
| 159 | HASH GROUP BY | | 5079 | 188K| | 86 (0)| 00:00:02 |
|*160 | INDEX RANGE SCAN | PK_PARAM | 7020 | 260K| | 86 (0)| 00:00:02 |
|*161 | INDEX RANGE SCAN | PK_PARAM | 7020 | 349K| | 86 (0)| 00:00:02 |
|*162 | SORT JOIN | | 65 | 8710 | | 162 (2)| 00:00:02 |
| 163 | VIEW | | 65 | 8710 | | 161 (1)| 00:00:02 |
|*164 | HASH JOIN | | 65 | 4745 | | 161 (1)| 00:00:02 |
| 165 | VIEW | VW_SQ_21 | 4810 | 103K| | 80 (0)| 00:00:01 |
| 166 | HASH GROUP BY | | 4810 | 178K| | 80 (0)| 00:00:01 |
|*167 | INDEX RANGE SCAN | PK_PARAM | 6497 | 241K| | 80 (0)| 00:00:01 |
|*168 | INDEX RANGE SCAN | PK_PARAM | 6497 | 323K| | 80 (0)| 00:00:01 |
|*169 | SORT JOIN | | 71 | 9514 | | 170 (2)| 00:00:03 |
| 170 | VIEW | | 71 | 9514 | | 169 (1)| 00:00:03 |
|*171 | HASH JOIN | | 71 | 5183 | | 169 (1)| 00:00:03 |
| 172 | VIEW | VW_SQ_19 | 4985 | 107K| | 84 (0)| 00:00:02 |
| 173 | HASH GROUP BY | | 4985 | 184K| | 84 (0)| 00:00:02 |
|*174 | INDEX RANGE SCAN | PK_PARAM | 6833 | 253K| | 84 (0)| 00:00:02 |
|*175 | INDEX RANGE SCAN | PK_PARAM | 6833 | 340K| | 84 (0)| 00:00:02 |
--------------------------------------------------------------------------------------------------------------------
FYI the code to produce this work of art looks like..
SELECT
SID
,MAX(GSID) GSID
,MAX(FIRSTNAME) FIRSTNAME
,MAX(LASTNAME)LASTNAME
... <-lines removed for sanity
...
,MAX(MANAGER_NAME)MANAGER_NAME
FROM
(SELECT
s1.objectid SID
,s1.paramvalue GSID
,s2.paramvalue FIRSTNAME
,s3.paramvalue LASTNAME
... <- ines removed for sanity
...
FROM
(select objectid, paramvalue from param p where typeentity='USER' and
paramname='GSID' and p.startdate = (select max(startdate) from param p1 where typeentity='USER'
AND paramname='GSID' AND p1.OBJECTID = p.OBJECTID )) s1
,(select objectid, paramvalue from param p where typeentity='USER'
and paramname='FIRSTNAME' and p.startdate = (select max(startdate) from param p1 where typeentity='USER'
AND paramname='FIRSTNAME' AND p1.OBJECTID = p.OBJECTID )) s2
,(select objectid, paramvalue from param p where typeentity='USER' and
paramname='LASTNAME' and p.startdate = (select max(startdate) from param p1 where typeentity='USER'
AND paramname='LASTNAME' AND p1.OBJECTID = p.OBJECTID )) s3
.... <- same thing for all 22 attibutes
) s22
WHERE s1.objectid = s2.objectid (+)
and s1.objectid= s3.objectid (+)
and s1.objectid= s4.objectid (+)
and s1.objectid= s5.objectid (+)
and s1.objectid= s6.objectid (+)
and s1.objectid= s7.objectid (+)
and s1.objectid= s8.objectid (+)
and s1.objectid= s9.objectid (+)
and s1.objectid= s10.objectid (+)
and s1.objectid= s11.objectid (+)
and s1.objectid= s12.objectid (+)
and s1.objectid= s13.objectid (+)
and s1.objectid= s14.objectid (+)
and s1.objectid= s15.objectid (+)
and s1.objectid= s16.objectid (+)
and s1.objectid= s17.objectid (+)
and s1.objectid= s18.objectid (+)
and s1.objectid= s19.objectid (+)
and s1.objectid= s20.objectid (+)
and s1.objectid= s21.objectid (+)
and s21.paramvalue = s22.objectid (+)
)alldata
group by alldata.sid