A lot of programming languages allow to declare functions which accept arbitrary number of attributes and such functions are often referred as varargs functions. Even Oracle has such functions – for example, SQL function decode or PL/SQL function utl_lms.format_message. Unfortunately, Oracle does not allow to declare user defined varargs functions but this post is not about that.

From time to time Data Architects have to deal with the challenge to store entities with arbitrary attributes. If all possible entities are not known in advance during the schema design then it’s not possible to create a table with all the necessary attributes. Possible solutions in such cases are

• EAV design + pivot
• Storing entities in XML/JSON format or simply as varchar (latter would require implementing the logic for parsing)

Above solutions are not even close from performance perspective to the design when we can create a table with all needed attributes in advance. Nevertheless there is a solution with very similar performance.
Continue reading “Varattrs tables in Oracle” →