Major

Today I did something Donald Duck would do. A couple years ago I built a house. Here are two doors to leave the house, one in front and also a back door. There are wooden stairs in the front door and a wooden terrace outside the back door. I just oiled the front stairs and just after that continued oiling at the back terrace. Now I am stuck inside for a while. It is time to open a beer and think of something else done today. Karhu has some virtual fishing to do.

Julian Dontcheff mentioned a while ago about 11.2.0.2 that it should actually be called 11R3. I was reading my oracle support. The issue in my spontaneous online demonstration in What is bugging me presentation is noticed. My demonstration was about "No results with function based indexes and OR expansion". Now there is a document in MOS "Things to Consider Before Upgrade to 11.2.0.2 Database Performance [ID 1320966.1]" updated 7.6.2011. On off patch is recommended as Oracle does not want to interfere optimizer with CPU or PSU patching. The fifth number in the version numbering. So are those four letters actually something that should be considered major version. Well 11.2.0.2 has so many other changes than the optimizer ones that it could be a 11R3. Just see the list of new features in 11.2.0.2 list in the documentation. OPTIMIZER_FEATURES_ENABLED It has already been from version 10.1.0.3 and 9.2.0.8 that the fourth number have had a meaning. Actually 11.2.0.2 is missing there. Yet another place to submit a user comment about the documentation.

While writing this post it seems like the 1320966.1 document is vanishing or is it just something about the flashy interface... The patch recommended to install was 9776940. Contact support before installing... Another thing mentioned there was the 11.2.0.3 patchset due out later this year.

Yet another thing. During last week there has been a slight peak in the visitor count of my blog. After Jonathan Lewis has made an Argh! issue about merge ignores check constraint and someone linking that post to his pages. Yes it actually is an Argh issue.

Actually yet another Argh! issue might be visualized as a snip

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.

Date variable in sqlplus

Having a huge query including several temporal joins and sum over time and several date type binds. I want to execute that query using sqlplus.

SQL> variable til date
Usage: VAR[IABLE] [  [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

Using binds in sqlplus by Tanel Poder and askTom discussing missing date variable support in sqlplus. Putting those together.

SQL> variable fro varchar2(10)
SQL> exec select sysdate into :fro from dual;

PL/SQL procedure successfully completed.

SQL> select 1 from dual where :fro < sysdate;

         1
----------
         1

SQL> print fro

FRO
--------------------------------
13.04.2011

Be aware about possible changes in execution plan caused by datatype conversions. Read the askTom discussion.

Changing NLS_DATE_FORMAT

SQL> alter session set nls_territory=america;

Session altered.

SQL> select 1 from dual where :fro < sysdate;
select 1 from dual where :fro < sysdate
                          *
ERROR at line 1:
ORA-01843: not a valid month


SQL> exec select sysdate into :fro from dual;

PL/SQL procedure successfully completed.

SQL> select 1 from dual where :fro < sysdate;

         1
----------
         1

SQL> print fro

FRO
--------------------------------
13-APR-11

And yet another aspect about date formats

SQL> select 1 from dual where :fro = trunc(sysdate);

         1
----------
         1

SQL> alter session set nls_date_format='yyyymmdd hh24mi';

Session altered.

SQL> exec select sysdate into :fro from dual;
BEGIN select sysdate into :fro from dual; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


SQL> variable fro varchar2(13)
SQL> exec select sysdate into :fro from dual;

PL/SQL procedure successfully completed.

SQL> select 1 from dual where :fro = trunc(sysdate);

no rows selected

SQL> print fro

FRO
--------------------------------
20110413 0902

Merge ignores check constraint

It was a while ago when we noticed that our enabled and validated constraints were not respected by our 11.2.0.1 database. The reason for this appeared to be a merge clause updating the rows and ignoring the constraints. A bug 9285259 was created.
Now the bug will be fixed in version 12.1 and hopefully 11.2.0.3 also. There is a patch available on top of 11.2.0.2 version. The patch installed online. Now we are receiving ORA-02290: check constraint violated as supposed. Also workaround was introduced. Our merge clause had only update part. By adding insert or delete part to the merge the constraint is noticed without the patch.

Removing duplicates

From asktom one can find an example removing duplicates. Here is another.

create table duplicates (n int);

insert into duplicates select level from dual connect by level < 100;

insert into duplicates select level from dual connect by level < 50;

delete from duplicates where rowid in (
select rid from (
select rowid rid, first_value(rowid)over(partition by n) frid, dup.*
from duplicates dup
) where frid != rid
)
;

NOCOUG Second SQL Challenge

Working with the second NoCOUg SQL Challenge. In the magazine there can be found also "advice for an Oracle Beginner" articles - worth reading.




Here is my five cents to towards the problem. Another answers may be found from Iggy Fernandez blog comments.

When I found the challenge, there were already some published answers to the riddle. So I started with minimizing the starting set. Got rid of nulls in the first place. And after a while ended up with a hierarchical query. On a way I draw a Graphviz picture of the riddle data. Maybe that visualizes some paths I was trying to follow trying to figure out alternative solutions. SQL commands for creating the required data.

with aa as (
select word1, word2, word3, word2 gr
  from riddle
 where word1 is not null
), bb as (
select gr,pre,word
  from aa
unpivot (word for pre in (word1 as 1, word2 as 2, word3 as 3))
), cc as (
select gr,pre,word
     , first_value(case when pre = 3 and word != gr then gr end ignore nulls)over(partition by word) bg
     , first_value(case when pre = 1 and word != gr then gr end ignore nulls)over(partition by word) ag
     , min(pre)over(partition by word) mi
     , max(pre)over(partition by word) ma
  from bb
), dd (gr,mi,ma,pre,word,ord)as (
select gr,mi,ma,pre,word,cast(2 as varchar2(10))
  from cc
 where cc.pre=2 and cc.bg is null and cc.ag is null
 union all
select cc.gr,cc.mi,cc.ma,cc.pre,cc.word
     , dd.ord||case when cc.pre = 1 then cc.mi else cc.ma end
from dd inner join cc on cc.pre in (1,3) and dd.word = cc.gr
)
select listagg(dd.word,' ')within group(order by rpad(dd.ord,10,'2'))
  from dd
;

Update 8.3.2011
Ordering with rpad seems like so borrowed from the riddle_tree. So here is another solution that maintains the ordering number while browsing the tree.

with aa as (
select word1, word2, word3, word2 gr
  from riddle
 where word1 is not null
), bb as (
select gr,pre,word
  from aa
unpivot (word for pre in (word1 as 1, word2 as 2, word3 as 3))
), cc as (
select gr,pre,word
     , first_value(case when pre = 3 and word != gr then gr end ignore nulls)over(partition by word) bg
     , first_value(case when pre = 1 and word != gr then gr end ignore nulls)over(partition by word) ag
     , min(pre)over(partition by word) mi
     , max(pre)over(partition by word) ma
  from bb
), dd (gr,mi,ma,pre,word,nord,lv)as (
select gr,mi,ma,pre,word,2222222,1
  from cc
 where cc.pre=2 and cc.bg is null and cc.ag is null
 union all
select cc.gr,cc.mi,cc.ma,cc.pre,cc.word
     , dd.nord+(cc.pre-2)*power(10,6-dd.lv)
     , dd.lv+1
from dd inner join cc on cc.pre in (1,3) and dd.word = cc.gr
)
select listagg(dd.word,' ')within group(order by dd.nord)
  from dd 
;

ORDER SIBLINGS BY CONNECT_BY_ROOT

In this post I am dealing with a sorting problem of a recursive query and giving a guideline how to implement such ordering.

We are patching an Oracle database to 11.2.0.2 and with one of our test case hit an error

ORA-30007: CONNECT BY ROOT operator is not supported in the START WITH or in the CONNECT BY condition

The problem query does not have connect_by_root in START WITH or CONNECT BY. But it is in order by "order siblings by connect_by_root". So the reported error is somewhat misleading.

What does this order siblings by connect_by_root is trying to accomplish. The hierarchical result is ordered first by some column from a root node of the hierarchy and after that with some columns at the same level of the hierarchy.

In a thread in www.sql.ru there may be found discussion about the same problem. With 10.2.0 ORA-00600: internal error code, arguments: [qkacon:FJswrwo] is reported. It is mentioned that giving a hint /*+ NO_CONNECT_BY_COST_BASED */ bypasses the ORA-00600 problem, but a new one is described. connect_by_root is returning nulls if the same query has siblings word in order by. So our query has problem and the newly introduced error in 11.2.0.2 is actually revealing that to us.

11.2 introduced an alternative way to write hierarchical queries. Here I introduce the problematic queries with a data set having two roots. And in the end a way to implement the requirement using recursive common table expression.

drop table emp purge;

CREATE TABLE EMP
(
EMPNO     NUMBER(4),
ENAME     VARCHAR2(10 BYTE),
MGR       NUMBER(4)
)
;

insert into emp(empno,mgr,ename) values (11,23,'SMITH');
insert into emp(empno,mgr,ename) values (12,16,'ALLEN');
insert into emp(empno,mgr,ename) values (13,16,'WARD');
insert into emp(empno,mgr,ename) values (14,19,'JONES');
insert into emp(empno,mgr,ename) values (15,16,'MARTIN');
insert into emp(empno,mgr,ename) values (16,19,'BLAKE');
insert into emp(empno,mgr,ename) values (17,19,'CLARK');
insert into emp(empno,mgr,ename) values (18,null,'SCOTT');
insert into emp(empno,mgr,ename) values (19,null,'KING');
insert into emp(empno,mgr,ename) values (20,16,'TURNER');
insert into emp(empno,mgr,ename) values (21,18,'ADAMS');
insert into emp(empno,mgr,ename) values (22,16,'JAMES');
insert into emp(empno,mgr,ename) values (23,14,'FORD');
insert into emp(empno,mgr,ename) values (24,17,'MILLER');

update emp set mgr = null where ename = 'SCOTT';

commit;


select em.*, rpad('-',level,'-')||empno , level
from emp em
start with em.mgr is null
connect by prior em.empno = em.mgr
;
18 SCOTT  -18 1
21 ADAMS 18 --21 2
19 KING  -19 1
14 JONES 19 --14 2
23 FORD 14 ---23 3
11 SMITH 23 ----11 4
16 BLAKE 19 --16 2
12 ALLEN 16 ---12 3
13 WARD 16 ---13 3
15 MARTIN 16 ---15 3
20 TURNER 16 ---20 3
22 JAMES 16 ---22 3
17 CLARK 19 --17 2
24 MILLER 17 ---24 3

Trying to add the described ordering:

select /*+ NO_CONNECT_BY_COST_BASED */em.*, level, connect_by_root ename cbr, rpad('-',level,'-')||empno
from emp em
start with em.mgr is null
connect by prior em.empno = em.mgr
order siblings by connect_by_root ename, empno
;

18 SCOTT  1 SCOTT -18
21 ADAMS 18 2 SCOTT --21
19 KING  1 KING -19
14 JONES 19 2 KING --14
23 FORD 14 3 KING ---23
11 SMITH 23 4 KING ----11
16 BLAKE 19 2 KING --16
12 ALLEN 16 3 KING ---12
13 WARD 16 3 KING ---13
15 MARTIN 16 3 KING ---15
20 TURNER 16 3 KING ---20
22 JAMES 16 3 KING ---22
17 CLARK 19 2 KING --17
24 MILLER 17 3 KING ---24

Rows from KING root should be ordered before SCOTT. So using the 11.2.0.2 database the problem is noticed and the ORA-30007: CONNECT BY ROOT operator is not supported in the START WITH or in the CONNECT BY condition is thrown.

How to bypass the problem with a recursive common table query:



with cte (empno,mgr,ename,cbr,l) as (
select empno,mgr,ename,ename cbr,1 from emp where mgr is null
union all
select em.empno,em.mgr,em.ename,ct.cbr,ct.l+1 from emp em inner join cte ct on em.mgr = ct.empno
)
SEARCH DEPTH FIRST BY cbr,empno SET rn
select te.*, rpad('-',l,'-')||empno
from cte te
;

19 KING KING 1 1 -19
14 19 JONES KING 2 2 --14
23 14 FORD KING 3 3 ---23
11 23 SMITH KING 4 4 ----11
16 19 BLAKE KING 2 5 --16
12 16 ALLEN KING 3 6 ---12
13 16 WARD KING 3 7 ---13
15 16 MARTIN KING 3 8 ---15
20 16 TURNER KING 3 9 ---20
22 16 JAMES KING 3 10 ---22
17 19 CLARK KING 2 11 --17
24 17 MILLER KING 3 12 ---24
18 SCOTT SCOTT 1 13 -18
21 18 ADAMS SCOTT 2 14 --21


Problem solved, nice feeling. Also a good taste in my mouth. Thanks to Ilkka and H. and The Yamazaki Single Malt Whisky aged 12 years Japanese whisky. Now to have some cake and buy tickets to Hakametsä Tappara ice hockey game.

Partitioned Outer Join

Today was the day I had to fill some sparse data. I actually used partition by right outer join. The documentation example describes the problem well. Nothing much else to say about that.

Driving a road

At my last post I talked about different ways to write SQL. Here is an example of such situation. There are three different queries written to get the similar results out of two tables. Here I am driving a road. So the information here is kind of spatial stored in relational way.

create table roadpoint(roadpoint_id number constraint roadpoint_pk primary key, roadnumber number not null, distance number not null);

create table region(region_id number constraint region_pk primary key, startpoint references roadpoint not null, endpoint references roadpoint not null);

insert into roadpoint select level,1,level*10 from dual connect by level<=20000;  


insert into region values (1,1,2);  
insert into region values (2,4,6);  
insert into region values (3,7,10);  
insert into region values (4,40,50);  
insert into region values (5,4006,4010);  

exec dbms_stats.gather_table_Stats(user,'REGION');  
exec dbms_stats.gather_table_Stats(user,'ROADPOINT');    

select * 
  from region reg, roadpoint st, roadpoint en 
 where reg.startpoint = st.roadpoint_id
   and reg.endpoint = en.roadpoint_id ;  

The problem here is that roadpoints inside region are not selected.

The needed rows may be impressed with a query like following.

select reg.region_id, rp.roadpoint_id,rp.roadnumber,rp.distance
from region reg, roadpoint rp
where exists (select null from roadpoint st, roadpoint en where reg.startpoint = st.roadpoint_id
 and reg.endpoint = en.roadpoint_id
 and rp.distance between st.distance and en.distance
);

SQL_ID  2q2uj40br0df2, child number 0
-------------------------------------
select reg.region_id, rp.roadpoint_id,rp.roadnumber,rp.distance from
region reg, roadpoint rp where exists (select null from roadpoint st,
roadpoint en where reg.startpoint = st.roadpoint_id   and reg.endpoint
= en.roadpoint_id   and rp.distance between st.distance and en.distance
)

Plan hash value: 2380388577

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |     25 |00:00:00.55 |     208K|       |       |          |
|*  1 |  FILTER                       |              |      1 |        |     25 |00:00:00.55 |     208K|       |       |          |
|   2 |   MERGE JOIN CARTESIAN        |              |      1 |    100K|    100K|00:00:00.01 |      60 |       |       |          |
|   3 |    TABLE ACCESS FULL          | REGION       |      1 |      5 |      5 |00:00:00.01 |       7 |       |       |          |
|   4 |    BUFFER SORT                |              |      5 |  20000 |    100K|00:00:00.01 |      53 |   690K|   486K|  613K (0)|
|   5 |     TABLE ACCESS FULL         | ROADPOINT    |      1 |  20000 |  20000 |00:00:00.01 |      53 |       |       |          |
|   6 |   NESTED LOOPS                |              |    100K|      1 |     25 |00:00:00.35 |     208K|       |       |          |
|*  7 |    TABLE ACCESS BY INDEX ROWID| ROADPOINT    |    100K|      1 |   4078 |00:00:00.31 |     200K|       |       |          |
|*  8 |     INDEX UNIQUE SCAN         | ROADPOINT_PK |    100K|      1 |    100K|00:00:00.10 |     100K|       |       |          |
|*  9 |    TABLE ACCESS BY INDEX ROWID| ROADPOINT    |   4078 |      1 |     25 |00:00:00.01 |    8167 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN         | ROADPOINT_PK |   4078 |      1 |   4078 |00:00:00.01 |    4089 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter( IS NOT NULL)
  7 - filter("EN"."DISTANCE">=:B1)
  8 - access("EN"."ROADPOINT_ID"=:B1)
  9 - filter("ST"."DISTANCE"<=:B1)
  10 - access("ST"."ROADPOINT_ID"=:B1)    

Taking a look the generated plan one might consider something else. Here are two alternative cursors c_join and c_drivetheroad. Those are measured with Tom Kytes runstats

DECLARE
  CURSOR c_join
  IS
select reg.region_id, rp.roadpoint_id,rp.roadnumber,rp.distance
from region reg, roadpoint st, roadpoint en, roadpoint rp
where reg.startpoint = st.roadpoint_id
 and reg.endpoint = en.roadpoint_id
 and rp.distance between st.distance and en.distance
;
--
  CURSOR c_drivetheroad
  IS
select lv region_id,roadpoint_id,roadnumber,distance
from (
select region_id
,last_value(region_id ignore nulls)over(partition by roadnumber order by rp.distance) lv
,last_value(region_id ignore nulls)over(partition by roadnumber order by rp.distance desc) fv
,staend
,point
,rp.roadnumber
,roadpoint_id
,distance
from (
select *
from (
select reg.region_id, st.distance startdistance, en.distance enddistance
from region reg, roadpoint st, roadpoint en
where reg.startpoint = st.roadpoint_id
 and reg.endpoint = en.roadpoint_id
)
unpivot (point for staend in (startdistance as '1', enddistance as '-1'))
) re, roadpoint rp
where re.point (+)= rp.distance
) qw
where lv=fv
;
--
BEGIN
   runstats_pkg.rs_start;
  FOR i IN 1 .. 100 LOOP
     FOR rec IN c_join LOOP
        NULL;
     END LOOP;
  END LOOP;
   runstats_pkg.rs_middle;
  FOR i IN 1 .. 100 LOOP
     FOR rec IN c_drivetheroad LOOP
        NULL;
     END LOOP;
  END LOOP;
   runstats_pkg.rs_stop;
END;
/

Before looking at runstats results lets see the plans used. Does it seem like the first join method look like a bit easier than the second alternative. A-rows in the first one are 25 at most as those are 20000 in the second alternative.

SQL_ID  fu5n5fqtksa7b, child number 0
-------------------------------------
select reg.region_id, rp.roadpoint_id,rp.roadnumber,rp.distance from
region reg, roadpoint st, roadpoint en, roadpoint rp where
reg.startpoint = st.roadpoint_id   and reg.endpoint = en.roadpoint_id  
and rp.distance between st.distance and en.distance

Plan hash value: 2085902882

---------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |     25 |00:00:00.02 |     296 |
|   1 |  NESTED LOOPS                  |              |      1 |  25003 |     25 |00:00:00.02 |     296 |
|   2 |   NESTED LOOPS                 |              |      1 |      5 |      5 |00:00:00.01 |      31 |
|   3 |    NESTED LOOPS                |              |      1 |      5 |      5 |00:00:00.01 |      19 |
|   4 |     TABLE ACCESS FULL          | REGION       |      1 |      5 |      5 |00:00:00.01 |       7 |
|   5 |     TABLE ACCESS BY INDEX ROWID| ROADPOINT    |      5 |      1 |      5 |00:00:00.01 |      12 |
|*  6 |      INDEX UNIQUE SCAN         | ROADPOINT_PK |      5 |      1 |      5 |00:00:00.01 |       7 |
|   7 |    TABLE ACCESS BY INDEX ROWID | ROADPOINT    |      5 |      1 |      5 |00:00:00.01 |      12 |
|*  8 |     INDEX UNIQUE SCAN          | ROADPOINT_PK |      5 |      1 |      5 |00:00:00.01 |       7 |
|*  9 |   TABLE ACCESS FULL            | ROADPOINT    |      5 |   5001 |     25 |00:00:00.02 |     265 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  6 - access("REG"."STARTPOINT"="ST"."ROADPOINT_ID")
  8 - access("REG"."ENDPOINT"="EN"."ROADPOINT_ID")
  9 - filter(("RP"."DISTANCE">="ST"."DISTANCE" AND "RP"."DISTANCE"<="EN"."DISTANCE"))    



SQL_ID  8as0wh80pucty, child number 0
-------------------------------------
select lv region_id,roadpoint_id,roadnumber,distance  from (  select 
region_id  ,last_value(region_id ignore nulls)over(partition by 
roadnumber order by rp.distance) lv  ,last_value(region_id ignore 
nulls)over(partition by roadnumber order by rp.distance desc) fv  
,staend  ,point  ,rp.roadnumber  ,roadpoint_id  ,distance  from (  
select *  from (  select reg.region_id, st.distance startdistance, 
en.distance enddistance  from region reg, roadpoint st, roadpoint en  
where reg.startpoint = st.roadpoint_id    and reg.endpoint = 
en.roadpoint_id  )  unpivot (point for staend in (startdistance as '1', 
enddistance as '-1'))  ) re, roadpoint rp  where re.point (+)= 
rp.distance  ) qw  where lv=fv
 
Plan hash value: 1653289237
 
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |     25 |00:00:00.08 |      84 |       |       |          |
|*  1 |  VIEW                                |              |      1 |  20000 |     25 |00:00:00.08 |      84 |       |       |          |
|   2 |   WINDOW SORT                        |              |      1 |  20000 |  20000 |00:00:00.08 |      84 |   903K|   523K|  802K (0)|
|   3 |    WINDOW SORT                       |              |      1 |  20000 |  20000 |00:00:00.06 |      84 |   832K|   511K|  739K (0)|
|*  4 |     HASH JOIN RIGHT OUTER            |              |      1 |  20000 |  20000 |00:00:00.02 |      84 |   968K|   968K|  797K (0)|
|*  5 |      VIEW                            |              |      1 |     10 |     10 |00:00:00.01 |      31 |       |       |          |
|   6 |       UNPIVOT                        |              |      1 |        |     10 |00:00:00.01 |      31 |       |       |          |
|   7 |        NESTED LOOPS                  |              |      1 |        |      5 |00:00:00.01 |      31 |       |       |          |
|   8 |         NESTED LOOPS                 |              |      1 |      5 |      5 |00:00:00.01 |      26 |       |       |          |
|   9 |          NESTED LOOPS                |              |      1 |      5 |      5 |00:00:00.01 |      19 |       |       |          |
|  10 |           TABLE ACCESS FULL          | REGION       |      1 |      5 |      5 |00:00:00.01 |       7 |       |       |          |
|  11 |           TABLE ACCESS BY INDEX ROWID| ROADPOINT    |      5 |      1 |      5 |00:00:00.01 |      12 |       |       |          |
|* 12 |            INDEX UNIQUE SCAN         | ROADPOINT_PK |      5 |      1 |      5 |00:00:00.01 |       7 |       |       |          |
|* 13 |          INDEX UNIQUE SCAN           | ROADPOINT_PK |      5 |      1 |      5 |00:00:00.01 |       7 |       |       |          |
|  14 |         TABLE ACCESS BY INDEX ROWID  | ROADPOINT    |      5 |      1 |      5 |00:00:00.01 |       5 |       |       |          |
|  15 |      TABLE ACCESS FULL               | ROADPOINT    |      1 |  20000 |  20000 |00:00:00.02 |      53 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("LV"="FV")
   4 - access("unpivot_view_014"."POINT"="RP"."DISTANCE")
   5 - filter("unpivot_view_014"."POINT" IS NOT NULL)
  12 - access("REG"."STARTPOINT"="ST"."ROADPOINT_ID")
  13 - access("REG"."ENDPOINT"="EN"."ROADPOINT_ID")

And now some runstats numbers

Run1 ran in 126 hsecs
Run2 ran in 800 hsecs
run 1 ran in 15,75% of the time

Name                                  Run1        Run2        Diff
STAT...table scan blocks gotte      25,500       5,500     -20,000
STAT...no work - consistent re      25,955       5,714     -20,241
STAT...consistent gets from ca      27,661       6,221     -21,440
STAT...consistent gets              29,930       8,425     -21,505
STAT...consistent gets from ca      29,930       8,425     -21,505
STAT...session logical reads        29,990       8,459     -21,531
LATCH.cache buffers chains          57,767      15,172     -42,595
STAT...session uga memory max      123,452     410,072     286,620
STAT...session pga memory max      131,072     524,288     393,216
STAT...sorts (rows)                     33   4,000,000   3,999,967
STAT...table scan rows gotten   10,000,500   2,000,500  -8,000,000

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
60,427      20,974     -39,453    288.10%

We actually read less rows and sort them and use memory a bit more in the c_drivetheroad version. The cursor c_join seems to be faster in this case. But the note that the number of a-rows vs rows gotten are not in sync. Also logical reads are one magnitude more in c_join run.

Many ways writing a query

Iggy Fernandez is writing about SQL Which Query is Better?—Part III. I am checking here queries from the original article that he did not include in his post. And something else.

HASH JOIN plans

SELECT lname
FROM personnel
WHERE 199170 = any (
  SELECT salary 
  FROM payroll 
  WHERE personnel.empid = payroll.empid) ;

SELECT lname
FROM personnel
WHERE 199170 in ( 
  SELECT salary 
  FROM payroll 
  WHERE personnel.empid = payroll.empid) ;

SELECT lname
FROM personnel
WHERE empid = any (
  SELECT empid 
  FROM payroll
  WHERE salary = 199170);

The HASH JOIN RIGHT SEMI plan:

SELECT lname

FROM personnel

WHERE 0 < (

SELECT count(*)

FROM payroll

WHERE personnel.empid = payroll.empid AND salary = 199170);


Mr Date mentioned in EMEA Harmony 2010 about using any operator that it is behaving relationally. Even thou one should not start using it.

Two additional queries resulting HASH JOIN plan:

SELECT lname

FROM personnel, (select empid from payroll where salary = 199170) payr

WHERE personnel.empid = payr.empid;

SELECT lname

FROM personnel inner join payroll

ON personnel.empid = payroll.empid and salary = 199170;

HASH JOIN RIGHT ANTI plan also possible plan for the question here.

SELECT lname

FROM personnel

WHERE 0 = (

SELECT count(*)

FROM payroll

WHERE personnel.empid = payroll.empid

AND salary != 199170);

SQL_ID b90mkx99aux26, child number 1

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

SELECT lname FROM personnel WHERE 0 = (SELECT count(*) FROM payroll

WHERE personnel.empid = payroll.empid AND salary != 199170)

Plan hash value: 103534934

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

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

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

| 0 | SELECT STATEMENT | | 1 | | 1004 |00:00:00.08 | 261 |

|* 1 | HASH JOIN RIGHT ANTI| | 1 | 9900 | 1004 |00:00:00.08 | 261 |

|* 2 | TABLE ACCESS FULL | PAYROLL | 1 | 8910 | 8896 |00:00:00.01 | 38 |

| 3 | TABLE ACCESS FULL | PERSONNEL | 1 | 9900 | 9900 |00:00:00.01 | 223 |

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

Predicate Information (identified by operation id):

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

1 - access("PERSONNEL"."EMPID"="PAYROLL"."EMPID")

2 - filter("SALARY"!=199170)

Without this rehersal I would not have writen anti join this way. Actually I would like to see an alternative sql resulting this plan.
The original article talked also about indexing the salary column.

create index payroll_salary_idx on payroll(salary);

With this data - no changes in the query plans. Trying a fat one

create index payroll_salary_fat_idx on payroll(salary,empid);

The only change in plans is that FULL TABLE SCAN of payroll changes to INDEX payroll_salary_fat_idx RANGE SCAN in some queries.The join method stays always the same.

Types of columns

Just participated C.J.Date two day seminar in 10 hours today. Thanks to the ash from Iceland. Monday was delayed.

Just a small thing picked up from the massive amount of information. To avoid type conversions behind the scenas while doing natural joins avoid using different types for columns named similarily. Just checking that

select * from (
select count(distinct data_type
                      ||'|'||cast(data_length as varchar2(30))
                      ||'|'||cast(data_precision as varchar2(30))
                      ||'|'||cast(data_scale as varchar2(30))
            ) over (partition by column_name) as dis
     , table_name
     , column_name
     , data_type
     , data_length
     , data_precision
     , data_scale 
  from user_tab_columns
) 
where dis > 1
;

Well on the other hand do not use select *. As an analogy I would suggess not to use natural joins. Mr Date suggested to use views on to of base tables. That makes sense. And a good thing here user_tab_columns has also the columns in views included.

Reducing the number of function calls

Using a slow function call in your query? Maybe you are calling it unnecessarily.

SQL> create or replace type ns_typ is table of number;
  2  /

Type created.

SQL> create or replace function rn(n number) return ns_typ is
  2  ret ns_typ;
  3  begin
  4   dbms_lock.sleep(1);
  5   select level bulk collect into ret from dual connect by level <= n;
  6  return ret;
  7  end;
  8  /

Function created.

SQL>
SQL> create table ta as select level n, mod(level,3)+1 m from dual connect by level <= 10;

Table created.

SQL> select * from ta;

         N          M
---------- ----------
         1          2
         2          3
         3          1
         4          2
         5          3
         6          1
         7          2
         8          3
         9          1
        10          2

10 rows selected.

SQL> set timi on

SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:10.01

The query is calling rn function for each ten rows of ta table. Each call takes one second as the function is using dbms_lock. There are only three distinct values that the function is needed to be called.

SQL> with aa as (
  2  select *
  3    from ta a
  4  ), bb as (
  5  select distinct m
  6    from aa
  7  ), cc as (
  8  select /*+materialize*/ b.m, dd.column_value n
  9    from bb b, table(rn(b.m)) dd)
 10  select *
 11    from aa
 12   where (n,m) in (select n,m from cc)
 13  ;

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.03

Alternatively you might consider using result cache for the function.

SQL> create or replace function rn(n number) return ns_typ result_cache is
  2  ret ns_typ;
  3  begin
  4   dbms_lock.sleep(1);
  5   select level bulk collect into ret from dual connect by level <= n;
  6  return ret;
  7  end;
  8  /

Function created.

Elapsed: 00:00:00.04
SQL>
SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.01
SQL>
SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:00.00

Cleanup

SQL> drop table ta purge;
SQL> drop function rn;
SQL> drop type ns_typ;

Pivoting EAV

If you are responsible for designing a data model and just consider to invent again and create this fine generic entity attribute value structure, maybe you should consider attending some teaching about the issue. For example some available soon by C.J. Date in and near Finland.

Well maybe you have a EAV model that you have to deal with. Example

SQL> create table eav as
  2  select 1 e, 'first' a, 'Timo' v from dual union all
  3  select 1 e, 'last' a, 'Raitalaakso' v  from dual union all
  4  select 1 e, 'nic' a, 'Rafu' v  from dual union all
  5  select 2 e, 'first' a, 'John' v  from dual union all
  6  select 2 e, 'last' a, 'Doe' v from dual
  7  ;

Table created.

SQL> select * from eav;

         E A     V
---------- ----- -----------
         1 first Timo
         1 last  Raitalaakso
         1 nic   Rafu
         2 first John
         2 last  Doe

You should not query it in a basic case using joins. Most possibly you have tens of joins to the same table.

SQL> select la.e, fi.v firs, la.v las
  2    from eav la, eav fi
  3   where la.e=fi.e
  4     and fi.a='first'
  5     and la.a='last'
  6  ;

         E FIRS        LAS
---------- ----------- -----------
         1 Timo        Raitalaakso
         2 John        Doe

It is a pivot you want to do.

SQL> select e, firs, las
  2    from eav
  3    pivot (max(v) for a in ('first' as firs, 'last' as las))
  4  ;

         E FIRS        LAS
---------- ----------- -----------
         1 Timo        Raitalaakso
         2 John        Doe

With the pivot you get the nullable columns also easier without filtering out the whole entity

SQL> select e, firs, las, ni
  2    from eav
  3    pivot (max(v) for a in ('first' as firs, 'last' as las, 'nic' as ni))
  4  ;

         E FIRS        LAS         NI
---------- ----------- ----------- -----------
         1 Timo        Raitalaakso Rafu
         2 John        Doe

Maybe you do not have 11g features available.

SQL> select e
  2       , max(case when a = 'first' then v end) firs
  3       , max(case when a = 'last' then v end) las
  4    from eav
  5   group by e
  6  ;

         E FIRS        LAS
---------- ----------- -----------
         1 Timo        Raitalaakso
         2 John        Doe

And the best thing to do with it might be.

SQL> drop table eav purge;

Table dropped.

not in null countdown

Just a reminder about not in and nulls. Maybe consider using not exists or anti join if any of the columns in not in list may be null. Or maybe one of the following might be the result you want.

SQL> create table nm as
  2  with le as (select level ev from dual connect by level<4)
  3  select l.ev e,e.ev v from le l, le e;

Table created.

SQL> select count(*) from nm;

  COUNT(*)
----------
         9

SQL> select count(*) from nm where (e,v) not in ((1,1));

  COUNT(*)
----------
         8

SQL> select count(*) from nm where (e,v) not in ((1,1),(2,2));

  COUNT(*)
----------
         7

SQL> select count(*) from nm where (e,v) not in ((1,null));

  COUNT(*)
----------
         6

SQL> select count(*) from nm where (e,v) not in ((1,1),(2,null));

  COUNT(*)
----------
         5

SQL> select count(*) from nm where (e,v) not in ((null,1),(2,null));

  COUNT(*)
----------
         4

SQL> select count(*) from nm where (e,v) not in ((1,null),(2,null));

  COUNT(*)
----------
         3

SQL> select count(*) from nm where (e,v) not in ((1,null),(2,null),(3,3));

  COUNT(*)
----------
         2

SQL> select count(*) from nm where (e,v) not in ((null,1),(null,2),(1,3),(2,3));

  COUNT(*)
----------
         1

SQL> select count(*) from nm where (e,v) not in ((null,null),(1,1));

  COUNT(*)
----------
         0

SQL> select count(*)
  2    from nm m
  3   where not exists (select n
  4                       from (select null n from dual) d
  5                      where m.v = d.n
  6                        and m.e = d.n);

  COUNT(*)
----------
         9

SQL> select count(*)
  2    from nm m
  3     left outer join (select 10 e, null n from dual) d
  4       on m.e=d.e
  5  where d.n is null;

  COUNT(*)
----------
         9

Data types in a view

Is there actually a TIME data type in Oracle? What is the data type of null? Boolean is a number or is it?

SQL> create view v as
  2  select null nul
  3       , time '12:00:00' tim
  4       , dbms_session.is_role_enabled('CREATE SESSION') boo
  5   from dual
  6  ;

View created.

SQL>
SQL> select column_name,data_type,data_length
  2    from user_tab_columns
  3   where table_name = 'V'
  4   order by 1
  5  ;

COLUMN_NAME  DATA_TYPE    DATA_LENGTH
------------ ------------ -----------
BOO          NUMBER                22
NUL          VARCHAR2               0
TIM          TIME(9)               20

SQL>
SQL> select nul from v;

N
-


SQL>
SQL> select tim from v;

TIM
--------------------------------------------------------------
12:00:00,000000000

SQL>
SQL> select boo from v;
select boo from v
                *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

null and time types from Laurent Schneider.

Equality -comparing text

Should we use case clause or still use decode? Yet again someting to be aware. Seems like case clause is trimming before comparing. And the same with DB2 minus. Postgres does not trim.

Oracle

SQL> select case when 'a' = 'a ' then 'same' else 'different' end as test from dual;
 
TEST
---------
same
 
SQL> select 'a' from dual minus select 'a ' from dual;
 
'A
--
a

SQL> select decode('a','a ','same','different') testdecode from dual;

TESTDECOD
---------
different

DB2

db2 => select case when 'a' = 'a ' then 'same' else 'different' end as test from sysibm.sysdummy1
 
TEST
---------
same
 
 1 record(s) selected.
 
db2 => select 'a' from sysibm.sysdummy1 minus select 'a ' from sysibm.sysdummy1
 
1
--
 
 0 record(s) selected.

Postgres

postgres=# select case when 'a' = 'a ' then 'same' else 'different' end as test ;
   test
-----------
 different
(1 row)

postgres=# select 'a' as a except select 'a ' as a;
 a
---
 a
(1 row)

SQL Server

1> select case when 'a' = 'a ' then 'same' else 'different' end as test ;
2> go
test
----
same

(1 rows affected)
1>
2> select 'a'
3> except
4> select 'a '
5> go

--

(0 rows affected)