Forum Stats

  • 3,751,478 Users
  • 2,250,366 Discussions
  • 7,867,435 Comments

Discussions

Index size is too big.

Hokins
Hokins Member Posts: 20
edited Jul 23, 2012 8:47AM in SQL & PL/SQL
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..
Tagged:

Answers

  • Galbarad
    Galbarad Member Posts: 82
    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
    Jonathan Lewis Member Posts: 9,729 Gold Crown
    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
    Hokins Member Posts: 20
    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
    sb92075 Member Posts: 42,196 Blue Diamond
    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
    Jonathan Lewis Member Posts: 9,729 Gold Crown
    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
    946222 Member Posts: 43
    edited Jul 23, 2012 8:47AM
    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
This discussion has been closed.