Creating Materialized View

You need sufficient privileges to create a materialized view in the particular DB.

At first I tried with an user name and password to create a materialized view. The particular User name doesn’t have privileges to create a materialized view.

CREATE MATERIALIZED MV_TEST

AS

SELECT * FROM EMPLOYEE;

I got the following error when I try to create a materialized view.

ORA-01031: insufficient privileges

So I need a Role to be granted to create materialized view.

GRANT CREATE MATERIALIZED VIEW TO USER_NAME;

GRANT SUCEEDED.

Then I tried with the above create statement with the corresponding user name. I can able to create Materialized view

Materialized View Refresh Methods

Today I went through some of the articles about the materialized view. In that article they mentioned that there are 3 types of MV Refresh Methods.

1) Force Refresh

2) Fast Refresh

3) Complete Refresh

These 3 types which are used while scheduling the Materialized view refesh.

Other that there are another method of refreshing which will be helpful when you are constantly changing the query of materialized view

On Demand Refresh – There is a oracle package which will help you to refresh the materialized view (DBMS_MVIEW.REFRSH)

We will explore about this package in upcoming blogs.

Gather Statistics for a table object

Today I leant how to gather statistics for a table. For this I have created a table and inserted some records into the table. Then I queried the DD(Data Dictionary) table with the following query.

Select num_rows from USER_TABLES WHERE TABLE_NAME = 'SOMA';

Obviously this didn’t give any result about the number of rows in the table.

If you want the above query to return the number of rows for a table, you have to gather statistics for that table.

In order to gather statistics, I used the following statement.

analyze table soma compute statistics;

I got the output that ‘TABLE ANALYZED’

Then I checked the output of the query.

Select num_rows from USER_TABLES WHERE TABLE_NAME = 'SOMA';

NUM_ROWS

-------------

6

Ranking Function - Oracle Analytical Functions

Analytical Function has following basic syntax:
function(argument) OVER(Analytic Clause)

The arguments of the function can be empty as in the below example;

Example1
Select RANK() OVER (ORDER BY SALARY), EMPLOYEEID
FROM EMPLOYEE

This query will generate the rank for each rows based on the salary. The Order by query in the analytical clause is similar to the ordinary Order by. So it will default to Ascending.

The in the above example will contain an Ordering, Partitioning or Windowing.

The advantages of Rank function over the traditional ranking queries.

We can generate the ROWNUM and then compute the RANK based on that. It is somehow tricky. Following are the problems with ROWNUM.

1) ROWNUM is computed as rows are retrieved from the database.
2) The Problem with ROWNUM is that It operates before the Order By sorting is executed.

We can achieve using ordinary query like this.

SELECT NAME, SALARY, ROWNUM(RANK) FROM
(SELECT NAME, SALARY FROM SOMA ORDER BY SALARY);

However the above query won’t solve the Ranking problem. Since we are showing the ROWNUM as the Rank. It won’t handle 2 rows if the 2 rows are having same salary.

There are some analytical functions to handle this Ranking issue : RANK(), ROW_NUMBER(), DENSE_RANK. We will see about these functions one by one.

1) ROW_NUMBER function:

SELECT EMPNAME. EMPNUM, SALARY, ROW_NUMBER() OVER (ORDER BY SALARY DESC) TOP_RANK FROM EMPLOYEE
ORDER BY EMPNAME;

Analytical Functions will generate a Ordering by themselves. Although it is useful, we have to be careful about the final result set ordering. So I am adding another ORDER BY to the external query to sort based on Employee name. The above query can be replicated using the ordinary query as follows.

SELECT EMPNAME, EMPNUM, SALARY, ROWNUM as RANK
FROM
(SELECT EMPNAME, EMPNUM, SALARY FROM
EMPLOYEE ORDER BY SALARY DESC)
ORDER BY EMPNAME
We can use RANK and DENSE_RANK function as the way we use ROW_NUMBER() function.

2) RANK() function:

Rank function will produce the row numbering and skip the rank if there is a tie.

SELECT
EMPNO, EMPNAME , SALARY,
RANK() OVER (ORDER BY SALARY DESC) RANK
FROM
EMPLOYEE

The result set of this query will be like this.
EMPNO EMPNAME SALARY RANK
1304 SOMA 10000 1
1309 SATHISH 9500 2
1410 SATHYA 9500 2
1516 SUNDAR 8000 4

3) DENSE_RANK() Function

DENSE_RANK Function acts similar to Rank function but instead of ranking the tied rows and move up to the next rank beyond the tie. It will not skip the next rank level.

SELECT
EMPNO, EMPNAME , SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) RANK
FROM
EMPLOYEE

The result set of this query will be like this.

EMPNO EMPNAME SALARY RANK
1304 SOMA 10000 1
1309 SATHISH 9500 2
1410 SATHYA 9500 2
1516 SUNDAR 8000 3

Tips for Efficient SQL

1) Use minus instead of EXISTS subqueries - Some say that using the minus operator instead of NOT IN and NOT Exists will result in a faster execution plan.
2) Use SQL analytic functions - The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL.
3) Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins - In many cases of NOT queries (but ONLY where a column is defined as NULL), you can re-write the uncorrelated subqueries into outer joins with IS NULL tests. Note that this is a non-correlated sub-query, but it could be re-written as an outer join.
select book_key from book where book_key NOT IN (select book_key from sales);
Below we combine the outer join with a NULL test in the WHERE clause without using a sub-query, giving a faster execution plan.
select b.book_key from book b, sales s where b.book_key = s.book_key(+) and s.book_key IS NULL;
4) Index your NULL values - If you have SQL that frequently tests for NULL, consider creating an index on NULL Values. To get around the optimization of SQL queries that choose NULL column values (i.e. where emp_name IS NULL), we can create a function-based index using the null value built-in SQL function to index only on the NULL columns.
5) Avoid the use of NOT IN or HAVING. Instead, a NOT EXISTS subquery may run faster (when appropriate).
6) Avoid the LIKE predicate = Always replace a "like" with an equality, when appropriate.
7) Never mix data types - If a WHERE clause column predicate is numeric, do not to use quotes. For char index columns, always use quotes. There are mixed data type predicates:
where cust_nbr = "123"where substr(ssn,7,4) = 1234
8) Use decode and case - Performing complex aggregations with the “decode” or "case" functions can minimize the number of times a table has to be selected.
9) Use those aliases - Always use table aliases when referencing columns.
10) Use explicit cursors over implicit cursors - Implicit cursors result in an extra fetch. Explicit cursors are opened by developers by using DECLARE, OPEN, FETCH and CLOSE cursor statements. Implicit cursors are opened by Oracle for DELETE, UPDATE, INSERT and SELECT statements.
Some Important Links:
* http://www.dbpd.com/vault/9801xtra.htm - 20 SQL Commandments