How To Lace Double Upper

improving query performance with QUERY REWRITE Materialized Views

From Oracle 8i there is functionality Oracle database called QUERY REWRITE, which under certain circumstances and settings configuration database objects, a SQL issued by a user is re-written by another equivalent that has the best cost. Consequently, this functionality is a requirement that the optimizer is configured as a CBO.

A common application for the use of QUERY REWRITE is given in data warehouse environments, where queries that use group functions on a fact table with millions of records can be resolved on a summary table (cardinality much smaller than fact table) containing the same information but with far fewer disk reads. summary tables listed should implement Materialized Views to take the property of QUERY REWRITE.

management system in a database that tracks the relational model, a view is a virtual table that represents the result of a query. Whenever you query or update a normal view, the RDBMS becomes these operations in queries or updates the tables used to define the view.

A materialized view uses a different approach: the result of the query is stored in a cache table, who will be regularly updated from the original tables in which the materialized view is based. This provides more efficient access at the expense of an increase in the size of the database and a possible lack of synchronization, ie the view data can potentially be out of kilter with the actual data, which management environments, which are used for making strategic decisions and tactics is an irrelevant issue.

Example:

The following objects create a table plays fact table. It is created from an Oracle view catalog containing a large volume of records. REM Preparation table with large volume of records

sqlplus scott / tiger September echo on September

termout off drop table articles;

create table objects

NOLOGGING as select * from all_objects union all select

* from all_objects union all select * from all_objects

/ REM more data are added to the table to make it more bulky objects REM mechanism is used to accelerate DIRECT PATH settlement process

insert / * + APPEND * / into objects
select * from objects; commit; insert / * + APPEND * / into objects

select * from objects;

commit; insert / * + APPEND * / into objects

select * from objects;

commit;

objects

analyze table compute statistics;

select count (*) from objects;

REM

a query is made with the group functions on the table objects. September

AutoTrace on September

timing on select owner, count (*) from articles group by owner;

1. OWNER COUNT (*) ----------
———-
2. CTXSYS 6264
ELAN 1272
3. HR 816
MDSYS 5640
4. ODM 9768
…
28 rows selected.
7. Elapsed: 00:00:07.06
8. Execution Plan
———————————————————-

0 SELECT STATEMENT Optimizer=CHOOSE

(Cost=2719 Card=28 Bytes=140) 1 0 SORT (GROUP BY) (Cost=2719 Card=28 Bytes=140) 2 1 TABLE ACCESS (FULL) OF 'OBJECTS'

(Cost = 1226 Card = 708456 Bytes = 3542280)

is noted that the cost of this first implementation plan is high because the query requires to do a full table scan on a board of seven millions of records to retrieve only 28 records eventually. What can you do? Create a materialized view based on consultation and enable query rewrite. REM

privilege gives the user query rewrite SCOTT which will work

sqlplus / as sysdba grant

query rewrite to scott;

REM active for the current session the ability to query rewrite

sqlplus scott / tiger
alter session set query_rewrite_enabled = true; Alter session set
query_rewrite_integrity = enforced;

REM Note that materialized view allows query rewrite Materialized view

create vm_resumen_objetos
build
Immediate
refresh on commit enable query rewrite as

select owner, count (*) from objects
group by owner /

REM statistics are generated materialized view (CBO requirement) vm_resumen_objetos analyze table compute statistics;

REM now see in action the effect of the VM with the same query that is based VM

AutoTrace

September traceonly

select owner, count (*) from objects

group by owner;

September AutoTrace

off timing off

September 28 rows selected.

Elapsed: 00:00:00.03 Execution Plan

-------------------- 0 SELECT STATEMENT Optimizer = CHOOSE

(Cost = 2 Card = 28 Bytes = 252)
1 0 TABLE ACCESS (FULL) OF 'VM_RESUMEN_OBJETOS'
(Cost = 2 Card = 28 Bytes = 252)

is noted that the cost of implementing this second plan is much less than the cost at the plan original implementation which is a result of the great impact it has on This second query performance. This is the fundamental idea of \u200b\u200bthe QUERY REWRITE.