Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to create bitmap index?

    Hi,

    In DB2 magazine (Quarter 3, 2003) is written:
    "The B-tree index is effective only when the selectivity is higher than 0.1. If selectivity is lower than 0.1, a bitmap index will be a god choise... ...you should use bitmap indexes on low cardinality..."

    How to create a bitmap index? How to create B-tree index? How to tell the DB2 to use on of this two types of indexes?

    Thanks,
    Grofaty

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How to create bitmap index?

    When you create an index in db2 it is always a B-tree index ...

    Bitmap index is build dynamically by the optimizer if it 'thinks' that a bitmap index is an efficinet one ...

    Refer to admin guide-performance for details

    Cheers

    Sathyaram


    Originally posted by grofaty
    Hi,

    In DB2 magazine (Quarter 3, 2003) is written:
    "The B-tree index is effective only when the selectivity is higher than 0.1. If selectivity is lower than 0.1, a bitmap index will be a god choise... ...you should use bitmap indexes on low cardinality..."

    How to create a bitmap index? How to create B-tree index? How to tell the DB2 to use on of this two types of indexes?

    Thanks,
    Grofaty
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2003
    Location
    Tucuman - Argentina
    Posts
    10

    Re: How to create bitmap index?

    You can also create a permanent bitmap index with the following sentence:

    CREATE ENCODED VECTOR INDEX index-name ON table-name (field1, field2)...

    Althoug im not completly sure of the sintax i dont have my system at hand to check it.
    You could also read this excellent whitepaper for aditional info for bitmap indexes:
    http://www.ibm.com/servers/enable/site/ bi/strategy/strategy.pdf


    Originally posted by sathyaram_s
    When you create an index in db2 it is always a B-tree index ...

    Bitmap index is build dynamically by the optimizer if it 'thinks' that a bitmap index is an efficinet one ...

    Refer to admin guide-performance for details

    Cheers

    Sathyaram
    Carlos Kozuszko

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605

    Re: How to create bitmap index?

    Hi,

    Is there any data in somekind of system table to check type of index (b-tree, bitmap)?

    Thanks,
    Grofaty

    Originally posted by sathyaram_s
    When you create an index in db2 it is always a B-tree index ...

    Bitmap index is build dynamically by the optimizer if it 'thinks' that a bitmap index is an efficinet one ...

    Refer to admin guide-performance for details

    Cheers

    Sathyaram

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How to create bitmap index?

    That's interesting ...

    I thought EVI is patented by IBM but are yet to make full use of it in DB2 .. I know they use some EVI in AS/400 db2, but still, i am not sure whether they have this create encoded vector index ..

    Well, I havent read your PDF yet, don't have Acrobat Reader(shame, isn't it?) on my new desktop ..

    Cheers

    sathyaram


    Originally posted by ckozus
    You can also create a permanent bitmap index with the following sentence:

    CREATE ENCODED VECTOR INDEX index-name ON table-name (field1, field2)...

    Althoug im not completly sure of the sintax i dont have my system at hand to check it.
    You could also read this excellent whitepaper for aditional info for bitmap indexes:
    http://www.ibm.com/servers/enable/site/ bi/strategy/strategy.pdf
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605

    Re: How to create bitmap index?

    Hi,

    I red an PDF document and I have tested this SQL.
    I got error:

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "VECTOR" was found following "CREATE ENCODED ".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    Is this command available in DB2 UDB for Linux/Unix/Windows? I think this is just for AS/400 (iSeries).

    Thanks,
    Grofaty

    Originally posted by ckozus
    You can also create a permanent bitmap index with the following sentence:

    CREATE ENCODED VECTOR INDEX index-name ON table-name (field1, field2)...

    Althoug im not completly sure of the sintax i dont have my system at hand to check it.
    You could also read this excellent whitepaper for aditional info for bitmap indexes:
    http://www.ibm.com/servers/enable/site/ bi/strategy/strategy.pdf

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How to create bitmap index?

    Is this command available in DB2 UDB for Linux/Unix/Windows? I think this is just for AS/400 (iSeries).


    The statement seems to be only for iSeries DB2 ? doesn't it ?

    Cheers


    sAthyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jun 2003
    Location
    Tucuman - Argentina
    Posts
    10

    Re: How to create bitmap index?

    I really dont know if it is available in other platforms... I've tested only on iSeries, is the only version of db2 i've ever used.

    Originally posted by sathyaram_s
    Is this command available in DB2 UDB for Linux/Unix/Windows? I think this is just for AS/400 (iSeries).


    The statement seems to be only for iSeries DB2 ? doesn't it ?

    Cheers


    sAthyaram
    Carlos Kozuszko

Posting Permissions

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