Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

INDEX and INDEX ORGANIZED TABLE

Posted on 2010-11-24
12
Medium Priority
?
1,614 Views
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
0
Comment
Question by:ajexpert
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 34206873
Have a read of the following article for an explanation of indexes:

http://www.orafaq.com/node/1403
0
 
LVL 16

Accepted Solution

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

http://www.dba-oracle.com/t_index_organized_tables.htm
0
 
LVL 14

Author Comment

by:ajexpert
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?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 16

Expert Comment

by:Milleniumaire
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.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 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
0
 
LVL 14

Author Comment

by:ajexpert
ID: 34207913
Great angellll.

Now my next question is, how to implement IOT?
0
 
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.
0
 
LVL 8

Assisted Solution

by:POracle
POracle earned 500 total points
ID: 34212403
angelIII,
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.

Thanks

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



 
0
 
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
0
 
LVL 8

Expert Comment

by:POracle
ID: 34212487
Ok, I understand now.

Thanks angelIII, for your quick response..  
0
 
LVL 3

Assisted Solution

by:htonkov
htonkov earned 500 total points
ID: 34214582
Ajexpert,
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 :) )

Regards,
Hrvoje
0
 
LVL 5

Expert Comment

by:Anand
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

927 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