o9i实体化视图

Oracle9iOracle20015Oracle9i.............................................................................................................3......................................................................................................................3..........................................................................................4....................................................................................................4......................................................................................5......................................................................................................................5..................................................................6........................................................................................................7..........................................................................8..............................................9..............................................................9..............................................9........................................................................10....................................................................................10...............................................................................11....................................................................................11....................................................................................12........................................................................12................................................................................13....................................................13...........................................................................................................13/...................................................................14........................................................................14............................................................................14....................................................................15........................................................15............................................................................15................................................................16............................................................................17....................................................................................17...........................................................................................................18....................................................................................18........................................................................19............................................................................19............................................................................20............................................................20....................................................................................................................20Oracle9i2Oracle9iOLTPOracle8iSQL/DBADBADBAOracleSQLOracle9i3yxOracle/5200010000Oracle8•Oracle•••DBADBA•••••Oracle9i4/1CityStateStatesCountryState,State,CityCityState1:n,•SQLCREATEDIMENSION2•OracleOracle9i511DBALEVELHERARCHYDBA2SQLATTRIBUTEcalendar_month_namemonthJOINKEY1:nFOREIGNKEYNOTNULL1.2.daymonthdaymonthquarteryear3.calendar_month_namemonth4.FOREIGNKEYNOTNULLPRIMARYKEYNOVALIDATERELYOracle9i62SQLCREATEDIMENSIONtimes_dimLEVELdayISTIMES.TIME_IDLEVELmonthISTIMES.CALENDAR_MONTH_DESCLEVELquarterISTIMES.CALENDAR_QUARTER_DESCLEVELyearISTIMES.CALENDAR_YEARLEVELfis_weekISTIMES.WEEK_ENDING_DAYLEVELfis_monthISTIMES.FISCAL_MONTH_DESCLEVELfis_quarterISTIMES.FISCAL_QUARTER_DESCLEVELfis_yearISTIMES.FISCAL_YEARHIERARCHYcal_rollup(dayCHILDOFmonthCHILDOFquarterCHILDOFyear)HIERARCHYfis_rollup(dayCHILDOFfis_weekCHILDOFfis_monthCHILDOFfis_quarterCHILDOFfis_year)ATTRIBUTEdayDETERMINES(day_number_in_week,day_name,day_number_in_month,calendar_week_number)ATTRIBUTEmonthDETERMINESOracle9i8(calendar_month_desc,calendar_month_number,calendar_month_name,days_in_cal_month,end_of_cal_month)ATTRIBUTEquarterDETERMINES(calendar_quarter_desc,calendar_quarter_number,days_in_cal_quarter,end_of_cal_quarter)ATTRIBUTEyearDETERMINES(calendar_year,days_in_cal_year,end_of_cal_year)ATTRIBUTEfis_weekDETERMINES(week_ending_day,fiscal_week_number);SUMMINMAXAVGCOUNT(*)COUNT(x)COUNT(DISTINCT)VARIANCESTDDEV,GROUPBYDLLCREATEALTERDROPOracle9i7••SQLDBASQL•CREATEMATERIALIZEDVIEW3costs_mvtimeprod_namcostsSELECTGROUPBYGROUPBYSQLWHERESYSDATEUSER3SQLCREATEMATERIALIZEDVIEWcosts_mvPCTFREE0STORAGE(initial8knext8kpctincrease0)BUILDIMMEDIATEREFRESHFASTONDEMANDENABLEQUERYREWRITEASSELECTtime_id,prod_name,SUM(unit_cost)ASsum_units,COUNT(unit_cost)AScount_units,COUNT(*)AScntFROMcostsc,productspWHEREc.prod_id=p.prod_idGROUPBYtime_id,prod_name;Oracle9i8CREATEMATERIALIZEDVIEWONPREBUILTTABLESELECTWITHREDUCEDPRECISIONDBADBMS_MVIEW.EXPLAIN_MVIEW3COUNT*4DBMS_MVIEW.EXPLAIN_MVIEW(PCT