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 VARCHAR2 STRUCTURE column with a t_numeric_structure object.
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)
| ID | STRUCTURE | NAME |
|---|---|---|
| 1 | 1 | Universal |
| 2 | 1.1 | Telekinesis |
| 3 | 1.1.1 | Force Push |
| ... | ... | ... |
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 STRUCTURE.
-- 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;
| ID | STRUCTURE | NAME | LEVEL1 | LEVEL3 | LEVEL3 | DEPTH | SORT | PARENT_ID |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | Universal | 1 | 0 | 0 | 1 | 001000000 | NULL |
| 2 | 1.1 | Telekinesis | 1 | 1 | 0 | 2 | 001001000 | 1 |
| 3 | 1.1.1 | Force Push | 1 | 1 | 1 | 3 | 001001001 | 2 |
| 4 | 1.1.2 | Force Pull | 1 | 1 | 2 | 3 | 001001002 | 2 |
| 5 | 1.1.3 | Force Jump | 1 | 1 | 3 | 3 | 001001003 | 2 |
| 6 | 2 | Light | 2 | 0 | 0 | 1 | 002000000 | NULL |
| 7 | 2.1 | Mind Trick | 2 | 1 | 0 | 2 | 002001000 | 6 |
| 8 | 2.1.1 | Force Persuation | 2 | 1 | 1 | 3 | 002001001 | 7 |
| 9 | 3 | Dark | 3 | 0 | 0 | 1 | 003000000 | NULL |
| 10 | 3.1 | Force wound | 3 | 1 | 0 | 2 | 003001000 | 9 |
| 11 | 3.1.1 | Force grip | 3 | 1 | 1 | 3 | 003001001 | 10 |
| 12 | 3.1.3 | Force choke | 3 | 1 | 3 | 3 | 003001003 | 10 |
| 15 | 2.2 | Force healing | 2 | 2 | 0 | 2 | 002002000 | 6 |
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';
The whole example can be run on LiveSQL and is available on GitHub.
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!
Top comments (0)