12 Advanced Indexes Copyright 2005 Oracle All rights

Objectives After completing this lesson, you should be able to do the following: •

Bitmap Indexes • • 12 -3 Compared with regular B*-tree indexes, bitmap indexes are

Bitmap Index Structure Each position in a bitmap stores information about a particular row.

Creating Bitmap Indexes SQL> CREATE BITMAP INDEX prod_supplier_id 2 ON sh. products (supplier_id); 12

Using Bitmap Indexes for Queries SQL> SELECT * 2 FROM products 3 WHERE supplier_id

Combining Bitmap Indexes Due to fast bit-and, bit-minus, and bit-or operations, bitmap indexes are

When to Use Bitmap Indexes Use bitmap indexes for: • Columns with low cardinality

Advantages of Bitmap Indexes When used appropriately, bitmap indexes provide: • Reduced response time

Bitmap Index Guidelines • Reduce bitmap storage by: – Declaring columns NOT NULL when

What Is a Bitmap Join Index? Sales Customers CREATE ON FROM WHERE 12 -12

Bitmap Join Index • • No join with the CUSTOMERS table is needed. Only

Bitmap Join Index: Advantages and Disadvantages • Advantages: – Good performance for join queries;

Indexes and Row Access Methods • Access methods discussed in an earlier lesson: –

Index Hints 12 -17 INDEX Scans an index in ascending order INDEX_ASC Scans an

INDEX_COMBINE Hint: Example SQL> 2 3 4 5 6 7 12 -18 SELECT --+INDEX_COMBINE(CUSTOMERS)

INDEX_COMBINE Hint: Example Execution Plan ----------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=491 Card=10481 Bytes =167696) 1

Star Transformation Dimension tables PRODUCTS SALES Facts table CHANNELS 12 -20 CUSTOMERS PROMOTIONS Copyright

Star Transformation • Execute star queries efficiently, especially in the following cases: – The

Star Transformation: Example SQL> 2 3 4 5 6 7 8 9 10 12

Steps in Execution 1. A bitmap index is used to identify rowsets for sales

Function-Based Indexes SQL> CREATE INDEX FBI_UPPER_LASTNAME 2 ON CUSTOMERS(upper(cust_last_name)); SQL> ALTER SESSION 2 SET

Function-Based Indexes: Usage Function-based indexes: • Materialize computational-intensive expressions • Facilitate case-insensitive searches •

Data Dictionary Information SQL> 2 3 4 5 6 12 -27 SELECT , FROM

Summary In this lesson, you should have learned about: • Bitmap indexes • Indexes

Slides: 25

Download presentation

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 12 -2 Copyright © 2005, Oracle. All rights reserved.

Bitmap Indexes • • 12 -3 Compared with regular B*-tree indexes, bitmap indexes are faster and use less space for low-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. Copyright © 2005, Oracle. All rights reserved.

Creating Bitmap Indexes SQL> CREATE BITMAP INDEX prod_supplier_id 2 ON sh. products (supplier_id); 12 -5 Row value Supplier ID 1 Supplier ID 2 Supplier ID 3 '1' 1 0 0 0 '2' 0 1 0 0 '3' 0 0 1 0 '4' 0 0 0 1 '2' 0 1 0 0 '3' 0 0 1 0 . . Copyright © 2005, Oracle. All rights reserved. Supplier ID. . . 4

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 12 -8 A B A and B A or B not A 1 1 0 0 1 1 1 0 0 0 0 0 1 0 1 1 Copyright © 2005, Oracle. All rights reserved.

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) • • 12 -9 Very large tables DSS systems with many ad hoc queries and few concurrent DML changes Copyright © 2005, Oracle. All rights reserved.

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) 12 -10 Copyright © 2005, Oracle. All rights reserved.

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) 12 -11 Copyright © 2005, Oracle. All rights reserved.

What Is a Bitmap Join Index? Sales Customers CREATE ON FROM WHERE 12 -12 BITMAP INDEX cust_sales_bji sales(c. cust_city) sales s, customers c c. cust_id = s. cust_id; Copyright © 2005, Oracle. All rights reserved.

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. SELECT SUM(s. amount_sold) FROM sales s, customers c WHERE s. cust_id = c. cust_id AND c. cust_city = 'Sully'; 4 5 12 -13 Copyright © 2005, Oracle. All rights reserved.

Bitmap Join Index: Advantages and Disadvantages • Advantages: – Good performance for join queries; space efficient – Especially useful for large-dimension tables in star schemas • Disadvantages: – More indexes are required: Up to one index per dimension-table column rather than one index per dimension table is required. – Maintenance costs are higher: Building or refreshing a bitmap join index requires a join. 12 -14 Copyright © 2005, Oracle. All rights reserved.

Indexes and Row Access Methods • Access methods discussed in an earlier lesson: – – • • 12 -16 B*-tree index access Table access by ROWID B*-tree index merge Fast full index scans Index hints Star transformation Copyright © 2005, Oracle. All rights reserved.

Index Hints 12 -17 INDEX Scans an index in ascending order INDEX_ASC Scans an index in ascending order INDEX_DESC Scans an index in descending order AND_EQUAL Merges single-column indexes INDEX_COMBINE Uses bitmap indexes INDEX_FFS Performs a fast full index scan NO_INDEX Disallows using a set of indexes Copyright © 2005, Oracle. All rights reserved.

INDEX_COMBINE Hint: Example SQL> 2 3 4 5 6 7 12 -18 SELECT --+INDEX_COMBINE(CUSTOMERS) cust_last_name FROM SH. CUSTOMERS WHERE ( CUST_GENDER= 'F' AND CUST_MARITAL_STATUS = 'single') OR CUST_YEAR_OF_BIRTH BETWEEN '1917' AND '1920' Copyright © 2005, Oracle. All rights reserved.

INDEX_COMBINE Hint: Example Execution Plan ----------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=491 Card=10481 Bytes =167696) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (Cost=491 …) 2 1 BITMAP CONVERSION (TO ROWIDS) 3 2 BITMAP OR 4 3 BITMAP AND 5 4 BITMAP INDEX (SINGLE VALUE) OF 'CUST_MARITAL_BIX' 6 4 BITMAP INDEX (SINGLE VALUE) OF 'CUST_GENDER_BIX' 7 3 BITMAP MERGE 8 7 BITMAP INDEX (RANGE SCAN) OF 'CUST_YOB_BIX' 12 -19 Copyright © 2005, Oracle. All rights reserved.

Star Transformation • Execute star queries efficiently, especially in the following cases: – The number of dimension tables is large. – The fact table is sparse. – Not all dimensions have constraining predicates. • • 12 -21 Set the STAR_TRANSFORMATION_ENABLED initialization parameter. Use the STAR_TRANSFORMATION hint. Copyright © 2005, Oracle. All rights reserved.

Star Transformation: Example SQL> 2 3 4 5 6 7 8 9 10 12 -22 SELECT , FROM , WHERE AND AND AND s. amount_sold, p. prod_name ch. channel_desc sales s, products p channels ch, customers c s. prod_id= p. prod_id s. channel_id = ch. channel_id s. cust_id = c. cust_id ch. channel_id in ('I', 'P', 'S') c. cust_city = 'Asten' p. prod_id > 40000; Copyright © 2005, Oracle. All rights reserved.

Steps in Execution 1. A bitmap index is used to identify rowsets for sales in channels I, P, or S. These are combined using a bitmap OR operation. 2. A bitmap is used for rows corresponding to sales in the city of Asten. 3. A bitmap is used for rows with product ID greater than 40, 000. 4. These three bitmaps are combined into a single bitmap using the bitmap AND operation. 5. This final bitmap is used to access rows that satisfy all the conditions from the fact table. 6. These rows from the fact table are next joined to the dimension tables. 12 -23 Copyright © 2005, Oracle. All rights reserved.

Function-Based Indexes SQL> CREATE INDEX FBI_UPPER_LASTNAME 2 ON CUSTOMERS(upper(cust_last_name)); SQL> ALTER SESSION 2 SET QUERY_REWRITE_ENABLED = TRUE; SQL> SELECT * 2 FROM customers 3 WHERE UPPER(cust_last_name) = 'SMITH'; 12 -25 Copyright © 2005, Oracle. All rights reserved.

Function-Based Indexes: Usage Function-based indexes: • Materialize computational-intensive expressions • Facilitate case-insensitive searches • Provide a simple form of data compression • Can be used for an NLS sort index 12 -26 Copyright © 2005, Oracle. All rights reserved.

Data Dictionary Information SQL> 2 3 4 5 6 12 -27 SELECT , FROM , WHERE AND i. index_name, i. index_type ic. column_name, i. status user_indexes i user_ind_columns ic i. index_name = ic. index_name i. table_name='SALES'; Copyright © 2005, Oracle. All rights reserved.

Summary In this lesson, you should have learned about: • Bitmap indexes • Indexes and row-access methods • Index hints • Star transformation • Function-based indexes 12 -28 Copyright © 2005, Oracle. All rights reserved.