Someone asked me how to model a table as an IOT in Oracle SQL Developer Data Modeler. But before I answer that question, we shoudld spend at least a second on just what an IOT is.
It’s an ‘Index Organized Table.’ The storage is organized by the index, versus say the default behavior of a heap structure.
Or as the docs put it:
Use the index_org_table_clause to create an index-organized table. Oracle Database maintains the table rows, both primary key column values and nonkey column values, in an index built on the primary key. Index-organized tables are therefore best suited for primary key-based access and manipulation. An index-organized table is an alternative to:
A noncluster table indexed on the primary key by using the CREATE INDEX statement
A cluster table stored in an indexed cluster that has been created using the CREATE CLUSTER statement that maps the primary key for the table to the cluster key…
I’ll say that as opposed to how things run in the SQL Server world, a vast majority of tables in Oracle are NOT IOTs.
Notice that I’m talking about things like ‘storage’, ‘SQL Server’, ‘Oracle?’ These are details not generally tackled in the relational model. Rather, it’s handled in the physical model.
Most modeling tools will offer a Logical and a Physical modelling paradigm. We break the physical into Relational and Physical components. We believe offering this further breakout gives you more flexibility.
Just How Do I Build an IOT?
First, design your table.
Then, open the Physical Model.
You’ll need to expand the Physical Models node and create a new one. I’m going with 12c because it’s new and I like new shiny toys.
Now expand the tables node, find your table, and open the properties.
We’ll want to look at the ‘Organization’ entry on the ‘General’ page.
Ta-da! I did it! –These are my 2 year old son’s 2 favorite phrases.
Now Let’s Test It
First let’s preview the DDL.
Right click on the table in the model diagram and ask for the DDL.
Ok, but if I create this in the database, how can I tell that it’s an IOT?
The easiest way is to look at the table bitmap label in the connection tree – this in Oracle SQL Developer mind you.
And that’s pretty much it. You’ll want to spend a good bit more time deciding what your use cases and performance requirements are before just throwing out a bunch of IOTs into your data model. But once you know that that’s what you want, setting it up in the Modeler is pretty straight-forward.
It was, however, my design showed two available Oracle versions under Physical Model: 12c and 12cR2. I made the changes to the 12c version but the DDL generation was being based on the 12cR2. After an afternoon of trying every preference, I found the reason. Thanks for the reply though Jeff. Hopefully this will help somebody else.
For some reason, Version 4.2.0 Build 932 is not generating the IOT in the DDL even if it is configured as you show it. Is there any other setting that would be stopping physical properties from being generated in the DDL?
Make sure the physical model is open in your browser.