• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1578
  • Last Modified:

ORACLE INDEX TABLES

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
camacho_marco
Asked:
camacho_marco
  • 8
  • 8
  • 7
  • +2
3 Solutions
 
mohammadzahidCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
I don't understand the question either.  What are you trying to do manually now?
0
 
camacho_marcoAuthor Commented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Mark GeerlingsDatabase AdministratorCommented:
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
 
mohammadzahidCommented:
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
 
camacho_marcoAuthor Commented:
Thanks 4 the info guys, and how do you deploy the index organization tables???
0
 
mohammadzahidCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
camacho_marcoAuthor Commented:
We are running Oracle 9i, and no a am not a DB expert so i am learnig.

Thanks 4 your help
0
 
camacho_marcoAuthor Commented:
Markgree can you post the diferences???
0
 
mohammadzahidCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
camacho_marcoAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
mohammadzahidCommented:
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
 
camacho_marcoAuthor Commented:
so the recomendation is to leave it as it is but monitor the index 1 a week?
0
 
mohammadzahidCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
camacho_marcoAuthor Commented:
Hi All,

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


CHeers
0
 
Mark GeerlingsDatabase AdministratorCommented:
I recommend a split between: slightwv, mohammadzahid and myself, since we all provided information (that I think was helpful) for this problem.
0
 
mohammadzahidCommented:
I totally agree with markgeer.
0
 
camacho_marcoAuthor Commented:
Sorry for the delay i will split the point with all of you

Thanks 4 all your time and Helpr.


Cheers
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 8
  • 8
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now