Ora Analytic Func: 2007

Monday, December 24, 2007

OWB Workflow

Today as usual i have browsed some of the links for OWB. I have come across this link in google.
http://cali97.blogspot.com/search/label/OWB
I am sure, out of 4 blogs, i dont have knowledge on 3. Then the blogger have blogged the following about a client interview question which explains about running the process flow from outside.

"I recently was contacted by one of my former clients that had a pretty good question. I actually had this questions asked a few times by various parties, so here is a summery and an answer!"Quick OWB question for you: Is it possible to execute process flows from outside of OWB once they have been created. Wondering if there is a template provided by oracle(like “Sql_exec_template for OWB mappings) to execute Process flows…"Well, OWB does contain a pl/sql package called WB_RT_API_EXEC with a function inside it called RUN_TASK that submits execution requests to the OWB runtime engine. The requests could be for mappings, process flows or scheduled jobs. The function spec looks like:

function run_task
(
p_location_name in varchar2,
p_task_type in varchar2,
p_task_name in varchar2,
p_custom_params in varchar2 default null,
p_system_params in varchar2 default null,
p_oem_friendly in number default 0,
p_background in number default 0)
return number;

This is actually the same function used by the sqlplus_exec_template.sql script in OWB 10gR2 that you mentioned in your email. It can be called using (assuming a repository owner connection exists):

(this is an example…)
declare
result_num number;
begin
result_num:= wb_rt_api_exec.run_task('PFMOD_LOCATION1', 'PROCESS', 'MY_PKG/MY_PF', ',', ',', 0 ,0);
end;

The full documentation of the function can be found in /owb/rtasst/wb_rt_api_exec.pl"

I have to go thru this documentation to update my knowledge. Will update you all on this.
Thanks,
Soma

Musings - OWB Workflow

I already know how to check whether the Control Center (CC) service is available or not using the template sql provided along with the OWB client installation.

I have come across this following thread in Oracle Warehouse Builder forum which explains how we can check the Workflow installation.

http://forums.oracle.com/forums/thread.jspa?threadID=597761&tstart=30

One guy has posted the following query to check the workflow installation.

SELECT
TEXT VERSION
FROM
.WF_RESOURCES
WHERE
TYPE = 'WFTKN'
AND NAME = 'WF_VERSION'".( - default owf_mgr)

I have checked with my repository installation. When I run the query I got table doesn’t exist. In that server we have installed Oracle Workflow (no idea on version).

OWB Useful links

Sunday, November 18, 2007

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

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

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

Wednesday, November 7, 2007

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

Sunday, November 4, 2007

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

Monday, August 6, 2007

Rename a Materialized View

A picture is worth a thousand words – Found this Proverb in the Wikipedia.

Today I came across a situation where I need to replace the existing materialized view with the new view as the view definition was changed. I created a materialized view with the new query definition with different name and dropped the old mview. When I tried to rename the new Mview I have created I came across the following error in the Database. Oracle won’t support renaming the materialized views.

I need to explore on this one further. Meanwhile i found the error code and description in the Ora-Code site, please take a look into that.

Meet you all with further details.

Sunday, June 17, 2007

Curious about using Oracle Analytical Functions

Getting Cumulative sum using Oracle Analytical Functions

1) The following code gives cumulative sum. The following code uses the SAL column from the standard EMP table
SQL> select b.sal,sum(a.sal) as cum_sal2 from emp a,emp b3 where a.rowid <= b.rowid4 group by b.rowid,b.sal
5 /

I found the code in the following link.
http://www.oracle.com/technology/oramag/code/tips2005/052305.html

2) Alternative Way to find Cumulative sum using Analytical Functions
SQL> select deptno, 2 ename, 3 sal, 4 sum(sal) over (partition by deptno 5 order by sal,ename) CumDeptTot, 6 sum(sal) over (partition by deptno) SalByDept, 7 sum(sal) over (order by deptno, sal) CumTot, 8 sum(sal) over () TotSal 9 from emp 10 order by deptno, sal;

a. CumDeptTot – This Sum will give the cumulative sum for each Department
b. SalByDept - This will give the Sum of Salary for Each Department

Description by the Author

sum(sal) over (partition by deptno order by sal, ename) CumDeptTot: Computes a running total on salaries within a department, in order of salaries from lowest salary to the highest. Adding the ORDER BY clause changed the behavior of the analytic function. Instead of working on every row in the partition, it worked only on the current and prior rows in the partition after sorting by SAL.

sum(sal) over (partition by deptno) SalByDept: Computes a department total. The SAL column was summed by department now. The individual EMP records can compare their salary against the total salary made in that department. The PARTITION keyword breaks up our result set into virtual partitions on which the analytic functions will be applied.

sum(sal) over (order by deptno, sal) CumTot: Creates a running total of the SAL column after the data was sorted by DEPTNO and then SAL. Since out entire result set would be sorted this way, it becomes a running total for our entire result set.

sum(sal) over () TotSal: Computes a grand total. This is equivalent to select sum(sal) from emp, but that query would return only one row. Here we get the sum of SAL associated with each detail row. We can easily compare each salary in the EMP table to the grand total sum of SAL.

Source: http://www.oracle.com/technology/oramag/code/tips2005/062005.html

Ora Analytic Func