DataWarehouseSchemasObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:ExplainthedifferencebetweenastarschemaandasnowflakeschemaDescribeastartransformationControlinitializationparametersrelatingtostarqueriesStarSchemaModel

SALESAMOUNTCOSTQUANTITY_SOLDCHANNELSCHANNEL_IDCHANNEL_DESCCHANNEL_CLASSCUSTOMERSCUST_IDCUST_GENDERCUST_CITY

TIMESTIME_IDDAY_NAMECALENDAR_YEARPROMOTIONSPROMO_ID

PROMO_NAME

DimensiontablesDimensiontablesFacttablePRODUCTS

PROD_IDPROD_NAMEPROD_DESCSnowflakeSchemaModelDimensiontableDimensiontableDimensiontableDimensiontableFacttableCHANNELSTIMESPRODUCTSSALES(amount,cost,

quantity_sold)SUPPLIERSCUSTOMERSCOUNTRIESPROMOTIONSCreatingDimensionsCREATEDIMENSIONproducts_dimLEVELproductIS(d_id)LEVELsubcategoryIS(d_subcategory)LEVELcategoryIS(d_category)...CATEGORYSUBCATEGORYPRODUCTCreatingHierarchyRelationshipsCREATEDIMENSIONproducts_dim...HIERARCHYprod_rollup(productCHILDOFsubcategoryCHILDOFcategory)ATTRIBUTEproductDETERMINES(d_name,d_desc,prod_weight_class,prod_unit_of_measure,prod_pack_size,prod_status,prod_list_price,

prod_min_price)ATTRIBUTEsubcategoryDETERMINES(prod_subcategory,prod_subcategory_desc)ATTRIBUTEcategoryDETERMINES(prod_category,prod_category_desc);ThirdNormalFormSchemasCUSTOMERSPRODUCTSORDERSORDER

ITEMSTuningStarQueriesAbitmapindexshouldbebuiltoneachoftheforeignkeycolumnsofthefacttableortables.SetSTAR_TRANSFORMATION_ENABLEDtoTRUE.Thisenablesanimportantoptimizerfeatureforstarqueries.ItissettoFALSEbydefault

forbackwardcompatibility.Analyzeallcorresponding

objects.select

….StarTransformationStartransformationisanoptimizationtechniquethatimplicitlyrewritestheoriginalstarquery.Astarqueryisprocessedintwophases:Thenecessaryrowsareretrievedfromthefacttable,creatingaresultset.Theresultsetisthenjoinedtothedimensiontables.Startransformationrequiresthattherebeasingle-columnbitmapindexoneveryjoincolumnofthefacttable.ContrastingStarTransformationandConventionalJoinsConventionaljoins:CauseeachinvolveddimensiontabletobesuccessivelyjoinedtothefacttableCausecostlyrow-cullingoperationsforeachsuccessivedimensiontablejoinEmployresource-consuminghashjoinsforrowcullingStartransformation:JoinsallinvolveddimensiontablesbeforethefacttableisaccessedProducesacompositesetofrowIDsbeforeaccessingthefacttableEmploysmoreefficientBooleanjoinoperationsStarTransformationHintsSTAR_TRANSFORMATIONhint:Usethebestplancontainingastartransformation,ifthereisone.FACT(<table_name>)hint:Thehintedtableshouldbeconsideredasthefacttableinthecontextofastartransformationquery.NO_FACT(<table_name>)hint:Thehintedtableshouldnotbeconsideredasthefacttableinthecontextofastartransformation.FACTandNO_FACThintsareusefulforstartransformationqueriescontainingmorethanonefacttable.StarQuery:ExampleSELECTch.channel_class,c.cust_city,t.calendar_quarter_desc,SUM(s.amount_sold)sales_amountFROMsaless,timest,customersc,channelschWHEREs.time_id=t.time_idANDs.cust_id=c.cust_idANDs.channel_id=ch.channel_idANDc.cust_state_province='CA'ANDch.channel_descIN('Internet','Catalog')ANDt.calendar_quarter_descIN('1999-Q1','1999-Q2')GROUPBYch.channel_class,c.cust_city,t.calendar_quarter_desc;StarTransformation:RewriteExampleSELECTs.amount_soldFROMsalessWHEREtime_idIN(SELECTtime_idFROMtimesWHEREcalendar_quarter_descIN('1999-Q1','1999-Q2'))ANDcust_idIN(SELECTcust_idFROMcustomersWHEREcust_state_province='CA')ANDchannel_idIN(SELECTchannel_idFROMchannelsWHEREchannel_descIN('Internet','Catalog'));StarTransformationExecutionPlanSORTGROUPBYHASHJOINHASHJOINTABLEACCESSBYINDEXROWIDSALESBITMAPCONVERSIONTOROWIDSBITMAPANDBITMAPMERGEBITMAPKEYITERATIONBUFFERSORTTABLEACCESSFULLCHANNELSBITMAPINDEXRANGESCANSALES_CHANNELS_BXBITMAPMERGEBITMAPKEYITERATIONBUFFERSORTTABLEACCESSFULLTIMESBITMAPINDEXRANGESCANSALES_TIMES_BXTABLEACCESSFULLCHANNELSTABLEACCESSFULLTIMESRetrievingFactRowsfromOneDimensionBITMAPKEYITERATIONDimensiontableaccessFacttablebitmapaccessBITMAPMERGEOnebitmapisproduced.RetrievingFactRowsfromAllDimensions…MultiplebitmapsareANDedtogether.ResultsetMERGEi…BITMAPANDMERGE1MERGEnBITMAP

conversiontorowIDsJoiningtheResultSetwithDimensionsHASHJOINDimension1tableaccessFacttableaccessfromresultsetDimensionntableaccessHASHJOINDimensionitableaccessHASHJOINStarTransformation:FurtherOptimizationInastartransformationexecutionplan,dimensiontablesareaccessedtwice:onceforeachphase.Thismaybeaperformanceissuewithbigdimensiontables,andbadselectivity.Ifthecostischeaper,atemporarytablemaybecreatedandusedinsteadofaccessingthesamedimensiontabletwice.Doneautomatically,transparenttotheend-userThefollowingPLAN_TABLEcolumnsshowtemporarytablesinformation:OBJECT_NAMEOTHERBitmapIndexStoragePerformanceForeachexisting

bitmapindexSet

COMPATIBLE

toSevere

slowdownbeforeraising

COMPATIBLE?RebuildSlowdown

afterraising

COMPATIBLE?YesYesCreatenew

bitmapindexesStaticPartitionPruningandStarQueryPruningisdeterminedatparsetime.Inmostcases,facttablesareneverdirectlyfiltered.SELECTprod_subcategory,cust_city,sum(amount_sold)FROMsaless,productsp,customerscWHEREd_id=d_idANDs.cust_id=c.cust_idANDprod_categoryIN('Men','Women')ANDcust_year_of_birth='1968'ANDs.time_idBETWEENTO_DATE('12.01.1999','mm.dd.yyyy')ANDTO_DATE('12.31.1999','mm.dd.yyyy')GROUPBYprod_subcategory,cust_city;StaticPartitionPruningPlan:ExampleSORT(GROUPBY)HASHJOINHASHJOINTABLEACCESS(BYLOCALINDEXROWID)OF'SALES'[12]BITMAPCONVERSION(TOROWIDS)BITMAPANDBITMAPMERGEBITMAPKEYITERATIONTABLEACCESSFULLOF'CUSTOMERS'BITMAPINDEX(RANGESCAN)OF'SALES_C_BX'[12]BITMAPMERGEBITMAPINDEX(RANGESCAN)OF'SALES_T_BX'[12]BITMAPMERGEBITMAPKEYITERATIONTABLEACCESS(FULL)OF'PRODUCTS'BITMAPINDEX(RANGESCAN)OF'SALES_P_BX'[12]TABLEACCESS(FULL)OF'CUSTOMERS'TABLEACCESS(FULL)OF'PRODUCTS'DynamicPartitionPruningandStarQueryPruningisdeterminedatruntime.Commonlyusedqueryform:SELECTprod_subcategory,sum(amount_sold)FROMsaless,productsp,timestWHEREd_id=d_idANDs.time_id=t.time_idANDt.calendar_month_number=12ANDt.calendar_year=1999ANDprod_categoryIN('Men','Women')GROUPBYprod_subcategory;