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
LVL 14
Who is Participating?
MilleniumaireConnect With a Mentor Commented:
The following explains index organised tables (or organized tables if you're America ;-):
Have a read of the following article for an explanation of indexes:
ajexpertAuthor Commented:
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?
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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.
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
ajexpertAuthor Commented:
Great angellll.

Now my next question is, how to implement IOT?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
POracleConnect With a Mentor Commented:
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....

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Ok, I understand now.

Thanks angelIII, for your quick response..  
htonkovConnect With a Mentor Commented:
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 :) )

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.
All Courses

From novice to tech pro — start learning today.