Tuesday, May 22, 2007

Keeping up with new features

if there is one thing hard with learning oracle , its catching up with its pace of releasing new features & functionalities.

Recently I saw a plsql code some developer had written at a client place, to remove all unwanted characters from a varchar2 column and just to retain alphabets and numbers..

I was quick to suggest a simple "translate" function would do the trick and PLSQL call can be totally avoided..I learnt about translate function when it was Oracle version 7.3 and I should consider lucky enough that its still there in database kernel, working the same way.

here is a test table to demonstrate the functionality..

SQL> select * from ccs_test_trans ;

C1 C2
---------- ----------
1 12 34 560
2 abcdefgh
3 ABC#$%@(
4 *&^^!%%
5 12345678
6 a;p 27s
7 olka8762;;

7 rows selected.



Assume c2 is the column, which somehow has all extra characters (since varchar2 accepts everything) and needs little bit of cleansing work.

this is how I would have used translate function..
logic is..use nested translate function..
1) first translate - capture all unwanted characters
2) second translate - removes them..leaving only the wanted characters intact.

and then use a replace function to remove the extra spaces (since translate would have replaced all unwanted characters to space/any other character you specify)

SQL> ed
Wrote file afiedt.buf

1 With v1 as
2 (select
3 'abcdefghijklmnopqrstuvwxyz'||
4 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'||
5 '0123456789' retain_chrs
6 from dual)
7 select c1,c2,
8 replace(translate( c2,
9 translate( c2,
10 retain_chrs,
11 lpad( '*', length(retain_chrs), '*' )
12 ),
13 ' ' ), ' ', '') as new_c2
14 from ccs_test_trans ,v1

SQL> /

C1 C2 NEW_C2
---------- ---------- ----------
1 12 34 560 1234560
2 abcdefgh abcdefgh
3 ABC#$%@( ABC
4 *&^^!%%
5 12345678 12345678
6 a;p 27s ap27s
7 olka8762;; olka8762

7 rows selected.



Now coding that wasnt difficult..but understanding or explaining that to anyone new certainly is..because they need to understand how translate works..

but as shown above, it certainly works..and does the work needed..

The question is..do we really need such a complicated query ..isnt there a simple way of solving the same?

Lets see with 10g we have regular expressions..and Oracle allows all POSIX references in SQL. So all I want is to retain all characters which are either numbers or alphabets and remove the rest..

:alnum -> denotes exactly the same ; either alphabets or numbers
REGEXP_REPLACE-> does the replace functionality

SQL> ed
Wrote file afiedt.buf

1 With v1 as
2 (select
3 'abcdefghijklmnopqrstuvwxyz'||
4 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'||
5 '0123456789' retain_chrs
6 from dual)
7 select c1,c2,
8 replace(translate( c2,
9 translate( c2,
10 retain_chrs,
11 lpad( '*', length(retain_chrs), '*' )
12 ),
13 ' ' ), ' ', '') as new_c2,
14 REGEXP_REPLACE( c2, '[^[:alnum:]]' , '') as RE_c2
15 from ccs_test_trans ,v1

SQL> /
-- results

C1 C2 NEW_C2 RE_C2
---------- ---------- ---------- ----------
1 12 34 560 1234560 1234560
2 abcdefgh abcdefgh abcdefgh
3 ABC#$%@( ABC ABC
4 *&^^!%%
5 12345678 12345678 12345678
6 a;p 27s ap27s ap27s
7 olka8762;; olka8762 olka8762

7 rows selected.



How cool and simple is that?

Lets say I also want to allow comma and periods in the c2 column and they should be retained. (added two new rows with sample data to test)

SQL> ed
Wrote file afiedt.buf

1 With v1 as
2 (select
3 'abcdefghijklmnopqrstuvwxyz'||
4 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'||
5 '0123456789' ||
6 ',.' retain_chrs
7 from dual)
8 select c1,c2,
9 replace(translate( c2,
10 translate( c2,
11 retain_chrs,
12 lpad( '*', length(retain_chrs), '*' )
13 ),
14 ' ' ), ' ', '') as new_c2,
15 REGEXP_REPLACE( c2, '[^[:alnum:]{,.}]' , '') as RE_c2
16 from ccs_test_trans ,v1

SQL> /
-- results

C1 C2 NEW_C2 RE_C2
---------- ---------- ---------- ----------
1 12 34 560 1234560 1234560
2 abcdefgh abcdefgh abcdefgh
3 ABC#$%@( ABC ABC
4 *&^^!%%
5 12345678 12345678 12345678
6 a;p 27s ap27s ap27s
7 olka8762;; olka8762 olka8762
8 12,34,56.0 12,34,56.0 12,34,56.0
9 abc, def.g abc,def.g abc,def.g

9 rows selected.



I havent dealt a lot with REGULAR expressions..but it certainly looks an interesting area..if NOT performance, atleast its worth a try - for its readability and flexibility for future modifications.
I am sure you would agree the following SQL reads way too easy that the previous one (using translate) and running for 10+ lines.

SQL> select c1,c2,REGEXP_REPLACE( c2, '[^[:alnum:]{,.}]' , '') as RE_c2
2 from ccs_test_trans
3 /

C1 C2 RE_C2
---------- ---------- ----------
1 12 34 560 1234560
2 abcdefgh abcdefgh
3 ABC#$%@( ABC
4 *&^^!%%
5 12345678 12345678
6 a;p 27s ap27s
7 olka8762;; olka8762
8 12,34,56.0 12,34,56.0
9 abc, def.g abc,def.g

9 rows selected.





Happy SQL-ing until next..





Sunday, May 13, 2007

ANSI & ORACLE MV - still a long way to go..

Anything new is always interesting and so is the my current project at a client place - which is sort of hybrid between Transactional and reporting system. Thanks to this project, I got a break from traditional TUNING tasks and am over-working on catching up with datawarehousing (DW) techniques & implementing them.

Here is something interesting what I figured last week (thanks to a question from client's dev team)..

-- Create 3 tables with 10 rows each
-- & add Primary keys

SQL> Create table test_tb1 as
2 select rownum c1, owner c2 from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb1 add (
2 constraint test_tb1_pk primary key(c1)
3 );

Table altered.

SQL> Create table test_tb2 as
2 select rownum c1, object_name c2 from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb2 add (
2 constraint test_tb2_pk primary key(c1)
3 );

Table altered.

SQL> Create table test_tb3 as
2 select rownum c1, object_type c2,created from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb3 add (
2 constraint test_tb3_pk primary key(c1)
3 )
4 /

Table altered.

-- this exercise is about MATERIALIZED VIEWS..so to have FAST REFRESHING (aka incremental refreshing) Materialized view implemented , we should first have MV LOG created on base tables.

--So here we create them


SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb1
2 with primary key,ROWID,sequence
3* (c2) including new values
SQL> /

Materialized view log created.

SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb2
2 with primary key,ROWID,sequence
3* (c2) including new values
SQL> /

Materialized view log created.

SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb3
2 with primary key,ROWID,sequence
3* (c2,created) including new values
SQL> /

Materialized view log created.

-- Now that we have base tables and MV logs ready, lets try creating a Materialized view..(using ANSI syntax)

SQL> ed
Wrote file afiedt.buf

1 Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from test_tb1 t1
13 JOIN test_tb2 t2
14 ON t1.c1 = t2.c1
15 JOIN test_tb3 t3
16* on t1.c1 = t3.c1
SQL> /
JOIN test_tb3 t3
*
ERROR at line 15:
ORA-12015: cannot create a fast refresh materialized view from a complex query

-- that fails ..because optimizer thinks its a complex query (I am not good as ANSI..and not sure if there is any way to rewrite within ANSI standard itself )..but the point is , I didnt expect that to be considered as complex query


-- that was the problem reported from Dev team to me..and I just thought to test if it was ANSI SQL which was creating the problem. So here I rewrite the same SQL using traditional joins

SQL> ed
Wrote file afiedt.buf

1 Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from
13 test_tb1 t1,
14 test_tb2 t2,
15 test_tb3 t3
16 Where
17 t1.c1 = t2.c1
18* and t1.c1 = t3.c1
SQL> /

Materialized view created.

-- WORKS!! Thats surprising..


SQL> drop materialized view test_mv1;

Materialized view dropped.

-- Lets try the same with outer joins too

SQL> ed
Wrote file afiedt.buf

1* drop materialized view test_mv1
SQL> Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from
13 test_tb1 t1,
14 test_tb2 t2,
15 test_tb3 t3
16 Where
17 t1.c1 = t2.c1
18 and t1.c1 = t3.c1(+)
19 /

Materialized view created.

-- Selecting from MV

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> select * from test_tb1;

C1 C2
---------- ------------------------------
1 SYS
2 SYS
3 SYS
4 SYS
5 SYS
6 SYS
7 SYS
8 SYS
9 SYS
10 SYS

10 rows selected.

-- Testing MV functionality by adding more data to base tables and
-- seeing if MV works fine.

SQL> insert into test_tb1 select 10+rownum c1 , 'NEW DATA' c2 from test_tb1
2 /

10 rows created.

SQL> select * from test_tb1;

C1 C2
---------- ------------------------------
1 SYS
2 SYS
3 SYS
4 SYS
5 SYS
6 SYS
7 SYS
8 SYS
9 SYS
10 SYS
11 NEW DATA
12 NEW DATA
13 NEW DATA
14 NEW DATA
15 NEW DATA
16 NEW DATA
17 NEW DATA
18 NEW DATA
19 NEW DATA
20 NEW DATA

20 rows selected.

SQL> commit;

Commit complete.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> begin
2 dbms_mview.refresh(list=>'TEST_MV1',METHOD=>'f');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> insert into test_tb2 select 10+rownum c1, 'New data' c2 from test_tb2
2 /

10 rows created.

SQL> select * from test_tb2;

C1 C2
---------- -------------------------
1 ICOL$
2 I_USER1
3 CON$
4 UNDO$
5 C_COBJ#
6 I_OBJ#
7 PROXY_ROLE_DATA$
8 I_IND1
9 I_CDEF2
10 I_PROXY_ROLE_DATA$_1
11 New data
12 New data
13 New data
14 New data
15 New data
16 New data
17 New data
18 New data
19 New data
20 New data

20 rows selected.

SQL> commit;

Commit complete.

SQL> begin
2 dbms_mview.refresh(list=>'TEST_MV1',METHOD=>'f');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX
11 NEW DATA
12 NEW DATA
13 NEW DATA
14 NEW DATA
15 NEW DATA
16 NEW DATA
17 NEW DATA
18 NEW DATA
19 NEW DATA
20 NEW DATA

20 rows selected.


-- works fine!!!

Moral: Though ANSI standard has been introduced in Oracle since 9.1 version, its always better to check back to traditional join syntax when sometimes you get unexpected error with sql/optimizer.





Saturday, May 5, 2007

PLSQL rocks..

Most of us know PLSQL is an extension of SQL meant for procedural needs and computations which cannot be done using SQL/"set theory"... It takes a while for programmers coming from procedural programming world (Cobol, Java, C etc) to switch to SQL because thinking in sets needs some time and practise before you can start enjoying the fun.

But fortunately or unfortunately, people who dont find SQL solution easily still somehow manage to solve everything in Oracle just by writing lots of procedural code using PL/SQL and call it within SQL..One classic example (funny too) is illustrated here.

But check out the PLSQL package guide and you will be amazed how many useful APIs there are..actually its been tough to catch up with the speed of new packages getting introduced with every new release.

I just happened to read something really cool and was amazed..Would you believe Oracle's engine has the capability of solving Linear algebra problems? Read on ..to believe..


From PLSQL documentation:

UTL_NAV.LAPACK_GESV Procedures
This procedure computes the solution to a real system of linear equations
a * x = b
where a is an n by n matrix and x and b are n by nrhs matrices.
The LU decomposition with partial pivoting and row interchanges is used to factor A
as
a = P * L * U
where P is a permutation matrix, L is unit lower triangular, and U is upper triangular.
The factored form of a is then used to solve the system of equations
a * x = b

Syntax
UTL_NLA.LAPACK_GESV (
n IN POSITIVEN,
nrhs IN POSITIVEN,
a IN OUT UTL_NLA_ARRAY_DBL,
lda IN POSITIVEN,
ipiv IN OUT UTL_NLA_ARRAY_INT,
b IN OUT UTL_NLA_ARRAY_DBL,
ldb IN POSITIVEN,
info OUT INTEGER,
pack IN flag DEFAULT 'C');

Solving Linear equation with PL/SQL :


I am taking a practical problem from this site :
http://mathforum.org/library/drmath/view/57278.html

Problem:
(1) A has three times as many sweets as B. If he gives B six sweets,
he will then have twice as many as B then has. How many sweets did
they each have to start with?

Solution:

Let x = number of sweets that B has initially; then 3x is the number
that A has. If now A gives 6 sweets to B then A has 3x-6 sweets and B
has x+6 sweets. Now we are told that after this transfer, A has twice
as many sweets as B, so we can write down an equation to represent
this fact, i.e.

3x-6 = 2(x+6)
3x-6 = 2x + 12
3x-2x = 12 + 6
x = 18

So initially B had 18 sweets and A had 3*18 = 54 sweets.

Check: After transfer B has 18+6 = 24, A has 54-6 = 48, and 48 is
twice 24.


Now, lets solve this using PL/SQL

Lets represent the data in Matrix form first..

Lets say 'a' is the number of sweets A has & 'b' is the number of sweets with B

i) Before Transfer..

a-3b=0 (since a exactly is 3 times of b)

ii) After transfer (A gives 6 sweets to B)

a-6 = 2 (b+6) (after transfer A has double the amount of what B has)
a-6 = 2b +12
a-2b = 18


So we have two equations..
a - 3b = 0 -- Equation 1
a - 2b = 18 -- Equation 2

Representing the same by Matrix theory..


| 1 -3 | | a | __ | 0 |
| 1 -2 | | b | -- | 18 |

<---A--> <-X-> -- <-B--> <== Matrix Notation

Now all we have to do is find values of Matrix X

Lets feed the same value into PLSQL and save ourselves some time...



SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 A utl_nla_array_dbl := utl_nla_array_dbl(1,1,-3,-2);
3 B utl_nla_array_dbl := utl_nla_array_dbl( 0,18);
4 X utl_nla_array_int := utl_nla_array_int(0,0);
5 result integer;
6 BEGIN
7 UTL_NLA.LAPACK_GESV (
8 n => 2, /* MATRIX A is 2*2 */
9 nrhs => 1, /* MATRIX B has 1 column */
10 a => A, /* PASS MATRIX A */
11 lda => 2, /* max(1,2)=2 */
12 ipiv => X, /******** THIS IS WHAT WE WANT TO FIND */
13 b => B, /* PASS MATRIX B */
14 ldb => 2, /* max(1,2)=2 */
15 info => result, /* Code execution status */
16 pack => 'C' /* default column-wise */
17 );
18 IF result=0 THEN
19 dbms_output.put_line('PLSQL call was successfully executed');
20 dbms_output.put_line(' Results');
21 dbms_output.put_line(' -------------');
22 FOR i IN 1..B.count LOOP
23 dbms_output.put_line('- '||chr(64+i) ||' = ' || TO_CHAR(B(i),'999'));
24 END LOOP;
25 ELSE
26 dbms_output.put_line('Error during PLSQL call..Troubleshooting reqd');
27 END IF;
28* END;

SQL> /
PLSQL call was successfully executed
Results
-------------
- A = 54
- B = 18

PL/SQL procedure successfully completed.

How cool is that? There are lots of subprograms in the same UTL_NAV package to solve other situations (say if matrix A is not n*n matrix but instead happens to be m*n size)..


Have more fun..by changing the data and see how fast can your PL/SQL program actually respond..I tried this site
to try couple of more linear equation problems and its working great

Something **NEW** learnt for the day :)