Have you ever been forced to create many tables to handle a single functionality? Or make a table that’s horribly vague because there were so many different ways one row could come in?
Let me give you an example. Let’s say you had to create a table that could support every different kind of electronic computing device. At first, you may think, easy! We’ll create a table with CPU, RAM, VIDEO_CARD…wait…what about iPods? Those are computers too. Video game consoles, like the new XBOX360, can run Linux. PDAs, cell phones, mainfraimes, robotics, entertainment systems, all of these are types of computing devices! How can you make a table that holds them all?
At this point, you feel you have four options. You can make a single table with hundreds of columns, most of them nullable, that can handle all of it. Or you can make a table with tons of columns like STRING1, STRING2, NUMBER1, NUMBER2 to handle any eventuality. Or, you can just make a different table for every single device out there. Lastly, you can make other tables that have two vague columns, NAME and VALUE, so you can have as many rows as you want to describe the object in any way you like…but what happens when you have to do an AND or OR where clause against it? You’re stuck with UNIONs and such and performance goes downhill.
Enter object oriented Oracle, using substitutable types. How would you like a single column that can hold all of these columns and more that’s extremely scalable and easy to manage? Yes please.
What we will do is create a base table to hold all these items with columns that pertain to the overall concept. For instance, it will have the columns ITEM_ID and QUANTITY.
But where’s the details? We will put all of them, every single on, in a column called ITEM. And the data type of ITEM will be ITEM_TYPE.
Yes, we’re going to make our own data type!
SQL> create or replace type item_type as object (
2 ITEM_NAME VARCHAR2(100)) not final;
3 /Type created.
SQL> desc item_type
item_type is NOT FINAL
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEM_NAME VARCHAR2(100)
Now that we have a data type of our own, we can assign it to a column of a table. But before we do that, we’re going to find out just how powerful these things are with substitutable data types.
SQL> create or replace type pda_type under item_type (
2 battery_life number,
3 colors number,
4 cell_capabilities varchar2(1));
5 /
Type created.
SQL> desc pda_type
pda_type extends KARAM.ITEM_TYPE
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEM_NAME VARCHAR2(100)
BATTERY_LIFE NUMBER
COLORS NUMBER
CELL_CAPABILITIES VARCHAR2(1)
Notice that even though we didn’t put it in our create statement, there’s an ITEM_NAME column in the data type. That’s because PDA_TYPE extends ITEM_TYPE, which contains ITEM_NAME! This is what a substitutable type truly is: it’s a datatype that takes pieces of other datatypes and extends them to form new and interesting things.
Let’s make one more for our experiment.
SQL> create or replace type pc_type under item_type (
2 cpu varchar2(50),
3 ram number,
4 video_card varchar2(100));
5 /
Type created.
SQL> desc pc_type
pc_type extends KARAM.ITEM_TYPE
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEM_NAME VARCHAR2(100)
CPU VARCHAR2(50)
RAM NUMBER
VIDEO_CARD VARCHAR2(100)
Now, we’re at last ready to create our table.
SQL> create table item (item_id number primary key, quantity number,
2 item item_type);
Table created.
SQL> desc item
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEM_ID NOT NULL NUMBER
QUANTITY NUMBER
ITEM ITEM_TYPE
And here we are, ready to go now. Let’s put some records into our new table. This is where it gets a bit interesting.
First, we’re going to insert a PDA.
SQL> insert into item (item_id, quantity, item) values
2 (1, 20, pda_type('Handspring Trio', 480, 256000000, 'Y'));
1 row created.
SQL> select * from item;
ITEM_ID QUANTITY
---------- ----------
ITEM(ITEM_NAME)
--------------------------------------------------------------------------------
1 20
PDA_TYPE('Handspring Trio', 480, 256000000, 'Y')
Let’s do one more, this time a PC.
SQL> insert into item (item_id, quantity, item) values
2 (2, 50, pc_type('Macbook Pro', '2.8GHz', 1024, 'ATI Built In'));
1 row created.
SQL> select * from item;
ITEM_ID QUANTITY
---------- ----------
ITEM(ITEM_NAME)
--------------------------------------------------------------------------------
1 20
PDA_TYPE('Handspring Trio', 480, 256000000, 'Y')
2 50
PC_TYPE('Macbook Pro', '2.8GHz', 1024, 'ATI Built In')
Did you notice that our insert had entirely different columns? That’s because the data type changed! It’s the beauty of substitutable types. Now, we have all our data in that column called ITEM. Let’s see what we can do with it.
Would you like to get all the devices that are PCs?
SQL> select item from item where item is of type (PC_TYPE);
ITEM(ITEM_NAME)
--------------------------------------------------------------------------------
PC_TYPE('Macbook Pro', '2.8GHz', 1024, 'ATI Built In')
Here’s the one that’s probably on ALL of your minds. How are we going to specifically pull a single column? Like CPU from ITEM where it’s a PC? This is where the TREAT function comes in. TREAT allows us to translate an item’s datatype into one of its substitutes. Look below:
SQL> select treat(item as pc_type).cpu from item where item is of type (pc_type);
TREAT(ITEMASPC_TYPE).CPU
--------------------------------------------------
2.8GHz
And there we go! We can actually pull the columns as we please out of the system. If you create your code properly, you can make it very simple to dynamically build queries.
Lastly, we may want to update some rows. This is the only place I find these abilities lacking. You will actually have to do an inline view to perform the update.
SQL> update (select treat(item as pc_type).cpu as cpu from item where item is of type (pc_type)) set cpu = '3.2GHz';
1 row updated.
The other option is to create an actual view that is updateable for each of your sub-types, which can be very convenient.
1 create or replace view pc_view as
2 select treat(item as item_type).item_name item_name, treat(item as pc_type).cpu cpu,
3 treat(item as pc_type).ram ram, treat(item as pc_type).video_card video_card
4* from item where item is of type(pc_type)
SQL> /
View created.
SQL> select * from pc_view;
ITEM_NAME
--------------------------------------------------------------------------------
CPU RAM
-------------------------------------------------- ----------
VIDEO_CARD
--------------------------------------------------------------------------------
Macbook Pro
3.2GHz 1024
ATI Built In
SQL> update pc_view set video_card = 'ATI 64MB';
1 row updated.
SQL> select video_card from pc_view;
VIDEO_CARD
--------------------------------------------------------------------------------
ATI 64MB
And now we can update and select easily any time we choose. One note: notice when we created the view, we TREATed item as ITEM_TYPE for the ITEM_NAME column. This is because we need to use the highest possible data type in order to make the column updateable.
It is somewhat confusing, but extremely powerful for scalable systems. Please leave a comment here if you have questions!
