chrbjo
asked on
What is an index table?
What is an index table? Does it mean that you store the whole table as an index? What are the dis- and advantages with using index tables?
Where can I find examples on how this is done (e.g. scripts) ?
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please update and finalize this old, open question. Please:
1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.
EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.
Thanks,
** Mindphaser - Community Support Moderator **
P.S. Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.
EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.
Thanks,
** Mindphaser - Community Support Moderator **
P.S. Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
Recommended disposition:
Accept jammalk's comment(s) as an answer.
DanRollins -- EE database cleanup volunteer
Accept jammalk's comment(s) as an answer.
DanRollins -- EE database cleanup volunteer
Force accepted
** Mindphaser - Community Support Moderator **
** Mindphaser - Community Support Moderator **
Index-organized tables are like regular tables with a primary key index on one or more of its columns.
Yes, the rows are stored in the form of an index, but primary key is compulsory.
To create an index-organized table, just add ORGANIZATION INDEX at the end of normal CREATE TABLE statement
CREATE TABLE <table name>(
<col1>,
<col2>,
.,
.,
.,
CONSTRAINT <primary key constraint name> PRIMARY KEY (<primary key columns>))
ORGANIZATION INDEX
Advantages:
1. Because data rows are stored in the index, index-organized tables provide faster key-based access to table data for queries that involve exact match or range search or both.
2. The storage requirements are reduced because key columns are not duplicated as they are in an ordinary table and its index. The data row stored with the key in an index-organized table only contains non-key column values.
3. Also, placing the data row with the key eliminates the additional storage that an index on an ordinary table requires for physical rowids, which link the key values to corresponding rows in the table.
This option is good for the tables like master tables with smaller no of frequent inserts and deletes.
Disadvantages:
It is not good for the tables with huge amount of data manipulation(i.e. inserts and deletes), by which the performance will come down.
Regards,