How to Model an IOT in Oracle SQL Developer Data Modeler

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:

index_org_table_clause

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.

So…

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.

Voila.

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.