asked on

INDEX and INDEX ORGANIZED TABLE

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

Milleniumaire

Have a read of the following article for an explanation of indexes:

http://www.orafaq.com/node/1403

ASKER CERTIFIED SOLUTION

Milleniumaire

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ajexpert

ASKER

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?

Milleniumaire

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.

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ajexpert

ASKER

Great angellll.

Now my next question is, how to implement IOT?

Guy Hengel [angelIII / a3]

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.

SOLUTION

POracle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

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

POracle

Ok, I understand now.

Thanks angelIII, for your quick response..

SOLUTION

htonkov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anand

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.