ZAHIR MOHIDEEN: June 2014

Wednesday, June 18, 2014

How to retrieve the underlying SQL for a view.

Some time , you run into performance tuning tasks , where non-performant query uses views . Those views in turn uses views one after the other .
Though , this is not a recommended practice , it is widely (ab)used . You need to reverse engineer the view to get the actual base tables and its filters and so on.
Most of the time , the query performs better when directly accessed with the base table ( it depends on the query , and other factors) .

In any case , Oracle 12c has introduced a new API ( dbms_utility.EXPAND_SQL_TEXT ) to get the underlying base table structures for the views .
This is in one of the most underutilized package , in my opinion ( dbms_utility ).

Let us get to the example .

SQL> CREATE OR REPLACE VIEW dept_sales_v
2 AS
3 SELECT
4 deptno AS DepartmentNumber ,
5 dname AS DepartmentName ,
6 loc AS Location
7 FROM
8 scott.dept
9 WHERE
10 deptno = 30 ;

View created.

Now , Let us use this new API to get the underlying DDL for the view.
You may notice that the generated DDL is conceptually the same , syntactically Oracle has created sub query and reference that sub query in the outer query.

SQL> SET serveroutput ON
SQL> SET LONG 10000
SQL> DECLARE
2 expsql CLOB ;
3 inpsql CLOB ;
4 BEGIN
5 inpsql := 'Select * From dept_sales_v' ;
6 dbms_utility.EXPAND_SQL_TEXT ( input_sql_text => inpsql, output_sql_text =>
7 expsql) ;
8 dbms_output.put_line( expsql) ;
9 END ;
10 /
SELECT "A1"."DEPARTMENTNUMBER" "DEPARTMENTNUMBER","A1"."DEPARTMENTNAME"
"DEPARTMENTNAME","A1"."LOCATION" "LOCATION" FROM (SELECT "A2"."DEPTNO"
"DEPARTMENTNUMBER","A2"."DNAME" "DEPARTMENTNAME","A2"."LOC" "LOCATION" FROM
"SCOTT"."DEPT" "A2" WHERE "A2"."DEPTNO"=30) "A1"

PL/SQL procedure successfully completed.

Now , let us create a view based on this view.

SQL> CREATE OR REPLACE VIEW EmpSales_v
2 AS
3 SELECT
4 v.DEPARTMENTNAME ,
5 v.location ,
6 e.EMPNO AS EmployeeCode,
7 e.ENAME AS EmployeeName
8 FROM
9 DEPT_SALES_V v ,
10 emp e
11 WHERE
12 v.DEPARTMENTNUMBER = e.deptno ;

View created.

Here is the SQL generated by Oracle for this view.

SQL> set serveroutput on
SQL> set long 10000
SQL> declare
2 expsql clob ;
3 inpsql clob ;
4 begin
5 inpsql := 'Select * From EmpSales_v' ;
6 dbms_utility.EXPAND_SQL_TEXT ( input_sql_text => inpsql, output_sql_text => expsql) ;
7 dbms_output.put_line( expsql) ;
8 end ;
9 /
SELECT "A1"."DEPARTMENTNAME" "DEPARTMENTNAME","A1"."LOCATION"
"LOCATION","A1"."EMPLOYEECODE" "EMPLOYEECODE","A1"."EMPLOYEENAME" "EMPLOYEENAME"
FROM (SELECT "A3"."DEPARTMENTNAME" "DEPARTMENTNAME","A3"."LOCATION"
"LOCATION","A2"."EMPNO" "EMPLOYEECODE","A2"."ENAME" "EMPLOYEENAME" FROM (SELECT
"A4"."DEPTNO" "DEPARTMENTNUMBER","A4"."DNAME" "DEPARTMENTNAME","A4"."LOC"
"LOCATION" FROM "SCOTT"."DEPT" "A4" WHERE "A4"."DEPTNO"=30) "A3",SCOTT."EMP"
"A2" WHERE "A3"."DEPARTMENTNUMBER"="A2"."DEPTNO") "A1"

PL/SQL procedure successfully completed.

Here is the SQL that has been formatted for Clarity.

Comments Welcome.

Monday, June 16, 2014

Misleading Error in adding node to the SQL Server Failover Cluster.

Ran into an issue ,while adding a node to existing SQL Server Cluster .

Got the following error during install .

The current SKU is invalid.

After few google searches , found the workaround.

The workaround is simple. Clear the entry for PID in ....\x64\Defaultsetup.ini ( make sure that you copy this file to different directory) and manually type in the product key during setup. By following this workaround , the setup finished sucessfully.

You can see http://support.microsoft.com/kb/957459 for additional information . Per MS , this is fixed in the cumulative update.

SQL Treasures.

The reason that I call this topic as treasure is that this feature was introduced in Oracle 8.1.6 ( a whiiiiiile back ) . Not many people are using ( to its fullest extent ) .

You would have guessed it by now . Yeah . It is analytics.

Analytics is one of the greatest additon to the SQL family . Though ,it is documented only in Datawarehousing guide . It is equally important in OLTP application .

It has ranking , reporting , statisticial functions to name a few.

As an example , let us go to scott schema .

In this post , I will cover few basic analytical SQLs . Some of the analytical constructs has been introduced in SQL Server 2005.

Rest of the analytical functions in the subsequent posts.

Example 1:

Usuage of ROW_NUMBER function .

This is to return a running sequence number.

SQL> SELECT empno,

  2         ename,

  3         job,

  4         sal,

  5         deptno,

  6         row_number() over(ORDER BY sal DESC) rn

  7  FROM emp;

EMPNO ENAME      JOB             SAL DEPTNO         RN

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

 7839 KING       PRESIDENT   5000.00     10          1

 7902 FORD       ANALYST     3000.00     20          2

 7788 SCOTT      ANALYST     3000.00     20          3

 7566 JONES      MANAGER     2975.00     20          4

 7698 BLAKE      MANAGER     2850.00     30          5

 7782 CLARK      MANAGER     2450.00     10          6

 7499 ALLEN      SALESMAN    1600.00     30          7

 7844 TURNER     SALESMAN    1500.00     30          8

 7934 MILLER     CLERK       1300.00     10          9

 7521 WARD       SALESMAN    1250.00     30         10

 7654 MARTIN     SALESMAN    1250.00     30         11

 7876 ADAMS      CLERK       1100.00     20         12

 7900 JAMES      CLERK        950.00     30         13

 7369 SMITH      CLERK        800.00     20         14

Example 2:

The following example has lot of functions .

1. LEAD and LAG will let us to look at the previous and next records's value .

2. RANK and DENSE_RANK let us to rank the particular record based on the column ( we specify ) . The difference between RANK and DENSE_RANK comes when there is a tie in the column value . DENSE_RANK does not skip the rank , where as the RANK does.

SQL> SELECT empno,
2         ename,
3         job,
4         sal,
5         deptno,
6         row_number() over(PARTITION BY deptno ORDER BY sal ASC) rn,
7         rank() over(PARTITION BY deptno ORDER BY sal ASC) rank,
8         dense_rank() over(PARTITION BY deptno ORDER BY sal ASC) dense_rank,
9         lag(sal) over(PARTITION BY deptno ORDER BY sal ASC) previous_sal,
10         lead(sal) over(PARTITION BY deptno ORDER BY sal ASC) next_sal
11 FROM emp
12 ORDER BY deptno, sal
13 /

EMPNO ENAME      JOB             SAL DEPTNO         RN       RANK DENSE_RANK PREVIOUS_SAL   NEXT_SAL
----- ---------- --------- --------- ------ ---------- ---------- ---------- ------------ ----------
7934 MILLER     CLERK       1300.00     10          1          1          1                    2450
7782 CLARK      MANAGER     2450.00     10          2          2          2         1300       5000
7839 KING       PRESIDENT   5000.00     10          3          3          3         2450
7369 SMITH      CLERK        800.00     20          1          1          1                    1100
7876 ADAMS      CLERK       1100.00     20          2          2          2          800       2975
7566 JONES      MANAGER     2975.00     20          3          3          3         1100       3000
7788 SCOTT      ANALYST     3000.00     20          4          4          4         2975       3000
7902 FORD       ANALYST     3000.00     20          5          4          4         3000
7900 JAMES      CLERK        950.00     30          1          1          1                    1250
7654 MARTIN     SALESMAN    1250.00     30          2          2          2          950       1250
7521 WARD       SALESMAN    1250.00     30          3          2          2         1250       1500
7844 TURNER     SALESMAN    1500.00     30          4          4          3         1250       1600
7499 ALLEN      SALESMAN    1600.00     30          5          5          4         1500       2850
7698 BLAKE      MANAGER     2850.00     30          6          6          5         1600

Example 3:

NTILE let us to divide the results in equal height .

In the following example , the result set is divided into three equal parts .

I find NTILE very useful in do it yourself (DIY) parallel-zing jobs . In DIY jobs , you could divvy up the result set and send it to parallel jobs either using DBMS_JOB / DBMS_SCHDULER.

SQL> SELECT empno, ename, job, sal, deptno, ntile(3) over(ORDER BY sal) ntile
2 FROM emp
3 ORDER BY sal
4 /

EMPNO ENAME      JOB             SAL DEPTNO      NTILE
----- ---------- --------- --------- ------ ----------
7369 SMITH      CLERK        800.00     20          1
7900 JAMES      CLERK        950.00     30          1
7876 ADAMS      CLERK       1100.00     20          1
7521 WARD       SALESMAN    1250.00     30          1
7654 MARTIN     SALESMAN    1250.00     30          1
7934 MILLER     CLERK       1300.00     10          2
7844 TURNER     SALESMAN    1500.00     30          2
7499 ALLEN      SALESMAN    1600.00     30          2
7782 CLARK      MANAGER     2450.00     10          2
7698 BLAKE      MANAGER     2850.00     30          2
7566 JONES      MANAGER     2975.00     20          3
7788 SCOTT      ANALYST     3000.00     20          3
7902 FORD       ANALYST     3000.00     20          3
7839 KING       PRESIDENT   5000.00     10          3

Example 4:

The first look at the following example may be little bit intimidating . If you look at the second example , it may be clear .

In the latter example , we get the ratio of the salaries in DEPT 10 . The total of sal is 8750 , out of which CLARK's salary is 2450 ( 28% of the department's total salary ).

It helped us in one scenario , where the requirement was to save the report into an excel spreadsheet format .
This report was developed using PowerBuilder . In PowerBuilder , we can save the contents of the datawindow into an excel spreadsheet . The developer has done in the ratio calculations in the front end ( computed column in PB lingo ) . When the data window was saved as an excel , the calculation did not make into an excel ( as only the result set of the SQL was saved ) . In this case , the following function came in handy.

SQL> SELECT empno,
2         ename,
3         job,
4         deptno,
5         sal,
6         ratio_to_report(sal) over() rr_whole,
7         ratio_to_report(sal) over(PARTITION BY deptno) rr_deptno
8 FROM emp
9 /

EMPNO ENAME      JOB       DEPTNO       SAL   RR_WHOLE RR_DEPTNO
----- ---------- --------- ------ --------- ---------- ----------
7782 CLARK      MANAGER       10   2450.00 0.08440999       0.28
7839 KING       PRESIDENT     10   5000.00 0.17226528 0.57142857
7934 MILLER     CLERK         10   1300.00 0.04478897 0.14857142
7566 JONES      MANAGER       20   2975.00 0.10249784 0.27356321
7902 FORD       ANALYST       20   3000.00 0.10335917 0.27586206
7876 ADAMS      CLERK         20   1100.00 0.03789836 0.10114942
7369 SMITH      CLERK         20    800.00 0.02756244 0.07356321
7788 SCOTT      ANALYST       20   3000.00 0.10335917 0.27586206
7521 WARD       SALESMAN      30   1250.00 0.04306632 0.13297872
7844 TURNER     SALESMAN      30   1500.00 0.05167958 0.15957446
7499 ALLEN      SALESMAN      30   1600.00 0.05512489 0.17021276
7900 JAMES      CLERK         30    950.00 0.03273040 0.10106382
7698 BLAKE      MANAGER       30   2850.00 0.09819121 0.30319148
7654 MARTIN     SALESMAN      30   1250.00 0.04306632 0.13297872

SQL> SELECT empno,
2         ename,
3         job,
4         deptno,
5         sal,
6         ratio_to_report(sal) over(PARTITION BY deptno) rr_deptno
7 FROM emp
8 Where deptno = 10
9 /

EMPNO ENAME      JOB       DEPTNO       SAL RR_DEPTNO
----- ---------- --------- ------ --------- ----------
7782 CLARK      MANAGER       10   2450.00       0.28
7839 KING       PRESIDENT     10   5000.00 0.57142857
7934 MILLER     CLERK         10   1300.00 0.14857142

Data comparison

When we want to synchronize the data between same or multiple databases , oracle 11g provides new package called DBMS_COMPARISON. This is a procedural logic.

If this is a straightforward operation , please use MERGE statement.

Let us look at an example.

1. Let us create an user in the source database and create a table called emp from scott schema.

SQL> grant connect , dba to testuser identified by tesTusr ;

Grant succeeded.

SQL> create table emp as select * from scott.emp;
Table created.

SQL> alter table emp add constraint emp_pk primary key(empno);
Table altered.

In the destinaton database , let me create an user called testuser.
And also create a database link to the source database.

SQL> grant connect , dba to testuser identified by tesTusr ;
Grant succeeded.

C:\Users\mohideen>sqlplus testuser/tesTusr

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 22 17:14:31 2010

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> create database link

2 remotedb_link connect to srcuser

3 identified by srcusr

4 using 'remotedb';

Database link created.

SQL> select * from dual@remotedb_link;

SQL> create table emp as select * from srcuser.emp@remotedb_link where 1= 2;

Table created.

OK... We are all set . Let us sync it...

SQL> DECLARE

2 scan_info dbms_comparison.comparison_type;

3 scan_id INTEGER;

4 consistent BOOLEAN;

5 BEGIN

7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',

9 schema_name => 'testuser',

10 object_name => 'emp',

11 dblink_name => 'remotedb_link',

12 remote_schema_name => 'srcuser',

13 remote_object_name => 'emp');

15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',

16 scan_info => scan_info);

18 IF consistent = TRUE THEN

19 dbms_output.put_line('No differences were found.');

20 ELSE

21 dbms_output.put_line('Differences were found.');

22 END IF;

24 dbms_comparison.converge(comparison_name => 'comp_emp',

25 scan_id => scan_info.scan_id,

26 scan_info => scan_info,

27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);

30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);

31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);

32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);

33 dbms_output.put_line('Remote Rows Deleted:' ||

34 scan_info.rmt_rows_deleted);

35 END;

36 /

DECLARE

ERROR at line 1:

ORA-23626: No eligible index on table TESTUSER.EMP

ORA-06512: at "SYS.DBMS_COMPARISON", line 4793

ORA-06512: at "SYS.DBMS_COMPARISON", line 448

ORA-06512: at line 8

OOPS ... We need to add an index . Oracle compares the data by index . ie , The data between the source and the destination objects should be uniquely identifiable.

SQL> alter table emp add constraint emp_pk primary key(empno);

Table altered.

SQL> DECLARE

2 scan_info dbms_comparison.comparison_type;

3 scan_id INTEGER;

4 consistent BOOLEAN;

5 BEGIN

7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',

9 schema_name => 'testuser',

10 object_name => 'emp',

11 dblink_name => 'remotedb_link',

12 remote_schema_name => 'srcuser',

13 remote_object_name => 'emp');

15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',

16 scan_info => scan_info);

18 IF consistent = TRUE THEN

19 dbms_output.put_line('No differences were found.');

20 ELSE

21 dbms_output.put_line('Differences were found.');

22 END IF;

24 dbms_comparison.converge(comparison_name => 'comp_emp',

25 scan_id => scan_info.scan_id,

26 scan_info => scan_info,

27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);

30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);

31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);

32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);

33 dbms_output.put_line('Remote Rows Deleted:' ||

34 scan_info.rmt_rows_deleted);

35 END;

36 /

Differences were found.

Scan ID: 1

Local Rows Merged:14

Remote Rows Merged:0

Local Rows Deleted:0

Remote Rows Deleted:0

PL/SQL procedure successfully completed.

Yes . We did it . The data has been synchronized. Oracle found there was a difference between the source and the destination objects . Since , we said synchorize the data if there were differences , it did it .

As you can see in the previous example ,

1. first , we have to create an comparison object ( line 8 )

2. second , we have to compare the objects ( line 15)

3. Based on the output of step 2, we sync ( converge) the data .. We can either say which site wins ( either REMOTE or LOCAL) .

SQL> select count(*) from emp;

COUNT(*)

----------

SQL> DECLARE

2 scan_info dbms_comparison.comparison_type;

3 scan_id INTEGER;

4 consistent BOOLEAN;

5 BEGIN

7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',

9 schema_name => 'testuser',

10 object_name => 'emp',

11 dblink_name => 'remotedb_link',

12 remote_schema_name => 'srcuser',

13 remote_object_name => 'emp');

15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',

16 scan_info => scan_info);

18 IF consistent = TRUE THEN

19 dbms_output.put_line('No differences were found.');

20 ELSE

21 dbms_output.put_line('Differences were found.');

22 END IF;

24 dbms_comparison.converge(comparison_name => 'comp_emp',

25 scan_id => scan_info.scan_id,

26 scan_info => scan_info,

27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);

30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);

31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);

32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);

33 dbms_output.put_line('Remote Rows Deleted:' ||

34 scan_info.rmt_rows_deleted);

35 END;

36 /

DECLARE

ERROR at line 1:

ORA-23627: Comparison object "TESTUSER"."COMP_EMP" already existed.

ORA-06512: at "SYS.DBMS_COMPARISON", line 4793

ORA-06512: at "SYS.DBMS_COMPARISON", line 448

ORA-06512: at line 8

Errr.. Let us delete the comparison object and try again

SQL> exec dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

PL/SQL procedure successfully completed.

SQL>

SQL> DECLARE

2 scan_info dbms_comparison.comparison_type;

3 scan_id INTEGER;

4 consistent BOOLEAN;

5 BEGIN

7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',

9 schema_name => 'testuser',

10 object_name => 'emp',

11 dblink_name => 'remotedb_link',

12 remote_schema_name => 'srcuser',

13 remote_object_name => 'emp');

15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',

16 scan_info => scan_info);

18 IF consistent = TRUE THEN

19 dbms_output.put_line('No differences were found.');

20 ELSE

21 dbms_output.put_line('Differences were found.');

22 END IF;

24 dbms_comparison.converge(comparison_name => 'comp_emp',

25 scan_id => scan_info.scan_id,

26 scan_info => scan_info,

27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);

30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);

31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);

32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);

33 dbms_output.put_line('Remote Rows Deleted:' ||

34 scan_info.rmt_rows_deleted);

35 END;

36 /

No differences were found.

Scan ID: 4

Local Rows Merged:0

Remote Rows Merged:0

Local Rows Deleted:0

Remote Rows Deleted:0

PL/SQL procedure successfully completed.

SQL> delete from emp where empno = 7934;

1 row deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

PL/SQL procedure successfully completed.

SQL> DECLARE

2 scan_info dbms_comparison.comparison_type;

3 scan_id INTEGER;

4 consistent BOOLEAN;

5 BEGIN

7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',

9 schema_name => 'testuser',

10 object_name => 'emp',

11 dblink_name => 'remotedb_link',

12 remote_schema_name => 'srcuser',

13 remote_object_name => 'emp');

15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',

16 scan_info => scan_info);

18 IF consistent = TRUE THEN

19 dbms_output.put_line('No differences were found.');

20 ELSE

21 dbms_output.put_line('Differences were found.');

22 END IF;

24 dbms_comparison.converge(comparison_name => 'comp_emp',

25 scan_id => scan_info.scan_id,

26 scan_info => scan_info,

27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);

30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);

31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);

32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);

33 dbms_output.put_line('Remote Rows Deleted:' ||

34 scan_info.rmt_rows_deleted);

35 END;

36 /

Differences were found.

Scan ID: 5

Local Rows Merged:1

Remote Rows Merged:0

Local Rows Deleted:0

Remote Rows Deleted:0

PL/SQL procedure successfully completed.