Solved

ORACLE INDEX TABLES

Posted on 2006-12-01
27
1,550 Views
Last Modified: 2012-06-21
Hi Experts,

       I am not a DB expert so i really need your help on this one, is there a way in Oracle that you can configure the DB in order to increase the Index Tables and Shchema tables??? We are having a lot of trouble doing this manualy.


Thanks 4 youe help

Cheers
0
Comment
Question by:camacho_marco
  • 8
  • 8
  • 7
  • +2
27 Comments
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 18055618
Sorry, I don't understand what you are trying to do? Are you planning on adding more indexes to existing database as well as SCHEMAS?

0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18055748
I don't understand the question either.  What are you trying to do manually now?
0
 
LVL 6

Author Comment

by:camacho_marco
ID: 18055860
No, the problem is that some times the Index Tables are increasing and when they are full the DB stops, this is not perfomed automatically we doit manually  and we whant to set the DB to increase the index tables automatically.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18055939
Indexes and tables are two different things in Oracle.  Tables may have indexes, but there is no such thing as "Index Tables" in Oracle, so that is part of the reason for some confusion regarding your question.

I think you are saying that as records are added to tables, the indexes grow larger (this is normal) but they apprently fill up the tablespace that they are in, and the tablespace is not set to autoextend.  Is that what happens, you have to add a file to a tablespace, or increase the size of the existing file(s) in a tablespace?

Which version of Oracle do you have?  In Oracle8.1 and higher, you can set the tablespaces to autoextend.  But if you do this, you then have to monitor the disk space in the server O/S instead to make sure that you do not fill up a disk or partition.
0
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 18056010
I agree with markgeer. Maybe you want to create "index organized tables" on a separate tablespace with autoextend feature ON on datafile. There are many advantages on using Index organized tables. Please visit the url below for details on IOT (Index organized tables).

http://www.oracle.com/technology/products/oracle9i/datasheets/iots/iot_ds.html
0
 
LVL 6

Author Comment

by:camacho_marco
ID: 18056837
Thanks 4 the info guys, and how do you deploy the index organization tables???
0
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 18056860
Index organized tables are created just like regular table but have additional clause it the DDL.

Here are some good examples on this url:  

http://www.psoug.org/reference/iot.html

Try them out.

Have fun :-)
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18056863
Are you sure that you want to use "index-organized tables"?  Yes, they offer some advantanges, and some space savings but there are disadvantages also.  Are you familiar with the advantages and disadvantages of "index-organized tables" in Oracle?

Which version of Oracle do you have?  The features and limitations of "index-organized tables" are different in the recent versions of Oracle.
0
 
LVL 6

Author Comment

by:camacho_marco
ID: 18057140
We are running Oracle 9i, and no a am not a DB expert so i am learnig.

Thanks 4 your help
0
 
LVL 6

Author Comment

by:camacho_marco
ID: 18059863
Markgree can you post the diferences???
0
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 18060969
I am not an expert and not 100% sure about the advantages/disadvantages on using IOT (index organized tables) but here are some basic differences. Markgeer should be able to provide you with more expert advise.

A relational table with a primary key constraint automatically has a unique index associated with the primary key. That makes query much faster.

An index organized table does not have an index. Instead, it uses entire table as an index. Every row in an IOT  is stored as b-tree index structure. The primary key values are used to arrange the data into branches.
The primary advantage of an IOT is that queries based on the primary key are faster than queries in heap-organized tables. IOT table also save space because it does not store the primary key values in both the index and the data blocks and because it does not store the ROWID values at all. Search is based on the primary key.

The primary disadvantage if using IOT is inserts, update, delete are slower because they cause an imbalance in the b-tree structure which requires Oracle to shuffle rows into different index blocks to rebalance the structure.

I suggest, if your table is very large and frequently updated, deleted, or inserted then IOT is probably not recommended.


 
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18068611
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm#2062969

open the above url and search for 'index_org_table_clause' and just browse thru that. That may give you more understanding about index-organized tables.

Thanks
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18068663
Mohammadzahid has covered some of the difference.  Another big difference is the fact (in Oracle9 at least) that you cannot have a secondary index on an index-organized table.  That can be a huge disadvantage, but this depends on your table and on your application.

I think we need to get back to your original problem, and try to solve that.  Don't just assume that using "index-organized" tables instead of standard tables is the best or only solution to your original problem.

Can you explain your orginal problem a bit more?  What is the basic problem?  Is it an index tablespace or tablespaces that fill up, but are not set to autoextend?

Also, please tell us what kind of application you have.  Is it a "canned" application that uses Oracle to store its data?  Or is it a customer application that was written just for your business?  Is it mainly a transaction-processing system, or a data warehouse?
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 34

Expert Comment

by:Mark Geerlings
ID: 18068713
Oops!  I made a mistake in one of my questions in my previous posting.  I intended to ask:  Is this a custom (not customer) application that was written just for your business?
0
 
LVL 6

Author Comment

by:camacho_marco
ID: 18069903
Hi All,

   Thanks 4 following up with me, this is a custom applcation that wa written for our bussiness, the issue is that the the index tablespace is growing, and we whant to set the index to increment the space  automatically, when  the index tablespace is full we need to increment the index tablespace manually. On a post taht Markgreer maeke he sugested to set the inex tablespace to autoextend  if we  do this  we have to monitor the disk space in the server O/S instead to make sure that you do not fill up a disk or partition. What is the best solution for us??


Thanks all
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 18070081
>>What is the best solution for us??

There's no right answer here.  Everyones business rules are different.  I use autoextending tablespaces myself because it works for me (and I have tons of disk space).  Since you have problems doing it manually, you might have bigger issues:  When you rezize it manually are you not giving it enough space?  What is causing this tablespace fo fill up rapidly?  If you are having some growth problems that you can't account for then setting autoextend on may cause bigger problems than it solves.

You also need to be careful on setting this on certain tablespaces.  For example, setting TEMP to autoextend can be dangerous.  A single developer running a massive cartesian query can cause TEMP to autoextend itself right out of disk space.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 100 total points
ID: 18070577
I agree, there is no "best solution" that works for all businesses.  For the TEMP tablespace(s) as slightwv mentioned, I really do not like autoextend.  I generally don't use autoextend for table or index tablespaces either, because that means you then have to monitor the space in the O/S, and at that level you have no idea why (or which table or index in Oracle) is causing the Oracle tablespace to autoextend.  But, if you have lots of disk space, and if your tablespaces are well below the size limit for your O/S and/or version of Oracle, you may find that setting "autoextend" on for table and index tablespaces can work well.

0
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 18070600
I suggest you monitor the activity of the tablespace periodically and make adjustments accordingly. Like others have suggested setting tablespace with AUTOEXTEND ON is not a good practise.

Do you mind telling us what type of table this index is created on? What is the function of the table in your application? Is it data holding table used during load process or temporary data staging table?
Normally indexes do not grow as fast on a table unless there is huge insert (data load) that takes place on the table.
0
 
LVL 6

Author Comment

by:camacho_marco
ID: 18071759
so the recomendation is to leave it as it is but monitor the index 1 a week?
0
 
LVL 11

Accepted Solution

by:
mohammadzahid earned 200 total points
ID: 18071807
Yes, you can use the script below to monitor tablespace growth on weekly or daily basis:

First you will need to create a table that serve as a repository for space management.

sql> create tablespace space_admin
extent management local
segment space management auto
/

sql> create table space_stats_data
(run_dttm date,
total_Space number,
used_space number,
avail_space number)
tablespace space_admin
/

/******** collect measurement
insert into space_stats_data
  select sysdate,
           tot.total,
          (tot.total - free.free_Space) used_Space,
          free.free_space free_space
from   (select ceil(sum(bytes) / 1024/1024) total
         from sys.dba_Data_files ) tot,
         (select ceil(sum(bytes)/1024/1024) free_space
         from sys.dba_free_space
) free
/

commit;

select * from space_admin

Export data to a csv file and build a graph. Try it out.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18071809
Unless you give us some more specific information (like: the size of the tablespace now; the size of the index(es); the number of records in the table(s) now; the number of new records that are added each week; the total size of the disk or partition; the free space on the disk or partition now) that may be the best recommendation we can give you for now.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18071897
I'm not going to re-read all the posts but I have a guess at the underlying problem that I don't recall someone mentioning:  You might check to see if you have a table or 12 in that tablespace (or some other object that's not an index).
0
 
LVL 6

Author Comment

by:camacho_marco
ID: 18081702
Hi All,

 Thanks 4 your help, markgeer: i will post all the info you are requesting tomorrow.


CHeers
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18230959
I recommend a split between: slightwv, mohammadzahid and myself, since we all provided information (that I think was helpful) for this problem.
0
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 18230997
I totally agree with markgeer.
0
 
LVL 6

Author Comment

by:camacho_marco
ID: 18233255
Sorry for the delay i will split the point with all of you

Thanks 4 all your time and Helpr.


Cheers
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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 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

18 Experts available now in Live!

Get 1:1 Help Now