1 / 26

Oracle Database 11g New Features for BI / DW

Oracle Database 11g New Features for BI / DW. New Partitioning Methods Invisible indexes PIVOT / UNPIVOT OLAP and SQL. Agenda. Database Enterprise Edition 11g (11.1.0.7.0) SQLDeveloper 11g (1.5.1) Warehouse Builder 11g (11.1.0.7.0) Analytical Workspace Manager (11.1.0.7.0B).

shada
Download Presentation

Oracle Database 11g New Features for BI / DW

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. Oracle Database 11gNew Features for BI / DW

  2. New Partitioning Methods • Invisible indexes • PIVOT / UNPIVOT • OLAP and SQL Agenda

  3. Database Enterprise Edition 11g (11.1.0.7.0) • SQLDeveloper 11g (1.5.1) • Warehouse Builder 11g (11.1.0.7.0) • Analytical Workspace Manager (11.1.0.7.0B) Oracle Product Stack

  4. Oracle Experience • Oracle Education: 2001 - 2006 • Oracle Consulting: 2006 - 2007 • Bicon 2007 – • Oracle Certifications • Oracle Certified Professional DBA 8i • Oracle Certified Professional DBA 9i • Oracle Certified Professional DBA 10g • Oracle Certified Associate PL/SQL 10g About Me

  5. Started in 2005 • Currently 8 employees • Consulting • Business Intelligence • DBA • Project Management About Bicon AS

  6. Simulated Sales-application • Customer • Geography (Region / Warehouse / Customer) • Market Segment (Market / Account /Customer) • Product • Category (Class / Family / Product) • Channel Demo Case

  7. CUSTOMER DIMENSION CUSTOMERS UNITS FACT CHANNEL DIMENSION TIME DIMENSION OWB PRODUCT DIMENSION CHANNELS ORDERS AWM PRODUCTS UNITS CUBE Demo Case

  8. New Partition / Subpartition combinations • LIST - HASH • LIST – LIST • LIST – RANGE • RANGE – RANGE • New Partitioning Methods • System Partitioning • Interval Partitioning • Virtual Partitioning • Reference Partitioning Partitioning

  9. Range Partitioning • Specify the first partition • Specify the interval for new partitions • New partitions generated when needed Interval Partitioning

  10. PARTITION BY RANGE (YEAR_NUMBER) INTERVAL (1) ( PARTITION BEFORE_2007 VALUES LESS THAN (2007) ); TIME DIMENSION TIME DIMENSION TIME DIMENSION 2006 BEFORE_2007 2007 SYS_P1004 2008 SYS_P1013 Interval Partitioning

  11. Virtual column • Not stored in the database • Calculated on the fly • Can’t be manipulated (DML) • Virtual Partitioning • Partioning on the virtual column • All Partitioning methods Virtual Partitioning

  12. CREATE TABLE CUSTOMER ( SHIP_TO_ID VARCHAR2(16 BYTE) NOT NULL, …………….. ………………………… …………..., WAREHOUSE_ID VARCHAR2(3 BYTE) NOT NULL, MARKET_SEGMENT_ID VARCHAR2(3 BYTE) NOT NULL , WAREHOUSE_MARKET_ID VARCHAR2(7) GENERATED ALWAYS AS ( WAREHOUSE_ID||' '||MARKET_SEGMENT_ID ) VIRTUAL ) PARTITION BY HASH(WAREHOUSE_MARKET_ID) PARTITIONS 5; Virtual Partitioning

  13. Uses Foreign Key to partition a table • Partition key can be any column in the referenced table (not just the Primary Key) • Partition operations in the referenced table is automatic propagated Reference Partitioning

  14. CREATE TABLE PRODUCT_DIM (ITEM_ID VARCHAR2(12) PRIMARY KEY, ....... ................. ......................., CLASS_ID VARCHAR2(4) NOT NULL) PARTITION BY LIST (CLASS_ID) ( PARTITION p_hrd VALUES('HRD'), PARTITION p_sft VALUES('SFT') ); PRODUCT DIMENSION P_HRD Reference Partitioning P_SFT

  15. CREATE TABLE UNITS_FACT ( ITEM_ID VARCHAR2(12 BYTE) ....... ................. CONSTRAINT UNITS_FACT_PROD_FK FOREIGN KEY (ITEM_ID) REFERENCES PRODUCT_DIM (ITEM_ID) ) PARTITION BY REFERENCE (UNITS_FACT_PROD_FK); PRODUCT DIMENSION UNITS_FACT UNITS_FACT_PROD_FK P_HRD P_HRD Reference Partitioning P_SFT P_SFT

  16. ALTER TABLE PRODUCT_DIM ADD PARTITION P_DEF VALUES(DEFAULT); PRODUCT DIMENSION PRODUCT DIMENSION UNITS_FACT UNITS_FACT UNITS_FACT_PROD_FK P_HRD P_HRD Reference Partitioning P_SFT P_SFT P_DEF P_DEF

  17. Problem: Maintenance of the referenced table • Cannot disable Foreign Key • Cannot disable Primary Key (parent-table) • You can drop the Primary Key (parent-table) but: Reference Partitioning

  18. Reference Partitioning

  19. Indexes can be made invisible for the Optimizer • Invisible Indexes will still be maintained • Optimizer_use_invisible_indexes • Controls the use of Invisible Indexes • Is modifiable in each session • TRUE: Optimizer will consider Invisible indexes • FALSE: Optimizer will not consider Invisible Indexes Invisible Indexes

  20. New Syntax for Pivot / Unpivot • Use the original query as an Inline View • Benefits: • More readable queries (Both PIVOT / UNPIVOT) • No need to use a dummy resultset. (UNPIVOT) • Full flexibility using XML as resultset PIVOT / UNPIVOT

  21. Use SQL to access OLAP-objects (CUBE_TABLE) • Use Materialized View Refresh for OLAP-objects • Use Query Rewrite against OLAP-cubes SQL and OLAP

  22. Access OLAP-objects with new table-function • FROM TABLE(CUBE_TABLE(’dimension’)); • FROM TABLE(CUBE_TABLE(’dimension;hierachy’)); • FROM TABLE(CUBE_TABLE(’cube’)); • Access OLAP-objects with default-views • Generated when creating OLAP-objects • Uses CUBE_TABLE SQL and OLAP – Cube Table

  23. Dimensions Materialized View Refresh • Complete • On Demand / Scheduled • Trusted / Enforced Constraints • Cubes Materialized View Refresh • Complete / Fast / Force • On Demand / Scheduled / On Commit • Trusted / Enforced Constraints SQL and OLAP - Materialized View

  24. SELECT COL1, COL2, COL3, SUM(COL4) FROM D1, D2, D3, D4, F WHERE D1.PK=F.FK1 AND D2.PK=F.FK2 AND D3.PK=F.FK3 AND D4.PK=F.FK4 GROUP BY COL1, COL2, COL3; Query Rewrite MATERIALIZED VIEW OLAP CUBE D1 D2 F SQL and OLAP - Materialized View D3 D4

  25. General limitations • Cubes must be compressed • Cubes must have • Dimensions • Normal Hierarchies (Not ragged / skip) • Levels • Measures • Query Rewrite Limitations • All dimensions aggregated in the same way SQL and OLAP - Materialized View

  26. Q/A

More Related