Posted on 2010-11-24
Last Modified: 2013-12-18
Experts, please explain how creating index on table makes the retrieval of data faster (Assuming we are selecting few records from large table)

Also, what are INDEX ORGANIZED TABLES?  Please explain in simple words with example as I couldnt get much from oracle docs

250 points each for 2 questions
Question by:ajexpert
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +3
LVL 16

Expert Comment

ID: 34206873
Have a read of the following article for an explanation of indexes:
LVL 16

Accepted Solution

Milleniumaire earned 125 total points
ID: 34206890
The following explains index organised tables (or organized tables if you're America ;-):
LVL 14

Author Comment

ID: 34206924
Thanks Milleniumaire:

I have already seen these links, but I am specifically looking for simplified explanation.

Also when we should make use of IOT in real time scenario?
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

LVL 16

Expert Comment

ID: 34207013
To be honest, I'm not sure I could explain the ideas any simpler!

"Also when we should make use of IOT in real time scenario?"  You don't actually request this information above.

I would say the best time to use an IOT is when you have a large amount of data that will be frequently accessed using a specific key, and the data is fairly static.  Also, the queries accessing the data would be looking for a small number of specific rows, rather than lots of rows.

I'm sure other experts will also have a view on when they should be used.
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points
ID: 34207861
I confirm with an example:
in our Telecom Database, the calls/sms/etc are rated online, and stored in a couple of tables, each partitioned by day ...
however, when we produce invoices, we actually would need the data to be organized by account, not by day.
=> the issue is that when in the per-day organization, we search data for a single account, the data is spread over the disk, so result in many block reads.
with a IOT table, all the call records for an account are in the same block(s), adjacent to each other. so, only 1 (physical) read will be needed for 99% of the customers.
much better than having 30*n blocks to read (n being 7 in our case: calls, sms, mms, events, data, gprs, adjustments/discounts/payments), presuming that the customer does some traffic every day.

hope this helps
LVL 14

Author Comment

ID: 34207913
Great angellll.

Now my next question is, how to implement IOT?
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34208186
short: you create the table with ORGANISATION INDEX clause ...
what exactly else would you need to know?
you cannot alter a table from/to IOT... you would have to recreate it.

Assisted Solution

POracle earned 125 total points
ID: 34212403
I am sorry angelIII, But I didn't understand your Telecom example.

For index organized table how can you manage all records for one account adjacent to each other.
If you go for a partition by a day then even IOT not able to put all data for one account adjacent to each other. You may have index column data and table data in one block for IOT.
Can please elaborate more on this.


To know what is index see my comment here....

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34212451
POracle, we have 2 tables:
* 1 "production" table (Partitioned by day) from the third-party that does all the rating etc
* 1 "offline" table (IOT by account)
1 daily process to take the previous day data from the prod table to copy over to the IOT table

Expert Comment

ID: 34212487
Ok, I understand now.

Thanks angelIII, for your quick response..  

Assisted Solution

htonkov earned 125 total points
ID: 34214582
simplest explanation how index helps quicky find a record in table is:

You have a table T with columns A, B and C; and you need to search table T by column A where that column has a specific value (e.g. 7)
Without index Oracle would have to look in each row of table to see value of column A and if is equal to 7.
However, if you create an index on column A, it would contain a **ORDERED** list of unique values of column A, with pointers to locations of records in table T where column A has a respective value.
Ordered sets of data can be quickly searched, to if you use index and search for value 7, you can quickly locate a list of rows in table where A=7, and retreive data of other columns, i.e. B and C

Of course, this explanation is simple and not completely accurate, but I you sad you need simple :)
(and that's how I would explain it to my aunt :) )


Expert Comment

ID: 34273682
Oracle Database Index is a an organized structure as simple as 'Table of Contents' in the begining of the book or 'Index' pages  at the end of the book. We all know how quickly we can retrive the details using these pointers from the book.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help on decision table structure 7 58
Oracle Nested table uses ? 2 60
Oracle Partitions. 1 24
supress error message in oracle form 4 14
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question