12 Oracle Design Consideration

Effective Schema Design

  • The Schema Plays an important role in  tuning joins
  • Choose data types for all primary and unique keys carefully, and remain consistent through out the schema
  • If you primary key column is varchar2(100), such a primary key is expensive because the large key must be carried in every referencing table, and also because Oracle bust compare long strings when performing a join
  • Create Index where necessary and if possible try to create indexes on multiple columns
  • Normalize your tables , so that there is not a lot of redundancy
  • Put necessary constraints on the table so that you have the right data.

Oracle sample datamodel

Oracle sample data model

Make sure data types across columns in related tables are the same otherwise Oracle has to do the data type conversion and also obviously it means use cant use the index. Make sure primary keys are unique and define constraints to make it unique. Make sure your primary keys are as short (small) as possible and create indexes to improve performance.

Separate Table space for Data and Index

  • It is important to create separate table-space for Indexes, Data, Rollback segments & temporary segments
  • Separating tables from indexes  is key to balancing disk I/O and reducing contention
  • This will significantly impact  nested loops join performance since nested loops joins make heavy use of indexes

Oracle tuning Separate table space for Index and data

  • Sort Merge Joins heavily rely on temporary segments, It’s important to dedicate a table space to holding temporary segments only and to designate each user’s temporary tablespace accordingly.
  • In a large database with many concurrent users performing lots of sorts, It can be beneficial to have several temporary table spaces spread across many disks
  • Be sure to size the temporary tablespace  large enough to accommodate enough concurrent sort-merge joins, and size the initial, next, and max extents properly so that temporary segments will be able to grow large enough to perform the desired sorts

Keep all of this in mind to create table space effectively.

Index Organized Tables (IOT)

IOT has their primary key data  and non-key  column data stored within the same B-Tree structure . Effectively, data is stored within the primary  key index.


Accessing data via primary keys is quicker as the key and data reside in the same structure. There is no need to read an index then read the table data in a seperate structure.

lack of duplication of the key columns in an index  and table mean the total storage requirements are reduced.


Index data is stored separately and actual data is stored separately. So when ever you use an index, you go to the index, read the rowID’s take the rowID’s and then go to the actual data. Imagine a scenario where both the index and data are stored together, that is exactly what an IOT is. The diagram above shows an IOT where the primary key and the data are stored in the same structure.

When to Use an IOT

  • Rows are not much longer than their index key. tables generally store data more compactly and  efficiently than an index with the same data.
  • Small lookup type tables (e.g. queried frequently, updated frequently, fits a relative small number of blocks)
  • Any table that you already are going to have an index that covers all the columns anyway (i.e. may as well save the space used by the table if the index duplicates 100% of the time)

Creating IOT

Oracle Create Index Organized tables

Oracle Create Index Organized tables

The only extra syntax in  rceating an IOT is Organization Index, Otherwise it’s just like creating a table.

Partitioned Tables

Oracle tuning partitioned tables

Oracle tuning partitioned tables

  • Partitioned tables allow data management operations such as data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than the entire table. This results in significantly reduced time for these operations.
  • Partitioning improves query performance

In today’s world data is growing at an alarming pace, Table sizes have grown and they are holding millions and millions of rows. what ever you do, the best SQL or index will not help improve the performance. Partitioning is where you divide the table into multiple logical pieces. For example if you have a table with 12 million rows for a whole year, you can divide the table into partitions for each month.If you partition the data by month it significantly improves the performance . When ever the table grows into a really large size partitioning is the only way you can control the performance . So partition your table into logical chunks that make sense.

There are 4 types o partitioning in Oracle. They are

  • Range partition
  • List partition
  • Hash Partition
  • Composite partition

Range Partition

range partitioning is a partitioning technique where ranges o data are stored separately in different sub tables.

Oracle SQL tuning : Range partitioning

Oracle SQL tuning : Range partitioning

List Partition

List Partitioning is a partitioning where technique where you specify a list of discrete values for the partitioning key in the description for each partition.

Oracle performance tuning list partitioning

Oracle performance tuning list partitioning

Hash Partition

hash partitioning is a partition technique where a hash key is used to distribute rows evenly across the different partitions (sub tables) . This is typically used where ranges aren’t appropriate, i.e. employee no, productid etc.

Oracle performance tuning hash partitioning

Oracle performance tuning hash partitioning

Composite Partition

Composite partitioning is partitioning technique that combines some o the other partitioning methods. the table is initially partitioned by the fist data distribution method and then each partition is sub-partitioned by the second data distribution method.

Oracle performance tuning : Composite partitioning method

Oracle performance tuning : Composite partitioning method

In this example for the main partitioning the months are being used while the sub partitions are based on the region.

In today’s data warehouse environment no table exist without a partition

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link  if you found this article interesting. (This will help  elevate my companies Search engine rankings)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s