Monday, April 30

How to do an "insert ignore" in Oracle

Ok, I should start off with a disclaimer. This is not a good idea to do.
Using the "insert ignore" statement is a way to let MySQL insert data from a dataset which may contain duplicate constraints to existing data, and simply skip the duplicate row.
Sounds like a great way to screw up data doesn't it? Should not be used unless you really know what is going on.

Ok, in MySQL we can do this.
mysql> insert ignore into a select * from b;
Query OK, 1 row affected (0.00 sec)
Records: 2 Duplicates: 1 Warnings: 0
But can this be done in Oracle without to much fuss? After a discussion on IRC a guy asked why can't we simply use the merge statement?
Well we can and it is probably a quite good suggestion if you *really* want to ditch those dupe rows.
-- So what do we got?
SQL> select * from a;

---------- ----------
1 2
2 3

SQL> select * from b;

---------- ----------
1 3
3 4

SQL> merge into a using b on (a.a=b.a)
when not matched then insert values (b.a,b.b);

1 row merged.

SQL> select * from a;

---------- ----------
1 2
2 3
3 4


-- Let's rollback that and have a look at the exeuction plan.

SQL> rollback;

Rollback complete.

SQL> set autotrace on
SQL> merge into a using b on (a.a=b.a)
when not matched then insert values (b.a,b.b);

1 row merged.

Execution Plan
Plan hash value: 1973318225

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | MERGE STATEMENT | | 2 | 64 | 7 (15)| 00:00:01 |
| 1 | MERGE | A | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 2 | 64 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| B | 2 | 52 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| A | 2 | 12 | 3 (0)| 00:00:01 |

Predicate Information (identified by operation id):

3 - access("A"."A"(+)="B"."A")

- dynamic sampling used for this statement

0 recursive calls
5 db block gets
15 consistent gets
0 physical reads
0 redo size
819 bytes sent via SQL*Net to client
778 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

Looks ok.

Sunday, April 15

Oracle under CentOS 5

I'm a bit late on this one, but ...
CentOS version 5 was released the other day. For those of you unfamiliar with CentOS it is RedHat Enterprise Linux recompiled and supported by the open source community.
Pretty much RHEL for free.
I can't say there is anything *really* exciting in the new version of RedHat, sure there is Xen support and the general updated versions of the applications included.
New version of Open Office, PostgreSQL etc.

One big difference is that everything has to be NTPL threaded. NPTL threading is the first real *good* threading implementation in Linux. The old Linux threads model is way old. Although NPTL has been available (and the default) in RHEL since version 3, it is not the only supported threading model.

-So what does this mean for Oracle?
No more LD_ASSUME_KERNEL hacks.
10g is fine with this, no problem at all. However 9i will have some issues with this, the old way to get Oracle running was simply to set LD_ASSUME_KERNEL to 2.4.19 and Linux would use the Linux threads model. No go in version 5.
Big question, will Oracle support 9i under RHEL5? I'm honestly not to bothered, by the time RHEL5 is mature and tested I really hope that people have switched to 10gR2.
9iR2 has been around for a lng time now and will be for some time. But new system design implementation on 9iR2?

I've done a quick test install of Centos 5 and 10gR2 ( and patchset and the install was very painless, pretty similar to Centos 4 with a few changes in the RPM's needed.
RedHat did add a few SHM parameters to sysctl.conf so that section needs reviewing though.

Wednesday, April 11

New dual socket AMD system from Dell

Dell just introduced a new two socket AMD Opteron system, the PowerEdge 2970, a system designed to compete with Sun x4200 and the HP DL385 G2.
Fairly similar to the Intel-based PowerEdge 2950 but with AMD procs. Same redundancy with dual psu's, two onboard nic's and the onboard DRAC5 remote management adapter.
Comes default with the 8 slot 2.5" SAS backplane which is nice to see. I'm all in favour of 2.5" drives over 3.5" in small servers. Up to 32Gb RAM, with expensive 4GB DIMMS that is, 32Gb comes in at about $35000 USD :)
Perfect little J2EE application server running Linux.

It's interesting to see the latest trends in that area. A number of our clients, major investment banks, are currently in the middle of Linux migration projects.
One thing I think slowed Linux adaptation in past has been the "32-bit barrier" and the limit it puts on memory allocation. A J2EE app with a maximum heap size of about 1.5Gb isn't very popular. 8 or even 16Gb is not unusual to see on the Solaris/SPARC side.
Now with AMD Opteron processors (and Intel EM64T) and a mature 64-bit Linux environment that problem has gone away. Websphere 6.0.2 runs perfectly on my test machine with 6Gb heap, performance is great.
Actually all but one of our "Linux clients" are moving to AMD machines over Intel Xeon's, HP and Sun AMD based hardware only so far.
One good example is a project that is currently being migrated from a six processor Sun v890 to a HP DL585, they even got a small performance increase.
Sad to see the SPARC's go but fun to see new platforms coming. Linux one one side and Solaris 10 on the other side.
But the SPARC's will be back, the new "rock" SPARC processor just hit silicone and with it's massive coherent memory access space of 256 Tb, the expected number of cores and performance. It will kick some serious ass.