Skip to main content

Ryan Lynch's Library tagged reference →  View Popular

Oracle 10g Database Concepts: Schema Objects

  • Bitmap Index Example


    Table 5-1 shows a portion of a company's customer data.



    Table 5-1 Bitmap Index Example











































    MARITAL_STATUS, REGION, GENDER, and INCOME_LEVEL are all low-cardinality columns. There are only three possible values for marital status and region, two possible values for gender, and four for income level. Therefore, it is appropriate to create bitmap indexes on these columns. A bitmap index should not be created on CUSTOMER# because this is a high-cardinality column. Instead, use a unique B-tree index on this column to provide the most efficient representation and retrieval.


    Table 5-2 illustrates the bitmap index for the REGION column in this example. It consists of three separate bitmaps, one for each region.



    Table 5-2 Sample Bitmap


    CUSTOMER #MARITAL_ STATUSREGIONGENDERINCOME_ LEVEL
    101singleeastmalebracket_1
    102marriedcentralfemalebracket_4
    103marriedwestfemalebracket_2
    104divorcedwestmalebracket_4
    105singlecentralfemalebracket_2
    106marriedcentralfemalebracket_3



























    Each entry or bit in the bitmap corresponds to a single row of the CUSTOMER table. The value of each bit depends upon the values of the corresponding row in the table. For instance, the bitmap REGION='east' contains a one as its first bit. This is because the region is east in the first row of the CUSTOMER table. The bitmap REGION='east' has a zero for its other bits because none of the other rows of the table contain east as their value for REGION.


    An analyst investigating demographic trends of the company's customers can ask, "How many of our married customers live in the central or west regions?" This corresponds to the following SQL query:


    SELECT COUNT(*) FROM CUSTOMER  
        WHERE MARITAL_STATUS = 'married' AND REGION IN ('central','west');



    Bitmap indexes can process this query with great efficiency by counting the number of ones in the resulting bitmap, as illustrated in Figure 5-8. To identify the specific customers who satisfy the criteria, the resulting bitmap can be used to access the table.

    • Index-Organized Table Applications


      The superior query performance for primary key based access, high availability aspects, and reduced storage requirements make index-organized tables ideal for the following kinds of applications:



      • Online transaction processing (OLTP)


      • Internet (for example, search engines and portals)


      • E-commerce (for example, electronic stores and catalogs)


      • Data warehousing


      • Analytic functions

  • 1 more annotations...
    • 1 - 20 of 50 Next › Last »
    Showing 20▼ items per page

    Selected Tags

    Related Tags

    Sponsored Links

    Ads by Google

    Diigo is about better ways to research, share and collaborate on information. Learn more »

    Join Diigo
    REGION='east'REGION='central'REGION='west'
    100
    010
    001
    001
    010
    010