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.

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

• 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.

Advantages

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

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

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.

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.

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.

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.

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)