Les09-高级索引.pptVIP

Advanced Indexes Objectives After completing this lesson, you should be able to do the following: Create bitmap indexes Identify bitmap index operations Specify bitmap index hints Use star transformations Create function-based indexes View data dictionary information Bitmap Indexes Compared with regular B*-tree indexes, bitmap indexes are faster and use less space forlow-cardinality columns. Each bitmap index comprises storage pieces called bitmaps. Each bitmap contains information about a particular value for each of the indexed columns. Bitmaps are compressed and stored in a B*-tree structure. Bitmap Index Structure Each position in a bitmap stores information about a particular row. Creating Bitmap Indexes Full Notes Page Using Bitmap Indexes for Queries Combining Bitmap Indexes Due to fast bit-and, bit-minus, and bit-or operations, bitmap indexes are efficient: When using IN (value_list) When predicates are combined with AND/OR When to Use Bitmap Indexes Use bitmap indexes for: Columns with low cardinality Columns that are frequently used in: Complex WHERE clause conditions Group functions (such as COUNT and SUM) Very large tables DSS systems with many ad hoc queries and few concurrent DML changes Advantages of Bitmap Indexes When used appropriately, bitmap indexes provide: Reduced response time for many ad hoc queries Substantial reduction of space usage compared to other indexing techniques Dramatic performance gains (even on low-end hardware) Bitmap Index Guidelines Reduce bitmap storage by: Declaring columns NOT NULL when possible Using fixed-length data types when feasible Using the command:ALTER TABLE … MINIMIZE RECORDS_PER_BLOCK Improve bitmap performance by increasing the following parameters: CREATE_BITMAP_AREA_SIZE (default 8 MB) BITMAP_MERGE_AREA_SIZE (default 1 MB) What Is a Bitmap Join Index? Bitmap Join Index No join with the CUSTOMERS table is needed. Only the index and the SALES table are used to evaluate the following query. Bitmap Join Ind