Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Location
    India
    Posts
    217

    Unanswered: Index for low cardinality column.

    I have a huge table, in which a particular column have duplicate values. If i make a distinct
    on this column i will get less than 10 values.

    I need to create an index on this column. Since there are lot of insert/updates on this table i
    don't want to create a bit map index.

    What other index can i create in the above case?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    A regular index, but whether it would be of any use depends on the distribution of the data and the "clustering factor" of the index. The optimizer is quite likely to prefer a full scan over this index in many/most cases.

    Distribution: Suppose your data looks like this:

    Code:
    select col, count(*) from mytable group by col;
    
    col count(*)
    --- --------
    A   736383
    B   756339
    C   456585
    D      112
    ...
    Here the optimizer might choose to use the index for a query "where col='D'" but not for "where col='A'".

    Clustering: If in the example above, all the 'A' rows are physically close together in the table, and ditto for other values, then the optimizer may use the index because it expects to only have to visit a small proportion of the blocks in the table. But if they are "all over the place", then it may have to visit most of the blocks and so may as well do a full scan.

    High clustering factor:
    Code:
    Block 1       Block 2           Block N
    ------------- ------------- ... ---------------
    AAAAAAAAAABBB BBBBBBBCCCCCC     IIJJJJJJJJJJJJJ
    ('A's found in 1/N blocks)

    Low clustering factor:
    Code:
    Block 1       Block 2           Block N
    ------------- ------------- ... ---------------
    AABCHJDGFSJJD HSDJDAAHHDGHD     DDHDDDFIAACDEAJ
    ('A's found in many blocks)

  3. #3
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    Thank you for the detailed explanation!
    Is IOT is the only way to make sure that the data will be stored in consequent blocks?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Clustered tables would be another way. Read all about both here.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One minor point but would a normal index actually help? A full table scan may be better

    Alan

  6. #6
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    Quote Originally Posted by AlanP
    One minor point but would a normal index actually help? A full table scan may be better

    Alan
    yeah, I agree. Really depends on your data distribution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •