Rafu on db

XMLType from no rows

2012-01-24T14:39:00.000+02:00

I used DBMS_XMLGEN package in my post constraint name generated. There I used GETXML function and got problems when the schema constraints were all named. So the query result was empty - no rows.

SELECT XMLTYPE(
         DBMS_XMLGEN.GETXML('SELECT 1 FROM dual WHERE 1=0')
       ) AS xml
  FROM dual
;

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

There is a built in DBMS_XMLGEN.GETXMLTYPE function to get the XMLType. This is how you get rid of the ORA-06502 problem.

SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT 1 FROM dual WHERE 1=0') AS xml
  FROM dual
;

A single serializable session throwing ORA-08177

2012-01-20T17:43:00.001+02:00

We have a newly created table in a 11.2.0.3 Enterprise edition instance. A single session is throwing ORA-08177 can't serialize access for this transaction.The whole service level is marked for trace.



exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('servicename')

No one else is updating the tables involved. There comes out only two trace files. One having only the select 1 from dual connection pool check. And in the another trace there may be found.


ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE
END OF STMT

Yes we wanted transactions to be serialized. Couple of inserts and internal staements. And an update to seg$ table thowing ORA-08177.


PARSING IN CURSOR #47594977567928 len=314 dep=1 uid=0 oct=6 lid=0 tim=1327068762208173 hv=3096556448 ad='de83deb8' sqlid='0kkhhb2w93cx0'
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL
, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where t
s#=:1 and file#=:2 and block#=:3
END OF STMT
PARSE #47594977567928:c=0,e=673,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1327068762208172
EXEC #47594977567928:c=1999,e=1411,p=0,cr=3,cu=1,mis=1,r=1,dep=1,og=4,plh=2170058777,tim=1327068762209667
STAT #47594977567928 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  SEG$ (cr=3 pr=0 pw=0 time=116 us)'
STAT #47594977567928 id=2 cnt=1 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ (cr=3 pr=0 pw=0 time=16 us cost=2 size=68 card=1)'
STAT #47594977567928 id=3 cnt=1 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=6 us cost=1 size=0 card=1)'
CLOSE #47594977567928:c=0,e=2,dep=1,type=3,tim=1327068762209778
EXEC #47594977567928:c=0,e=125,p=0,cr=3,cu=1,mis=0,r=1,dep=1,og=4,plh=2170058777,tim=1327068762209931
CLOSE #47594977567928:c=0,e=1,dep=1,type=3,tim=1327068762209972
EXEC #47594977567928:c=0,e=129,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,plh=2170058777,tim=1327068762210127
CLOSE #47594977567928:c=0,e=1,dep=1,type=3,tim=1327068762210167
EXEC #47594984047704:c=50992,e=50764,p=7,cr=576,cu=241,mis=1,r=0,dep=0,og=1,plh=0,tim=1327068762211409
ERROR #47594984047704:err=8177 tim=1327068762211441
STAT #47594984047704 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=1 us)'

*** 2012-01-20 16:12:42.223
WAIT #47594984047704: nam='log file sync' ela= 11537 buffer#=84 sync scn=604674242 p3=0 obj#=-1 tim=1327068762223055
WAIT #47594984047704: nam='SQL*Net break/reset to client' ela= 21 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=1327068762223170
WAIT #47594984047704: nam='SQL*Net break/reset to client' ela= 3715 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=1327068762226909

After awhile the problem vanished... No solutions in this post. Just wondering why...

Oracle instance memory usage

2012-01-19T10:09:00.000+02:00

The right place to look for memory usage of a process in at operating system level. Interpreting the shared memory usage might be misleading. The top command seems to add the shared part to each process. And thou makes it hard to get right numbers. Here is a blog post trying to do that. Thou you could ask the numbers also from Oracle.Parameters influencing Oracle instance memory usage limits. 11g instance might have memory target set or sga and pga set separately.



select name,display_value 
  from v$parameter 
 where name like 'mem%target' 
    or name like 'pga%' 
    or name like 'sga%'
;

The actual PGA usage might hit and go above those limits. The current allocation can be queried.



select round(sum(bytes)/1024/1024/1024,3) SGA_G 
  from v$sgastat;

select round(value/1024/1024/1024,3) PGA_G
  from v$pgastat 
 where name = 'total PGA allocated';

Most often the database load varies over time. For resource planning it is vital to know how the memory usage is changing during night time. There might be going on some batch jobs whose behavior is not seen during day time.If you have diagnostics pack purchased you can also ask what was the situation earlier. Here is a query getting hourly memory usages of an Oracle instance.



select sga.allo sga, pga.allo pga,(sga.allo+pga.allo) tot,trunc(SN.END_INTERVAL_TIME,'mi') time
  from
(select snap_id,round(sum(bytes)/1024/1024/1024,3) allo 
   from DBA_HIST_SGASTAT 
  group by snap_id) sga
,(select snap_id,round(sum(value)/1024/1024/1024,3) allo 
    from DBA_HIST_PGASTAT where name = 'total PGA allocated' 
   group by snap_id) pga
, dba_hist_snapshot sn 
where sn.snap_id=sga.snap_id
  and sn.snap_id=pga.snap_id
order by sn.snap_id desc
;

Longest roman number

2012-01-16T18:47:00.000+02:00

It is possible to ask Oracle database the roman number presentation of a number. Just use to_char function with RN format. Just wondering what is the longest roman number.


select rn,len
 from (
  select rn, max(length(rn))over() mlen, length(rn) len
    from (
    select to_char(level,'RN') rn from dual connect by level <= 4000
    )
  )
where mlen=len
;

Well they all are not 15 characters. Need to trim.


select n, rn,len
 from (
  select n, rn, max(length(rn))over() mlen, length(rn) len
    from (
    select level n, trim(to_char(level,'RN')) rn from dual connect by level <= 4000
    )
  )
where mlen=len
;

3888 MMMDCCCLXXXVIII 15
4000 ############### 15

4000 and above are not converted. Also negative numbers and zero are not available. But in between 3888 seems to be "the longest".

Using client identifier to populate a column

2012-01-10T22:01:00.000+02:00

Earlier I wrote about missing dbms_session.get_client_identifier. Here is a compound trigger example using session client identifier to populate a column in a table.



drop table t;

create table t(n number, client_id varchar2(30) not null);

create or replace trigger t_iuc for insert or update on t 
  compound trigger
  cli varchar2(30);
 before statement is
  begin
   cli := SYS_CONTEXT('userenv', 'CLIENT_IDENTIFIER');
  end before statement;
 before each row is
  begin
   :new.client_id := cli;
  end before each row;
end;
/

exec dbms_session.set_identifier('rafu')

insert into t(n) select level from dual connect by level < 4;

select * from t;

1 rafu
2 rafu
3 rafu

Constraint name generated

2012-01-04T11:39:00.000+02:00

Generated constraint names makes development, testing and production environment error messages vary. And those reported SYS_C006206630 could be more informative. To identify those generated constraint names *_constraints views has the information available at generated column. The constraint search_condition is a long typed column and thou hard to use. Working with long columns document gives tools to convert the search column to varchar2. This query reports generated named constraints in users schema.


WITH xml AS (
          SELECT DBMS_XMLGEN.GETXMLTYPE(
                     'select co.constraint_name
                           , co.table_name
                           , cc.column_name
                           , co.constraint_type
                           , co.r_constraint_name
                           , co.search_condition  
                        from user_constraints co
                        left outer join user_cons_columns cc
                          on co.constraint_name = cc.constraint_name 
                       where co.generated = ''GENERATED NAME''')
                    AS xml
          FROM   dual
          ), concol as (
  SELECT extractValue(xs.object_value, '/ROW/TABLE_NAME')       AS table_name
  ,      extractValue(xs.object_value, '/ROW/COLUMN_NAME')      AS column_name
  ,      extractValue(xs.object_value, '/ROW/CONSTRAINT_TYPE')  AS constraint_type
  ,      extractValue(xs.object_value, '/ROW/CONSTRAINT_NAME')  AS constraint_name
  ,      extractValue(xs.object_value, '/ROW/R_CONSTRAINT_NAME')  AS r_constraint_name
  ,      extractValue(xs.object_value, '/ROW/SEARCH_CONDITION') AS search_condition
  FROM   xml x
  ,      TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
)
select cc.* 
  from concol cc
order by table_name, column_name,constraint_type
;

Give names to the reported constraints to your modeling tool. NOT NULL checks are not first in the list to be named. NOT NULL error messages are informative with the generated names also. But naming also those make the different (DEV,TST,PRD) schemas a bit more similar. SQL Developer Data modeler 3.1 EA3 has the possibility to name those also at relational model. Formerly it was only possible at physical model. If you feel like too much work modeling those at least change the reported names.



ALTER TABLE test RENAME CONSTRAINT SYS_C006206630 TO test_pk;

unindex 11.2 cross schema

2011-12-28T15:30:00.003+02:00

Foreign keys might point to another schema. Here is an unindex query for such situation. My earlier queries unindex and unindex 11.2 were built on top of user_* views. Here the queries are using all_* views. I am not using dba_* views as all_* views are usable for wider audience than just dba privileged.Reminder: you probably do not need all of those, but they might explain you some poor execution times or TM lock waits.



select case when i.index_name is not null
            then 'OK'
            else '****'
       end ok
     , c.owner
     , c.table_name
     , c.constraint_name
     , c.cols
     , i.index_name
from (
  select a.owner
       , a.table_name
       , a.constraint_name
       , listagg(b.column_name, ' ' ) 
          within group (order by column_name) cols
      from all_constraints a, all_cons_columns b
     where a.owner = b.owner
       and a.constraint_name = b.constraint_name
       and a.constraint_type = 'R'
  group by a.owner,a.table_name, a.constraint_name
 ) c
 left outer join
 (
  select index_owner
       , table_name
       , index_name
       , cr
       , listagg(column_name, ' ' ) 
          within group (order by column_name) cols
    from (
        select index_owner
             , table_name
             , index_name
             , column_position
             , column_name
             , connect_by_root(column_name) cr
          from all_ind_columns
       connect by prior column_position-1 = column_position
              and prior index_name = index_name
              and prior index_owner = index_owner
         )
    group by index_owner, table_name, index_name, cr
) i on c.owner = i.index_owner and c.cols = i.cols and c.table_name = i.table_name
order by ok,owner,table_name,constraint_name;

The index could be unique

2011-12-20T13:55:00.000+02:00



drop table uq;

create table uq(pk int constraint uq_pk primary key, col int, col2 int);

create index uq_col_idx on uq(col,pk);

Index uq_col_idx is not unique but it might be defined as such. It contains all columns in a unique primary key. To find such candidates is just another relational division question.



with co as (
select con.constraint_name
     , con.table_name
     , cco.column_name
     , count(*) over (partition by con.constraint_name) ccc 
  from user_constraints con 
 inner join user_cons_columns cco 
    on cco.constraint_name=con.constraint_name 
 where con.constraint_type in ('P','U') 
   and con.deferrable = 'NOT DEFERRABLE' 
), ix as (
select idx.table_name,idx.index_name,ico.column_name,idx.uniqueness
  from user_indexes idx 
 inner join user_ind_columns ico 
    on idx.index_name = ico.index_name
), ixco as (
select ix.table_name
     , ix.index_name
     , ix.column_name
     , co.constraint_name
     , co.ccc
     , count(*) over (partition by co.constraint_name,ix.index_name) ixc
     , ix.uniqueness
  from co, ix
 where co.table_name=ix.table_name 
  and co.column_name=ix.column_name
)
select distinct table_name,index_name could_be_unique,constraint_name based_on
  from ixco
 where uniqueness = 'NONUNIQUE'
   and ccc=ixc
 order by table_name,index_name
;

TABLE_NAME                     COULD_BE_UNIQUE                BASED_ON
------------------------------ ------------------------------ ------------------------------
UQ                             UQ_COL_IDX                     UQ_PK


drop index uq_col_idx;

create unique index uq_col_idx on uq(col,pk);

A table vanishing from a plan

2011-12-14T00:39:00.002+02:00

Christian Antognini published his presentation slides Challenges and Chances of the 11g Query Optimizer. Worth reading to get a nice overview.The 11g new feature "join elimination" is mentioned there. There is a similar behavior even in 10gR2 optimizer. A table mentioned in a query text vanishes from the query plan. It is not a join but a correlated sub query with a aggregate.


select 1 
  from dual mai 
 where exists (
   select max(1) 
     from dual inn 
    where mai.dummy=inn.dummy
)
;

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|   1 |  FAST DUAL       |      |
---------------------------------

The sub query and the "inn" table is not seen in the plan. There is no need as the aggregate returns a row. This is not the case in my earlier post about not exists null. There the sub query is not correlated and it is using not exists.The not exists correlated makes similar elimination. But in addition adds a filter(NULL IS NOT NULL) just before entering even the first table.


select 1 
  from dual mai 
 where not exists (
   select max(1) 
     from dual inn 
    where mai.dummy=inn.dummy
)
;

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|*  1 |  FILTER          |      |
|   2 |   FAST DUAL      |      |
---------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(NULL IS NOT NULL)

One could ask, why would somebody write such a query...

A Question

2011-11-25T10:44:00.001+02:00

I attended the Jonathan Lewis seminar three weeks ago and wrote just after that about information overload. The things learned there have helped me understanding issues I have to deal with the Oracle optimizer. The amount of information gained need some time to melt into mind. One issue talked there was a question.The original written question was "How many choises does Oracle have in this example?" With addition there was mentioned "for all versions".



drop table t1;

create table t1( 
  n1 number
, n2 number
, n3 number
, constraint t1p primary key(n1)
, constraint t1u unique (n2));

select count(*) from t1;

I answered wrongly mentioning also that it depends on the version.As so often my mind started to think howcome I answered such an answer. Maybe I was thinking a question: How many different execution plan possibilities does Oracle have in this example? Unfortunately I am not attending UKOUG to throw this to the OakTable Challenge. Feel free to use "Give me at least one that was not in the list of the Jonathan's answer and how to get there." I will be coming back to the issue after 7th of December.

Filtering outer join to hash join plan

2011-11-23T11:36:00.001+02:00

I gave a SQL presentation a couple of years ago. There I mentioned the possibility to restrict an outer join when using ANSI joins. Every now and then seen such an join condition. Maybe even written myself. This week such a query has been bothering me with a long running nested loops access path. This was a simpler query than the one with outer join exists predicate I have mentioned earlier.While dealing with it I saw a quite funny looking predicate.



drop table aa;

drop table bb;

create table aa as 
select rownum id
     , nullif(mod(rownum,3),1) val 
  from dual connect  by level < 12;

create table bb as 
select rownum id
     , nullif(mod(rownum,3),0)+10 val 
  from dual connect by level < 9;

alter table aa modify id not null;

alter table bb modify id not null;

select aa.* from aa;

        ID        VAL
---------- ----------
         1
         2          2
         3          0
         4
         5          2
         6          0
         7
         8          2
         9          0
        10
        11          2

select bb.* from bb;

        ID        VAL
---------- ----------
         1         11
         2         12
         3
         4         11
         5         12
         6
         7         11
         8         12

Desired results:

   
        ID        VAL         ID        VAL
---------- ---------- ---------- ----------
         1
         2          2          2         12
         3          0
         4
         5          2          5         12
         6          0
         7
         8          2          8         12
         9          0
        10
        11          2

A normal outer join uses hash join plan. Adding the predicate aa.val = 2 to the join condition I get the result I want.


select * 
  from aa 
  left outer join bb 
    on aa.id=bb.id
;

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN OUTER   |      |
|   2 |   TABLE ACCESS FULL| AA   |
|   3 |   TABLE ACCESS FULL| BB   |
-----------------------------------

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

   1 - access("AA"."ID"="BB"."ID")
   
   
select * 
  from aa 
  left outer join bb 
    on aa.id=bb.id and aa.val = 2
;

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  NESTED LOOPS OUTER  |      |
|   2 |   TABLE ACCESS FULL  | AA   |
|   3 |   VIEW               |      |
|*  4 |    FILTER            |      |
|*  5 |     TABLE ACCESS FULL| BB   |
-------------------------------------

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

   4 - filter("AA"."VAL"=2)
   5 - filter("AA"."ID"="BB"."ID")

But the 11.1.0.7 optimizer goes to a nested loops path. My problem is that the table BB is kind of big. Together with plan row 4 filter is not filtering out so many rows the execution is full table scanning the table bb too many times.Throwing the same query to 11.2.0.3 optimizer I get a hash join access path. Nice. That was something I was hoping for. But look at the access predicate.


-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN OUTER   |      |
|   2 |   TABLE ACCESS FULL| AA   |
|   3 |   TABLE ACCESS FULL| BB   |
-----------------------------------

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

   1 - access("AA"."ID"="BB"."ID" AND "AA"."VAL"=CASE  WHEN ("BB"."ID"
              IS NOT NULL) THEN 2 ELSE 2 END )

Obviously the outer join (+) are missing next to BB.ID. You need to read it from the word OUTER next to the HASH JOIN. Sqldeveloper is able to visualize the predicate differently.



AA.ID=BB.ID(+) AND AA.VAL=CASE  WHEN (BB.ID(+) IS NOT NULL) THEN 2 ELSE 2 END

But that case statement seems kind of weird. "If something then constant otherwise the same constant". Should that case statement not be possible to be optimized to be a simple constant 2? I rewrote my query to use the old style join. And with 11.2.0.3 the plan and results stay the same. But going back to 11.1.0.7 things change.



select aa.*,bb.* 
 from aa, bb 
where aa.id=bb.id(+) 
  and aa.val = case when (bb.id(+) is not null) then 2 else 2 end
;

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN OUTER   |      |
|*  2 |   TABLE ACCESS FULL| AA   |
|   3 |   TABLE ACCESS FULL| BB   |
-----------------------------------

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

   1 - access("AA"."ID"="BB"."ID")
   2 - filter("AA"."VAL"=2)

The case statement is optimized as a simple constant and used as a filter staright to the table AA.Query results change compared to 11.2.0.3 results. Actually the thing I wanted out is without the else part in the predicate.


select aa.*,bb.* 
 from aa, bb 
where aa.id=bb.id(+) 
  and aa.val = case when (bb.id(+) is not null) then 2 end
;
-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN OUTER   |      |
|   2 |   TABLE ACCESS FULL| AA   |
|   3 |   TABLE ACCESS FULL| BB   |
-----------------------------------

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

   1 - access("AA"."ID"="BB"."ID" AND "AA"."VAL"=CASE  WHEN ("BB"."ID"
              IS NOT NULL) THEN 2 END )

The results are what I was requesting. This may be rewoten back to ANSI join that is understood also by 11.1.0.7 optimizer and the execution goes to a hash join path.


select aa.*,bb.* 
  from aa 
  left outer join bb 
    on aa.id=bb.id 
   and aa.val = case when (bb.id is not null) then 2 end
;

I am satisfied with the results. Well throw another problem to the optimizers. How about nulls. Change the "and aa.val =" to "and aa.val is null" and even 11.2.0.3 is not able to go to a hash join plan. Here is an problematic nested loops access path query and a dirty trick on a way to a hash path.



select aa.*,bb.*
  from aa 
  left outer join bb 
    on aa.id=bb.id 
   and aa.val is null
;

select * 
  from aa 
  left outer join bb 
    on aa.id=bb.id 
   and coalesce(nullif(aa.val,bb.id),aa.val) is null
;

Skip locked

2011-11-17T19:06:00.001+02:00

SQL SELECT SKIP LOCKED not mentioned in new features part of the SQL document. Seems to be there already in 11gr1.


SQL> create table skiplocked as select  level n from dual connect by level < 3;

Table created.

SQL> select * from skiplocked where n=1 for update;

         N
----------
         1

At the same time another session


SQL> select * from skiplocked;

         N
----------
         1
         2

SQL> select * from skiplocked for update skip locked;

         N
----------
         2

As the 11gr1 have been around for a while. Rob Van Wijk has written something about the feature.
http://rwijk.blogspot.com/2007/12/parallellism-in-skip-locked-scenario.html
http://rwijk.blogspot.com/2009/02/for-update-skip-locked.html
"when using FOR UPDATE SKIP LOCKED, records are locked when they are fetched, not when the cursor is opened" Need to read those again when the need for this feature comes...

Installing an agent and not a free lunch

2011-11-17T16:57:00.001+02:00

Installing an Cloud Control 12 agent using rpm method. So not letting the Cloud Control do the agent installation, but do it manually at the agent node.While generating the rpm


$OMS_HOME/bin/emcli get_agentimage_rpm -destination=/tmp -platform="Linux x86-64"

I got an error


Agent image to rpm conversion failed

and the logfile stating


Directory /usr/lib/oracle doesnt exist. Please create the directory with write permissions and then retry the emcli command.

Well that is mentioned in the document Prerequisites, but This requirement is for the server OMS side.So as a root before issuing the command:


mkdir /usr/lib/oracle
chown oracle:oinstall /usr/lib/oracle

Also note that Editing agent.properties File (Only for Virtual Hosts) means here (Mandatory). Put the agent node host name there.

ORACLE_HOSTNAME(Only for Virtual Hosts) Enter the virtual host name where you want to install the Management Agent.

And before issuing the make sure the machines see each other. After installed the agent I had a small issue with machine naming at /etc/hosts. The installed agent and host appeared yellow "Pending" at the summary pie chart. After fixing the visibity of the agent machine emcli resyncAgent fixed the issue.

$OMS_HOME/bin/emcli resyncAgent -agent="agentname:3872"

Would it be cool to have following situation possible:

But there is no such thing as a free lunch. XE Options and Major Features Not Included ... Tuning PackNice to see that XE 11g installation changes the default control_management_pack_access DIAGNOSTIC+TUNING to NONE by default. It is changed actually with a comment that the default is changed because not an EE installation.

I would not have guessed before starting to install an agent to end up browsing licensing documentation. Seems like Even in PE: The Management Packs are not included in Personal Edition.

Next to the offline agent loading and generating oracle-agt-12.1.0.1.0-1.0.i386.rpm as the Cloud Control has only the oracle-agt-12.1.0.1.0-1.0.x86_64.rpm available. As i would like to try out agent installation also to a -platform="Linux x86" Lets see how that goes in the future...

Information overload

2011-11-03T23:53:00.001+02:00

More than two days of Jonathan Lewis an then some other Ougf autumn seminar presentations. Feels like I am a beginner. I actually do not know enough. There is so much to learn about the basics. The change vector in Oracle. To learn the basics today it is much harder to figure out today than ten years ago. There is so many performance improvements disturbing the basic calculation based on numbers in simple tests.

Mark to your calendars two last days at the end of may 2012. There will be a Ougf seminar in Hämeenlinna. Just another time to feel closer as a beginner. And better yet learn things to unlearn...

Well just a tought. Lending an example from Jonathans teaching. Should the bind name be a part to be considered a part of sql text by optimizer?


drop table t1;

drop table t2;

create table t1(n1 number, n2 number);

create table t2(n1 number, n2 number);

create index t1_i1 on t1(n1);

create index t2_i1 on t2(n1);

First without binds.


 
select t1.n2,t2.n2
 from t1, t2
 where t1.n1=1
   and t2.n1=1
   and t1.n1=t2.n1
;
select * from table(dbms_xplan.display_cursor(format=>'basic +predicate'));

EXPLAINED SQL STATEMENT:
------------------------
select t1.n2,t2.n2  from t1, t2  where t1.n1=1    and t2.n1=1    and 
t1.n1=t2.n1
 
Plan hash value: 1051316565
 
-----------------------------------------------
| Id  | Operation                     | Name  |
-----------------------------------------------
|   0 | SELECT STATEMENT              |       |
|   1 |  NESTED LOOPS                 |       |
|   2 |   NESTED LOOPS                |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |
|*  4 |     INDEX RANGE SCAN          | T1_I1 |
|*  5 |    INDEX RANGE SCAN           | T2_I1 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2    |
-----------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("T1"."N1"=1)
   5 - access("T1"."N1"="T2"."N1")
       filter("T2"."N1"=1)

Nice looking plan.and now with binds.


select t1.n2,t2.n2
 from t1, t2
 where t1.n1=:b1
   and t2.n1=:b1
   and t1.n1=t2.n1
;

select * from table(dbms_xplan.display_cursor(format=>'basic +predicate'));

EXPLAINED SQL STATEMENT:
------------------------
select t1.n2,t2.n2  from t1, t2  where t1.n1=:b1    and t2.n1=:b1    
and t1.n1=t2.n1
 
Plan hash value: 810726339
 
------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|*  1 |  FILTER                        |       |
|   2 |   MERGE JOIN CARTESIAN         |       |
|   3 |    TABLE ACCESS BY INDEX ROWID | T1    |
|*  4 |     INDEX RANGE SCAN           | T1_I1 |
|   5 |    BUFFER SORT                 |       |
|   6 |     TABLE ACCESS BY INDEX ROWID| T2    |
|*  7 |      INDEX RANGE SCAN          | T2_I1 |
------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_NUMBER(:B1)=TO_NUMBER(:B1))
   4 - access("T1"."N1"=TO_NUMBER(:B1))
       filter("T1"."N1"=TO_NUMBER(:B1))
   7 - access("T2"."N1"=TO_NUMBER(:B1))
       filter("T2"."N1"=TO_NUMBER(:B1))
 

 
select t1.n2,t2.n2
 from t1, t2
 where t1.n1=1
   and t2.n1=1
   and t1.n1=t2.n1
;

Plan changed. Oracle knows :B1 should be :B1 1 - filter(TO_NUMBER(:B1)=TO_NUMBER(:B1))But why the plan changed? The bind might be null. And NUL is not equalt to NULL. How about sql monitor report.


 
 
SQL Monitoring Report

SQL Text
------------------------------
select /*+monitor*/t1.n2,t2.n2 from t1, t2 where t1.n1=:b1 and t2.n1=:b1 and t1.n1=t2.n1

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)     
 Instance ID         :  1                   
 Session             :  RAFU (28:21)        
 SQL ID              :  dm8z90v1mk8tb       
 SQL Execution ID    :  16777217            
 Execution Started   :  11/03/2011 13:54:27 
 First Refresh Time  :  11/03/2011 13:54:27 
 Last Refresh Time   :  11/03/2011 13:54:27 
 Duration            :  .002418s            
 Module/Action       :  SQL Developer/-     
 Service             :  SYS$USERS           
 Program             :  SQL Developer       
 Fetch Calls         :  1                   

Binds
========================================================================================================================
| Name | Position |     Type     |                                        Value                                        |
========================================================================================================================
| :B1  |        1 | VARCHAR2(32) | 1                                                                                   |
| :B1  |        2 | VARCHAR2(32) | 1                                                                                   |
========================================================================================================================

Global Stats
==============================
| Elapsed |  Other   | Fetch |
| Time(s) | Waits(s) | Calls |
==============================
|    0.00 |     0.00 |     1 |
==============================

SQL Plan Monitoring Details (Plan Hash Value=810726339)
=======================================================================================================================================
| Id |            Operation             | Name  |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                  |       | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
=======================================================================================================================================
|  0 | SELECT STATEMENT                 |       |         |      |           |        |     1 |          |          |                 |
|  1 |   FILTER                         |       |         |      |           |        |     1 |          |          |                 |
|  2 |    MERGE JOIN CARTESIAN          |       |       1 |    1 |           |        |     1 |          |          |                 |
|  3 |     TABLE ACCESS BY INDEX ROWID  | T1    |       1 |    1 |           |        |     1 |          |          |                 |
|  4 |      INDEX RANGE SCAN            | T1_I1 |       1 |    1 |           |        |       |          |          |                 |
|  5 |     BUFFER SORT                  |       |       1 |      |           |        |       |          |          |                 |
|  6 |      TABLE ACCESS BY INDEX ROWID | T2    |       1 |      |           |        |       |          |          |                 |
|  7 |       INDEX RANGE SCAN           | T2_I1 |       1 |      |           |        |       |          |          |                 |
=======================================================================================================================================

B1 is named nicely. But through jdbc seems like the bind variable naming is lost at some point. A named bind becomes named like :B1 and :B2 all thou there was only one name while binding.

Variable inlist

2011-10-31T16:05:00.000+02:00

There comes every now and then discussions about how to pass varying in list to a SQL statement as a single bind. Just currently there is such discussion going on at oracle-l mailing list "Large IN LIST in an OBIEE query". Also several discussions may be found in Oracle discussion forums.

One problem with these varying in lists is that the SQL query is different for every amount of values passed to an in list.



where col in (?,?);


where col in (?,?,?);

Another problem is that an in list accepts only 1000 different values. This may be overcome using OR:



where col in (?,?,...,?) --1000 placeholders
       or col in (?,?,...,?) --1000 placeholders
       or col in (?,?,?);

But that is just plain old stupid. It seems to be the thing that comes to a developers mind first. As the approach is seen too often.

All kinds of single string passing approaches can be found using Google. With those there is an issue of maximum length 4000 bytes of varchar2 datatype.

One approach is to create an temp table, populate that before executing a query and using the temp table as a normal table in the query. Problems: Two round trips to the database. How to populate the temp table efficiently.

It was two years ago I attended Tanel Poder Advanced Oracle Troubleshooting Seminar. He mentioned an approach using SQL Collection types and table function to implement the varying inlist with just one bind. Actually seems like he is having his seminar starting just todayhttp://blog.tanelpoder.com/seminar/. Lucky you attending. This week I will have a pleasure to attend Jonathan Lewis seminar in Helsinki.

Just a bit earlier I wrote about how to populate a table efficiently using Java. This approach may be used to populate the temp table. But why do that when you can do it all in a single call as Tanel described.



create type numbers is table of number;

And Map the NUMBERS type to ArrayDescriptor.createDescriptorA test java source inlist.java.



        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("NUMBERS", c);
        ARRAY a = new ARRAY(ad, c, elems.toArray());
        OraclePreparedStatement ops 
          = (OraclePreparedStatement)c.prepareStatement(
                 "select /*+gather_plan_statistics*/ count(*) cou"+
                 "   from inlist_test "+
                 "  where num in ( " +
                 "        select column_value " +
                 "          from table(?))");
        ops.setARRAY(1, a);

        ResultSet rs = ops.executeQuery();

This approach works also for another types of Collections Dates, Strings and so . Just create own table of type for each needed. Readily availabel SYS.ODCINUMBERLIST seems to be safe to use with Collections smaller than about 32k. So no additional database objects need to be greated.



ArrayDescriptor ad = ArrayDescriptor.createDescriptor("SYS.ODCINUMBERLIST", c);

One problem in the discussions have been how the optimizer sees the cardinalities of the collection. Here are three different plans from 11.2.0.2 depending on the Collection and table size. As you can see quite large collections may be passed to the database using this technique. Plan 1497893883 is generated using one million numbers in the inlist. With large Collections be aware of the memory consumption I mentioned in the using Java post.


SQL_ID  fzb8ysr291xz3, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ count(*) cou   from inlist_test   
where num in (         select column_value           from table(:1 ))
 
Plan hash value: 4131715231
 
----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |      1 |        |      1 |00:00:00.01 |       3 |       |       |          |
|   1 |  SORT AGGREGATE                     |             |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|*  2 |   HASH JOIN SEMI                    |             |      1 |      1 |     24 |00:00:00.01 |       3 |  1517K|  1517K| 1308K (0)|
|   3 |    TABLE ACCESS FULL                | INLIST_TEST |      1 |     99 |     99 |00:00:00.01 |       3 |       |       |          |
|   4 |    COLLECTION ITERATOR PICKLER FETCH|             |      1 |     25 |     25 |00:00:00.01 |       0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------
 
 
 
Plan hash value: 1497893883
 
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |      1 |        |      1 |00:00:01.15 |    1528 |   1262 |       |       |          |
|   1 |  SORT AGGREGATE                     |             |      1 |      1 |      1 |00:00:01.15 |    1528 |   1262 |       |       |          |
|*  2 |   HASH JOIN RIGHT SEMI              |             |      1 |      1 |    999K|00:00:04.22 |    1528 |   1262 |    34M|  6431K|   48M (0)|
|   3 |    COLLECTION ITERATOR PICKLER FETCH|             |      1 |   1000K|   1000K|00:00:00.71 |       0 |      0 |       |       |          |
|   4 |    TABLE ACCESS FULL                | INLIST_TEST |      1 |    893K|    999K|00:00:00.69 |    1528 |   1262 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------
 

 
Plan hash value: 2750379354
 
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        |      1 |00:00:00.45 |    1528 |   1262 |       |       |          |
|   1 |  SORT AGGREGATE                      |             |      1 |      1 |      1 |00:00:00.45 |    1528 |   1262 |       |       |          |
|*  2 |   HASH JOIN                          |             |      1 |   3125 |  99999 |00:00:00.44 |    1528 |   1262 |  3749K|  1936K| 5625K (0)|
|   3 |    SORT UNIQUE                       |             |      1 |    100K|    100K|00:00:00.11 |       0 |      0 |  4588K|   893K| 4078K (0)|
|   4 |     COLLECTION ITERATOR PICKLER FETCH|             |      1 |    100K|    100K|00:00:00.07 |       0 |      0 |       |       |          |
|   5 |    TABLE ACCESS FULL                 | INLIST_TEST |      1 |    893K|    999K|00:00:00.68 |    1528 |   1262 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("NUM"=VALUE(KOKBF$))
 
Note
-----
   - dynamic sampling used for this statement (level=2)

Congratulations to the winners

2011-10-25T19:27:00.000+03:00

I received just an email from Iggy Fernandez. My answer was not in the set of winners. Congratulations to all three. The challenge might be nice rehearsal to solve using Spatial Network Data Model SQL extension. The email as received:

The Second International NoCOUG SQL Challenge was published on 2/13/11 in the February 2011 issue of the NoCOUG Journal (http://bit.ly/gVNZsW). SQL commands to create the data were provided at http://bit.ly/g58WVn. The challenge was to find the secret message hidden in a seemingly random collection of words. The winners are Andre Araujo (Australia), Rob van Wijk (Netherlands), and Ilya Chuhnakov (Russia.) Each winner will receive an Amazon Kindle from contest sponsor Pythian and the August Order of the Wooden Pretzel in keeping with the pronouncement of Steven Feuerstein that“some people can perform seeming miracles with straight Es-Cue-El, but the statements end up looking like pretzels created by somebody who is experimenting with hallucinogens.”

The full announcement can be read in the 100th issue of the NoCOUG Journal (http://bit.ly/rC2gRA).

How many function calls and sysdate evaluated

2011-10-21T15:30:00.004+03:00

Talking here about function calls and when sysdate is evaluated. Starting with a silly function f2 that takes a second to execute and a session that shows only the seconds out of a date type.



SQL> alter session set nls_date_format='ss';

Session altered.

SQL> create or replace function f2( x in date ) return date
  2    as
  3    begin
  4            dbms_lock.sleep(1);
  5            return x;
  6    end;
  7  /

Function created.

SQL> select a a, a b from (select f2(sysdate) a from dual);

A  B
-- --
37 37

Elapsed: 00:00:02.02

Written a query that uses the function once. And seems like sysdate is evaluated at the main query and it returns the same value for both a and b. But how come the execution time is two seconds?From the optimizer trace can be seen that the query is transformed to have two calls to the function. That explains the execution time.


Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "RAFU"."F2"() "A","RAFU"."F2"() "B" FROM "SYS"."DUAL" "DUAL"


SQL> select f2(sysdate) a, f2(sysdate) b from dual;

A  B
-- --
39 39

Elapsed: 00:00:02.02

Similar result. Lets change the function to have its own cursor.



SQL> create or replace function f3 return date
  2    as
  3    x date;
  4    begin
  5            dbms_lock.sleep(1);
  6            select sysdate into x from dual;
  7            return x;
  8    end;
  9  /

Function created.

SQL> select a a, a b from (select f3() a from dual);

A  B
-- --
42 43

Elapsed: 00:00:02.02

Execution time the same and the result changed. The sysdate is evaluated at the time cursor in the function is called. Remember the query transformation. The transformation may be avoided and the function is called actually only once.



SQL> select a a, a b from (select /*+no_merge*/ f3() a from dual);

A  B
-- --
54 54

Elapsed: 00:00:03.01

Be aware query transformations may lead to changing query results when function are mixed in a query.How about transaction isolation. Does that have influence on the issue when the function is called twice. So is the sysdate behaving like changing rownum. The changing rownum is talked in Laurent Schneider blog and comments there.



SQL> set transaction read only;

Transaction set.

Elapsed: 00:00:00.00
SQL> SELECT F3() A,F3() B FROM DUAL;

A  B
-- --
03 04

Elapsed: 00:00:02.01
SQL> rollback;

The sysdate is not respecting the read only setting on the transaction but it is getting its value just when asked.

New package DBMS_XPAN?

2011-09-28T11:40:00.001+03:00

Is this humor from error message writer?


SQL> select * from table(dbms_xplan.display_cursor(format=>'allsts'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
Error: format 'allsts' not valid for DBMS_XPAN.DISPLAY_CURSOR()

Migrating to 11.2.0.3 ORA-00976

2011-09-27T15:34:00.001+03:00

You can have exists even in join condition in addition to what Rob Van Wijk has writen. We have code lines that has such exists together with an connect by query.Patching an 11.2.0.2 database to 11.2.0.3In 11.2.0.2 one can run such a query. Here is one stupid example of such.



select d2.dummy from dual d1 left outer join dual d2 on exists (select 1 from dual connect by level < 3); 

D
-
X

In 11.2.0.3 you get an error thrown to your face.



ORA-00976: Specified pseudocolumn or operator not allowed here.
Cause: LEVEL, PRIOR, ROWNUM, CONNECT_BY_ROOT, CONNECT_BY_ISLEAF or CONNECT_BY_ISCYCLE was specified at an illegal location.
Action: Remove LEVEL, PRIOR, ROWNUM, CONNECT_BY_ROOT, CONNECT_BY_ISLEAF or CONNECT_BY_ISCYCLE.

The query may be rewritten like 11.2.0.3 parser is satisfied.



with cte as (select 1 from dual connect by level < 3)
select d2.dummy from dual d1 left outer join dual d2 on exists (select 1 from cte);

D
-
X

This is a runtime problem. Run your tests before patching production. Actually this is not just a runtime problem. We had the code inside a pl/sql procedure. The procedure is valid in 11.2.0.2 environment. After patching the procedure is still valid just waiting for the next compile or runtime problem of this code.Well there is most certainly a reason for such problem appearing. Might this be a side affect for a Bug correction 8724314 mentioned in 11.2.0.3 Patch Set - List of Bug Fixes by Problem Type [ID 1348303.1]. Might this usage of common table expression workaround be just a next bug waiting to appear.

11.2.0.3

2011-09-26T11:39:00.000+03:00

Just downloading Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER

Patches are correcting bugs and adding new features. I guess that there is a new value to OPTIMIZER_FEATURES_ENABLED parameter coming.

Time to unlearn. Tom Kyte talked at Oug Harmony 2011 last spring about caching inside a SQL query. He wrote about the same topic in Oracle Magazine september 2011. The thing seems like a candidate to be implemented in Oracle SQL engine in some future release. (I guess not yet in this 11.2.0.3). Just when a wide developer audience has learned to wrap pl/sql funtion calls inside a query to avoid multiple calls.

The thing to be unlearned might be a bug. Causing something not to perform or do wrong behavior in certain version. In some future version the bug might be corrected and the workaround becomes obsolete. These are currently most often details that influence some specific situation. If you want to know the basics and more. Jonathan Lewis is comig to Finland this autumn. A two day learning session is available. Optimizing Oracle 1.-2.11.2011. And in addition Oracle User Group Finland autumn seminar 3.11.2011 seems to have pleasure to have his presentation. Agenda and registration information available to both at http://www.ougf.fi/ A lot guess working in this post. To avoid that attend.

Avoid temp usage while table reorg

2011-09-21T16:58:00.001+03:00

Richard Foote blog is having questioning posts. Thanks to the latest solution there we have a possible alternative while reorganizing a huge table.Just hit an "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP" problem while doing table reorganization.Who and what sql is consuming temp helps identifying the problem statement.

insert /*+append*/ into targettable select * from sourcetable order by sortcolumn;

The source table has an index on sortcolumn. Using that it is possible to avoid sorting and temp usage.

insert /*+append*/ into targettable select /*+index(sourcetable sortcolumn_idx)*/ * from sourcetable order by sortcolumn;

Password expiring

2011-09-01T11:55:00.003+03:00

Yet again...
A new 181 days ago created 11g database instance...
A software connection pool user is not allowed to connect to the database...

ORA-28002: the password will expire within 7 days
OR
ORA-28001: the password has expired


select profile from dba_users where username = 'POOLUSER';



DEFAULT



create profile pool_profile limit PASSWORD_LIFE_TIME unlimited;



alter user pooluser profile pool_profile; 



select profile from dba_users where username = 'POOLUSER';



POOL_PROFILE

set role

2011-08-16T17:26:00.003+03:00

Trying to get autotrace out of SQL Developer and getting error message.


Failed to access V$MYSTAT.

Please obtain read catalog privilege

from your database administrator:

grant SELECT_CATALOG_ROLE to RAFU

grant SELECT ANY DICTIONARY toRAFU

NOTE: you need to reconnect your current session

in order for the settings change to have an effect

Granted the privileges. I guess less would be enough. Weird note. Why do I need to reconnect the session? There exists SET ROLE command.


SET ROLE ALL;

That does the job. No reconnect needed.

Crabby code

2011-08-10T09:54:00.002+03:00


ORA-00020: maximum number of processes 500 exceeded

Connections are using DEDICATED connections. The pooling is done in the middle tier. Several pools. Connection leakage is not a database fault but it becomes to a everybody problem. While figuring out the problem, I managed to produce a following 10046 SQL trace.


ERROR #1:err=12899 tim=1312894876206660

WAIT #1: nam='SQL*Net break/reset to client' ela= 20 driver id=675562835 break?=1 p3=0 obj#=-1 tim=1312894876206740

WAIT #1: nam='SQL*Net break/reset to client' ela= 303 driver id=675562835 break?=0 p3=0 obj#=-1 tim=1312894876207059

WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=1312894876207098

WAIT #1: nam='SQL*Net message from client' ela= 247133 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=1312894876454273

Using the Oracle friendly search.

Talking about SQL*Net break/reset to client events Tanel Poder mentions these breaks are caused by bad application design. This is something I was trying to find out, but he is talking about MERGE statements. My trace included only inserts.

In the Xtrace manual it is mentioned "err" being equal to 12899 (ORA-12899 is "value too large for column"): That makes sense.

Luckily I had taken the trace with binds. And could point out the actual column causing the root problem. Only the first 255 characters of the bind values seems to be populated to the trace. The problem column was larger in this case. Using My Oracle support page Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output [ID 39817.1] it is possible to read the trace. In the bind part avl Actual value length (array length too).. Compared that to the column size at the position of the bind in the insert statement and the root problem was found.

About bad application design
* The application should know how long values can be inserted to each column.
* If the application receives an error it should have an error handler that releases the reserved resources. This time the unreleased resource was a database connection.

Major

2011-06-09T00:07:00.007+03:00

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

OUG Harmony 2011

2011-05-20T17:04:00.003+03:00

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

2011-05-09T17:39:00.002+03:00

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

2011-05-06T10:54:00.004+03:00

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

2011-05-04T23:30:00.003+03:00

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

2011-04-13T08:23:00.003+03:00

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

11.2 XE beta and SQL Developer 3

2011-04-01T23:36:00.002+03:00

11.2 XE beta available.

And seems like not a april fool Tanel Poder describing features.

Documentation available. And a dedicated discussion forum.

SQL Deveoper 3 beta days seems to be over

Descending index is function based

2011-04-01T11:28:00.002+03:00

Descendin index is implemented as function based index in Oracle. This is documented and discussed.
Nulls may lead to a surprise with a desc unique index.


SQL> create table a(a varchar2(2 char), b varchar2(2 char) );

Table created.

SQL>
SQL> select table_name,column_name,data_type,data_length from user_tab_cols where table_name = 'A';

TABLE_NAME                     COLUMN_NAME                    DATA_TYPE
------------------------------ ------------------------------ --------------------------------------
A                              A                              VARCHAR2
A                              B                              VARCHAR2

SQL>
SQL> create unique index a_idx on a (a,b desc);

Index created.

SQL>
SQL> select table_name,column_name,data_type,data_length from user_tab_cols where table_name = 'A';

TABLE_NAME                     COLUMN_NAME                    DATA_TYPE
------------------------------ ------------------------------ --------------------------------------
A                              A                              VARCHAR2
A                              B                              VARCHAR2
A                              SYS_NC00003$                   RAW

SQL>
SQL> select index_type from user_indexes where index_name = 'A_IDX';

INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL

SQL>
SQL> select * from user_ind_columns where index_name = 'A_IDX';

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
A_IDX                          A
A
              1             8           2 ASC

A_IDX                          A
SYS_NC00003$
              2            13           0 DESC


SQL>
SQL> select * from USER_IND_EXPRESSIONS where index_name = 'A_IDX';

INDEX_NAME                     TABLE_NAME                     COLUMN_EXPRESSION
------------------------------ ------------------------------ --------------------------------------
A_IDX                          A                              "B"

SQL>
SQL> insert into a values(null,null);

1 row created.

SQL>
SQL> insert into a values(null,null);
insert into a values(null,null)
*
ERROR at line 1:
ORA-00001: unique constraint (KIOS.A_IDX) violated


SQL>
SQL> drop index a_idx;

Index dropped.

SQL>
SQL> create unique index a_idx on a (a,b);

Index created.

SQL>
SQL> insert into a values(null,null);

1 row created.

SQL>
SQL> insert into a values(null,null);

1 row created.

Installing

2011-04-01T11:15:00.002+03:00

Just to remember. Have to try next time when installing a new computer.
http://ninite.com

Greatest Common Divisor

2011-03-25T08:19:00.006+02:00

Need a greatest common divisor function? Vadim Tropashko has written a SQL implementation of such. Here it is wrapped inside a function.



create or replace function greatest_common_div(n1 number, n2 number) 
return number 
as
ret number;
begin
with NumberSet as ( 
select column_value Element from table(sys.odcinumberlist( n1, n2 ))
), Integers as (
select level num# from dual connect by level <= least( n1, n2 )
)
select max(Divisor) into ret 
from (
  select Divisor
    from (
    select num# as Divisor from Integers 
    where num# <= (select min(Element) from NumberSet)
  ), NumberSet 
  where mod(Element, Divisor)=0
  group by Divisor
 having count(*) = (select count(*) from NumberSet)
);
return ret;
end;
/


select greatest_common_div(15,123) from dual;

3

And a 11.2 version. Idea borrowed from http://wiki.postgresql.org. This is a better performing one as it handles only two numbers.


create or replace function greatest_common_div2(n1 number, n2 number) 
return number 
as
ret number;
begin
WITH t(a,b) AS (
    select n1, n2 from dual
UNION ALL
    SELECT b, mod(a,b) FROM t
    WHERE b > 0
)
SELECT a into ret
  FROM t 
 WHERE b = 0
;
return ret;
end;
/

And the same without pl/sql to sql context switch. Added 12.9.2011



create or replace function greatest_common_div3(n1 number, n2 number) 
return number 
as
ret number;
begin
ret := n1;
if (n2 != 0) then 
 ret := greatest_common_div3(n2,mod(n1,n2));
end if;
return ret;
end;
/

with numbers as (select level num from dual connect by level < 100)
, test as (
select a.num a
     , b.num b
     , greatest_common_div(a.num,b.num) gcd
     , greatest_common_div2(a.num,b.num) gcd2
     , greatest_common_div3(a.num,b.num) gcd3
 from numbers a, numbers b
)
select * from test where gcd!=gcd2 or gcd!=gcd3
;

Update 28.3.2011
Added an user defined aggregate also.



CREATE OR REPLACE TYPE t_gcd_agg AS OBJECT
(
  g_number  number,
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_gcd_agg)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_gcd_agg,
                                       value  IN      number )
     RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_gcd_agg,
                                         returnValue  OUT  number,
                                         flags        IN   NUMBER)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_gcd_agg,
                                     ctx2  IN      t_gcd_agg)
    RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY t_gcd_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_gcd_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_gcd_agg(NULL);
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_gcd_agg,
                                       value  IN      number )
    RETURN NUMBER IS
  BEGIN
    SELF.g_number := greatest_common_div3(case when self.g_number is null then value else self.g_number end, value);
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_gcd_agg,
                                         returnValue  OUT  number,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := SELF.g_number;
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_gcd_agg,
                                     ctx2  IN      t_gcd_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_number := greatest_common_div3(case when self.g_number is null then ctx2.g_number else self.g_number end
                                        , case when ctx2.g_number is null then self.g_number else ctx2.g_number end);
    RETURN ODCIConst.Success;
  END;
END;
/

CREATE OR REPLACE FUNCTION gcd (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_gcd_agg;
/

select gcd(column_value) from table(sys.odcinumberlist(4,8,34));

2

with test as (
select 'a' a, 4 n from dual union all
select 'a' a, 6 n from dual union all
select 'b' a, 30 n from dual union all
select 'b' a, 60 n from dual union all
select 'b' a, 96 n from dual
)
select a,gcd(n) 
from test 
group by a;

a 2
b 6

grep in windows

2011-03-16T16:32:00.002+02:00

Missing grep in windows.
Power shell to the rescue.

findstr -s "search_string" files

Merge ignores check constraint

2011-03-11T11:35:00.002+02:00

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

2011-03-09T17:01:00.002+02:00

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
)
;

Deprecation

2011-03-03T09:27:00.003+02:00

During last two weeks I have noticed twice the use of oracle.jdbc.driver.OracleDriver with 11g version. It has been almost five years ago when the Oracle jdbc driver package "oracle.jdbc.driver." deprecated.

With 11g please use oracle.jdbc.OracleDriver instead of the deprecated oracle.jdbc.driver.OracleDriver.

This might be found in your application server connection pool settings, project jdbc.properties or hard coded something like java.lang.Class.forName("oracle.jdbc.driver.OracleDriver");
as it should be
java.lang.Class.forName("oracle.jdbc.OracleDriver");

NOCOUG Second SQL Challenge

2011-02-28T19:00:00.008+02:00

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 
;

revoke through a db link

2011-02-23T13:53:00.003+02:00

Why would someone do such a thing? Following will end up an active a user session stuck waiting "SQL*Net message from dblink".



sqlplus /nolog

connect / as sysdba


drop user a cascade;

drop user b cascade;


create user a identified by a;

grant create session to a;

grant create procedure to a;

grant create database link to a;


create user b identified by b;

grant create session to b;


connect a/a

declare 
 comm varchar2(200);
begin 
 select 'create database link b connect to b identified by b using '''||
        sys_context('userenv','db_unique_name')||'''' into comm 
   from dual;
 execute immediate comm;
end;
/


create or replace procedure p as
begin
execute immediate 'revoke execute on p from b';
end;
/


grant execute on p to b;

exec a.p@b

not exists null

2011-02-09T23:56:00.003+02:00

I wrote earlier about not in and null. Be careful also when using not exists predicate together with a sub query resulting nulls.


SQL> select 1 from dual where not exists (select 1 from dual where 1=0);

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

SQL> select 1 from dual where 1=0;

no rows selected



SQL> select 1 from dual where not exists (select max(1) from dual where 1=1);

no rows selected

SQL> select max(1) from dual where 1=1;

    MAX(1)
----------
         1



SQL> select 1 from dual where not exists (select max(1) from dual where 1=0);

no rows selected

SQL> select max(1) from dual where 1=0;

    MAX(1)
----------


SQL> select 1 from dual where not exists (select null from dual);

no rows selected

SQL> select null from dual;

N
-

In the sub query there is a row but it is null, unknown. So not exists null evaluates to false. More about the issue in responses to a oracle-l mailing list post.

ORDER SIBLINGS BY CONNECT_BY_ROOT

2011-01-28T14:06:00.016+02:00

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

2011-01-27T15:44:00.002+02:00

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.

GetClientIdentifier

2011-01-24T14:45:00.002+02:00

Having a connection pool that connects to a schema with one username. The end users are authenticated to your application. It is possible to get the end user identifier information visible to database level v$session client_identifier column. DBMS_SESSION.SET_IDENTIFIER may be used or with JDBC setEndToEndMetrics method of the oracle.jdbc.OracleConnection.

After setting client identifier information to a session it is possible to set database tracing to a client identifier. But how about your own code, how is it possible to read the client identifier information set in the session? There is no DBMS_SESSION.GET_IDENTIFIER method. One might use select to v$session view but there might be not grants to do so.

Another possibility might be to user DBMS_APPLICATION_INFO.SET_CLIENT_INFO. DBMS_APPLICATION_INFO.READ_CLIENT_INFO is available. Setting client info is not available through JDBC EndToEndMetrics. Client info changes are possible to be set so the client identifier changes also. Set ALTER SYSTEM SET EVENTS 'CLIENTID_OVERWRITE'; This way the tracing by the end user becomes available.

The client identifier is available through SYS_CONTEXT so the GET_IDENTIFIER may be achieved using
select SYS_CONTEXT('userenv', 'CLIENT_IDENTIFIER') from dual;

Driving a road

2010-12-14T21:18:00.005+02:00

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

2010-12-09T23:58:00.004+02:00

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 lnameFROM 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

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

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

| 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.

Loading a collection with check option

2010-10-10T16:49:00.004+03:00

Lets add a new requirement to inserting a collection of data. All rows inserted should be in some range. An example of such code may be found I my earlier post load using Java

We have a collection of data. At this point we do not know the values included. A new requirement should be implemented. The upper bound of values should be checked before committing the insert. Should we browse through the collection before throwing it in?

Alex Nuijten just posted Inline View Check Option. That could be used. So the inserting method might be something like


private static void insertArray(Connection c, List elems, int upperbound) 
            throws SQLException {
        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("SIT", c);
        ARRAY a = new ARRAY(ad, c, elems.toArray());
        OraclePreparedStatement ops 
           = (OraclePreparedStatement)c.prepareStatement(
                        "insert into insert into " + 
                        " (select s, s2 " +
                        "    from si where s <= ? " +
                        "    with check option) " +
                        " select * " +
                        "    from table(?)");
        ops.setInt(1, upperbound);
        ops.setARRAY(2, a);

        System.out.println(ops.executeUpdate());
        ops.close();
        c.commit();
    }

If everything is ok there is no need to browse through the collection in client side. In invalid collection situation there is the round trip to the database to get the error "ORA-01402: view WITH CHECK OPTION where-clause violation". The List implementation for elems should be the place to handle such requirement. Just had to write something about a inline view with check option feature. Maybe some day there is similar pl/sql code that might benefit this.

ISO year week day to date

2010-10-07T23:21:00.003+03:00

Trying to get date out of three of numbers. Three numbers are ISO standard year, ISO standard week and number of a day in a week. The first day of a week is Monday.


select to_date ('2010 40 4','iyyy iw d') from dual;

ORA-01820: format code cannot appear in date input format
01820. 00000 -  "format code cannot appear in date input format"

A date specification contained an invalid format code. Only the following
may be specified when entering a date: year, month, day, hours, minutes,
seconds, Julian day, A.M./P.M. and B.C./A.D.


select to_timestamp ('2010 40 4','iyyy iw d') from dual;

Surprise, no luck, the same error.

From Oracle support formely known as metalink can be found a statement that the feature has not been in such priority to be implemented. Build your own function. I am too lazy to do that. And as I know the timerange I am interested in I use brute force. Use data. We are in a database. It is built to store data. I will use that ability.



create table isoyearweekday_to_date(
isoyearweekday number(7) constraint isoyearweekday_to_date_pk primary key
, dat date not null) 
organization index;

insert into isoyearweekday_to_date 
select to_char(d,'iyyyiwd')
     , d 
  from (
   select to_date('17991231','yyyymmdd')+level d
     from dual 
   connect by level <= to_date('22000101','yyyymmdd')-to_date('18000101','yyyymmdd')+1
  )
;

select * from isoyearweekday_to_date where isoyearweekday = 2010404
;

2010404 07.10.2010

null - quilty or not

2010-10-01T23:17:00.002+03:00

It is Friday evening. Watching a recording of Mentalist.

true - false - null
had some drinks - sober - a drink too much
innocent - pulled the trigger - too drunk to remember

Usefull indexes

2010-09-24T17:20:00.001+03:00

Do we actually need such indexes?


select owner,table_name,index_name 
  from all_indexes 
 where distinct_keys < 2
   and num_rows > 100;

Ezconnect

2010-09-20T22:06:00.002+03:00

Today I noticed the reason why I have not bothered to use ezconnect method with sqlplus connections.


sqlplus system/oracle@localhost/orcl

I do not like to reveal my passwords on screen if possible.


sqlplus system@localhost/orcl

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Additional quotes are needed with ezconnect, when I do not want to write the password on command line.


sqlplus system@\"localhost/orcl\"
Enter password:


sqlplus /nolog
SQL> conn system@"localhost/orcl"
Enter password:
Connected.

Seems like 11.2 client does not need NAMES.DIRECTORY_PATH= (ezconnect) line in sqlnet.ora anymore to use the method. 10.1, 10.2 and 11.1 version client installations need that line there.

extended audit_trail

2010-09-16T22:21:00.002+03:00

Want to audit issued sql_text and binds in 11g database?

Documentation says that audit_trail parameter may have values


Parameter type String
Syntax AUDIT_TRAIL = { none | os | db [, extended] | xml [, extended] }

Trying


SQL> alter system set audit_trail='db, extended' scope=spfile;
alter system set audit_trail='db, extended' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value db, extended for parameter audit_trail, must be from
among extended, xml, db_extended, false, true, none, os, db

Uups, the documented one did not work. I will try out the first suggested one.


SQL> alter system set audit_trail='extended' scope=spfile;
alter system set audit_trail='extended' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00096: invalid value EXTENDED for parameter audit_trail, must be from among
NONE | OS | DB | DB,EXTENDED | XML | XML,EXTENDED

Uups, Oracle did it again. DB,EXTENDED seems a bit closer to the one documented.



SQL> alter system set audit_trail='DB,EXTENDED' scope=spfile;
alter system set audit_trail='DB,EXTENDED' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value DB,EXTENDED for parameter audit_trail, must be from
among extended, xml, db_extended, false, true, none, os, db

Back to the original error message. Yet another quess...


SQL> alter system set audit_trail='db_extended' scope=spfile;

System altered.

Yes I found it. After reboot sql_text and binds are collected.

Should the documentation say


Parameter type String
Syntax AUDIT_TRAIL = { none | true | false | os | db | db_extended | xml }

No. The problem is that issuing a String parameter as a quoted 'string'.

The correct way to do documented way


SQL> alter system set audit_trail=db, extended scope=spfile;

System altered.

This way also the new xml, extended is accepted, if someone likes it that way. Also some old (10.1) and alternative ways are also accepted. Although we should use only the documented ones.


SQL> alter system set audit_trail=true scope=spfile;
SQL> alter system set audit_trail=false scope=spfile;
SQL> alter system set audit_trail=db_extended scope=spfile;
SQL> alter system set audit_trail=db,extended scope=spfile;
SQL> alter system set audit_trail=db,  extended scope=spfile;
...

So minor issue, but should the ORA-00096 error messages be updated? Maybe. Compared to other product error messages the information Oracle gives in those are mostly understandable.

Anoying SQL Developer

2010-09-14T01:14:00.006+03:00

Yet another new installation of SQL Developer. I am writing SQL. The query includes ANALYTIC functions not AGGREGATE ones. SQL Developer hits and adds some group by clause somewhere in between my clause. Getting rid of that.

Tools

Preferences...

Code editor

Completion Insight

unselect Autogenerate GROUP BY clause.

I so wish this would be the default.

Lean

2010-09-06T23:47:00.005+03:00

Just had a pleasure to participate a session with Jim Coplien. His earlier presentation about the main issues seems to be found also online. Need to listen that again later. MVC and DCI. Model and data in both the part closest to the storage seems to be the first words to name software architectures. Maybe also the Lean Architecture book to be read.

Baag

2010-08-24T09:32:00.003+03:00

I have been involved in many situations that all come to the issue of knowing not quessing. The ideas behind BAAG should be considered in many problem solving situations. Avoid guessworking, RTFM (Read that fine manual), ...

To avoid guesworking recommended attendance Oracle User Group Finland Autumn Seminar 2010 4. November 2010 in Pörssitalo, Helsinki, Finland

locator index size

2010-06-24T00:37:00.004+03:00

Just dealing a situation with 10.2.0.4 locator index. Size of a index had grown to 1.3G. After rebuilding it had size less than 50M.
Maybe there is going on something similar than Richard Foote is talking about bitmap indexes behaving in 9.2 and 10.2.
Trying a similar test with a locator index.


CREATE TABLE foo AS 
 SELECT mod(ROWNUM,1000)+1 ID
     , SYSDATE+mod(ROWNUM,1000)+1 fro
     , SYSDATE+mod(ROWNUM,1000)+2 til
     , 'FOO' NAME 
  FROM (SELECT NULL A FROM
(SELECT NULL A FROM dual CONNECT BY LEVEL <= 1000),
(SELECT NULL A FROM dual CONNECT BY LEVEL <= 1000));

CREATE OR REPLACE FUNCTION TF(FRO DATE, TIL DATE) RETURN SDO_GEOMETRY deterministic as 
BEGIN
RETURN MDSYS.SDO_GEOMETRY(2002,NULL, NULL, SDO_ELEM_INFO_ARRAY (1,2,1),
    SDO_ORDINATE_ARRAY(to_number(to_char(FRO,'j')),0,to_number(to_char(TIL,'j')),0));
END;
/

select min(to_number(to_char(FRO,'j'))),max(to_number(to_char(TIL,'j'))) from foo;

2455373 2456373

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'FOO',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2455373, 2456373, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

commit;

CREATE INDEX FOO_LOCATOR_IDX ON FOO(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index
;

analyze index FOO_LOCATOR_IDX compute statistics;

SELECT index_name, blevel, leaf_blocks, num_rows FROM user_indexes WHERE index_name = 'FOO_LOCATOR_IDX';

"INDEX_NAME" "BLEVEL" "LEAF_BLOCKS" "NUM_ROWS"
"FOO_LOCATOR_IDX" "" "" ""

Nothing there. It is a domain index and storing its structures in a table.


select segment_name,bytes,blocks from user_segments where segment_name like 'MDRT_%';

MDRT_11976$ 92274688 11264

SELECT count(*) FROM MDRT_11976$;

33342

And now populating in a loop as Richard did with bitmap index.



CREATE TABLE bar AS 
 SELECT * FROM foo 
  WHERE 0=1
;

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'BAR',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2455373, 2456373, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

commit;

CREATE INDEX BAR_LOCATOR_IDX ON BAR(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index
;

begin
 FOR i IN 1..1000 loop
  FOR j IN 1..1000 loop
   INSERT INTO bar VALUES (j, SYSDATE+j, SYSDATE+j, 'FOO');
   COMMIT;
  END loop;
 end loop;
end;
/



select segment_name,bytes,blocks from user_segments where segment_name like 'MDRT_%';

MDRT_11976$  92274688      11264
MDRT_119DF$ 125829120      15360


SELECT count(*) FROM MDRT_11976$;

33342

select count(*) from MDRT_119DF$;

58084

Well that does not explain the 1.3G size. Similar result with 11.2.0.1, 11.1.0.7 and 10.2.0.4.

Visual tuning

2010-06-16T00:08:00.004+03:00

It was a nice opportunity to attend beta testing a tool that does something that I do with pen and paper quite often when trying to figure out query relations.

Could not attend a webinar last Thursday. Just waiting for the opportunity to see the replay.

Exclusion Constraints

2010-05-19T00:03:00.004+03:00

In future Postgresql versions they are talking about exclusion constraints. Hitting just the thing I have been writing about in several posts about not overlapping and how to dirty hack implement it somehow with Oracle. Nice video presentation about the issue from San Francisco PostgreSQL User Group. And some pdf about the same issue.

Types of columns

2010-05-18T20:13:00.003+03:00

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.

How long will it take?

2010-04-30T12:39:00.006+03:00

Reloading a table.


truncate table sometable;

insert /*+append*/ into sometable select ...

commit;

How long will it take?

Table is populated earlier and the space consumed will be about the same after reload.

With following technique I was able to get such an estimate after starting the insert.

Before truncate see how much space there is to be consumed.


select sum(bytes) from user_segments where segment_name = 'SOMETABLE';

4445110272

Check the execution plan of the insert statement. Ensure it will start consuming space at the beginning of execute.

Start the load. Identify the session doing the insert statement and estimate.


select ((targetb-currb)/currb)*interv+currt estimatedfinnish 
  from (
 select currt
       -(select sql_exec_start from v$session where sid = :sid)  interv
     , currb, currt
     , :b4445110272 targetb
   from (
  select systimestamp currt
       , sum(bytes) currb 
    from user_segments 
   where segment_name like 'SOMETABLE' 
));

That is giving some time in the future.

Reasons why the estimate is not correct.

1. The execution plan does not start consuming space at the beginning.
2. The space used before truncate was not the same that it will be at the end of load.
3. Indexes in the table to be populated.

If there are indexes they are builded after insert phase. Estimate was given for the table to be populated. Index building phase may be monitored by looking at V$SESSION_LONGOPS.

Not overlapping (MV approach)

2010-04-27T21:17:00.004+03:00

Presented earlier a not overlapping function based unique indexes approach. In this post I am using a materialized view and a unique constraining that. Several commits seen in here because MV approach makes constraints kind of deferrable. The MV is refreshed at commit phase. As the dirty hack function based unique indexes on the table itself are violated straight at the insert.



DROP TABLE Z CASCADE CONSTRAINTS PURGE;

DROP TABLE YEARS CASCADE CONSTRAINTS PURGE;

DROP MATERIALIZED VIEW Z_MV;

CREATE TABLE Z(Z NUMBER(16) NOT NULL
             , VALIDFROM NUMBER(4) NOT NULL
             , VALIDTILL NUMBER(4) NOT NULL
             , CONSTRAINT FRO2000 CHECK (2000 < VALIDFROM)
             , CONSTRAINT TIL2050 CHECK (VALIDTILL <= 2050)
             , CONSTRAINT FROTIL CHECK (VALIDFROM <= VALIDTILL)
             );


CREATE TABLE YEARS AS 
SELECT 2000+LEVEL TIM FROM DUAL CONNECT BY LEVEL < (2051-2000)
;

CREATE MATERIALIZED VIEW LOG ON Z WITH ROWID
;

CREATE MATERIALIZED VIEW LOG ON YEARS WITH ROWID
;

CREATE MATERIALIZED VIEW Z_MV REFRESH FAST ON COMMIT AS
SELECT Z.ROWID ZRID,T.ROWID TRID,Z.Z,T.TIM 
  FROM Z INNER JOIN YEARS T ON VALIDFROM < T.TIM AND T.TIM <= VALIDTILL 
;

With Oracle 11.1.0.7 and 11.2.0.1 getting
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

No worries. It is about inner join syntax not so widely supported with MVs.



CREATE MATERIALIZED VIEW Z_MV REFRESH FAST ON COMMIT AS
SELECT Z.ROWID ZRID,T.ROWID TRID,Z.Z,T.TIM 
  FROM Z,YEARS T 
 WHERE VALIDFROM < T.TIM AND T.TIM <= VALIDTILL 
;

ALTER TABLE Z_MV ADD CONSTRAINT Z_MV_U UNIQUE (Z,TIM);

CREATE INDEX Z_MV_ZRID_IDX ON Z_MV(ZRID);

ZRID indexed to give the optimizer at least a possibility to do small updates "fast". More about the issue may be read from Alberto Dell'Era's Oracle blog



INSERT INTO Z VALUES(1,2001,2011);

INSERT INTO Z VALUES(1,2011,2011);

COMMIT;

INSERT INTO Z VALUES(1,2010,2012);

COMMIT;

SQL ERROR: ORA-12008: ERROR IN MATERIALIZED VIEW REFRESH PATH
ORA-00001: UNIQUE CONSTRAINT (RAFU.Z_MV_U) VIOLATED


INSERT INTO Z VALUES(2,2049,2050);

COMMIT;

INSERT INTO Z VALUES(2,2049,2050);

COMMIT;

SQL ERROR: ORA-12008: ERROR IN MATERIALIZED VIEW REFRESH PATH
ORA-00001: UNIQUE CONSTRAINT (RAFU.Z_MV_U) VIOLATED



INSERT INTO Z VALUES(2,2010,2012);

COMMIT;

INSERT INTO Z VALUES(2,2001,2049);

COMMIT;

SQL ERROR: ORA-12008: ERROR IN MATERIALIZED VIEW REFRESH PATH
ORA-00001: UNIQUE CONSTRAINT (RAFU.Z_MV_U) VIOLATED


INSERT INTO Z VALUES(2,2014,2017);

COMMIT;

SELECT * FROM Z ORDER BY Z, VALIDFROM;

1 2001 2011
1 2011 2011
2 2010 2012
2 2014 2017
2 2049 2050

SELECT COUNT(*) FROM Z_MV;

16

Pipelining

2010-04-27T09:28:00.003+03:00

In reducing number of function calls I wrote about how to rewrite a query that is calling a function. Another approach to the issue is to alter the function. The function includes only a SQL clause. The whole result of the clause is bulk collected first and then returned. The usage of the function is in IN clause. IN predicate is satisfied if there is one equality coming out of the select. So in the best cases it is not needed to populate the whole bulk collect inside the function. Using pipelining as an alternative here.

Original bulk collect version.


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.

And the pipelined version of the function.


SQL> create or replace function rnpiped(n number) return ns_typ pipelined is
  2  begin
  3    for ret in
  4     (select level l from dual connect by level <= n)
  5    loop
  6     dbms_lock.sleep(1/n);
  7     pipe row (ret.l);
  8    end loop;
  9  end;
 10  /

If the whole result returned from the function is needed there is no great difference in the execution times. Actually it seems to be increasing a bit.



SQL> select * from table(rn(10));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

10 rows selected.

Elapsed: 00:00:01.00
SQL>
SQL> select * from table(rnpiped(10));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

10 rows selected.

Elapsed: 00:00:01.07

But when used in IN predicate the results with this data are even better than in the previous post.


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.00
SQL>
SQL> select * from ta a where a.n in (select * from table(rnpiped(a.m)));

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

Elapsed: 00:00:01.95

And putting both together.



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.09
SQL>
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(rnpiped(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:00.68

Paging before join and aggregation

2010-04-23T13:36:00.004+03:00

First test data.


drop table chi purge;

drop table par purge;


create table par 
( o varchar2(30)
, t varchar2(30)
, n varchar2(30)
, constraint par_pk primary key(o,t,n)) 
organization index ;

create table chi 
( o varchar2(30)
, t varchar2(30)
, n varchar2(30)
, c varchar2(30)
, constraint chi_pk primary key(o,t,n,c)
, constraint chi_par_fk foreign key(o,t,n) references par(o,t,n)) 
organization index ;

insert into par select distinct owner, object_type, object_name from dba_objects;

insert into chi(o,t,n,c) 
select distinct ta.owner, ob.object_type, ta.table_name, ta.column_name 
from all_tab_columns ta 
inner join all_objects ob 
   on ta.owner=ob.owner and TA.TABLE_NAME = ob. object_name
;

commit;

exec dbms_stats.gather_table_stats(user,'PAR');

exec dbms_stats.gather_table_stats(user,'CHI');

Query to start with and the info about execution.


SQL> SELECT n,t,o,cou
  2    FROM (  SELECT pa.o, pa.t, pa.n, COUNT ( * ) cou
  3              FROM par pa LEFT OUTER JOIN chi ch
  4                      ON pa.o = ch.o and pa.t = ch.t and pa.n = ch.n
  5          GROUP BY pa.o, pa.t, pa.n
  6          ORDER BY pa.t, pa.o, pa.n)
  7   WHERE ROWNUM < 3;

N                              T                              O                                     COU
------------------------------ ------------------------------ ------------------------------ ----------
C_COBJ#                        CLUSTER                        SYS                                     1
C_FILE#_BLOCK#                 CLUSTER                        SYS                                     3

Elapsed: 00:00:02.26

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |        |      1 |        |      2 |00:00:02.19 |    2014 |
|   2 |   VIEW                          |        |      1 |    112K|      2 |00:00:02.19 |    2014 |
|*  3 |    SORT GROUP BY STOPKEY        |        |      1 |    112K|      2 |00:00:02.19 |    2014 |
|   4 |     MERGE JOIN OUTER            |        |      1 |    112K|    166K|00:00:01.93 |    2014 |
|   5 |      SORT JOIN                  |        |      1 |  64558 |  64558 |00:00:00.52 |     666 |
|   6 |       INDEX FULL SCAN DESCENDING| PAR_PK |      1 |  64558 |  64558 |00:00:00.06 |     666 |
|*  7 |      SORT JOIN                  |        |  64558 |    112K|    112K|00:00:00.87 |    1348 |
|   8 |       INDEX FULL SCAN           | CHI_PK |      1 |    112K|    112K|00:00:00.11 |    1348 |
----------------------------------------------------------------------------------------------------

The alternative to do paging first and aggregate only the rows to be retrieved.

Paging done in par inline view line 9, order by need to be done on both line 8 for paging and 14 to get the results back in order after joining and aggregating.



SQL>   SELECT pap.n,
  2           pap.t,
  3           pap.o,
  4           COUNT ( * ) cou
  5      FROM (SELECT *
  6              FROM (  SELECT pa.o, pa.t, pa.n
  7                        FROM par pa
  8                    ORDER BY pa.t, pa.o, pa.n)
  9             WHERE ROWNUM < 3
 10            ) pap
 11       LEFT OUTER JOIN chi ch
 12         ON pap.o = ch.o AND pap.t = ch.t AND pap.n = ch.n
 13  GROUP BY pap.o, pap.t, pap.n
 14  ORDER BY pap.t, pap.o, pap.n;

N                              T                              O                                     COU
------------------------------ ------------------------------ ------------------------------ ----------
C_COBJ#                        CLUSTER                        SYS                                     1
C_FILE#_BLOCK#                 CLUSTER                        SYS                                     3

Elapsed: 00:00:00.20

-----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY NOSORT      |        |      1 |      2 |      2 |00:00:00.20 |     673 |
|   2 |   NESTED LOOPS OUTER       |        |      1 |      2 |      4 |00:00:00.20 |     673 |
|   3 |    VIEW                    |        |      1 |      2 |      2 |00:00:00.20 |     666 |
|*  4 |     COUNT STOPKEY          |        |      1 |        |      2 |00:00:00.20 |     666 |
|   5 |      VIEW                  |        |      1 |  64558 |      2 |00:00:00.20 |     666 |
|*  6 |       SORT ORDER BY STOPKEY|        |      1 |  64558 |      2 |00:00:00.20 |     666 |
|   7 |        INDEX FULL SCAN     | PAR_PK |      1 |  64558 |  64558 |00:00:00.06 |     666 |
|*  8 |    INDEX RANGE SCAN        | CHI_PK |      2 |      1 |      4 |00:00:00.01 |       7 |
-----------------------------------------------------------------------------------------------

Notice cou column cannot be in order by condition.
Join needs to be outer.

Reducing the number of function calls

2010-04-21T16:13:00.003+03:00

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;

Packing rows

2010-04-16T20:14:00.004+03:00

So worth tasting. Having a Keisari Strong Munchener 5,7% brewed by Nokian panimo. Figuring out a thing done today.

The case was that we have a relation that has the only one over time feature present. There is the possibility that the same attribute is the same in several continuous rows. And we want to put those rows together in a query result. So the question here was how to implement pack operator presented in C.J. Date book Temporal data and the relational model. Some good alternatives are presented in Joe Celko's thinking in sets: auxiliary, temporal, and virtual tables in SQL. He presents a OLAP Function Solution. Here we have the not overlapping present so the question is a bit simpler. And to a example...


CREATE TABLE Z(Z NUMBER(16) NOT NULL
             , VALIDFROM NUMBER(4) NOT NULL
             , VALIDTILL NUMBER(4) NOT NULL
);

INSERT INTO Z VALUES (1,2000,2003);

INSERT INTO Z VALUES (1,2003,2004);

INSERT INTO Z VALUES (2,2004,2005);

INSERT INTO Z VALUES (2,2005,2008);

INSERT INTO Z VALUES (1,2008,2010);

SELECT Z, MINVALIDFROM, MAX(VALIDTILL) MAXVALIDTILL
  FROM (
  SELECT Z, VALIDFROM,VALIDTILL,PREVTILL
       , MAX(CASE WHEN VALIDFROM <= PREVTILL
                  THEN NULL
                  ELSE VALIDFROM
              END) 
         OVER ( PARTITION BY Z ORDER BY VALIDFROM, VALIDTILL
                ROWS UNBOUNDED PRECEDING) MINVALIDFROM
    FROM (
    SELECT Z
         , VALIDFROM,VALIDTILL
         , LAG(VALIDTILL) 
           OVER (PARTITION BY Z ORDER BY VALIDTILL) PREVTILL
      FROM Z
   ) 
)
GROUP BY Z, MINVALIDFROM
ORDER BY MINVALIDFROM, Z
;

1 2000 2004
2 2004 2008
1 2008 2010

The simpler part is to use LAG.

Datafile HWM

2010-04-09T08:50:00.005+03:00

Cleaning up "joins gone wild" application bug. First cleaned up a mess that consumed a lot of space in tablespaces. After that had to move objects with high block_id values so the datafile size may be redused. To avoid hitting the error ORA-03297: file contains used data beyond requested RESIZE value. Alter table move may be used only the objects are not used. If you need to do this as an online operation use DBMS_REDEFINITION.

Generators I used for tablespaces containing only tables.


SELECT file_id,'alter table '||owner||'.'||segment_name||' move'||
   case when segment_type='TABLE PARTITION' then ' PARTITION '||partition_name
        when segment_type='TABLE SUBPARTITION' then ' SUBPARTITION '||partition_name 
    end ||';' sq, 
MAX(BLOCK_ID) max_block_id,
SEGMENT_NAME, partition_name, SEGMENT_TYPE
  FROM DBA_EXTENTS
 WHERE FILE_ID in (4,5,6,7,8)
 group by file_id,owner,SEGMENT_NAME, partition_name, SEGMENT_TYPE
 having MAX(BLOCK_ID) > 100000
 order by file_id,max_block_id desc
;

select 'alter database datafile '||file_id||' resize '||(ceil(max(block_id)*db_block_size/1024/1024/1024))||'G;'
  from dba_extents, (
    select value db_block_size 
      from v$parameter 
     where name='db_block_size' 
     )
 where FILE_ID in (4,5,6,7,8)
 group by db_block_size,file_id
 order by 1
;

Indexes went broken.


select 'alter index '||index_name||' rebuild;' from user_indexes where status = 'UNUSABLE' order by 1;

select 'alter index '||index_name||' rebuild partition '||partition_name||';' from user_ind_partitions where status = 'UNUSABLE' order by 1;

select 'alter index '||index_name||' rebuild subpartition '||partition_name||';' from user_ind_subpartitions where status = 'UNUSABLE' order by 1;

Tablespaces including indexes.


SELECT distinct 'alter index '||owner||'.'||segment_name||' rebuild'||
   case when segment_type='INDEX PARTITION' then ' PARTITION '||partition_name
        when segment_type='INDEX SUBPARTITION' then ' SUBPARTITION '||partition_name 
    end ||' online;' sq, 
MAX(BLOCK_ID) max_block_id
  FROM DBA_EXTENTS
 WHERE FILE_ID in (9,10,11,12,13)
 group by file_id,owner,SEGMENT_NAME, partition_name, SEGMENT_TYPE
 having MAX(BLOCK_ID) > 100000
 order by file_id,max_block_id desc
;

And after that datafile resize.

Anoying Toad

2010-04-06T17:44:00.002+03:00

Two anoying features of Toad. Use SQLPLUS or SQL Developer instead.


create table pa(p number constraint pa_pk primary key) 
partition by hash(p) 
partitions 2;

select partition_name from user_tab_partitions where table_name = 'PA';

SYS_P61
SYS_P62

Truncating a partition with Toad gives an error.


alter table pa truncate partition SYS_P61;

ORA-14006: invalid partition name

Modifying index visibility with Toad gives an error.


alter index pa_pk invisible;

ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations

alter index pa_pk visible;

ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations

Just get rid of the semi colon and you are able to use these commands with Toad.


alter index pa_pk visible

A dark side of Easter

2010-04-04T00:01:00.003+03:00

It was an Easter a while ago. I had a bottle of Finnish porter beer. It was made by Laitilan breweries. My mother in law got some of it and made it clear that it tasted like mämmi. She put some vanilla sauce in the class and the taste was, well similar like mämmi with vanilla sauce. This year Laitilan breweries has a bottle that has a yellow lable saying Mämmi on it. They have actually made beer out of mämmi. Well if you do not know what mämmi is take alook of something else from Finnish kitchen called Kalakukko. They make that mainly in Kuopio. I had an exiting evening in Hakametsä. Ice hockey arena in Tampere. My favourite ice hockey team Tappara just won Kalpa - a team from Kuopio.

Putting things together that do not actually have nothing to do with each other make sometimes wheels running. Ice hockey and mämmi have nothing in common like vanilla sauce and Kuopio. During last year I have had several occasions when the ideas from spatial advisor have came to the rescue. There have been several cases when I have been dealing with query performance problems with temporal data. Well spatial information is more close to temporal than Kalpa is to Laitila. These ideas have come to the rescue several times when the number count in tables have increased from a million to ten or hundred million. Changing a traditional b-tree index to a function based locator index and a query where predicate to use that has helped the response time to be usable. Joining similar table with greater than or between comparison has also been a troublesome to deal with. Using those function based locator indexes and sdo_join have helped a lot.

Lets have a simple example not from those real life experiences like I did not reveal earlier. Lets copy the base situation from sum over time writing. Notable here is that the locator indexing is available also in free XE version and also SE and EE. The basic idea for function based locator index may be expressed like


DROP TABLE T CASCADE CONSTRAINTS PURGE;

DROP FUNCTION TF;


CREATE TABLE T (FRO DATE, TIL DATE, N NUMBER);

INSERT INTO T VALUES (TO_DATE('09.04.2009','dd.mm.yyyy'), TO_DATE('10.04.2009','dd.mm.yyyy'), 1);

INSERT INTO T VALUES (TO_DATE('10.04.2009','dd.mm.yyyy'), TO_DATE('12.04.2009','dd.mm.yyyy'), 2);

INSERT INTO T VALUES (TO_DATE('11.04.2009','dd.mm.yyyy'), TO_DATE('14.04.2009','dd.mm.yyyy'), 3);

COMMIT;

Query performing not with satisfactory response time.


SELECT * 
  FROM T 
 WHERE TIL >= TO_DATE('11.04.2009','dd.mm.yyyy') 
   AND FRO <= TO_DATE('14.04.2009','dd.mm.yyyy')
;

10.04.2009 12.04.2009 2
11.04.2009 14.04.2009 3

CREATE OR REPLACE FUNCTION TF(FRO DATE, TIL DATE) RETURN SDO_GEOMETRY deterministic as 
BEGIN
RETURN MDSYS.SDO_GEOMETRY(2002,NULL, NULL, SDO_ELEM_INFO_ARRAY (1,2,1),
    SDO_ORDINATE_ARRAY(to_number(to_char(FRO,'j')),0,to_number(to_char(TIL,'j')),0));
END;
/

SELECT TO_CHAR(to_date('19000101','yyyymmdd'),'J'),TO_CHAR(to_date('22000101','yyyymmdd'),'J') FROM dual;

--2415021 2524594

DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'T';

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'T',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2415021, 2524594, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

COMMIT;

CREATE INDEX T_LOCATOR_IDX ON T(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index;

SELECT * 
  FROM T 
 WHERE SDO_FILTER(TF(FRO,TIL),
                  TF(TO_DATE('11.04.2009','dd.mm.yyyy')
                    ,TO_DATE('14.04.2009','dd.mm.yyyy'))
                 ) = 'TRUE'
;

10.04.2009 12.04.2009 2
11.04.2009 14.04.2009 3

Just try with your own temporal data and PlanViz and compare the a-rows inside your query.

An another thing that I sayed in sum over time writing was "I like to see continous pair stored as open ended". It is just one thing to deal with this approach. SQL standard by the way says that the end in time interval should not be included in the interval. But if the end is the same than the start moment then the interval is presenting the one spot in the timeline. So in that case the end is not excluding.

Yet another thing about timelines and my earlier postings. Last year I wrote about some ideas about not overlapping daily. The idea presented in this post might near to the answer missing in that post. But it is not possible to create a domain index as a unique.

By the way Mämmi beer with vanilla sauce got called today white russian by my mother in law. Wonder what might Dude in Big Lebowski say about that.

Visualizing plans grants

2010-03-28T18:58:00.004+03:00

A while ago I post about to use plan visualization by Tanel Poder. That has been a tool worth using. Thou that should be a tool also to other developers than DBAs. A-Rows and A-Time are such valuable information about the execution bottlenecs inside SQL execution.

How to get it working without dba rights.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

User has no SELECT privilege on V$SESSION

It is not possible to grant select rights straight to v$ views as Coscan has writen.


grant select on v_$session to rafu;

And a try


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL

Giving a grant


grant select on v_$sql_plan_statistics_all to rafu;

And after that no success. The same output.
11.2 documentation says that to use DBMS_XPLAN.DISPLAY_CURSOR the calling user must have SELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views.


grant select on v_$sql_plan to rafu;

grant select on v_$sql to rafu;

And we are there. User rafu may use DBMS_XPLAN.DISPLAY_CURSOR with sql_id=>null parameter. Also grant alter session to avoid the need to use gather_plan_statistics hint might be recommended. That is how you enable ALTER SESSION SET statistics_level = ALL;


grant alter session to rafu;

Also giving these grants through a role to developer users is worth considering.

Pivoting EAV

2010-03-26T04:13:00.006+02:00

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.

Parallel query distribution methods

2010-03-11T21:03:00.003+02:00

Figuring out should I recommend parallelizing materialized view refresh for several mvs or use parallel partition wise joins. Found valuable help for the issue just yesterday by Tony Hasler. Will be using both. Unfortunately no blog writing coming up about the issue.

not in null countdown

2010-03-05T23:41:00.004+02:00

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

2010-02-14T17:35:00.004+02:00

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.

Open position

2010-02-12T23:46:00.002+02:00

It has been a while since Pirjo Salo wrote about job opportunities for database specialist in Finland. Be aware something to be available soon.

Equality -comparing text

2010-02-08T10:02:00.004+02:00

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)

select into

2010-01-25T12:53:00.004+02:00

How come lines 9 and 10 do not execute in the following is_active function? Documentation says that use into_clause only when there is one row in the result. Should the remaining code be ignored when there are no rows. Be sure to have exactly that one row available when using select into structure. Count(*) is a way here to be sure there is the one row.



SQL> CREATE OR REPLACE function is_active(i_is_active number)
 2      return number as
 3      ret number := 0;
 4  begin
 5      dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
 6      select 1 into ret
 7        from dual
 8       where i_is_active = 1;
 9      dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
10      if ret is null then ret := 0;
11      end if;
12      return ret;
13  end;
14  /

Function created.

SQL>
SQL> select is_active(1),is_active(0) from dual
 2  ;

IS_ACTIVE(1) IS_ACTIVE(0)
------------ ------------
          1

-0-before-1-
-1-after -1-
-0-before-0-
SQL>
SQL>
SQL> CREATE OR REPLACE function is_active_usingcount(i_is_active number)
 2      return number as
 3      ret number := 0;
 4  begin
 5      dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
 6      select count(*) into ret
 7        from dual
 8       where i_is_active = 1;
 9      dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
10      return ret;
11  end;
12  /

Function created.

SQL>
SQL> select is_active_usingcount(1),is_active_usingcount(0) from dual
 2  ;

IS_ACTIVE_USINGCOUNT(1) IS_ACTIVE_USINGCOUNT(0)
----------------------- -----------------------
                     1                       0

-0-before-1-
-1-after -1-
-0-before-0-
-0-after -0-

You get "ORA-01422: exact fetch returns more than requested number of rows" if there are more than one row. Should there be an exception also to the case when there are no rows?

Update 4.11.2010:
Well there is a exception thrown. Just not catching it anywhere.


CREATE OR REPLACE function is_active(i_is_active number)
       return number as
       ret number := 0;
  begin
       dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
       select 1 into ret
       from dual
      where i_is_active = 1;
     dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
     if ret is null then ret := 0;
     end if;
     return ret;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN return -1;
    end;
/

select is_active(1),is_active(0) from dual;

IS_ACTIVE(1) IS_ACTIVE(0)
------------ ------------
1            -1

Only one (yet again)

2010-01-22T21:27:00.004+02:00

Is that not a normal DBA work week.
Installing Oracle on HP-UX cluster
Trying to resolve problematic CPU usage -Solaris
Rewriting loooong query more readable
Fiquring out 9.2 RAC
Talking about a PL/SQL function having comments from the 90's
Talking about modeling tools
Partitioning postgresql
A very good chinese dinner at Dong Bei Hu
Some dark beer
Night in a hotel
Reading awr reports and creating some of my own
Making ssh connections to a 10.2 RAC environment
Installing 11.2
Dumping a datafile block, boy that wait information in v$session mislead me. Should have used snapper. Well the blocks were the same both in primary and standby
Rewriting that loooong query so that it will perform when there will be 10M rows in the future
Sitting in a company meeting and hearing that Solita is recruiting new people

Luckily some actual modeling work also. How to constrain a model where there is a variation of the only one problem. The difference here was that a country does not have to have a default currency. But if it has any currencies there has to be one as a default. And also a restriction that no virtual columns. Oracle version 10.2. In the following example currencies goes to A table and countries to D.


drop  materialized view da_atleast_one_mv;

drop table a cascade constraints purge;

drop table d cascade constraints purge;


create table d(d_id number primary key, current_a_id number)
;

create table a(a_id number primary key
             , d_id references d not null
             , constraint a_u unique(d_id,a_id))
;

alter table d add constraint d_curr_fk foreign key (d_id,current_a_id) references a(d_id,a_id) deferrable initially deferred;


create materialized view log on d with rowid;

create materialized view log on a with rowid;

create materialized view da_atleast_one_mv 
refresh on commit as 
select d.rowid drid, a.rowid arid, d.d_id, a.a_id
  from d, a
 where d.current_a_id(+)=a.a_id
;

alter table da_atleast_one_mv modify d_id constraint da_atleast_one_nn not null;

insert into d values (0,null);

commit;

--now we have a country without any currencies

insert into d values (1,null);

insert into a values (1,1);

commit;
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into ("RAFU"."DA_ATLEAST_ONE_MV"."D_ID")

insert into d values (2,2);

insert into a values (2,2);

commit;

select * from da_atleast_one_mv;

update d set current_a_id=null where d_id = 2;

commit;
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into ("RAFU"."DA_ATLEAST_ONE_MV"."D_ID")

Potential not nulls

2010-01-21T01:06:00.003+02:00

Something to send to a development team of the model or just run the clauses from these generators. Here are ways to figure out potential missing not null constraints.

First a test table



create table nnc(n number, m number);

insert into nnc values (1,2);

commit;

By counting the actual rows



select 'select ''--alter table '||table_name||' modify '||column_name||' not null;'' c from ( select count(*) from '||table_name||' having count(*)>0 and count(*) = sum(case when '||column_name||' is not null then 1 end));' 
  from user_tab_columns 
 where nullable='Y'
 order by table_name,column_name
;

select '--alter table NNC modify M not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when M is not null then 1 end));

select '--alter table NNC modify N not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when N is not null then 1 end));

--alter table NNC modify M not null;

--alter table NNC modify N not null;

Or another way by using stats



exec dbms_stats.gather_table_stats(user,'NNC');

select t.table_name, c.column_name, t.num_rows 
  from user_tab_cols c 
 inner join user_tables t 
    on t.table_name=c.table_name
 where c.nullable='Y'
   and t.temporary='N'
   and num_rows > 0
   and num_nulls = 0
 order by t.table_name,c.column_name
;


select '--alter table '||t.table_name||' modify '||c.column_name||' not null;'
  from user_tab_cols c 
 inner join user_tables t 
    on t.table_name=c.table_name
 where c.nullable='Y'
   and t.temporary='N'
   and num_rows > 0
   and num_nulls = 0
 order by t.table_name,c.column_name
;

And the thirdth option putting those together. Candidates from stats and after that checking the actual data.



select 'select ''--alter table '||table_name||' modify '||column_name||' not null;'' c from ( select count(*) from '||table_name||' having count(*)>0 and count(*) = sum(case when '||column_name||' is not null then 1 end));' 
  from user_tab_columns 
 where nullable='Y'
   and table_name in (
    select t.table_name
      from user_tab_cols c 
     inner join user_tables t 
        on t.table_name=c.table_name
     where c.nullable='Y'
       and t.temporary='N'
       and num_rows > 0
       and num_nulls = 0
   )
 order by table_name,column_name
;

select '--alter table NNC modify M not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when M is not null then 1 end));

select '--alter table NNC modify N not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when N is not null then 1 end));

--alter table NNC modify M not null;

--alter table NNC modify N not null;

Well maybe the stats version is the one to use.

I prefer having names also to the not null constraints. So after dealing with the developer



alter table NNC modify M constraint nnc_m_nn not null;

Visualizing plans

2010-01-19T00:21:00.002+02:00

http://tech.e2sn.com/apps/planviz seems something that will help me tomorrow.

Rely constraint validated mess

2010-01-13T18:02:00.002+02:00

Not so happy with the html support pages. Could not create a SR. Had to go back to flash pages.

Today's issue is about a constraint that is in validated state. Oracle documentation says that if you use rely constraints, you should know what you are doing. Maybe you did not know and want to go back to norely mode. Here is an example what you should not do.



SQL> create table pa (pa_id number constraint pa_pk primary key);

Table created.

SQL>
SQL> insert into pa
  2  select level pa_id from dual connect by level < 3;

2 rows created.

SQL>
SQL> create table ch(ch_id number constraint ch_pk primary key
  2                , pa_id not null constraint ch_pa_fk references pa
  3                )
  4  ;

Table created.

SQL>
SQL> insert into ch select level ch_id, level pa_id from dual connect by level < 3;

2 rows created.

SQL>
SQL> alter table ch modify constraint ch_pa_fk disable novalidate;

Table altered.

SQL>
SQL> alter table ch modify constraint ch_pa_fk rely;

Table altered.

SQL>
SQL> select status,validated,rely from user_constraints where constraint_name = 'CH_PA_FK';

STATUS   VALIDATED     RELY
-------- ------------- ----
DISABLED NOT VALIDATED RELY

SQL>
SQL> insert into ch select level+10 ch_id, level+10 pa_id from dual connect by level < 3;

2 rows created.

SQL> select * from pa;

     PA_ID
----------
         1
         2

SQL> select * from ch;

     CH_ID      PA_ID
---------- ----------
         1          1
         2          2
        11         11
        12         12

SQL>
SQL> alter table ch modify constraint ch_pa_fk enable;

Table altered.

SQL>
SQL> --Why was that possible?
SQL>
SQL> alter table ch modify constraint ch_pa_fk validate;

Table altered.

SQL>
SQL> --Why was that possible?
SQL>
SQL> alter table ch modify constraint ch_pa_fk norely;

Table altered.

SQL>
SQL> alter table ch modify constraint ch_pa_fk enable validate;

Table altered.

SQL>
SQL> select status,validated,rely from user_constraints where constraint_name = 'CH_PA_FK';

STATUS   VALIDATED     RELY
-------- ------------- ----
ENABLED  VALIDATED

SQL>
SQL> select *
  2    from ch c
  3    where not exists (select null from pa p where p.pa_id = c.pa_id)
  4  ;

no rows selected

SQL>
SQL> alter table ch modify constraint ch_pa_fk disable novalidate;

Table altered.

SQL>
SQL> select *
  2    from ch c
  3    where not exists (select null from pa p where p.pa_id = c.pa_id)
  4  ;

     CH_ID      PA_ID
---------- ----------
        11         11
        12         12

SQL> alter table ch modify constraint ch_pa_fk enable validate;
alter table ch modify constraint ch_pa_fk enable validate
                                 *
ERROR at line 1:
ORA-02298: cannot validate (SYSTEM.CH_PA_FK) - parent keys not found

Going back from rely mode to norely. Go first to norely and validate after.

New year even happier

2010-01-12T00:09:00.004+02:00

After migrating to 11.2 flash really has been driving me nuts. Mainly the migration from 11.1 gave us improved performance. But being a SR generator has made me feel not so warm thoughts for flash interface. Not yet faced a problem that we have not been able to overcome. Hopefully supporthtml.oracle.com makes my life happier. Thank you Laurent Schneider.

SQL Tuning reading plans and temp usage

2009-12-07T23:46:00.007+02:00

What the heck is using so much temp space? Luckily we have an environment with

1. Diagnostic pack purchased

2. 11.2.0.1 version - TEMP_SPACE_ALLOCATED in v$active_session_history

3. software using dbms_application info - action and module populated

And following queries will help. First to get overview and the last queries give pointers inside execution of the queries. Now we will have something where to start fixing our temp wasters.

Trying to figure out stuff from execution plans? Here are some presentations worth reading from Tanel Poder, Christian Antognini and Kyle Hailey.


select sql_id,action,module
      ,min(sql_exec_start),max(sql_exec_start),max(sample_time)
      ,min(TEMP_SPACE_ALLOCATED) mi
      ,trunc(avg(TEMP_SPACE_ALLOCATED)) av
      ,max(TEMP_SPACE_ALLOCATED) mx 
  from v$active_session_history h
 group by sql_id,action,module
 having max(TEMP_SPACE_ALLOCATED) > 0
 order by mx desc;

select * 
  from v$active_session_history h
 order by TEMP_SPACE_ALLOCATED desc nulls last;

select sql_id,action,module
      ,o.object_type,o.object_name,h.sql_plan_operation,h.sql_plan_options
      ,min(sample_time),max(sample_time)
      ,min(TEMP_SPACE_ALLOCATED) mi
      ,trunc(avg(TEMP_SPACE_ALLOCATED)) av
      ,max(TEMP_SPACE_ALLOCATED) mx 
  from v$active_session_history h 
   left outer join dba_objects o on o.object_id = h.current_obj#
 group by sql_id,action,module,o.object_type,o.object_name
         ,SQL_PLAN_OPERATION,SQL_PLAN_OPTIONS
 having max(TEMP_SPACE_ALLOCATED) > 0
 order by mx desc;

DBA_HIST_ACTIVE_SESS_HISTORY for searching temp users from a longer period.


select sql_opname,sql_id,module,action
      ,count(distinct sql_exec_start),max(temp_space_allocated) mx 
  from DBA_HIST_ACTIVE_SESS_HISTORY
  where temp_space_allocated > 0
  group by sql_opname,sql_id,module,action
  order by mx desc
;

25 hour day

2009-12-01T12:27:00.002+02:00



alter session set TIME_ZONE = 'Europe/Helsinki';

select to_timestamp_tz('28032010','ddmmyyyy') 
     + interval '3' hour 
     + interval '30' minute as a
from dual;

28.3.2010 4:30:00.000000000 +03:00 

select to_timestamp_tz('31102010','ddmmyyyy')
     + interval '3' hour 
     + interval '30' minute as b
from dual;

31.10.2010 3:30:00.000000000 +03:00 

select to_timestamp_tz('31102010','ddmmyyyy') 
     + interval '4' hour 
     + interval '30' minute as c
from dual;

31.10.2010 3:30:00.000000000 +02:00 

select to_timestamp_tz('31102010','ddmmyyyy') 
     + interval '24' hour 
     + interval '30' minute as d
from dual;

31.10.2010 23:30:00.000000000 +02:00

Invalid procedure

2009-11-21T00:15:00.002+02:00

Wonder what is in there. Self invalidating procedure. Executable although invalid.


SQL> drop table i purge;

Table dropped.

SQL> create table i(n number);

Table created.

SQL> create or replace procedure INVALID_EXECUTABLE wrapped
  2  a000000
  3  b2
  4  abcd
  5  abcd
  6  abcd
  7  abcd
  8  abcd
  9  abcd
 10  abcd
 11  abcd
 12  abcd
 13  abcd
 14  abcd
 15  abcd
 16  abcd
 17  abcd
 18  abcd
 19  7
 20  133 148
 21  /GKO5fy/wMAULSQ3EONeuteOeyYwgzLINa5qfHQCrcHqNAWjUjjq5aQl2wunqxfypGG6DTUC
 22  yHBZPTO1oTh2rTHCnRckEhNR70+2N0Mxn4pbg7fZJwVKX9dFOCicXmazqP6JcBIEQF1QCSdZ
 23  DK5WOcSpQXvAnj5qIawt3H+JSwVpk8gG4oVLFcJ/Du2MO8CJvj7IVzAKn1TGVhx7nATQ1Xoh
 24  FtjOUd1kLeAhNpt+DGEwkLz/U3ZKLO8ZUrzcIIYGLJsBqyicCNA73FdMJNCGw7WlVkNl7Vji
 25  GmA6ougclJqWHoi4fzY81Efcubz7O35pxg==
 26  /

Procedure created.

SQL> select * from i;

no rows selected

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
VALID

SQL> exec INVALID_EXECUTABLE

PL/SQL procedure successfully completed.

SQL> select * from i;

         N
----------
         1

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
INVALID

SQL> alter procedure INVALID_EXECUTABLE compile;

Procedure altered.

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
VALID

SQL> exec INVALID_EXECUTABLE

PL/SQL procedure successfully completed.

SQL> select * from i;

         N
----------
         2

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
INVALID

SQL> exec INVALID_EXECUTABLE

PL/SQL procedure successfully completed.

SQL> select * from i;

         N
----------
         3

SQL> select status from user_objects where object_name='INVALID_EXECUTABLE';

STATUS
-------
INVALID

PLSQL_WARNINGS

2009-11-20T10:23:00.003+02:00

Struggling with pl/sql code having EXCEPTION WHEN OTHERS THEN NULL; lines.


alter session set plsql_warnings = 'ENABLE:6009';

and compile your code. You get a nice report of your buggy code. There are quite a few other warnings available since 10.1. Here is a sqlplus script to be run on your development/test environment to get a information about your possible problematic code. The compilewarnings.sql script generates a p.txt file containing the output.


SQL> compilewarnings.sql

Check constraints and AND

2009-11-08T17:10:00.003+02:00

Rob van Wijk has described nicely check constraints in inheritance conversion to a relation using single table implementation. The issue I planned to write about some day. Similar approach also to checks depending on state/status fields.

unindex 11.2

2009-11-03T22:18:00.008+02:00

Tom Kyte has updated his unindex documentation. It is good to review your work every now and then. I guessed he had a valid version of unindex somewhere. Just did not find it at the moment.

Jonathan Lewis talking about the foreign key indexing issue in OTN. It might be that not all foreign keys need an index in your schema.

11.2 new function listagg is useful also in unindex. Here is a listagg version of unindex for the new Oracle version.


select case when i.index_name is not null
            then 'OK'
            else '****'
       end ok
     , c.table_name
     , c.constraint_name
     , c.cols
     , i.index_name
from (
  select a.table_name
       , a.constraint_name
       , listagg(b.column_name, ' ' ) 
          within group (order by column_name) cols
      from user_constraints a, user_cons_columns b
     where a.constraint_name = b.constraint_name
       and a.constraint_type = 'R'
  group by a.table_name, a.constraint_name
 ) c
 left outer join
 (
  select table_name
       , index_name
       , cr
       , listagg(column_name, ' ' ) 
          within group (order by column_name) cols
    from (
        select table_name
             , index_name
             , column_position
             , column_name
             , connect_by_root(column_name) cr
          from user_ind_columns
       connect by prior column_position-1 = column_position
              and prior index_name = index_name
         )
    group by table_name, index_name, cr
) i on c.cols = i.cols and c.table_name = i.table_name
;

Even thou unindex query is not the kind of query that is run several times a day, I measured execution times from different versions. The test schema contains 1700 foreign keys. Performance comparison


"col_cnt > ALL" 13 sec
"connect by"     3 sec 
"listagg"        1 sec

Foreign keys may point also to and from another schema. Here you can find a version using ALL_CONSTRAINTS and ALL_CONS_COLUMNS views.

TOra

2009-11-02T23:19:00.003+02:00

Old news, but I just noticed, TOra project is alive. 2.0.0 version have been available for Windows almost a year already. I used the product before Quest purchased it from Underscore AB. Worth trying next to SQL Developer, Toad and/or other tools.

OUGF Autumn Seminar

2009-10-29T00:14:00.002+02:00

Today is the last day to register to OUGF Autumn Seminar 2009 at Vanha ylioppilastalo (The old student house), Helsinki 5.11.2009. Also time for me to put together a presentation about hierarchical storing structures and - queries. To be held there in finnish.

Coding Dojo

2009-10-28T23:32:00.003+02:00

So, the day came to use PL/SQL MapReduce. Sooner than I thought two days ago. The purpose to use it was not parallel execution but the example itself. Today I participated Solita Oy free time activity coding dojo. And the simplest task there was to put together SpellNumbers and count used letters. I used the brute force method to spell all required numbers. I guess others used more sophisticated approaches. Mine is for sure the only SQL implementation today. Did not get to see other implementations and missed Sauna, because I left early to play with my kids.

My implementation euler17.sql

Direct path insert and Data Guard

2009-10-27T08:49:00.004+02:00

Christian Antognini describes nicely the direct path insert hints from version 10.2 to 11.2.

Direct path insert works with Data Guard. No undo is generated for direct path insert and redo is generated. No redo is generated if nologgigin is set on the destination table. With Data Guard force logging should be set. Good explanation about this can be found in several discussions in asktom.

MapReduce

2009-10-26T19:38:00.006+02:00

There might come a day to use parallel SQL processing. A good description about Map-Reduce model using Parallel Pipelined Table Functions and parallel operations.

Not overlapping daily

2009-10-08T22:53:00.004+03:00

Attending. First day gone. Looking forward for tommorow. Tanel should you call your seminar Basic Oracle Troubleshooting Seminar as you talk about how a certain c program is executing function by function? Recommended attendance to all who troubleshoot Oracle.

Back to basics. Should it not be basics of a rdbms system to launch a trigger on an event that it is instructed to execute. With Oracle the issue is not so obvious. A certain kind of a compound trigger will not fire when called through jdbc. Metalink -soon to retire- bug no 6785707.

Another reason for my previous post. But was i thinking too complex. There is actually no need to track bitwise the used years. The problem is not O 2^n problem, but O n. As n is the number of distinct possible values in the validity interval. How about changing possible values from yearly to daily.



drop table z cascade constraints purge;

create table z(z number(16) not null
              , validfrom date not null
              , validtill date not null
              , constraint tilld check (trunc(validfrom)=validfrom)
              , constraint fromd check (trunc(validtill)=validtill)
              , constraint fro2000 
                     check (to_date('20000101','yyyymmdd') < validfrom)
              , constraint til2050 
                     check (validtill <= to_date('20500101','yyyymmdd'))
              , constraint frotil check (validfrom <= validtill)
              );

begin 
 for i in (
  select 'create unique index z'||d||' 
              on z (case when validfrom <= to_date('''||d||''',''yyyymmdd'') 
                          and to_date('''||d||''',''yyyymmdd'') < validtill 
                         then z 
                         else null 
                     end)' createindex
   from
  (select level l
        , to_char(to_date('20000101','yyyymmdd')+level-1,'yyyymmdd') d 
     from dual 
  connect by level<=to_date('20500101','yyyymmdd')-to_date('20000101','yyyymmdd')
  )
  order by l
  )
 loop
  execute immediate i.createindex;
 end loop;
end;
/

begin
*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000
ORA-06512: at line 18

Not a production ready aproach. Howcome index creation states that no more columns for a table?


select count(*) 
  from user_indexes 
 where table_name = 'Z';

  COUNT(*)
----------
       997

select count(*) 
  from user_tab_columns 
 where table_name = 'Z';

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

select count(*) 
  from user_tab_cols 
 where table_name = 'Z';

  COUNT(*)
----------
      1000

A function based index is creating a invisible virtual column to table. A table may have at least 1000 columns.

Well we have 997 first days covered. How a small inserting test is performing with those 997 indexes?



SQL> insert into z
  2  values(1,to_date('20010101','yyyymmdd'),to_date('20010102','yyyymmdd'));

1 row created.

Elapsed: 00:00:00.50
SQL>
SQL> insert into z
  2  values(1,to_date('20020101','yyyymmdd'),to_date('20020102','yyyymmdd'));

1 row created.

Elapsed: 00:00:00.40
SQL>
SQL> insert into z
  2  values(1,to_date('20020102','yyyymmdd'),to_date('20030101','yyyymmdd'));

1 row created.

Elapsed: 00:00:00.42
SQL>
SQL> insert into z
  2  values(1,to_date('20020201','yyyymmdd'),to_date('20020202','yyyymmdd'));
insert into z
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z20020201) violated


Elapsed: 00:00:01.50
SQL>
SQL> select * from z;

         Z VALIDFRO VALIDTIL
---------- -------- --------
         1 20010101 20010102
         1 20020101 20020102
         1 20020102 20030101

Elapsed: 00:00:01.01
SQL>
SQL> update z
  2     set validfrom=to_date('20010101','yyyymmdd')
  3   where z=1 and validfrom=to_date('20020101','yyyymmdd');
update z
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z20010101) violated


Elapsed: 00:00:01.15

Not overlapping

2009-09-28T23:33:00.004+03:00

Lets have a validity period in a table and a rule not to have overlapping periods. The correct solution is to have an another table having the concurrency lock and a combound trigger to handle the overlapping check. Or might there be an alternative way? A way that does not force us to rely that all code modifying the table remember to use the concurrency lock table before modifying the periods. Here it comes. A great abuse of the "bad hack" function based indexes. No need for the concurrency table or triggers.



drop table z cascade constraints purge;

create table z(z number(16) not null
             , validfrom number(4) not null
             , validtill number(4) not null
             , constraint fro2000 check (2000 < validfrom)
             , constraint til2050 check (validtill <= 2050)
             , constraint frotil check (validfrom <= validtill)
             );

create or replace function f(fro number,til number) 
return number 
deterministic
as
  n number(16);
begin
  n:=0;
  for i in fro..(til-1) loop
    n:= n + power(2,i-2001);
  end loop;
  return n;
end;
/


create unique index z1 on z (case when bitand(1,f(validfrom,validtill)) = 1 then z else null end); 

create unique index z2 on z (case when bitand(2,f(validfrom,validtill)) = 2 then z else null end); 

create unique index z3 on z (case when bitand(4,f(validfrom,validtill)) = 4 then z else null end); 

create unique index z4 on z (case when bitand(8,f(validfrom,validtill)) = 8 then z else null end); 

create unique index z5 on z (case when bitand(16,f(validfrom,validtill)) = 16 then z else null end); 

create unique index z6 on z (case when bitand(32,f(validfrom,validtill)) = 32 then z else null end); 

create unique index z7 on z (case when bitand(64,f(validfrom,validtill)) = 64 then z else null end); 

create unique index z8 on z (case when bitand(128,f(validfrom,validtill)) = 128 then z else null end); 

create unique index z9 on z (case when bitand(256,f(validfrom,validtill)) = 256 then z else null end); 

create unique index z10 on z (case when bitand(512,f(validfrom,validtill)) = 512 then z else null end); 

--You maybe got the idea. Left out indexes z11-z46 ... 

create unique index z47 on z (case when bitand(70368744177664,f(validfrom,validtill)) = 70368744177664 then z else null end); 

create unique index z48 on z (case when bitand(140737488355328,f(validfrom,validtill)) = 140737488355328 then z else null end); 

create unique index z49 on z (case when bitand(281474976710656,f(validfrom,validtill)) = 281474976710656 then z else null end); 

SQL> insert into z values(1,2001,2011);

1 row created.

SQL> insert into z values(1,2011,2011);

1 row created.

SQL> insert into z values(1,2010,2012);
insert into z values(1,2010,2012)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z10) violated

SQL> insert into z values(2,2049,2050);

1 row created.

SQL> insert into z values(2,2049,2050);
insert into z values(2,2049,2050)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z49) violated

SQL> insert into z values(2,2010,2012);

1 row created.

SQL> insert into z values(2,2001,2049);
insert into z values(2,2001,2049)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z10) violated

SQL> insert into z values(2,2014,2017);

1 row created.

Denormalize safely

2009-09-21T22:18:00.003+03:00

For some reason there is a need to denormalize values from p table to c table.
How to ensure that denormalized values are the same that original values in p?


drop table c cascade constraints purge;

drop table p cascade constraints purge;

create table p(p_id number(10) primary key
             , p_name varchar2(200) not null);

create table c(c_id number(10) primary key
             , p_id constraint c_p_fk references p
             , c_value varchar2(200) not null);

The denormalization.



alter table c add (p_name varchar2(200));

alter table p add unique (p_name,p_id);

alter table c add constraint c_p_2fk foreign key(p_name,p_id) references p(p_name,p_id);

Make the c_p_2fk deferrable if there is a need to update the denormalized values. To satisfy unindex:


create index c_p_2fk_idx on c(p_id,p_name);

Only one (is_current)

2009-09-11T00:45:00.004+03:00

Kimball writes in The Date Warehouse Toolkit about slowly changing dimension type 6. Seen such structures with marked current values in a separate attribute. Like in wikipedia. If the dimension is storing transaction time as durations then the current indicator column might as well be a virtual column in 11g, evaluated to Y if the end day is set to end_date value. No need to update the current indicator column when populating new values for a dimension.

There should be at most one current indicator = Y row for each supplier. To satisfy this requirement it is possible to create a unique constraint to a virtual column. Actually I would replace the Y value with the supplier_key if the end of days is set in the row. And put the unique key to that column as show earlier. Adding such a column to an existing model might be problematic because the ETL or mainetenance software might populate new rows before updating the old ones. Should there exist atomic multi table merge? With 11g and virtual columns this is quite easy to overcome by setting the newly created unique constraint deferrable initially deferred.

But how about 10.2 without virtual columns. Lets dig into the only one, insert first and update then problem with an example without durations.


drop table di purge;

drop materialized view di_mv;

create table di(di_id number(4) primary key
               , sour number(4) not null
               , val number(4) not null
               , is_current number(1) not null);

Each sour should have only one current. This can be acheaved by creating a bad hack function based index.


create unique index d_only_one_u on di(case when is_current = 1 then sour end);

insert into di (di_id,sour,val,is_current) values (1,1,0,0);

insert into di (di_id,sour,val,is_current) values (2,1,1,1);

insert into di (di_id,sour,val,is_current) values (3,2,2,1);

Problematic ETL prosess might want to populate new values first and update the current status at the end of transaction.


insert into di (di_id,sour,val,is_current) values (4,2,3,1);
insert into di (di_id,sour,val,is_current) values (4,2,3,1)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.D_ONLY_ONE_U) violated

Unique index is checked immediately and now row is allowed to be inserted.
Glue on commit refreshable materialized view to the model.


drop index d_only_one_u;

create materialized view log on di with rowid; 

create materialized view di_mv refresh on commit as
select sour,is_current, rowid drid
  from di
 where is_current = 1
;

alter table di_mv
add constraint sour_u unique (sour);

insert into di (di_id,sour,val,is_current) values (5,2,3,1);

commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (RAFU.SOUR_U) violated

And it behaves like an deferrable initially deferred unique constraint. Uniquenes check is done at the end of a transaction.


insert into di (di_id,sour,val,is_current) values (6,2,3,1);

update di set is_current = 0 where di_id = 3;

commit;

Ok. The downside is that di_mv is using space. May be worth using, if your data population is beeing coded by humans.

Only one (alternative 2)

2009-09-01T00:32:00.002+03:00

10g friendly approach to store primary currencies. No virtual column used. Structure seems a lot like Tom Kyte suggested. Difference is that all country currencies are here populated in the same table and the materialized view replaced with a foreign key constraint.


create table country(country varchar2(2) constraint country_pk primary key
                   , primary_currency varchar(3) not null
                    )
;

create table currency(country references country
                    , currency varchar(3)
                    , constraint currency_pk primary key (currency,country)
                    )
;

alter table country
  add constraint primary_curr_in_country_curr
  foreign key(primary_currency,country)
  references currency(currency,country)
  deferrable initially deferred
;

Populating


insert into country (country,primary_currency) values ('US','USS');

insert into currency (country,currency) values ('US','USD');

insert into currency (country,currency) values ('US','USN');

insert into currency (country,currency) values ('US','USS');

commit;

insert into country (country,primary_currency) values ('FI','EUR');

insert into currency (country,currency) values ('FI','EUR');

insert into currency (country,currency) values ('FI','FIM');

commit;

Changing the default. Easier than before. Less code


update country set primary_currency='USD' where country='US';

commit;

select * from country;
country primary_currency
US      USD
FI      EUR

select * from currency;
country currency
FI      EUR
FI      FIM
US      USD
US      USN
US      USS

Testing the model:



update country set primary_currency='USD' where country='FI';

commit;

ORA-02091: transaction rolled back
ORA-02291: integrity constraint (RAFU.PRIMARY_CURR_IN_COUNTRY_CURR) violated -
parent key not found

insert into country (country, primary_currency) values ('SW','SEK');

commit;

ORA-02091: transaction rolled back
ORA-02291: integrity constraint (RAFU.PRIMARY_CURR_IN_COUNTRY_CURR) violated -
parent key not found

There is only one default currency per country, should be obvious.

Be sure to run unindex on your production model.

Load using Java

2009-08-31T11:04:00.006+03:00

Cary Millsap is writing good things about measuring when tuning performance. His paper Making friends "Optimizing the insert program" is talking how to insert 10000 rows using Java. There are presented only possibilities to insert using Statement in a loop or using PreparedStatement in a loop. Should there be a alternative way also presented? Avoid looping statements in Java and populate all rows just in single call to the database.

Timing results inserting 10000 rows:


Array
Executed in 0 min 0 s 313 ms.
Prepared
Executed in 0 min 2 s 985 ms.

ARRAY:


create table si (s number(19),s2 number(19));
create or replace type nums is object ( n1 number(19), n2 number(19));
create type sit is table of nums;

    private static void insertArray(Connection c, List elems) 
            throws SQLException {
        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("SIT", c);
        ARRAY a = new ARRAY(ad, c, elems.toArray());
        OraclePreparedStatement ops 
           = (OraclePreparedStatement)c.prepareStatement(
                        "insert into si " +
                        " select *" +
                        "    from table(?)");
        ops.setARRAY(1, a);

        System.out.println(ops.executeUpdate());
        ops.close();
        c.commit();
    }

Prepared:


    private static void insertPrepared(Connection c, List elems) 
            throws SQLException {
        PreparedStatement ps = c.prepareStatement("insert into si values(?,?)");
        Iterator i = elems.iterator();
        while ( i.hasNext()) 
        {
            Object[] o = i.next();
            ps.setInt(1, (Integer)o[0]);
            ps.setInt(2, (Integer)o[1]);
            ps.executeUpdate();
        }
        ps.close();
        
        c.commit();
    }

Here are some timings for other number of rows. It shows that if you are inserting 100-1000 rows this approach might be worth considering. Measure yourself. Be sure to have enough memory available for your Java.


ms      localhost      remote db 
rows    prepared array prepared array
10      94       140   93       141
100     125      141   125      156
1000    313      203   844      265
10000   1500     344   16297    453
100000  12063    1422  206210   2078
1000000 182063   java.lang.OutOfMemoryError: Java heap space

insertintoselect.java

Only one

2009-08-27T13:08:00.004+03:00

Last September Tom Kyte wrote a yet another good article in Oracle magazine The Trouble with Triggers. The point about triggers is very good, but the example and the "Correct Answer" I do not like. He is suggesting to model currencies to two separate tables. In my opinion the data model is entirely wrong if the same thing is modeled in many places. "Less code equals fewer bugs. Look for ways to write less code." Here is my approach to the problem without a materialized view and materialized view logs.

The problem itself is that
-a country must have a default currency
-there is only one default currency per country


create table country(country varchar2(2) constraint country_pk primary key);

create table currency(country references country
                    , currency varchar(3)
                    , is_primary varchar2(1) not null 
                                             check (is_primary in ('Y','N'))
                    , primary_country 
                            as (case when is_primary = 'Y' then country end) 
                               virtual 
                        constraint only_one_primary_u unique 
                    , constraint currency_pk primary key (currency,country)
                    )
;

alter table country
  add constraint must_have_at_least_one_primary
  foreign key(country)
  references currency(primary_country)
  deferrable initially deferred;

And that is it. One may see that there is used the "bad hack" behind only_one_primary_u. The unique constraint generates a function-based normal index.
The good thing about this is that constraints are visible in constraint list, not implemented only in a index or a materialized view.


select * 
  from user_constraints 
 where constraint_name 
    in ('ONLY_ONE_PRIMARY_U','MUST_HAVE_AT_LEAST_ONE_PRIMARY');

Populating


insert into country (country) values ('US');

insert into currency (country,currency,is_primary) values ('US','USD','N');

insert into currency (country,currency,is_primary) values ('US','USN','N');

insert into currency (country,currency,is_primary) values ('US','USS','Y');

commit;

insert into country (country) values ('FI');

insert into currency (country,currency,is_primary) values ('FI','EUR','Y');

insert into currency (country,currency,is_primary) values ('FI','FIM','N');

commit;

Changing the default


update currency set is_primary = 'N' where country = 'US' and currency = 'USS';

update currency set is_primary = 'Y' where country = 'US' and currency = 'USD';

commit;

select * from country;

COUNTRY
US
FI

select * from currency;

COUNTRY	CURRENCY	IS_PRIMARY	PRIMARY_COUNTRY
US	USD		Y		US
US	USN		N	
US	USS		N	
FI	EUR		Y		FI
FI	FIM		N

A country must have a default currency


insert into country (country) values ('NO');

commit;

ORA-02091: transaction rolled back
ORA-02291: integrity constraint (RAFU.MUST_HAVE_AT_LEAST_ONE_PRIMARY) violated - parent key not found

insert into country (country) values ('SW');

insert into currency (country,currency,is_primary) values ('SW','EUR','N');

commit;

ORA-02091: transaction rolled back
ORA-02291: integrity constraint (RAFU.MUST_HAVE_AT_LEAST_ONE_PRIMARY) violated - parent key not found

There is only one default currency per country


insert into currency (country,currency,is_primary) values ('FI','USD','Y');

ORA-00001: unique constraint (RAFU.ONLY_ONE_PRIMARY_U) violated

Multi table insert atomic?

2009-08-19T15:30:00.003+03:00

Reading SQL and Relational Theory: How to Write Accurate SQL Code. Talking about constraints and data modifications. There should be able to update several tables in one atomic operation. Oracle multi table insert could be something to that direction, but no atomicy. Bug still open from version not any more supported.


SQL> create table a (a_id number(1) primary key
  2                , b_id number(1) not null);

Table created.

SQL> create table b (b_id number(1) primary key
  2                , a_id not null constraint b_a_fk references a );

Table created.

SQL> alter table a add
  2    constraint a_b_fk foreign key (b_id) references b
  3      deferrable initially deferred;

Table altered.

SQL> insert all into b values (x,x)
  2  into a values (x,x)
  3  select 0 x
  4    from dual;
insert all into b values (x,x)
*
ERROR at line 1:
ORA-02291: integrity constraint (RAFU.B_A_FK) violated - parent key not found


SQL> insert all into a values (x,x)
  2  into b values (x,x)
  3  select 1 x
  4    from dual;

2 rows created.

SQL> insert into a values (2,2);

1 row created.

SQL> insert into b values (2,2);

1 row created.

SQL> select * from a;

      A_ID       B_ID
---------- ----------
         1          1
         2          2

SQL> rollback;

Rollback complete.