Feeds:
Posts
Comments

Posts Tagged ‘Materialized view’

To achieve optimum performance for data-intensive queries, materialized views and indexes are essential when tuning SQL statements. The SQL Access Advisor enables to optimize data access paths of SQL queries by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload.

A materialized view provides access to table data by storing the results of a query in a separate schema object. A materialized view contains the rows resulting from a query against one or more base tables or views.

A materialized view log is a schema object that records changes to a master table’s data, so that a materialized view defined on the master table can be refreshed incrementally.

The SQL Access Advisor also recommends bitmap, function-based, and B-tree indexes. A bitmap index provides a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. A functional index derives the indexed value from the table data. For example, to find character data in mixed cases, a functional index can be used to look for the values as if they were all in uppercase characters.

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/04/oversize-of-datatype-varchar2-causes-performance-issue/

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Advertisements

Read Full Post »

Error Description:
The scenario is I have created a materialized view through database link over a table reside on the remote database.

I used the ON COMMIT option and it fails with ORA-12054 as below.

SQL> create materialized view scott.mv_phones
2 TABLESPACE users
3 REFRESH FAST on commit
4 WITH rowid
5 AS select ID, PASS,
6 CONN_EXPIRY_DATE, CONN_STATUS
7 from PHONES@lnxdb where upper(IS_SOLD)=’Y’;
from PHONES@lnxdb where upper(IS_SOLD)=’Y’
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Cause of the Problem
The ON COMMIT clause of CREATE MATERIALIZE VIEW has some restrictions. Such as,

•ON COMMIT clause is not supported for materialized views containing object types.

•With ON COMMIT clause inserting into the materialized view table(master table) is not supported by selecting rows from remote tables.

In our case we satisfy the second restriction and hence error comes.

Solution of the Problem
To implement the solution you have to provide ON DEMAND clause. You have to create as below.
SQL> create materialized view scott.mv_phones
2 TABLESPACE users
3 REFRESH FAST on demand
4 WITH rowid
5 AS select ID, PASS,
6 CONN_EXPIRY_DATE, CONN_STATUS
7 from PHONES@lnxdb where upper(IS_SOLD)=’Y’;

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/04/oversize-of-datatype-varchar2-causes-performance-issue/

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

https://samadhandba.wordpress.com/2010/12/29/materialized-views-in-oracle/

https://samadhandba.wordpress.com/2011/02/04/what-is-view-in-oracle/

https://samadhandba.wordpress.com/2011/02/04/benefits-of-using-views/

 https://samadhandba.wordpress.com/2011/02/04/ora-12091-cann…rialized-views/

Read Full Post »

Problem Description
While I attempt to reorganize a table online whenever I do with DBMS_REDEFINITION.can_redef_table or DBMS_REDEFINITION.START_REDEF_TABLE procedure on the table it fails with error ORA-12091 as below.

SQL> exec dbms_redefinition.can_redef_table(‘CR_2’, ‘OUT_CDR’);
BEGIN dbms_redefinition.can_redef_table(‘CR_2’, ‘OUT_CDR’); END;

*
ERROR at line 1:
ORA-12091: cannot online redefine table “CR_2″.”OUT_CDR” with materialized views
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 137
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 1478
ORA-06512: at line 1

Cause of the Problem
If you unsuccessfully launched the DBMS_REDEFINITION.START_REDEF_TABLE procedure
once, the materialized view would be created on the interim table and still be remained there. So subsequent run of the DBMS_REDEFINITION.CAN_REDEF_TABLE BMS_REDEFINITION.START_REDEF_TABLE will return error until you remove the materalized view against the table.

Solution of the Problem
Solution 01:
Remove the snapshot log and materialized view created during the unsuccessful
first execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure.
You can define the snapshot log and materialized view log of current table by,
SQL> select log_table from user_snapshot_logs;

LOG_TABLE
——————————
MLOG$_IN_CDR
MLOG$_OUT_CDR

SQL> select master,log_table from user_mview_logs;MASTER                         LOG_TABLE------------------------------ ------------------------------IN_CDR                         MLOG$_IN_CDROUT_CDR                        MLOG$_OUT_CDR

Remove it by,
SQL> DROP MATERIALIZED VIEW LOG ON IN_CDR;
Materialized view log dropped.

SQL> DROP MATERIALIZED VIEW LOG ON OUT_CDR;
Materialized view log dropped.

You can use the keyword SNAPSHOT in place of MATERIALIZED VIEW. The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.

Now execute your statement like,
SQL> exec dbms_redefinition.can_redef_table(‘CR_2’, ‘OUT_CDR’);
PL/SQL procedure successfully completed.
And it works.

Solution 02:
Run the dbms_redefinition.abort_redef_table procedure which will automatically do the clean up task. This procedure will remove the temporary objects that are created by the redefinition process such as materialized view logs.
SQL> exec dbms_redefinition.abort_redef_table(‘CR_2’, ‘IN_CDR’, ‘IN_CDR_’);
PL/SQL procedure successfully completed.

Related post:

https://samadhandba.wordpress.com/2010/12/29/materialized-views-in-oracle/

https://samadhandba.wordpress.com/2011/02/04/what-is-view-in-oracle/

https://samadhandba.wordpress.com/2011/02/04/benefits-of-using-views/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cann…erialized-view/

Read Full Post »

A view in oracle is nothing but a stored sql scripts. View itself contain no data. Whenever we query on a view underlying SQL scripts is executed. Suppose I have created a view as CREATE VIEW TEST_VIEW AS SELECT NAME FROM TEST; Now whenever I query on view TEST_VIEW underlying script SELECT NAME FROM TEST is executed.

A view can be queried, and the data it represents can be changed. Data in a view can be updated or deleted, and new data inserted. These operations directly alter the tables on which the view is based and are subject to the integrity constraints and triggers of the base tables.

Workaround Example:
Connect as a dba user and grant only insert privilege to table TEST on arju schema table to india.

SQL> CONN ARJU/A
Connected.

SQL> revoke SELECT ON TEST from india;
Revoke succeeded.

SQL> grant insert ON TEST TO INDIA;
Grant succeeded.

SQL> CONN INDIA/T
Connected.
SQL> CREATE VIEW ARJU_SCHMEA_VIEW AS SELECT * FROM ARJU.TEST;
View created.

Since india has only insert privilege on the base table so he can create view but can’t select any row from the base table or view.

SQL> SELECT * FROM ARJU_SCHMEA_VIEW;
SELECT * FROM ARJU_SCHMEA_VIEW
*
ERROR at line 1:
ORA-01031: insufficient privileges
Now grant him select privilege on the base table.

SQL> CONN ARJU/A
Connected.
SQL> grant SELECT ON TEST TO INDIA;
Grant succeeded.

SQL> CONN INDIA/T
Connected.
SQL> SELECT * FROM ARJU_SCHMEA_VIEW;
no rows selected

Now insert row in to the view.
SQL> INSERT INTO ARJU_SCHMEA_VIEW VALUES(1);
1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM ARJU_SCHMEA_VIEW;
A
———-
1

SQL> SELECT * FROM ARJU.TEST;
A
———-
1

So if underlying view is modified base table is affected.

Related post:

https://samadhandba.wordpress.com/2010/12/29/materialized-views-in-oracle/

https://samadhandba.wordpress.com/2011/02/04/benefits-of-using-views/

Read Full Post »

1)Verify Optimizer Statistics:
——————————————–
The query optimizer uses statistics gathered on tables and indexes when determining the optimal execution plan. If these statistics have not been gathered, or if the statistics are no longer representative of the data stored within the database, then the optimizer does not have sufficient information to generate the best plan.

So, gather statistics of all tables that are involved in SQL statements. You can check whether your statistics is up to date or not by querying
SELECT COUNT(*) FROM table_name;
and,
select NUM_ROWS from dba_tables where table_name=’TABLE_NAME’;

If they are almost same then you have correct optimizer statistics. If they don’t match then gather new statistics.

2)Review the Execution Plan:
—————————————
When tuning (or writing) a SQL statement, the goal is to drive from the table that has the most selective filter. This means that there are fewer rows passed to the next step. If the next step is a join, then this means that fewer rows are joined. Check to see whether the access paths are optimal.

We can check it by examine the optimizer execution plan following,

•The plan is such that the driving table has the best filter.

•The join order in each step means that the fewest number of rows are being returned to the next step (that is, the join order should reflect, where possible, going to the best not-yet-used filters).

•The join method is appropriate for the number of rows being returned. For example, nested loop joins through indexes may not be optimal when many rows are being returned.

•Views are used efficiently. Look at the SELECT list to see whether access to the view is necessary.

•There are any unintentional Cartesian products (even with small tables).

•Each table is being accessed efficiently:

-Consider the predicates in the SQL statement and the number of rows in the table. Look for suspicious activity, such as a full table scans on tables with large number of rows, which have predicates in the where clause. Determine why an index is not used for such a selective predicate.

-A full table scan does not mean inefficiency. It might be more efficient to perform a full table scan on a small table, or to perform a full table scan to leverage a better join method (for example, hash_join) for the number of rows returned.

If any of these conditions are not optimal, then consider restructuring the SQL statement or the indexes available on the tables.

3)Restructuring the SQL Statements
———————————————-
Often, rewriting an inefficient SQL statement is easier than modifying it. If you understand the purpose of a given statement, then you might be able to quickly and easily write a new statement that meets the requirement.

While restructuring the SQL statements keep in mind of the following issues.

•Use equijoins whenever possible.
That is compose predicate using AND and =.

•Avoid Transformed Columns in the WHERE Clause.
That is use
a=b instead of to_number(a)=to_number(b)

When you need to use SQL functions on filters or join predicates, do not use them on the columns on which you want to have an index; rather, use them on the opposite side of the predicate, as in the following statement; if you have index on varcol

TO_CHAR(numcol) = varcol

rather than

varcol = TO_CHAR(numcol)

•Write Separate SQL Statements for Specific Tasks.
SQL is not a procedural language. Using one piece of SQL to do many different things usually results in a less-than-optimal result for each task. If you want SQL to accomplish different things, then write various statements, rather than writing one statement to do different things depending on the parameters you give it.

It is always better to write separate SQL statements for different tasks, but if you must use one SQL statement, then you can make a very complex statement slightly less complex by using the UNION ALL operator.

•Use of EXISTS versus IN for Subqueries.
In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

4)Controlling the Access Path and Join Order with Hints
————————————————————————————–
You can use hints in SQL statements to instruct the optimizer about how the statement should be executed. Hints, such as /*+FULL */ control access paths.

Join order can have a significant effect on performance. The main objective of SQL tuning is to avoid performing unnecessary work to access rows that do not affect the result. This leads to three general rules:

•Avoid a full-table scan if it is more efficient to get the required rows through an index.

•Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows.

•Choose the join order so as to join fewer rows to tables later in the join order.

•Be careful when joining views, when performing outer joins to views, and when reusing an existing view for a new purpose.

•Joins to complex views are not recommended, particularly joins from one complex view to another. Often this results in the entire view being instantiated, and then the query is run against the view data.

•Beware of writing a view for one purpose and then using it for other purposes to which it might be ill-suited. Querying from a view requires all tables from the view to be accessed for the data to be returned. Before reusing a view, determine whether all tables in the view need to be accessed to return the data. If not, then do not use the view. Instead, use the base table(s), or if necessary, define a new view. The goal is to refer to the minimum number of tables and views necessary to return the required data.

•An outer join within a view is problematic because the performance implications of the outer join are not visible.

•Consider using materialized views.

5)Restructuring the Indexes
———————————————–
Often, there is a beneficial impact on performance by restructuring indexes. This can involve the following:

Remove nonselective indexes to speed the DML.
Index performance-critical access paths.
Consider reordering columns in existing concatenated indexes.
Add columns to the index to improve selectivity.

6)Modifying or Disabling Triggers and Constraints
———————————————————–
Using triggers consumes system resources. If you use too many triggers, then you can find that performance is adversely affected and you might need to modify or disable them.

7)Restructuring the Data
—————————————–
After restructuring the indexes and the statement, you can consider restructuring the data.

Introduce derived values. Avoid GROUP BY in response-critical code.

Review your data design. Change the design of your system if it can improve performance.

Consider partitioning, if appropriate.

8)Combine Multiples Scans with CASE Statements
———————————————————–
Often, it is necessary to calculate different aggregates on various sets of tables. Usually, this is done with multiple scans on the table, but it is easy to calculate all the aggregates with one single scan. Eliminating n-1 scans can greatly improve performance.

9)Maintaining Execution Plans Over Time
——————————————————-
You can maintain the existing execution plan of SQL statements over time either using stored statistics or stored SQL execution plans. Storing optimizer statistics for tables will apply to all SQL statements that refer to those tables. Storing an execution plan (that is, plan stability) maintains the plan for a single SQL statement. If both statistics and a stored plan are available for a SQL statement, then the optimizer uses the stored plan.

Read Full Post »

To achieve optimum performance for data-intensive queries, materialized views and indexes are essential when tuning SQL statements. The SQL Access Advisor enables to optimize data access paths of SQL queries by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload.

A materialized view provides access to table data by storing the results of a query in a separate schema object. A materialized view contains the rows resulting from a query against one or more base tables or views.

A materialized view log is a schema object that records changes to a master table’s data, so that a materialized view defined on the master table can be refreshed incrementally.

The SQL Access Advisor also recommends bitmap, function-based, and B-tree indexes. A bitmap index provides a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. A functional index derives the indexed value from the table data. For example, to find character data in mixed cases, a functional index can be used to look for the values as if they were all in uppercase characters.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table’s data. Materialized views, which store data based on remote tables are also, know as snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.

For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.

In this article, we shall see how to create a Materialized View and discuss Refresh Option of the view.

In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.

Primary Key Materialized Views
The following statement creates the primary-key materialized view on the table emp located on a remote database.

SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;

Materialized view created.

Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.

Rowid Materialized Views
The following statement creates the rowid materialized view on table emp located on a remote database:

SQL> CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID
AS SELECT * FROM emp@remote_db;

Materialized view log created.

Subquery Materialized Views

The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:

SQL> CREATE MATERIALIZED VIEW mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
(SELECT * FROM dept@remote_db d
WHERE e.dept_no = d.dept_no)

REFRESH CLAUSE
[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]

The refresh option specifies:

The refresh method used by Oracle to refresh data in materialized view
Whether the view is primary key based or row-id based
The time and interval at which the view is to be refreshed
Refresh Method – FAST Clause

The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.

You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.

SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.

Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

Refresh Method – COMPLETE Clause
The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

Refresh Method – FORCE Clause
When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.

PRIMARY KEY and ROWID Clause
WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.

Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

Rowid materialized views should have a single master table and cannot contain any of the following:

Distinct or aggregate functions
GROUP BY Subqueries , Joins & Set operations
Timing the refresh
The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes

SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 2
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;

Materialized view created.

In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.

Related post:

https://samadhandba.wordpress.com/2010/12/29/materialized-views-in-oracle/

https://samadhandba.wordpress.com/2011/02/04/what-is-view-in-oracle/

https://samadhandba.wordpress.com/2011/02/04/benefits-of-using-views/

Read Full Post »