Agenda

2. Agenda • What are materialized views • What are materialized view logs • How to create • Query Rewrite • Why constraints matter • Dimensions • Debugging (why isn’t it working) • Interesting use of materialized view • Caching • Client cache • Server Results Cache (JIT-MV) • PLSQL Function Cache

3. What are materialized views

4. What are materialized views • In a distributed system – they are the segments formally known as snapshots • Snapshots available since version 7.0 of Oracle • Read only since 7.0 • Updatable since 7.1.6 • Used for replication • Horizontal subsets • Vertical subsets • Will not be discussing this use further

5. What are materialized views • Conceptually: They are the indexes of your data warehouse • Materialized views are query results that have been stored in advance so long-running calculations are not necessary when you actually execute your SQL statements. From a physical design point of view, materialized views resemble tables or partitioned tables and behave like indexes in that they are used transparently and improve performance. • Physically stored on disk • Pre-Join • Pre-aggregate • Pre-transformed data… • Answer the question once, not over and over

6. What are materialized views • Conceptually: They are the indexes of your data warehouse • A performance thing • Answer once, use over and over again • They can be a constraint mechanism in your transactional system • A department may not have more than 100 employees Mv1.sql Mv2.sql

7. What are materialized views • In summary, of summary tables • Goal – decreased response time for large queries • Transparent in the same fashion Indexes are transparent • Consumes storage space (they exist) • Transparently maintained as the underlying data changes

8. What are materialized view logs

9. What are materialized view logs • Used to track • Row by row changes • Invalidation ranges (direct path operations) • Are real tables SQL> create materialized view log on emp with rowid (deptno) 2 including new values; SQL> desc mlog$_emp Name Null? Type ----------------------------------- -------- ------------------------ DEPTNO NUMBER(2) M_ROW$$ VARCHAR2(255) SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2(1) OLD_NEW$$ VARCHAR2(1) CHANGE_VECTOR$$ RAW(255)

10. What are materialized view logs • Permit “incremental” – also known as ‘fast’ refresh • May contain • Sequencing (ordering) information • Rowids • Primary keys • Object Ids • Any column value actually • What you need depends on what you want to do • SYS.SUMDELTA$ used for direct path operations Mvl1.sql

11. How to create

12. How to create… • Create materialized view • On prebuilt table (with or without reduced precision), we’ll use this later • Physical properties • Segment attributes • Compression * - details in a couple of slides • Organization • Heap, index, external • Cluster

13. How to create… • Create materialized view • Partitioning • Refresh • Fast, complete, force • On demand, On commit • Start with and Next • Most things you can specify for a table, you can for a Materialized View • We’ve already seen a couple of examples (and we’ll see more)

14. How to create… • Refresh Process • Complete • Delete + Insert (10g and above by default) • Override with ATOMIC_REFRESH=>FALSE • Truncate + Insert /*+ APPEND */ (9i and before by default) • Override with snapshot group • Incremental (‘fast’) • Always procedural Mvcomp.sql

15. Query Rewrite

16. Queries are re-written automatically Product Sum byProduct Year Sales City Sum byCity Data pre-summarizedand automatically maintained by the database Quarter Month Sum byDay Materialized Views Date Query Rewrite • This is the magic Rewrite1.sql

17. Query Rewrite • Parameters • Query Rewrite Enabled • True or False • 9ir2 and before – false by default • 10g and above – true by default

18. Query Rewrite • Parameters • Query Rewrite INTEGRITY • ENFORCED: Queries will be rewritten using only constraints and rules that are enforced and guaranteed by Oracle. • *TRUSTED: Queries will be rewritten using the constraints that are enforced by Oracle as well as the relationships we have told Oracle about that exist in the data but are not enforced by Oracle. • STALE_TOLERATED: Queries will be rewritten to use materialized views even if Oracle knows the data contained in the materialized view is 'stale' *we’ll explore this in the next section

19. Query Rewrite – steps to match • Full exact text match. • In this method, an exact string match in the set of available, possible materialized views found in the data dictionary is considered. • The algorithm used is 'friendlier' (more flexible) than a shared pool comparison as it ignores white space, case of characters and other formatting.

20. Query Rewrite – steps to match • Partial text match. • The optimizer compares the remaining text of the query beginning with the FROM clause to the remaining text of the materialized view's defining query. ops$tkyte%ORA11GR1> create materialized view mv 2 refresh FAST 3 enable QUERY REWRITE 4 as 5 select object_type, count(*) 6 from t 7 group by object_type; Materialized view created. ops$tkyte%ORA11GR1> select object_type, count(*) 2 from t 3 where object_type in ( 'TABLE', 'VIEW' ) 4 group by object_type;

21. Query Rewrite – steps to match • General query rewrite methods. • These enable the use of a materialized view even if it contains • only part of the data, • more data than requested, • or data that can be converted.

22. Query Rewrite – steps to match • General query rewrite methods. • Data sufficiency: • Can the required data be obtained from a given materialized view? • If you ask for column X and column X is not in the materialized view and, furthermore, it is not retrievable via some join with the materialized view, then Oracle will not rewrite the query to use that view.

23. Query Rewrite – steps to match • General query rewrite methods. • Join compatibility: • Ensures that the any JOIN required by the submitted query can be satisfied by the materialized view . • Very similar to data sufficiency, do we have everything we need to join to the materialized view in place of base table

24. Query Rewrite – steps to match • General query rewrite methods. • Grouping compatibility: • This is required if both the materialized view and query contain a GROUP BY (or implied) clause. • If the materialized view is grouped at the same level or is grouped at a higher level of detail than is needed, the query can be rewritten to use the materialized view. ops$tkyte%ORA11GR1> create materialized view mv 2 refresh FAST 3 enable QUERY REWRITE 4 as 5 select object_type, count(*) 6 from t 7 group by object_type; Materialized view created. ops$tkyte%ORA11GR1> select count(*) 2 from t;

25. Query Rewrite – steps to match • General query rewrite methods. • Aggregate compatibility: • This is required if both query and materialized view contain aggregates. • It will ensure that the materialized view can satisfy the required aggregates. • It can perform some interesting rewrites in some cases. • For example, it will recognize that AVG(X) is the same as SUM(X)/COUNT(X) so a query that requires AVG(X) can be satisfied by a materialized view with the SUM and COUNT

26. Query Rewrite – steps to match • Biggest question is always • We’ll investigate in debugging • Sometimes we are smarter than software Why didn’t it rewrite?

27. Why constraints matter

28. Why constraints matter • Constraints are facts • Constraints are more information • Constraints convey information to the optimizer • The presence of constraints open up access paths that would not be otherwise available. • Examples…

29. Why constraints matter • Cons1 – check constraints • Cons2 – not null constraints • Cons3 – primary/foreign key constraints • Cons4 – putting them all together with a materialized view

30. Dimensions

31. Dimensions • Dimensions are facts • Dimensions are more information • Dimensions convey information to the optimizer • The presence of Dimensions open up access paths that would not be otherwise available. • Look familiar? • The more metadata we assert to the database, the better it can do…

32. Dimensions • Describes to Oracle how to “roll up” data • You have a DATE column • Which implies Month-Year • Which implies FY-Quarter • Which implies FY • Which implies Calendar Year Quarter • Which implies Calendar Year • So, if you have a materialized view at the Month-Year level… dim.sql

33. Dimensions hierarchy cy_rollup ( day child of month_year child of cy_qtr child of cy ) hierarchy fy_rollup ( day child of month_year child of fy_qtr child of fy ) • Validation • We asserted that • What if we “lied” (wrong answers) • That is why trusted exists • We can validate our data easily validate.sql

34. Debugging (why isn’t it working)

35. Debugging • DBMS_MVIEW Package • Explain MVIEW • Learn what is possible and why the “not possible” is not possible. • Exp_mview.sql • Explain REWRITE • Learn how a query was rewritten • Or why it was not • Exp_rewrite.sql

36. Interesting use of Materialized view Besides the prior constraint example

37. Interesting Use • Do it yourself online redefinition • When you want to purge, without purging • Uses prebuilt table option • Example • Table T3, we want to remove all rows where a<25 • That is 1,000,000 out of 8,000,000 rows – spread all over • Kill the indexes, leave lots of white space • So, let’s redefine (reorg) instead of delete – mostly directly path, nologging operations if we wish redef.sql

38. Interesting Use • On commit “trigger” • Be careful • You do not control how many times we call you! • Dbms_transaction might be useful commit.sql

39. Caching

40. Cache More • Everyone knows the fastest way to do something is – to not do it • Client Side Cache • Server Results Cache (JIT-MV) • PL/SQL Function results cache

41. Cache More • Client Side Cache • System level setting CLIENT_RESULT_CACHE_SIZE • Static setting (scope=spfile only) • Can be overridden by sqlnet.ora setting • OCI_RESULT_CACHE_MAX_SIZE • OCI_RESULT_CACHE_MAX_RSET_SIZE • OCI_RESULT_CACHE_MAX_RSET_ROWS • Max size can be higher or lower • But not if disabled on server!

42. Cache More • Client Side Cache • System level setting CLIENT_RESULT_CACHE_LAG • Static setting (scope=spfile only) • Define in milliseconds, default 3000ms (3 seconds) • If client goes back and forth often enough, would never be used • Each round trip will keep cache validated properly.

43. Cache More • Client Side Cache • View CLIENT_RESULT_CACHE_STATS$ shows utilization Oratest.java

44. Cache More • Server Result Cache • Just in time materialized view • Settings: • result_cache_max_size • result_cache_mode • result_cache_remote_expiration Demo: src.sql

45. Cache More • PL/SQL Function Cache • For PL/SQL called from SQL predominantly • Or for fast cross session variables • Update a table • Use getter/setter functions • Cache the getter function, read table once Demo: fcache.sql

46. Q A & <Insert Picture Here>