Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

INDEX and INDEX ORGANIZED TABLE

Posted on 2010-11-24
12
Medium Priority
?
1,603 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
[X]
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
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

715 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