Solved

INDEX and INDEX ORGANIZED TABLE

Posted on 2010-11-24
12
1,517 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 125 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
 
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 142

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

Author Comment

by:ajexpert
ID: 34207913
Great angellll.

Now my next question is, how to implement IOT?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

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 125 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 142

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 125 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_20703
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now