1 / 24

New Analytic View Features in Oracle Database 12cR2

Explore the concepts, terminology, and practical applications of Analytic Views in Oracle Database 12cR2. Learn how to build attribute dimensions and hierarchies, validate views, and leverage the power of Analytic Views for improved data analysis.

hmartin
Download Presentation

New Analytic View Features in Oracle Database 12cR2

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. Stop Guessing, Start Analyzing: New Analytic View Featuresin Oracle Database 12cR2 Jim Czuprynski Zero Defect Computing, Inc.

  2. My Credentials • 35+ years of database-centric IT experience • Oracle DBA since 2001 • Oracle 9i, 10g, 11g, 12c OCP • Oracle ACE Director • 100+ articles on databasejournal.com and IOUG SELECT • Co-author of 4 Oracle books • Oracle-centric blog (Generally, It Depends) • Regular speaker at Oracle OpenWorld, IOUG COLLABORATE, Hotsos Symposium, and Regional OUGs

  3. Our Agenda • Analytic Views in 12cR2: Concepts and Terminology • Building Attribute Dimensions: CREATE ATTRIBUTE DIMENSION • Describing Explicit Hierarchies: CREATE HIERARCHY • Building Analytic Views: CREATE ANALYTIC VIEW • Validating Hierarchies and Analytic Views with DBMS_HIERARCHY • Putting It All Together: Analytic View Query Examples • Conclusions

  4. Analytic Views in 12cR2:Concepts and Terminology

  5. Why Analytic Views? • Which tables are really dimensions? • Correct choice is crucial • Hierarchies are sometimes not apparent • Where does the business logic go? • In SQL, in a middle tier, or within the database? • Overhead of each choice is an important design consideration • How will it perform relative to other systems on the same database / on the same platform? • Analytical processing is CPU-intensive • Often, huge amounts of data are retrieved and discarded before finding a query’s solution • Engineered systems can help … if you’ve got one! Data Warehouse architecture design is not trivial!

  6. Hierarchies, Everywhere You Look Implicit, obvious hierarchies are legion • Dayswithinweek, month, quarter, year • Locales withincounty/parish, state/province, country … but explicit hierarchies are not uncommon! • Insurance: Insureds within policy, organization, group • Manufacturing: Parts within assembly, model, make DifferentCxOs need different hierarchies • CFO: Transactions within accounts & ledgers • CIO: Components within servers, datacenters, regions • SVP: Territorieswithindistricts & regions • HCM: Persons within departments & divisions

  7. Database, Applications, and Human Factors • Database Environment: • Oracle 12cR2 (12.2.0.1) database on Oracle Linux • Eight (8) virtualized CPUs • Schema: TPC-DS • 7 fact tables, 17 dimensions • Fact tables partitioned for better access paths and parallelism • Workloads: Between horrible and nasty! • 100+ standard queries answering complex business questions • Business rules not always 100% clear • Users constantly writing inefficient queries because they don’t know how to join tables together correctly

  8. Building Attribute Dimensions and Hierarchies CREATE ATTRIBUTE DIMENSION CREATE HIERARCHY

  9. 2 # Add and populate new columns in TPCDS.DATE_DIM: ALTER TABLE tpcds.date_dim ADD ( d_moy_key CHAR(07) ,d_moy_abbr CHAR(03) ,d_moy_name VARCHAR2(09) ,d_qoy_key CHAR(06) ); UPDATE tpcds.date_dim SET d_moy_key = TO_CHAR(d_date, 'YYYY-MM') ,d_moy_abbr = TO_CHAR(d_date, 'MON') ,d_moy_name = TO_CHAR(d_date, 'Month') ,d_qoy_key = TO_CHAR(d_date, 'YYYY') || 'Q' ||TO_CHAR(d_date,'Q'); COMMIT; Preparations # Grant appropriate privileges to user accounts: -- Grant appropriate privileges to user accounts GRANT CREATE ATTRIBUTE DIMENSION TO tpcds; GRANT CREATE HIERARCHY TO tpcds; GRANT CREATE ANALYTIC VIEW TO tpcds; 1

  10. Building Attribute Dimensions # Attribute Dimension (cont’d): . . . LEVEL month LEVEL TYPE months KEY d_moy_key MEMBER NAME d_moy_key MEMBER CAPTION d_moy_abbr MEMBER DESCRIPTION d_moy_name ORDER BY d_moy_key DETERMINES (d_qoy_key) LEVEL quarter LEVEL TYPE quarters KEY d_qoy_key MEMBER NAME d_qoy_key MEMBER CAPTION d_qoy_key MEMBER DESCRIPTION d_qoy_key ORDER BY d_qoy_key DETERMINES (d_year) LEVEL year LEVEL TYPE years KEY d_year MEMBER NAME TO_CHAR(d_year) MEMBER CAPTION TO_CHAR(d_year) MEMBER DESCRIPTION TO_CHAR(d_year) ORDER BY d_year ALL MEMBER NAME 'ALL DATES' MEMBER CAPTION 'All Dates' MEMBER DESCRIPTION 'All Timestamps‘; Attribute dimension created. # Create an Attribute Dimension: CREATE OR REPLACE ATTRIBUTE DIMENSION tpcds.avad_dates DIMENSION TYPE TIME USING tpcds.date_dim ATTRIBUTES ( d_year ,d_qoy_key ,d_quarter_name ,d_moy_key ,d_moy_abbr ,d_moy_name ,d_date ,d_date_sk ,d_day_name ) LEVEL day LEVEL TYPE days KEY d_date_sk ALTERNATE KEY d_date MEMBER NAME TO_CHAR(d_date,'yyyy-mm-dd') MEMBER CAPTION d_day_name MEMBER DESCRIPTION d_day_name ORDER BY d_date DETERMINES (d_moy_key) . . . Attribute Dimension Dimension Attributes Explicit Levels of Detail

  11. Creating Explicit Hierarchies # Create a Hierarchy: SQL> CREATE OR REPLACE HIERARCHYtpcds.avhy_dates USING tpcds.avad_dates( day CHILD OF month CHILD OF quarter CHILD OF year ); Hierarchy created. Attribute Dimension # Show the resulting Hierarchy: SELECT level_name ,hier_order ,depth ,d_year ,d_qoy_key ,d_moy_key ,member_name ,member_unique_name ,member_caption ,member_description FROM tpcds.avhy_dates WHERE level_name <> 'DAY' AND d_year = 2017 ORDER BY hier_order; Hierarchy Sample Data for 2017 (from TPCDS.AVHY_DATES) Member HierHierHierQtrMth Member Unique Member Member Level Order Depth Year Key Key Name Name Caption Description ---------- ------- ------ ------ -------- -------- ---------- -------------------- ---------- ------------ YEAR 44723 1 2017 2017 [YEAR].&[2017] 2017 2017 QUARTER 44724 2 2017 2017Q1 2017Q1 [QUARTER].&[2017Q1] 2017Q1 2017Q1 MONTH 44725 3 2017 2017Q1 2017-01 2017-01 [MONTH].&[2017-01] JAN January MONTH 44757 3 2017 2017Q1 2017-02 2017-02 [MONTH].&[2017-02] FEB February MONTH 44786 3 2017 2017Q1 2017-03 2017-03 [MONTH].&[2017-03] MAR March QUARTER 44818 2 2017 2017Q2 2017Q2 [QUARTER].&[2017Q2] 2017Q2 2017Q2 MONTH 44819 3 2017 2017Q2 2017-04 2017-04 [MONTH].&[2017-04] APR April MONTH 44850 3 2017 2017Q2 2017-05 2017-05 [MONTH].&[2017-05] MAY May MONTH 44882 3 2017 2017Q2 2017-06 2017-06 [MONTH].&[2017-06] JUN June QUARTER 44913 2 2017 2017Q3 2017Q3 [QUARTER].&[2017Q3] 2017Q3 2017Q3 MONTH 44914 3 2017 2017Q3 2017-07 2017-07 [MONTH].&[2017-07] JUL July MONTH 44946 3 2017 2017Q3 2017-08 2017-08 [MONTH].&[2017-08] AUG August MONTH 44978 3 2017 2017Q3 2017-09 2017-09 [MONTH].&[2017-09] SEP September QUARTER 45009 2 2017 2017Q4 2017Q4 [QUARTER].&[2017Q4] 2017Q4 2017Q4 MONTH 45010 3 2017 2017Q4 2017-10 2017-10 [MONTH].&[2017-10] OCT October MONTH 45042 3 2017 2017Q4 2017-11 2017-11 [MONTH].&[2017-11] NOV November MONTH 45073 3 2017 2017Q4 2017-12 2017-12 [MONTH].&[2017-12] DEC December 17 rows selected. HierarchyDescription

  12. Building Analytic Views:CREATE ANALYTIC VIEW Creating Analytic Views Validating Hierarchies and Analytic Views

  13. Creating Analytic Views: Unpacking the Basics # Create a simple Analytic View: SQL> CREATE OR REPLACE ANALYTIC VIEWtpcds.av_ss_basic USING tpcds.store_sales DIMENSION BY ( avad_dates KEY ss_sold_date_sk REFERENCES d_date_sk HIERARCHIES (avhy_datesDEFAULT) ) MEASURES ( dtl_qty FACT ss_quantity ); Analytic view created. Analytic View Primary Source Attribute Dimensions Hierarchies Measures

  14. Analytic Views: More Complex MEASUREs # Create a more complex Analytic View: CREATE OR REPLACE ANALYTIC VIEW tpcds.av_ss_complex USING tpcds.store_sales DIMENSION BY ( avad_dates KEY ss_sold_date_sk REFERENCES d_date_sk HIERARCHIES (avhy_dates DEFAULT) ) MEASURES ( ss_qty FACT ss_quantity -- Aggregations within hierarchy levels: ,ytd_qty AS ( SUM(ss_qty) OVER (HIERARCHY avhy_dates BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL year) ) ,qtd_qty AS ( SUM(ss_qty) OVER (HIERARCHY avhy_dates BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL quarter) ) . . . … and SHARE_OF measures capture shares of the whole within a hierarchy … # Complex Analytic View (cont’d): . . . ,qty_prior_period AS ( LAG(ss_qty) OVER (HIERARCHY avhy_dates OFFSET 1) ) -- SHARE OF Totals: ,qty_shr_year AS ( SHARE_OF(ss_qty HIERARCHY avhy_dates LEVEL year) ) ,qty_shr_qtr AS ( SHARE_OF(ss_qty HIERARCHY avhy_dates LEVEL quarter) ) -- Qualified Data Regions: ,qty_pre_y2k AS ( QUALIFY (ss_qty, avhy_dates = year['1999']) ) ,qty_post_y2k AS ( QUALIFY (ss_qty, avhy_dates = quarter['2000Q1']) ) ) DEFAULT MEASURE ss_qty; Analytic view created. Measures can also leverage windowing functions… … and qualified data regionsare filteredsubsets of measures

  15. Analytic Views: Metadata

  16. Validating a Hierarchy

  17. Validating Analytic Views and Hierarchies . . . rtnValid := DBMS_HIERARCHY.VALIDATE_CHECK_SUCCESS( topobj_name => 'AVHY_DATES' ,topobj_owner => 'TPCDS' ,log_number => rtnHY ,log_table_name => 'ADV_VALIDATIONS' ,log_table_owner_name => 'TPCDS'); DBMS_OUTPUT.PUT_LINE('AVHY_DATES Validity: ' || rtnValid); rtnValid := DBMS_HIERARCHY.VALIDATE_CHECK_SUCCESS( topobj_name => 'AV_SS_BASIC' ,topobj_owner => 'TPCDS' ,log_number => rtnAV ,log_table_name => 'ADV_VALIDATIONS' ,log_table_owner_name => 'TPCDS'); DBMS_OUTPUT.PUT_LINE('AV_SS_BASIC Validity: ' || rtnValid); END; / 2 # … or simply query resulting log table’s content: COL log_number FORMAT 99999 HEADING "Log #" COL action_order FORMAT 99999 HEADING "Act|#" COL object_owner FORMAT A12 HEADING "Object|Owner" COL object_name FORMAT A12 HEADING "Object|Name" COL action FORMAT A08 HEADING "Action" COL action_dtm FORMAT A19 HEADING "Done At" COL error_number FORMAT 99999 HEADING "Error|#" COL error_text FORMAT A30 HEADING "Shortened Error Message" TTITLE "Results of Analytic View + Hierarchy Validation|(from TPCDS.ADV_VALIDATIONS)" SELECT log_number ,action_order ,object_owner ,object_name ,action ,to_char(time, 'yyyy-mm-dd.hh24:mi') action_dtm ,error_number ,SUBSTR(error_message,1,30) error_text FROM tpcds.adv_validations WHERE error_number IS NOT NULL; TTITLE OFF 3 # Validate Hierarchy and Analytic View: DECLARE rtnAV NUMBER; rtnHY NUMBER; rtnValid VARCHAR2(7); BEGIN -- Validating an AV Hierarchy: rtnHY := DBMS_HIERARCHY.VALIDATE_HIERARCHY( hier_name => 'AVHY_DATES' ,hier_owner_name => 'TPCDS' ,log_table_name => 'ADV_VALIDATIONS' ,log_table_owner_name => 'TPCDS' ); -- Validating an Analytic View: rtnAV := DBMS_HIERARCHY.VALIDATE_ANALYTIC_VIEW( analytic_view_name => 'AV_SS_BASIC' ,analytic_view_owner_name => 'TPCDS' ,log_table_name => 'ADV_VALIDATIONS' ,log_table_owner_name => 'TPCDS' ); . . . 2 # Create a logging table: BEGIN DBMS_HIERARCHY.CREATE_VALIDATE_LOG_TABLE( table_name => 'ADV_VALIDATIONS' ,owner_name => 'TPCDS' ,ignore_if_exists => TRUE ); END; / 1

  18. Putting It All Together: Analytic View Query Examples

  19. Querying an Analytic View: A Simple Example # A simple AV query: COL level_name FORMAT A10 HEADING "Hier|Level" COL member_name FORMAT A10 HEADING "Member|Name" COL dtl_qty FORMAT 999,999,999.99 HEADING "Store|Sales|Quantity" TTITLE "Basic Analytic Query Example|(from TPCDS.AV_SS_BASIC)" SELECT avhy_dates.level_name ,avhy_dates.member_name ,dtl_qty FROM tpcds.av_ss_basic WHERE avhy_dates.level_name IN ('ALL','YEAR','QUARTER','MONTH') ORDER BY avhy_dates.hier_order ; TTITLE OFF Basic Analytic Query Example (from TPCDS.AV_SS_BASIC) Store Hier Member Sales Level Name Quantity ---------- ---------- --------------- ALL ALL DATES 288,272,713.00 YEAR 1998 57,064,038.00 QUARTER 1998Q1 13,983,442.00 MONTH 1998-01 4,684,654.00 MONTH 1998-02 4,398,090.00 MONTH 1998-03 4,900,698.00 QUARTER 1998Q2 14,263,620.00 MONTH 1998-04 4,679,562.00 MONTH 1998-05 4,871,646.00 MONTH 1998-06 4,712,412.00 QUARTER 1998Q3 14,430,832.00 MONTH 1998-07 4,844,202.00 MONTH 1998-08 4,915,358.00 MONTH 1998-09 4,671,272.00 QUARTER 1998Q4 14,386,144.00 MONTH 1998-10 4,857,150.00 MONTH 1998-11 4,681,908.00 MONTH 1998-12 4,847,086.00 . . . YEAR 1999 57,368,014.00 QUARTER 1999Q1 14,159,312.00 MONTH 1999-01 4,896,268.00 MONTH 1999-02 4,455,816.00 MONTH 1999-03 4,807,228.00 QUARTER 1999Q2 14,307,102.00 MONTH 1999-04 4,730,778.00 MONTH 1999-05 4,857,914.00 MONTH 1999-06 4,718,410.00 QUARTER 1999Q3 14,426,508.00 MONTH 1999-07 4,909,008.00 MONTH 1999-08 4,817,114.00 MONTH 1999-09 4,700,386.00 QUARTER 1999Q4 14,475,092.00 MONTH 1999-10 4,860,494.00 MONTH 1999-11 4,707,378.00 MONTH 1999-12 4,907,220.00 . . . . . . YEAR 2000 57,511,340.00 QUARTER 2000Q1 14,326,914.00 MONTH 2000-01 4,819,758.00 MONTH 2000-02 4,592,016.00 MONTH 2000-03 4,915,140.00 QUARTER 2000Q2 14,322,272.00 MONTH 2000-04 4,727,808.00 MONTH 2000-05 4,903,956.00 MONTH 2000-06 4,690,508.00 QUARTER 2000Q3 14,402,968.00 MONTH 2000-07 4,879,852.00 MONTH 2000-08 4,850,890.00 MONTH 2000-09 4,672,226.00 QUARTER 2000Q4 14,459,186.00 MONTH 2000-10 4,897,608.00 MONTH 2000-11 4,686,910.00 MONTH 2000-12 4,874,668.00 . . . . . . YEAR 2002 57,478,306.00 QUARTER 2002Q1 14,218,054.00 MONTH 2002-01 4,879,048.00 MONTH 2002-02 4,435,116.00 MONTH 2002-03 4,903,890.00 QUARTER 2002Q2 14,289,346.00 MONTH 2002-04 4,679,752.00 MONTH 2002-05 4,912,014.00 MONTH 2002-06 4,697,580.00 QUARTER 2002Q3 14,529,304.00 MONTH 2002-07 4,911,188.00 MONTH 2002-08 4,892,030.00 MONTH 2002-09 4,726,086.00 QUARTER 2002Q4 14,441,602.00 MONTH 2002-10 4,881,696.00 MONTH 2002-11 4,670,818.00 MONTH 2002-12 4,889,088.00 YEAR 2003 1,732,884.00 QUARTER 2003Q1 1,732,884.00 MONTH 2003-01 1,732,884.00 89 rows selected. . . . YEAR 2001 57,118,131.00 QUARTER 2001Q1 14,125,274.00 MONTH 2001-01 4,925,032.00 MONTH 2001-02 4,350,420.00 MONTH 2001-03 4,849,822.00 QUARTER 2001Q2 14,214,084.00 MONTH 2001-04 4,715,698.00 MONTH 2001-05 4,830,712.00 MONTH 2001-06 4,667,674.00 QUARTER 2001Q3 14,369,151.00 MONTH 2001-07 4,851,668.00 MONTH 2001-08 4,849,892.00 MONTH 2001-09 4,667,591.00 QUARTER 2001Q4 14,409,622.00 MONTH 2001-10 4,826,386.00 MONTH 2001-11 4,704,742.00 MONTH 2001-12 4,878,494.00 . . .

  20. A More Complex AV Query COL level_name FORMAT A10 HEADING "Hier|Level" COL member_name FORMAT A10 HEADING "Member|Name" COL ss_qty FORMAT 999,999,999 HEADING "Store|Sales|Quantity" COL ytd_qty FORMAT 999,999,999 HEADING "Y-T-D Quantity" COL qtd_qty FORMAT 999,999,999 HEADING "Q-T-D Quantity" COL qty_prior_period FORMAT 999,999,999 HEADING "Prior Year|Quantity" COL qty_shr_year FORMAT 999.99 HEADING "Prior|Year|Share" COL qty_shr_qtr FORMAT 999.99 HEADING "Prior|Qtr|Share" COL qty_pre_y2k FORMAT 999,999,999 HEADING "Pre-Y2K|Quantity" COL qty_post_y2k FORMAT 999,999,999 HEADING "Post-Y2K|Quantity" TTITLE "Complex Analytic Query Example|(from TPCDS.AV_SS_COMPLEX)" SELECT avhy_dates.level_name ,avhy_dates.member_name ,ss_qty ,ytd_qty ,qtd_qty ,qty_prior_period ,qty_shr_year ,qty_shr_qtr ,qty_pre_y2k ,qty_post_y2k FROM tpcds.av_ss_complex WHERE avhy_dates.level_name IN ('ALL','YEAR','QUARTER','MONTH') ORDER BY avhy_dates.hier_order; • Complex Analytic Query Example • (from TPCDS.AV_SS_COMPLEX) • Store Prior PriorPrior • Hier Member Sales Y-T-D Q-T-D Period Year Qtr Pre-Y2K Post-Y2K • Level Name Quantity QuantityQuantityQuantity Share Share Quantity Quantity • ---------- ---------- ------------ ------------ ------------ ------------ ------- ------- ------------ ------------ • ALL ALL DATES 288,272,713 57,368,014 14,326,914 • YEAR 1998 57,064,038 57,064,038 1.00 57,368,014 14,326,914 • QUARTER 1998Q1 13,983,442 13,983,442 13,983,442 .25 1.00 57,368,014 14,326,914 • MONTH 1998-01 4,684,654 4,684,654 4,684,654 .08 .34 57,368,014 14,326,914 • MONTH 1998-02 4,398,090 9,082,744 9,082,744 4,684,654 .08 .31 57,368,014 14,326,914 • MONTH 1998-03 4,900,698 13,983,442 13,983,442 4,398,090 .09 .35 57,368,014 14,326,914 • QUARTER 1998Q2 14,263,620 28,247,062 14,263,620 13,983,442 .25 1.00 57,368,014 14,326,914 • MONTH 1998-04 4,679,562 18,663,004 4,679,562 4,900,698 .08 .33 57,368,014 14,326,914 • MONTH 1998-05 4,871,646 23,534,650 9,551,208 4,679,562 .09 .34 57,368,014 14,326,914 • MONTH 1998-06 4,712,412 28,247,062 14,263,620 4,871,646 .08 .33 57,368,014 14,326,914 • QUARTER 1998Q3 14,430,832 42,677,894 14,430,832 14,263,620 .25 1.00 57,368,014 14,326,914 • MONTH 1998-07 4,844,202 33,091,264 4,844,202 4,712,412 .08 .34 57,368,014 14,326,914 • MONTH 1998-08 4,915,358 38,006,622 9,759,560 4,844,202 .09 .34 57,368,014 14,326,914 • MONTH 1998-09 4,671,272 42,677,894 14,430,832 4,915,358 .08 .32 57,368,014 14,326,914 • QUARTER 1998Q4 14,386,144 57,064,038 14,386,144 14,430,832 .25 1.00 57,368,014 14,326,914 • MONTH 1998-10 4,857,150 47,535,044 4,857,150 4,671,272 .09 .34 57,368,014 14,326,914 • MONTH 1998-11 4,681,908 52,216,952 9,539,058 4,857,150 .08 .33 57,368,014 14,326,914 • MONTH 1998-12 4,847,086 57,064,038 14,386,144 4,681,908 .08 .34 57,368,014 14,326,914 • YEAR 1999 57,368,014 57,368,014 57,064,038 1.00 57,368,014 14,326,914 • QUARTER 1999Q1 14,159,312 14,159,312 14,159,312 14,386,144 .25 1.00 57,368,014 14,326,914 • MONTH 1999-01 4,896,268 4,896,268 4,896,268 4,847,086 .09 .35 57,368,014 14,326,914 • MONTH 1999-02 4,455,816 9,352,084 9,352,084 4,896,268 .08 .31 57,368,014 14,326,914 • MONTH 1999-03 4,807,228 14,159,312 14,159,312 4,455,816 .08 .34 57,368,014 14,326,914 • . . . . . . YEAR 2002 57,478,306 57,478,306 57,118,131 1.00 57,368,014 14,326,914 QUARTER 2002Q1 14,218,054 14,218,054 14,218,054 14,409,622 .25 1.00 57,368,014 14,326,914 MONTH 2002-01 4,879,048 4,879,048 4,879,048 4,878,494 .08 .34 57,368,014 14,326,914 MONTH 2002-02 4,435,116 9,314,164 9,314,164 4,879,048 .08 .31 57,368,014 14,326,914 MONTH 2002-03 4,903,890 14,218,054 14,218,054 4,435,116 .09 .34 57,368,014 14,326,914 QUARTER 2002Q2 14,289,346 28,507,400 14,289,346 14,218,054 .25 1.00 57,368,014 14,326,914 MONTH 2002-04 4,679,752 18,897,806 4,679,752 4,903,890 .08 .33 57,368,014 14,326,914 MONTH 2002-05 4,912,014 23,809,820 9,591,766 4,679,752 .09 .34 57,368,014 14,326,914 MONTH 2002-06 4,697,580 28,507,400 14,289,346 4,912,014 .08 .33 57,368,014 14,326,914 QUARTER 2002Q3 14,529,304 43,036,704 14,529,304 14,289,346 .25 1.00 57,368,014 14,326,914 MONTH 2002-07 4,911,188 33,418,588 4,911,188 4,697,580 .09 .34 57,368,014 14,326,914 MONTH 2002-08 4,892,030 38,310,618 9,803,218 4,911,188 .09 .34 57,368,014 14,326,914 MONTH 2002-09 4,726,086 43,036,704 14,529,304 4,892,030 .08 .33 57,368,014 14,326,914 QUARTER 2002Q4 14,441,602 57,478,306 14,441,602 14,529,304 .25 1.00 57,368,014 14,326,914 MONTH 2002-10 4,881,696 47,918,400 4,881,696 4,726,086 .08 .34 57,368,014 14,326,914 MONTH 2002-11 4,670,818 52,589,218 9,552,514 4,881,696 .08 .32 57,368,014 14,326,914 MONTH 2002-12 4,889,088 57,478,306 14,441,602 4,670,818 .09 .34 57,368,014 14,326,914 YEAR 2003 1,732,884 1,732,884 57,478,306 1.00 57,368,014 14,326,914 QUARTER 2003Q1 1,732,884 1,732,884 1,732,884 14,441,602 1.00 1.00 57,368,014 14,326,914 MONTH 2003-01 1,732,884 1,732,884 1,732,884 4,889,088 1.00 1.00 57,368,014 14,326,914 89 rows selected.

  21. Under the Covers: Hidden Yet Elegant Complexity! Execution Plan ---------------------------------------------------------- Plan hash value: 894232153 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 53 | | 3 (34)| 00:00:01 | | 1 | SORT ORDER BY | | 1 | 53 | | 3 (34)| 00:00:01 | | 2 | VIEW | AV_SS_BASIC| 1 | 53 | | 2 (0)| 00:00:01 | | 3 | TEMP TABLE TRANSFORMATION | | | | | | | | 4 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D669E_438366 | | | | | | | 5 | WINDOW SORT | | 604 | 76104 | | 57 (18)| 00:00:01 | | 6 | VIEW | | 604 | 76104 | | 56 (17)| 00:00:01 | | 7 | UNION-ALL | | | | | | | | 8 | HASH GROUP BY | | 201 | 6432 | | 20 (20)| 00:00:01 | | 9 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 73049 | 2282K| | 17 (6)| 00:00:01 | | 10 | HASH GROUP BY | | 201 | 2412 | | 19 (16)| 00:00:01 | | 11 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 73049 | 856K| | 16 (0)| 00:00:01 | | 12 | HASH GROUP BY | | 201 | 804 | | 17 (6)| 00:00:01 | | 13 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 201 | 804 | | 16 (0)| 00:00:01 | | 14 | VIEW | | 1 | 126 | | 1 (0)| 00:00:01 | |* 15 | COUNT STOPKEY | | | | | | | | 16 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 1 | | | 1 (0)| 00:00:01 | | 17 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D669F_438366 | | | | | | |* 18 | VIEW | | 604 | 36844 | | 5 (0)| 00:00:01 | | 19 | TABLE ACCESS FULL | SYS_TEMP_0FD9D669E_438366 | 604 | 76104 | | 5 (0)| 00:00:01 | |* 20 | HASH JOIN | | 1 | 139 | | 8161 (79)| 00:00:01 | | 21 | VIEW | | 37 | 2072 | | 8156 (79)| 00:00:01 | | 22 | HASH GROUP BY | | 37 | 3182 | | 8156 (79)| 00:00:01 | | 23 | NESTED LOOPS | | 37 | 3182 | | 8155 (79)| 00:00:01 | . . . . . . | 72 | HASH GROUP BY | | 1 | 77 | | 8047 (80)| 00:00:01 | |* 73 | HASH JOIN | | 159M| 11G| 4216K| 2314 (29)| 00:00:01 | | 74 | TABLE ACCESS FULL | SYS_TEMP_0FD9D669D_438366 | 73048 | 3352K| | 129 (1)| 00:00:01 | | 75 | VIEW | VW_VT_CA4EAB06 | 218K| 6398K| | 1080 (18)| 00:00:01 | | 76 | VECTOR GROUP BY | | 218K| 2772K| | 1080 (18)| 00:00:01 | | 77 | HASH GROUP BY | | 218K| 2772K| | 1080 (18)| 00:00:01 | | 78 | KEY VECTOR USE | :KV0000 | 5760K| 71M| | 1079 (18)| 00:00:01 | | 79 | PARTITION RANGE ALL | | 5760K| 49M| | 1078 (18)| 00:00:01 | |* 80 | TABLE ACCESS INMEMORY FULL | STORE_SALES | 5760K| 49M| | 1078 (18)| 00:00:01 | |* 81 | VIEW | | 37 | 1591 | | 3 (0)| 00:00:01 | | 82 | TABLE ACCESS FULL | SYS_TEMP_0FD9D669F_438366 | 604 | 33824 | | 3 (0)| 00:00:01 | |* 83 | VIEW | | 604 | 50132 | | 5 (0)| 00:00:01 | | 84 | TABLE ACCESS FULL | SYS_TEMP_0FD9D669E_438366 | 604 | 76104 | | 5 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 15 - filter(ROWNUM=1) 18 - filter("H"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='ALL' OR "H"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='MONTH' OR "H"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='QUARTER' OR "H"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='YEAR') . . . 83 - filter("TGT$AVAD_DATES#AVHY_DATES#FULL$"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='ALL' OR "TGT$AVAD_DATES#AVHY_DATES#FULL$"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='MONTH' OR "TGT$AVAD_DATES#AVHY_DATES#FULL$"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='QUARTER' OR "TGT$AVAD_DATES#AVHY_DATES#FULL$"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='YEAR') Note ----- - vector transformation used for this statement

  22. Conclusions

  23. Conclusions Analytic Views offer: • Easier construction of hierarchies, including implicit support for date-based hierarchies • Simpler analytical queries with less guesswork • Nicely integrated with 12cR2 DBIM features (if licensed!) Don’t forget to: • Validate your hierarchies to avoid unpleasant surprises • Use unique values in hierarchy levels, especially date-based! Useful Oracle Database 12cR2 documentation: • SQL Language Reference • Data Warehousing Guide, Chapters 24, 25, 26

  24. Thank You For Your Kind Attention! If you have any technical questions, feel free to: • E-mail me at jczuprynski@zerodefectcomputing.com • Follow my blog (Generally, It Depends): http://jimczuprynski.wordpress.com • Follow me on Twitter (@JimTheWhyGuy) • Connect with me on LinkedIn: https://www.linkedin.com/in/jczuprynski

More Related