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.
What we can do is creating an object type for each entity which needs to be persisted in a table. The key advantage in terms of flexibility is that we do not need to create all the object types before creating a table – it is enough to have only base object in advance. Let me show how it works below.
set feedback off create or replace type o force as object ( key int ) not final / create or replace type o_value under o ( value int ) / create or replace type o_person under o ( name varchar2(30), dob date ) / create table t(id int generated as identity primary key, value o); insert into t(value) select o(1) from dual; insert into t(value) select o_value(2, 100) from dual; insert into t(value) select o_person(3, 'Ivan', date '2020-09-27') from dual; commit; select * from t; select t.value.key key from t t where t.value is of type (o); select t.value.key key, treat(t.value as o_value).value value from t t where t.value is of type (o_value); select t.value.key key, treat(t.value as o_person).name name, treat(t.value as o_person).dob dob from t t where t.value is of type (o_person); create or replace type o_dummy under o ( str varchar2(30) ) / insert into t(value) select o_dummy(1, 'Hello') from dual; select t.value.key key, treat(t.value as o_dummy).str str from t t where t.value is of type (o_dummy); commit;
Output
ID VALUE ---------- ------ 1 O(1) 2 O(2) 3 O(3) KEY ---------- 1 2 3 KEY VALUE ---------- ---------- 2 100 KEY NAME DOB ---------- ------------------------------ --------- 3 Ivan 27-SEP-20 KEY STR ---------- ------------------------------ 1 Hello
As you may have noticed, type o_dummy was created when table already exists but we still can store values for a new object in the table because it is inherited from the base object. For brevity I named such tables varattrs tables because effectively this allows us to store entities with whatever attributes we want in a single table and access each attribute like it is a separate column.
So what about performance? Surprisingly it’s almost identical for varattrs table and normal relational table. For below test execution time on my laptop is 6.9 vs 7.0 seconds. Please note that there is no specific order without order by thus sample output may be different.
set serveroutput on drop table t1; drop table t2; drop type o force; create table t1 ( n1 number, n2 number, n3 number, n4 number, n5 number, n6 number, n7 number, n8 number, n9 number, n10 number, n11 number, n12 number, n13 number, n14 number, n15 number, n16 number, n17 number, n18 number, n19 number, n20 number, n21 number, n22 number, n23 number, n24 number, n25 number, n26 number, n27 number, n28 number, n29 number, n30 number ); create or replace type o force as object(key int) not final; / create or replace type o_30 under o ( n1 number, n2 number, n3 number, n4 number, n5 number, n6 number, n7 number, n8 number, n9 number, n10 number, n11 number, n12 number, n13 number, n14 number, n15 number, n16 number, n17 number, n18 number, n19 number, n20 number, n21 number, n22 number, n23 number, n24 number, n25 number, n26 number, n27 number, n28 number, n29 number, n30 number ) / create table t2(value o); insert into t1 select level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level from dual connect by level <= 2e6; insert into t2(value) select o_30(level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level, level) from dual connect by level <= 2e6; commit; set timing on declare cursor c is select n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12, n13, n14, n15, n16, n17, n18, n19, n20, n21, n22, n23, n24, n25, n26, n27, n28, n29, n30 from t1; type tp is table of c%rowtype; arr tp; begin open c; fetch c bulk collect into arr; close c; dbms_output.put_line(arr(9).n9); end; / / declare cursor c is select t.value.n1 n1, t.value.n2 n2, t.value.n3 n3, t.value.n4 n4, t.value.n5 n5, t.value.n6 n6, t.value.n7 n7, t.value.n8 n8, t.value.n9 n9, t.value.n10 n10, t.value.n11 n11, t.value.n12 n12, t.value.n13 n13, t.value.n14 n14, t.value.n15 n15, t.value.n16 n16, t.value.n17 n17, t.value.n18 n18, t.value.n19 n19, t.value.n20 n20, t.value.n21 n21, t.value.n22 n22, t.value.n23 n23, t.value.n24 n24, t.value.n25 n25, t.value.n26 n26, t.value.n27 n27, t.value.n28 n28, t.value.n29 n29, t.value.n30 n30 from (select treat(t.value as o_30) value from t2 t where t.value is of type (o_30)) t; type tp is table of c%rowtype; arr tp; begin open c; fetch c bulk collect into arr; close c; dbms_output.put_line(arr(9).n9); end; / / set timing off
Final thing to mention is that this solution can be implemented on any Oracle version which supports inheritance. As far as I remember, inheritance was introduced in Oracle 9i, so the solution has already been available for two decades and it is not a new feature by any means.
SQLMDX 