1 / 25

Advanced Indexes

This lesson covers how to create and use bitmap indexes, including operations, hints, and transformations. It also discusses function-based indexes and data dictionary information.

trickman
Download Presentation

Advanced Indexes

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Advanced Indexes

  2. 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

  3. 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.

  4. Bitmap Index Structure • Each position in a bitmap stores information about a particular row. 0 Row #1 Row #2 1 Row #3 1 0 1 1 0

  5. Creating Bitmap Indexes SQL> CREATE BITMAP INDEX prod_supplier_id 2 ON sh.products (supplier_id); Supplier ID 1 Supplier ID 2 Supplier ID 3 Supplier ID 4 ... Rowvalue '1' '2' '3' '4' '2' '3' . . 0 1 0 0 1 0 . . 1 0 0 0 0 0 . . 0 0 1 0 0 1 . . 0 0 0 1 0 0 . .

  6. Using Bitmap Indexes for Queries Supplier ID 3 SQL> SELECT * 2 FROM products 3 WHERE supplier_id = 3; 0 0 1 0 0 1 . . Rowsreturned

  7. 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 A and B A or B not A A B 1 0 0 0 0 1 1 1 0 1 0 1 0 1 1 1 0 1 0 0 1 1 0 0 1

  8. 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

  9. 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)

  10. Bitmap Index Guidelines • Reduce bitmap storage by: • Declaring columns NOTNULL 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)

  11. What Is a Bitmap Join Index? Sales Customers CREATE BITMAP INDEX cust_sales_bji ON sales(c.cust_city) FROM sales s, customers c WHERE c.cust_id = s.cust_id;

  12. 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'; 54

  13. 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.

  14. Indexes and Row Access Methods • Access methods discussed in an earlier lesson: • B*-tree index access • Table access by ROWID • B*-tree index merge • Fast full index scans • Index hints • Star transformation

  15. Index Hints INDEX INDEX_ASC INDEX_DESC AND_EQUAL INDEX_COMBINE INDEX_FFS NO_INDEX Scans an index in ascending order Scans an index in ascending order Scans an index in descending order Merges single-column indexes Uses bitmap indexes Performs a fast full index scan Disallows using a set of indexes

  16. INDEX_COMBINE Hint: Example SQL> SELECT --+INDEX_COMBINE(CUSTOMERS) 2 cust_last_name 3 FROM SH.CUSTOMERS 4 WHERE ( CUST_GENDER= 'F' AND 5 CUST_MARITAL_STATUS = 'single') 6 OR CUST_YEAR_OF_BIRTH BETWEEN '1917' 7 AND '1920'

  17. 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 • 7 BITMAP INDEX (RANGE SCAN) OF 'CUST_YOB_BIX'

  18. Star Transformation Dimension tables PRODUCTS CUSTOMERS SALES Facts table CHANNELS PROMOTIONS TIMES

  19. 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. • Set the STAR_TRANSFORMATION_ENABLED initialization parameter. • Use the STAR_TRANSFORMATION hint.

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

  21. 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.

  22. 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';

  23. 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

  24. Data Dictionary Information SQL> SELECT i.index_name, i.index_type 2 , ic.column_name, i.status 3 FROM user_indexes i 4 , user_ind_columns ic 5 WHERE i.index_name = ic.index_name 6 AND i.table_name='SALES';

  25. Summary • In this lesson, you should have learned about: • Bitmap indexes • Indexes and row-access methods • Index hints • Star transformation • Function-based indexes

More Related