Archives mensuelles : janvier 2013

Rotating table with Oracle UNPIVOT

I had recently faced a SQL question about rotating a table

The question was how to rotate transform a table gettint multiple columns into rows

Here is a small sample to create the table

CREATE TABLE test_tab(productm VARCHAR2(30),
                      leg1 VARCHAR2(30),
                      legval1 INTEGER,
                      leg2 VARCHAR2(30),
                      legval2 INTEGER,
                      leg3 VARCHAR2(30),
                      legval3 INTEGER);

INSERT INTO test_tab
VALUES ('prod 1', 'subprod 1-1', 11, 'subprod 1-2', 12, 'subprod 1-3', 13);
INSERT INTO test_tab
VALUES ('prod 2', 'subprod 2-1', 21, 'subprod 2-2', 22, 'subprod 2-3', 23);
INSERT INTO test_tab
VALUES ('prod 3', 'subprod 3-1', 31, 'subprod 3-2', 32, 'subprod 3-3', 33);
INSERT INTO test_tab
VALUES ('prod 4', 'subprod 4-1', 41, 'subprod 4-2', 42, NULL, NULL);
INSERT INTO test_tab
VALUES ('prod 5', 'subprod 5-1', 51, NULL, NULL, NULL, NULL);
COMMIT;

SELECT * FROM test_tab;

PRODUCTM  LEG1         LEGVAL1  LEG2         LEGVAL2  LEG3         LEGVAL3
--------- ------------ -------- ------------ -------- ------------ --------
prod 1    subprod 1-1        11 subprod 1-2        12 subprod 1-3        13
prod 2    subprod 2-1        21 subprod 2-2        22 subprod 2-3        23
prod 3    subprod 3-1        31 subprod 3-2        32 subprod 3-3        33
prod 4    subprod 4-1        41 subprod 4-2        42                 
prod 5    subprod 5-1        51                                          

And the result needed was something like that

PRODUCTM         PRODNAME         PRODVAL
---------------- ---------------- ---------------
prod 1           subprod 1-1                   11
prod 1           subprod 1-2                   12
prod 1           subprod 1-3                   13
prod 2           subprod 2-1                   21
prod 2           subprod 2-2                   22
prod 2           subprod 2-3                   23
prod 3           subprod 3-1                   31
prod 3           subprod 3-2                   32
prod 3           subprod 3-3                   33
prod 4           subprod 4-1                   41
prod 4           subprod 4-2                   42
prod 5           subprod 5-1                   51

After some researchs i finally found that Oracle 11G got the solution for me

SELECT productm, prodname, prodval
FROM   test_tab unpivot((prodname, prodval) 
FOR    prod IN( (leg1, legval1),
                (leg2, legval2),
                (leg3, legval3)));

Tuning Database link operations

I was recently trying to tune one of my process that is working with a database link. The process is to insert datas based on a join using a small local table and a distant big one. The problem i was hitting was that because Oracle does not have enough information on the distant table it was sending me back all rows from the distant table.

After some research i found on the Oracle documentation (http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements006.htm#BABEGIJC)

DRIVING_SITE Hint

Description of driving_site_hint.gif follows
Description of the illustration driving_site_hint.gif

(See « Specifying a Query Block in a Hint »tablespec::=)

The DRIVING_SITE hint instructs the optimizer to execute the query at a different site than that selected by the database. This hint is useful if you are using distributed query optimization.

For example:

SELECT /*+ DRIVING_SITE(departments) */ * 
  FROM employees, departments@rsite 
  WHERE employees.department_id = departments.department_id;

If this query is executed without the hint, then rows from departments are sent to the local site, and the join is executed there. With the hint, the rows fromemployees are sent to the remote site, and the query is executed there and the result set is returned to the local site.

But after doing some tests my statment was still retrieving locally the distant table. So i have continue my research and finally found this article on metalink Limitations of DRIVING_SITE Hint [ID 825677.1] that says

« So keep in mind when using the DRIVING_SITE hint this is merely for query optimization and not intended for DML or DDL. »

So this hint can be usefull in some case but not in mine…

Hope this will help.