ch2 data warehouse schemas

8/12/2019 CH2 Data Warehouse Schemas

http://slidepdf.com/reader/full/ch2-data-warehouse-schemas 1/25

Copyright © 2005, Oracle. All rights reserved.

Data Warehouse Schemas



2-2 Copyright © 2005, Oracle. All rights reserved.

Objectives

After completing this lesson, you should be able to do

the following:

• Explain the difference between a star schema and

a snowflake schema• Describe a star transformation

• Control initialization parameters relating to star

queries




Star Schema Model

SALES

AMOUNTCOST

QUANTITY_SOLD CHANNELS

CHANNEL_IDCHANNEL_DESCCHANNEL_CLASS

CUSTOMERS

CUST_IDCUST_GENDERCUST_CITY

TIMES

TIME_IDDAY_NAMECALENDAR_YEAR

PROMOTIONS

PROMO_ID PROMO_NAME

Dimension tables Dimension tables

Fact table

PRODUCTS

PROD_ID

PROD_NAME

PROD_DESC




Snowflake Schema Model

Dimension table

Dimension table

Dimension table

Dimension table

Fact table

CHANNELS

TIMESPRODUCTS

SALES(amount,cost,quantity_sold)

SUPPLIERS

CUSTOMERS

COUNTRIES

PROMOTIONS




Creating Dimensions

CREATE DIMENSION products_dim

LEVEL product IS(products.prod_id)

LEVEL subcategory IS(products.prod_subcategory)

LEVEL category IS(products.prod_category) ...

CATEGORY

SUBCATEGORY

PRODUCT




Creating Hierarchy Relationships

CREATE DIMENSION products_dim...HIERARCHY prod_rollup (

product CHILD OF

subcategory CHILD OFcategory) ATTRIBUTE product DETERMINES

(products.prod_name, products.prod_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, prod_status, prod_list_price,

prod_min_price) ATTRIBUTE subcategory DETERMINES(prod_subcategory, prod_subcategory_desc)

ATTRIBUTE category DETERMINES(prod_category, prod_category_desc);


http://slidepdf.com/reader/full/ch2-data-warehouse-schemas 7/252-9 Copyright © 2005, Oracle. All rights reserved.

Third Normal Form Schemas

CUSTOMERS

PRODUCTS

ORDERS

ORDERITEMS



Tuning Star Queries

• A bitmap index should be built on each of the

foreign key columns of the fact table or tables.

• Set STAR_TRANSFORMATION_ENABLED to TRUE.

–

This enables an important optimizer feature for starqueries.

– It is set to FALSE by default

for backward compatibility.

• Analyze all corresponding

objects.select ….



Star Transformation

• Star transformation is an optimization technique

that implicitly rewrites the original star query.

• A star query is processed in two phases:

–

The necessary rows are retrieved from the facttable, creating a resul t set.

– The result set is then joined to the dimension

tables.

• Star transformation requires that there be a single-

column bitmap index on every join column of thefact table.



Contrasting Star Transformation and

Conventional Joins

• Conventional joins:

– Cause each involved dimension table to be

successively joined to the fact table

– Cause costly row-culling operations for each

successive dimension table join

– Employ resource-consuming hash joins for row

culling

• Star transformation:

–Joins all involved dimension tables before the facttable is accessed

– Produces a composite set of row IDs before

accessing the fact table

– Employs more efficient Boolean join operations



Star Transformation Hints

• STAR_TRANSFORMATION hint: Use the best plan

containing a star transformation, if there is one.

• FACT(<table_name>) hint: The hinted table

should be considered as the fact table in thecontext of a star transformation query.

• NO_FACT (<table_name>) hint: The hinted table

should not be considered as the fact table in the

context of a star transformation.

• FACT and NO_FACT hints are useful for startransformation queries containing more than one

fact table.



Star Transformation: Rewrite Example

SELECT s.amount_soldFROM sales s

WHERE time_id IN (SELECT time_idFROM times

WHERE calendar_quarter_descIN('1999-Q1','1999-Q2'))

AND cust_id IN (SELECT cust_idFROM customers

WHERE cust_state_province =

'CA') AND channel_id IN(SELECT channel_idFROM channels

WHERE channel_desc IN('Internet','Catalog'));



Star Transformation Execution Plan

SORT GROUP BYHASH JOIN

HASH JOINTABLE ACCESS BY INDEX ROWID SALESBITMAP CONVERSION TO ROWIDSBITMAP AND

BITMAP MERGEBITMAP KEY ITERATIONBUFFER SORTTABLE ACCESS FULL CHANNELSBITMAP INDEX RANGE SCAN SALES_CHANNELS_BX

BITMAP MERGE

BITMAP KEY ITERATIONBUFFER SORTTABLE ACCESS FULL TIMESBITMAP INDEX RANGE SCAN SALES_TIMES_BX

TABLE ACCESS FULL CHANNELSTABLE ACCESS FULL TIMES



Retrieving Fact Rows from One Dimension

BITMAPKEY

ITERATION

Dimension

table

access

Fact table

bitmap

access

BITMAP MERGE

One bitmap

is

produced.



Retrieving Fact Rows from All Dimensions

…

Multiple bitmaps

are

ANDed together.

Result set

MERGE i

…

BITMAP AND

MERGE 1 MERGE n

BITMAP

conversion

to row IDs



Joining the Result Set with Dimensions

HASH JOIN

Dimension 1

table

access

Fact table

access

from

result set

Dimension n

tableaccess

HASH JOIN

Dimension i

table

access

HASH JOIN



Star Transformation: Further Optimization

• In a star transformation execution plan, dimension

tables are accessed twice: once for each phase.

• This may be a performance issue with big

dimension tables, and bad selectivity.

• If the cost is cheaper, a temporary table may be

created and used instead of accessing the same

dimension table twice.

– Done automatically, transparent to the end-user

• The following PLAN_TABLE columns showtemporary tables information:

– OBJECT_NAME

– OTHER



Bitmap Index Storage Performance

For each existing

bitmap index

SetCOMPATIBLE

to 10.2.0.0

Severe

slowdown before raisingCOMPATIBLE?

Rebuild

Slowdown

after raisingCOMPATIBLE? Yes

Yes

Create new

bitmap indexes



Static Partition Pruning and Star Query

• Pruning is determined at parse time.

• In most cases, fact tables are never directly

filtered.

SELECT prod_subcategory, cust_city,sum(amount_sold)

FROM sales s, products p, customers c WHERE s.prod_id = p.prod_id AND

s.cust_id = c.cust_id AND prod_category IN ('Men','Women') ANDcust_year_of_birth ='1968' ANDs.time_id BETWEENTO_DATE('12.01.1999','mm.dd.yyyy') ANDTO_DATE('12.31.1999','mm.dd.yyyy')

GROUP BY prod_subcategory,cust_city;



Static Partition Pruning Plan: Example

SORT (GROUP BY)HASH JOINHASH JOINTABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SALES' [12]BITMAP CONVERSION (TO ROWIDS)BITMAP AND

BITMAP MERGEBITMAP KEY ITERATIONTABLE ACCESS FULL OF 'CUSTOMERS'BITMAP INDEX (RANGE SCAN) OF 'SALES_C_BX' [12]

BITMAP MERGEBITMAP INDEX (RANGE SCAN) OF 'SALES_T_BX' [12]BITMAP MERGE

BITMAP KEY ITERATIONTABLE ACCESS (FULL) OF 'PRODUCTS'BITMAP INDEX (RANGE SCAN) OF 'SALES_P_BX' [12]

TABLE ACCESS (FULL) OF 'CUSTOMERS'TABLE ACCESS (FULL) OF 'PRODUCTS'



Dynamic Partition Pruning and Star Query

• Pruning is determined at run time.

• Commonly used query form:

SELECT prod_subcategory, sum(amount_sold)

FROM sales s, products p, times t WHERE s.prod_id = p.prod_id AND

s.time_id = t.time_id ANDt.calendar_month_number = 12 ANDt.calendar_year = 1999 AND

prod_category IN ('Men','Women')GROUP BY prod_subcategory;



Dynamic Partition Pruning Determination

• During execution, the list of eligible partitions is

computed.

• The corresponding recursive SQL can be retrievedfrom the OTHER column of PLAN_TABLE.

SELECT DISTINCT TBL$OR$IDX$PART$NUM("SALES",0,d#,p#,"TIME_ID")

FROM (SELECT /*+ SEMIJOIN_DRIVER */ T.TIME_IDFROM TIMES T

WHERE T.CALENDAR_MONTH_NUMBER = 12 ANDT.CALENDAR_YEAR=1999)

ORDER BY 1;



Summary

In this lesson, you should have learned how to:

• Distinguish star schemas and snowflake schemas

• Interpret a star transformation execution plan

•

Set the STAR_TRANSFORMATION initializationparameter

• Use dynamic partition pruning



Practice 2: Overview

This practice covers the following topics:

• Analyzing star transformation execution plans

• Understanding temporary table optimization for

star transformation

ch2 data warehouse schemas

Documents