OUG Harmony 2011

Just going home from OUG Harmony seminar. I took the room of a cancelled session and gave a presentation that I put together after Thursday dinner. I tittled the presentation "What is bugging me". So satisfied with other presentations I attended. Eg now I know indexing possibilities in MySQL.

In my presentation I got to show an email from my report on oracle documentation. The issue Tom Kyte mentioned in his keynote speak. There is a feedback possibility in every page in online documentation pages. It is working as Tom mentioned.

Also mentioned beta testing possibility. Currently 11.2 XE available.

One issue was dealing with ora_rowscn. I noticed that my oracle support pages does have only one workaround to a Bug 9360157: WRONG RESULTS WHEN USING ORA_ROWSCN PSEUDOCOLUMN USING ANSI JOIN


The buggy way:

select dep.n,dep.ora_rowscn  
  from testnorowdep nodep  
 inner join testrowdep dep on dep.n=nodep.n
  where dep.ora_rowscn > :scn ; 

The mentioned workaround SQL 92 join way - do not use ansi join.

select dep.n,dep.ora_rowscn  
  from testnorowdep nodep  , testrowdep dep  
 where dep.n=nodep.n 
   and dep.ora_rowscn > :scn;

An additional workaround - use ora_scn in join condition

  select dep.n,dep.ora_rowscn 
   from testnorowdep nodep  
 inner join testrowdep dep 
    on dep.n=nodep.n 
    and dep.ora_rowscn > :scn;

BAUD

Earlier I wrote about sqlplus client tool and date datatype. This post is about presenting dates.

"BAUD: Battle against Unclear Dataformats" Piet de Visser commenting on Alex Gorbachev tweet
"In the times of total globalization why people still battle with different date formats like 02/03/11 vs 03/02/11 - solution is 03-Feb-2011"



The solution is not alone 03-Feb-2011. NLS_LANGUAGE or NLS_DATE_LANGUAGE need to be fixed also.

Let us assume your date string is 03-Mar-2011. It might be that your session is talking POLISH.

SQL> alter session set nls_date_format='yyyy-mm-dd';

SQL> alter session set nls_language='POLISH';

SQL> select to_date('01-mar-2011','dd-mon-yyyy') from dual;

2011-03-01

SQL> alter session set nls_language='AMERICAN';

SQL> select to_date('01-mar-2011','dd-mon-yyyy') from dual;

2011-03-01

Ok that is fine. But if your data is in POLISH and you just happen to read a string in CROATIAN...

SQL> select to_date('01-lip-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CROATIAN') from dual;

2011-06-01

SQL> select to_date('01-lip-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=POLISH') from dual;

2011-07-01

There are also another similar month abbreviations that may be converted to different months depending on language.

SQL> alter session set nls_date_format='yyyy-mm-dd';

SQL> select to_date('01-lis-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CROATIAN') from dual;

2011-10-01

SQL> select to_date('01-lis-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CZECH') from dual;

2011-11-01

SQL> select to_date('01-lis-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=POLISH') from dual;

2011-11-01




SQL> select to_date('01-srp-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CROATIAN') from dual;

2011-07-01

SQL> select to_date('01-srp-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CZECH') from dual;

2011-08-01

So in case of Feb, is it in AMERICAN, DANISH, DUTCH, ENGLISH, GERMAN, GERMAN DIN, ICELANDIC, ITALIAN, LATIN AMERICAN SPANISH, LATIN SERBIAN, LATVIAN, MALAY, MEXICAN SPANISH, NORWEGIAN, ROMANIAN, SLOVAK, SLOVENIAN, SPANISH or SWEDISH?

many to many access path -view

Having a many to many relationship. It is the access path from one table to another. Lets call them cust, ord and prod tables.

cust 1-* ord *-1 prod

There is a need to query prod that has an access path to a certain cust. And we need a database view to implement this. So we want prod out and give a cust to the query as a parameter. The first impression to a developer is to do joins to ord table. That would lead to duplicates in the result. Something that is not desired. It is possible to implement a fine performing view without those duplicates. Here is an example of such.

create table cust (cust_id number constraint cust_pk primary key, cname varchar2(20) not null);

create table prod (prod_id number constraint prod_pk primary key, pname varchar2(20) not null);

create table ord ( ord_id number constraint ord_pk primary key
                 , cust_id constraint ord_cust_fk references cust not null
                 , prod_id constraint ord_prod_pk references prod not null
                 , amount number not null);

create index ord_cust_fk_idx on ord(cust_id,prod_id);

create or replace view custprod as
select cust.cust_id
     , cust.cname
     , prod.prod_id
     , prod.pname
  from cust,prod
 where exists (select 1 from ord where ord.prod_id=prod.prod_id and ord.cust_id=cust.cust_id)
;

select prod_id,pname from custprod where cust_id = :cust;

So there it is. Take only those rows to the from clause you want results from. Use exists on another parts of the access path.
Some data and a query plan.

insert all into cust(cust_id,cname) values (le,le||le)
           into prod(prod_id,pname) values (le,le||le)
 select level le from dual connect by level < 1000; 

begin 
 dbms_stats.gather_table_stats(user,'PROD');
 dbms_stats.gather_table_stats(user,'CUST');
end;
/

insert into ord select rownum,cust_id,prod_id,mod(cust_id,prod_id) from cust, prod where cust_id <= prod_id;

begin 
 dbms_stats.gather_table_stats(user,'ORD');
end;
/


select prod_id,pname from custprod where cust_id = :cust;

--------------------------------------------------------
| Id  | Operation                    | Name            |
--------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |
|   1 |  NESTED LOOPS                |                 |
|   2 |   NESTED LOOPS               |                 |
|   3 |    NESTED LOOPS              |                 |
|   4 |     INDEX UNIQUE SCAN        | CUST_PK         |
|   5 |     SORT UNIQUE              |                 |
|   6 |      INDEX RANGE SCAN        | ORD_CUST_FK_IDX |
|   7 |    INDEX UNIQUE SCAN         | PROD_PK         |
|   8 |   TABLE ACCESS BY INDEX ROWID| PROD            |
--------------------------------------------------------

drop table ord;

drop table prod;

drop table cust;

drop view custprod;

Denormalize for Safety

12.5.2011 the last day to register to OUG Harmony 2011 19-20.5.2011, Paasitorni, Helsinki. Agenda worth reading.

Just posted a kind of denormalize for safety post to Oracle SQL forum. Using the ideas i wrote earlier denormalize safely and presented in OUGF seminar autumn.