Indexing%20the%20Warehouse%20summary

1
Indexing the Warehouse summary
2
What is an index?

• Structure separate from the table data it refers
  to, storing the location of rows in the database
  based on the column values specified when the
  index is created.
• They are used in data warehouse to improve
  warehouse throughput

3
What columns to index?

• Selectivity measurment of the number of
  distinct values in a table column compared to the
  number of rows in the whole table.
• selectivity (rows in table / distinct values )
  (1 / rows in table) 1 / distinct values
• Selection criteria specify which rows of
  information are to be included in the query
  result set. First criterion starts with the where
  keyword, all subsequent starts with the keyword
  and.
• Predicate of SQL statement the part where the
  selection criteria are specified.

4
Selectivity consideration

• When the column value is found in less than 5
  (selectivity lt5) of all the rows in a table
  good candidate for an index
• Example
• rows in table NUM_ROWS from USER_TABLES
• 49 000
• distinct values NUM_DISTINCT from
  USER_TAB_COLUMNS 7 8
• 1/8 0.125 or 12.5

5
Mentioned in predicate consideration

• Columns that are commonly parts of query
  selection criteria are good candidates.
• Example
• select sum(aggr_day), region
• from day_summary, region
• where trans_date between 01-JAN-1999 and
• 31-DEC-1999

6
Index suppresion

• If a function is to be performed on the column,
  the columns index is not used.
• Example
•  
• select ....
• from day_summary
• where to_char (trans_date)
•  
• If a column is used in a predicate with and
  without function performed on, it may still be a
  candidate for an index. Analyze the number of SQL
  statements using function. Implement index if it
  optimizes the stastement without function.

7
Types of indexes

• Single-Column Index
• create index purchase_1
• on purchase (purchase_id) ...
•  
• Composite Index (concatenated)
• create index purchase_2
• on purchase (cust_id, purchase_date,
  total_amount) ...

8
Oracle indexing approaches

• B-tree indexes
• branch blocks or upper level blocks point to the
  corresponding lower-level blocks
• leaf blocks contain the Oracle ROWID that points
  at the location of the actual row the leaf refers
  to
•  
• Why is it so popular in Oracle products?
• simplicity
• easy to maintain
• high retrieval speed of highly selective column
  values (high cardinality)
• the size of the table has little or no impact on
  the speed with which B-tree indexed data can be
  fetched

9
Where does it work best?

• select ... where colA ABC
• select ... where colA between A12 and R45

10
Bitmap index

• for columns with very few unique values (low
  cardinality)
• built for one column at a time
• stream of bits each bit relates to a column
  value in a single row of table

11
Bitmap index

• create bitmap index person_region on person
  (region)
• Row Region North bitmap East bitmap West bitmap
  South bitmap
• 1 North 1 0 0 0
• 2 East 0 1 0 0
• 3 West 0 0 1 0
• 4 West 0 0 1 0
• 5 South 0 0 0 1
• 6 North 1 0 0 0
• When to use it
• tables that have no or little insert/update
  are good candidates (static data in warehouse)
• columns that have low cardinality are good
  candidates (if the cardinality of a column is lt
  0.1 that the column is ideal candidate,
  consider also 0.2 1)

12
Bitmap vs. the B-tree

• Unique col. Values Card. () B-tree
  space Bitmap space
• 500,000 50.00 15.29 12.35
• 100,000 10.00 15.21 5.25
• 10,000 1.00 14.34 2.99
• 100 0.01 13.40 1.38
• 5 lt0.01 13.40 0.78

13
Indexes on Partitioned Tables

• Local index built on a partitioned table when
  the partition keys of the index match those of
  table (easy to manage)
• Global index built on a partitioned table with
  a different set of partition keyst than the
  corresponding data segment
• Prefixed index leftmost column in a partitioned
  index match the leftmost column in the indexs
  partition key
• Nonprefixed index leftmost column in a
  partitioned index differs the leftmost column in
  the indexs partition key

14
Index-organized tables

• Traditional database have a data segment and an
  index segment
• Index-organized tables merge the two traditional
  segments into one.
• The data is the index and the index is the data.

15
Index-organized tables

• Defining
• create table country (
• county_code varchar2(3),
• constraint country_pk primary key
  (country_code))
• organization index tablespace misc_look
• pcttreshold 20 overflow tablespace
  misc_lok_overflow
• Benefits
• faster retrival time to get to the data
• Where to use it
• code tables
• applications that support text searches on
  document collections
• spatial applications
• OLAP applications

16
Index creation and maintenance guidelines

• load data then create indexes
• allocate large sort space for index creation
• use big temporary workspace
• do not be affraid of overindexing minimum
  retrieval time for query processing is crucial in
  the data warehouse