Earlier this week, I introduced a way to extract a chunk of functionality from a view into an Object Type, using the object-oriented capabilities of the Oracle database.
Today I want to go one step further and make the Object Type not part of the view logic, but part of the underlying table. The goal is to get rid of the still rather complicated view and replace the
STRUCTURE column with a
Quick reminder of our table: Structured Force Powers based on their alignment (Universal, Light, Dark) and the proficiency needed (Precondition to use Force Push is Telekinesis)
Adding the new object type column is straight forward:
-- First add a new column to the table alter table force_powers add struct t_numeric_structure; -- Then fill the new column update force_powers set struct = t_numeric_structure(structure); -- We can now easily select from the type select p.id, p.name, p.struct.structure() structure from force_powers p; -- By the way: It doesnt work to select -- without a table alias select p.id, p.name, struct.structure() structure from force_powers p;
So far so easy, but we want to make sure that the
STRUCT column is unique so we can safely drop the old
-- It doesnt work to add a unique constraint on the type alter table force_powers add constraint force_powers_uq_struct_new unique ( struct ); -- Neither can it be done via a function alter table force_powers add constraint force_powers_uq_struct_new unique ( struct.structure() ); -- And also not via a unique function-based index -- because the function is not deterministic create unique index idx_force_powers_struct on force_powers ( struct.structure() ); -- But its totally possible to have a unique constraint -- on the PROPERTIES of the type alter table force_powers add constraint force_powers_uq_struct_new unique ( struct.c_level1, struct.c_level2, struct.c_level3 ); -- Now lets get rid of the old structure column alter table force_powers drop column structure; -- And Assure the unique index works insert into force_powers ( name, struct ) select 'some name', t_numeric_structure('4.0.0') from dual connect by level <= 10; -- Also via update update force_powers p set struct = t_numeric_structure('4.0.0') where p.struct.structure() like '3%'; -- We still can insert a new row insert into force_powers ( name, struct ) values ( 'Force healing', t_numeric_structure('2.2')); -- And update update force_powers p set struct = t_numeric_structure('3.1.3') where p.struct.structure() = '3.1.2';
We can now reduce the
SELECT to the following
select base.id, base.struct.structure() structure, base.name, base.struct.level1() level1, base.struct.level2() level3, base.struct.level3() level3, base.struct.depth() depth, base.struct.sort() sort, (select id from force_powers parent where parent.struct.structure() = base.struct.parent().structure() ) parent_id from force_powers base;
But what about these function-based indizes?
Functions must be deterministic to be used in a function-based index.
Deterministic means that a function will always return the same value based on the input parameters. But what does deterministic mean for member functions of object types stored in a database table?
I have a theory and wanted to discuss with the awesome people at AskTOM – unfortunately new questions are closed at the moment so they can care for the backlog of questions.
(I want to stress that I really appreciate that approach! Setting boundaries is important and for me, not allowing new questions temporarily is an expression of their dedication to quality. Thank you!)
My guess is, that the properties are treated as input parameters to the member functions. After all,
SELF is an implicit input parameter of every member function so if a function of an object type relies solely on the object’s properties, it can be seen as deterministic.
I prepared a simplified example on LiveSQL and will update this post with the AskTOM question once they’re open to new questions again.
Let’s use structure() for a function-based index
-- So ... back to that function-based index problem create unique index idx_force_powers_uq_struct on force_powers ( struct.structure() ); -- Function is not deterministic - so lets change that. create or replace type t_numeric_structure force as object ( c_level1 number(3,0), c_level2 number(3,0), c_level3 number(3,0), constructor function t_numeric_structure( i_struct varchar2 ) return self as result, member function p$_position_for_level( i_string varchar2, i_level positiven ) return pls_integer, member function level1 return pls_integer, member function level2 return pls_integer, member function level3 return pls_integer, member function structure return varchar2 deterministic, member function sort return varchar2, member function depth return pls_integer, member function parent return t_numeric_structure2 ); / -- We cant do this because our type is used in a table -- Therefore we have to remove it from the table, -- change it and then re-add it: -- First store the value in a new column alter table force_powers add strucutre_backup varchar2(12); update force_powers p set strucutre_backup = p.struct.structure(); -- Now remove the type-column and change the type alter table force_powers drop column struct;
I skip the re-creation of the type here, it’s really just adding
deterministic to the member function.
-- Now Re-add the type alter table force_powers add struct t_numeric_structure; update force_powers p set struct = t_numeric_structure(strucutre_backup); -- Add the unique index -- We are using substr here to tell the index that the -- resulting string will be a varchar2(12) -- Otherwise it will assume varchar2(4000) for we can not -- hint the length of a varchar2 returned by a function create unique index idx_force_powers_uq_struct on force_powers ( substr(struct.structure(),1,12) );
Let’s now test again whether the unique index works:
insert into force_powers ( name, struct ) select 'some name', t_numeric_structure('4.0.0') from dual connect by level <= 10; -- Fails with Unique Index violated update force_powers p set struct = t_numeric_structure('4.0.0') where p.struct.structure() like '3%'; -- Fails with Unique Index violated -- The index is also used when querying set autotrace on select p.id, p.name, p.struct.structure() structure from force_powers p where p.struct.structure() = '1.1.1';
I probably should have made this two CodeExamples, but what’s the benefit of having an object in the table if you can’t put indizes on its functions or properties?
It might be a bit uncommon, but I really see huge benefits for readability of database code when object types are used thoughtfully.
Oh – and I really enjoyed spending some of my nights on this! 😁