Index size is too big.

Hokins

Hi ,

I am using a combined(3 fields) unique index (Normal) on a table which has around 17 M records.
The size if index is 6074 MB. Due to which I/O and cluster wait for index is consuming 90% of time spent on many queries. So help me find a solution.

Thanks..

Galbarad

hi

maybe you need partitioning your table
and create local index in each partitions?

http://docs.oracle.com/cd/B10501_01/server.920/a96524/c12parti.htm

good luck

Jonathan Lewis

Hokins wrote:

I am using a combined(3 fields) unique index (Normal) on a table which has around 17 M records.
The size if index is 6074 MB. Due to which I/O and cluster wait for index is consuming 90% of time spent on many queries. So help me find a solution.

Points to consider:

With these figures the average size of an index entry seems to be around 300 to 350 bytes - is this consistent with your knowledge of the data (particularly the avg_col_len for the 3 columns, plus overheads of around 15 bytes).

If the index is much larger than expected (say more than twice the size) is there an ongoing reason why it should be behaving in this way, or is it possible that some very large-scale deletions have left lots of empty space in the index that is slow in being re-used.

Is the time lost on queries, light-weight (OLTP) DML, or batch DML ? How are you demonstrating to yourself that it is the size of the index that is causing problems, rather than the way in which the index is being used ?

Regards
Jonathan Lewis

Hokins

Hi,

Well the 3 fields are 2 X number, 1 X varchar2(25). Which is much less than 300 bytes.

No large scale deletion is going on in the DB.

Query which i have seen is a select statement. No idea why there is lot of cluster wait and I/O.

sb92075

Hokins wrote:
Hi ,

I am using a combined(3 fields) unique index (Normal) on a table which has around 17 M records.
The size if index is 6074 MB. Due to which I/O and cluster wait for index is consuming 90% of time spent on many queries. So help me find a solution.

post SQL & results that show above is true.

Jonathan Lewis

Hokins wrote:
Hi,

Well the 3 fields are 2 X number, 1 X varchar2(25). Which is much less than 300 bytes.

If your two numbers were calculated and you hadn't set a precision on the columns then an index entry could take about 87 bytes; if you had then run into the leaf block split concurrent problem that could make the index four times the minimum size - which fits the numbers you've given. But I'm only pointing that out to give you some idea of how much information you haven't given us.

No large scale deletion is going on in the DB.

Has it done so in the past

>

Query which i have seen is a select statement. No idea why there is lot of cluster wait and I/O.

Some select just have to do a lot of I/O - but you haven't given us any clues about what the query is doing and what might be reasonable.


Bottom line - it sounds as if your index is much larger than it reasonably ought to be, and you ought to find out why. Once you've found out why, and addressed the problem, you may still see more I/O and cluster wait than you want to.

Regards
Jonathan Lewis

946222

Besides the suggestions already given you may want to consider using index compression. If you want to give compression a try, you should first analyze the index to see if compression gives a desired result. This can be accomplished by issuing following the command:

ANALYZE INDEX index_name VALIDATE STRUCTURE;

Then you should run the following query:

SELECT * FROM index_stats;

and look at the columns OPT_CMPR_COUNT and OPT_CMPR_PCTSAVE. The OPT_CMPR_COUNT will give will the number of columns that Oracle recommends you to compress, which is from 0 to n-1 on unique indexes, begin 'n' the number of columns in the index; and OPT_CMPR_PCTSAVE will give you the expected percentage of saving from the compression.

If this query return 0 for any of these columns you won't have any savings by doing compression so don't have the trouble. But if it says that you could achieve a good compression you can compress your index using the following statement:

ALTER INDEX index_name REBUILD COMPRESS n;

Where 'n' in the query above is the number of columns suggested by OPT_CMPR_COUNT.

Edited by: Paulo Petruzalek on 23/07/2012 05:47

Edited by: Paulo Petruzalek on 23/07/2012 05:47