Skip to Main Content
  • Questions
  • Physical design of table to maximize performance


Question and Answer

Tom Kyte

Thanks for the question.

Asked: September 27, 2010 - 7:28 pm UTC

Last updated: September 28, 2010 - 1:15 pm UTC


Viewed 1000+ times

You Asked

This question is a very generalized question, something like asking for fast=true, so please bear with me.

What can I do from a physical design point of view to maximize performance and concurrency if a table undergoes hundreds of thousands of select operations and maybe around 80,000 inserts, same amount of updates and deletes in say one hour in a high transaction rate OLTP database. The insert/update/delete numbers are just examples. In reality these would be much higher. How much higher is not known at this time as we are still in design phase.

I am looking for some guidelines which I can test with my application.


and Tom said...

You could write a book on this :) I called my Expert Oracle Database Architecture - and you would be interested in many of the chapters but in particular the ones on datatypes, tables and indexes.

You might need partitioning - to spread inserts out over many segments, to avoid hot spots in right hand side indexes (on dates, on sequences for example)...

You might need index organized tables - a little slower in most cases to insert into but if you query up multiple rows that arrive randomly over time in the table - it could be used to cluster those rows together making retrieval more efficient.

You probably want to use Automatic segment space management (ASSM) for concurrency reasons - to avoid having to figure out what pctused, freelists and freelist groups should be set to (but you should understand what the difference between manual and auto is...)

You'll want to understand how datatypes are physically stored so you can think about things like pctfree - and how to maximize your potential performance with LOBs if you are using them - and so on.

In short - you want to understand at some level how things work. The concepts guide would be a good starting place, if you like the way I write - those chapters in Expert Oracle Database Architecture would be a good starting place.

You'll need to think about concurrency - things like ASSM, partitioning and even clustering techniques (IOT's - index organized tables, hash/btree clusters) can be used.

You'll need to think about archiving over time perhaps.

You might need to consider the need to reorganize every once in a long while - and to design to facilitate that (think partitioning again)


  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Concept guide.

Jozef Babjak, September 28, 2010 - 12:31 pm UTC

Hello Tom,

as so many times, you pointing people to Concepts Guide again. Please, can you provide a direct link to the document, the most recent one?

Let me explain: I'm not Oracle specialist, I'm "only" interested in as general IT specialist and developer. I'm interested in some top-level approaches how to solve concurrency, scalability and performance problems "by design". Last time I read Oracle Concepts Guide (years ago), it has much less than 100 pages. Weeks ago I tried to find newer version of it, but I only found a document 600 or 800 pages - it does not seem to be a "brief introduction", so I'm not sure that I found the correct document. Or does Oracle complexity changes so dramatically? :-)

By the way, I have a Czech translation of you book mentioned above, and it was one of most useful books I ever read, even if I'm dealing with databases only marginaly. I have read it twice.
Tom Kyte
September 28, 2010 - 12:49 pm UTC

... I read Oracle Concepts Guide (years ago), it has much less than 100 pages. ...

It was never that small. The Oracle 7.3 concepts guide (15 years ago) is 525 pages.

the 11.2 concepts guide (which I helped trim back :) weighs in at 454 pages

it only contains "database core fundementals" now - the extraneous stuff is gone.

so, it is actually smaller today than it used to be....

Oleksandr Alesinskyy, September 28, 2010 - 12:56 pm UTC

As far as I can remember it was reorganized (and renamed) somewhen between 7.0 and 7.3 (at 7.2 I guess). At 7.0 it was really thin - likely between 150 and 300 pages.

Sure, I have no proof on hands and am not sure if this version of the guide may be found on Internet.
Tom Kyte
September 28, 2010 - 1:15 pm UTC

well.... I pull off my old v7.0 server concepts guide (december, 1992)

chapter    page count
content     36
  1         46
  2          7
  3         25
  4          7
  5         24
  6         16
  7         19
  8          6
  9         38
 10         29
 11          8
 12          7
 13         66
 14         19
 15         14
 16         10
 17         12
 18         16
 19          9
 20          6
 21         33
 22         21
 23         21
 24          7
 25         12
 AppendixA   9
 AppendixB   2
 index      28
 total     553

I keep that one document purely out of sentimental value :)

It was bigger back in the day then it is now...

You can't find the documentation?!?

APH, September 28, 2010 - 1:08 pm UTC

Please, can you provide a direct
link to the document, the most recent one?

Here's the (current as of today) link to the 11.2 Concepts Guide - it isn't hidden. I think if you do an search for "Oracle 11.2 Concepts Guide" you'll probably end up in the right place.

Weeks ago I tried to find newer version of it, but I only found a document 600 or 800 pages...

The current document is 454 pages, cover-to-cover, including the table of contents, preface, glossary, and index. Maybe Oracle has gotten a bit more complicated since version 4. Gosh, I would hope so!

A reader, September 28, 2010 - 6:23 pm UTC

Thanks Tom. We already use ASSM. I have partitioned the table but I had to create hash partition since there was no specific key for range partition. The table does not have any LOB columns. I have asked for a review of the application design to reduce IO.

I do have your book Expert Oracle Database Architecture and refer to it all the time. Your book gives a complete picture, with pros and cons, of every feature.


A reader, September 28, 2010 - 6:35 pm UTC


I pull off my old v7.0 server concepts guide

Oleksandr Alesinskyy, September 29, 2010 - 2:35 pm UTC


More to Explore


If you are new to partitioning, check out Connor McDonald's introduction series here.


Documentation set on VLDB and Partitioning.