Niall's Oracle Pages

I'm off to sunny Florida tomorrow, followed by freezing NJ a week later, so I thought now would be a good time to look back at 2005. There are probably as many ways to do this as there are sunday newspaper supplements, but I've chosen to pick my choice of the top posts from various Oracle blogs (plus a couple of associated sites).

I'll start, as my teachers taught me, at the beginning.

Lisa Dobson who runs the newbie dba blog for, well people at the start of their dba career, posted a couple of articles (I've picked the first) on What do I need to know?

Now the most common response to this you'll receive in an online forum is the rather rude RTFM. Tom Kyte had a view on this response back in July as often with Tom's blog the discussion it generated was nearly as good as the original post.

Also with beginnings and comments, we see Mogens Norgaard starting out on the blog thing and immediately using comments to post new content:)

One of the nice things about blogs is that they allow the personality of the author through, sometimes though personality can become ego. This trend was nicely pointed up by Doug Burns here.

The other trend that I see occurring is blog authors using their blogs to explain something they know well to a wider audience. I hope this continues. So my next choice is not a post, but a series of posts by David Aldridge in which he talks about optimisation and new uses for materialized views. The series starts here.

On the other hand this gem spotted by both Oracle-wtf and Fabian Pascal doesn't explain anything at all much. Least of all joins.

I have just taken delivery of Jonathan Lewis' new book - a review will have to wait until the new year. One thing however that immediately stands out is the meticulous attention to detail. It is this same attention to detail, coupled with clear and direct explanation that characterised Jonathan's series of articles named after Lewis Carroll's mysterious beast the Snark.

Finally and by way of Pete Finnigan and possibly a prelude to one of the Hot Topics for Oracle in 2006 is my namesake David Litchfield's critique of Oracle Corporation's approach to security.

I have added a couple of items to the Resources list on the right hand side of the site. The first is my Guide to Generic Connectivity which I inexplicably failed to link to when I originally posted it. The second is the second presentation that I gave at the recent UKOUG conference on retro-fitting Oracle 10g style end-to-end tracing (down towards the bottom of the page) to Oracle 9i and below systems. The presentation is called Snatches of Eternity and can be found under the presentations links.

Thanks to Doug Burns who points out this article that claims to show by deductive logic that SQL Server is a superior platform to Oracle.

Now I don't think there are many who will rush to agree with the article, and as Doug says it is briefly amusing. Unfortunately I find it rather sad in the end. The reason I find it sad is that in fact the author does not employ deductive reasoning at all. His argument goes.

Proposition 1: SQLServer is cheaper than Oracle.
Proposition 2: SQLServer is easier to manage than Oracle.
Fact 3: Documented minimum hardware for SQLServer is lower spec than that for Oracle
Proposition 4: Systems that install on lower spec hardware perform as well on lower spec hardware as systems that install on higher spec hardware all the way up the scale.
Proposition 5: Both systems have all features required for modern business systems.

That is it, there is in fact no deduction made. Presumably in fact the missing premise that he has in mind is summed up as "Cheaper, easier to manage systems that meet a minimum feature list and will install on old, cheap hardware are superior to those that are more expensive, have more features than the minimum feature list and will install on old but slightly more expensive hardware". Since we can deduce that SQL Server would win on this criteria if the propositions are all true. As well as the somewhat dubious unspoken assumption listed above the planks of even this small argument are somewhat rotten.

Proposition 1: SQL Server is cheaper than Oracle.
This is entirely dependent upon edition and the negotiating skills of your organisation.
Proposition 2: SQL Server is easier to manage than Oracle.
These guys might differ
Proposition 4: If something will install on low spec hardware it will always outperform software that requires a higher base install when running on the same hardware. Does anyone truly believe this?
Proposition 5: Both systems have all features required for modern business systems. Given that the article was written in the days of SQL 2000 presumably the development of SQL 2005 has been a waste of time since SQL 2000 already met all the requirements of modern business systems. The reality of course is that an extended feature set allows smart developers to implement functionality more efficiently and possibly more quickly and reliably.

In addition of course SQL 2005 comes with some price increases and much higher minimum install requirements and greater functionality - perhaps it too is inferior to SQL2000 on this 'logical' basis.

In common with other sources, I may have accidentally given the impression that Rafa Benitez clearly didn't have a clue what he was doing and that winning the Champions League last year was an unfortunate fluke that would permanently harm Liverpools progression; following Peter Crouch's match winning performance on Saturday and the second 0-0 win over Chelski in as many years I can only conclude that everything that Rafa touches turns to Red (surely Gold - ed) and that any criticism is hideously half-baked and ill-thought out.

GnomeBlog

We were having the NULL conversation at work today. During the course of which I asserted that you could build a unique index on a nullable column, but that the nulls would not be indexed. My colleagues asserted that you couldn't. So some testing was done.

SQL> drop table t1;

Table dropped.

SQL> create table t1(c1 number not null, c2 number);

Table created.

SQL> insert into t1(c1,c2) values(1,null);

1 row created.

SQL> insert into t1(c1,c2) values(2,null);

1 row created.

SQL> commit;

Commit complete.

SQL> create unique index i1 on t1(c2);

Index created.

SQL> insert into t1(c1,c2) values(2,null);

1 row created.

SQL> rollback;

Rollback complete.

SQL> drop index i1;

Index dropped.

SQL> create unique index i1 on t1(c1,c2);

Index created.

SQL> insert into t1(c1,c2) values(2,null);
insert into t1(c1,c2) values(2,null)
*
ERROR at line 1:
ORA-00001: unique constraint (USERNAME.I1) violated


SQL>


(I have only edited the username). So, technically, my assertion that you could create a unique index on a nullable column but that the nulls are ignored appears to be correct in the single column case, but not in the multi-column case. I decided to have a look at the actual data stored in the indexes.


SQL> insert into t1(c1,c2) values(2,1);

1 row created.

SQL> commit;

Commit complete.

SQL>

SQL> CREATE INDEX I2 ON T1(C2);

Index created.

SQL> INSERT INTO T1 VALUES(3,NULL);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT HEADER_FILE,HEADER_BLOCK,BLOCKS
2 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='I1' AND OWNER=USER;

HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
1 39593 8

1 row selected.

SQL> ALTER SYSTEM DUMP DATAFILE 1 BLOCK 39594;

System altered.

SQL> SELECT HEADER_FILE,HEADER_BLOCK,BLOCKS
2 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='I2' AND OWNER=USER;

HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
1 39601 8

1 row selected.

SQL> ALTER SYSTEM DUMP DATAFILE 1 BLOCK 39602;

System altered.

SQL>



This gave the following for the multi-column index.

Leaf block dump
===============
header address 142746204=0x882225c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7986=0x1f32
kdxcoavs 7942
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8036
row#0[8024] flag: ------, lock: 0, len=12, data:(6): 00 40 76 fa 00 00
col 0; len 2; (2): c1 02
col 1; NULL
row#1[7998] flag: ------, lock: 0, len=14, data:(6): 00 40 76 fa 00 02
col 0; len 2; (2): c1 03
col 1; len 2; (2): c1 02
row#2[8012] flag: ------, lock: 0, len=12, data:(6): 00 40 76 fa 00 01
col 0; len 2; (2): c1 03
col 1; NULL
row#3[7986] flag: ------, lock: 2, len=12, data:(6): 00 40 76 fa 00 03
col 0; len 2; (2): c1 04
col 1; NULL
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 1 minblk 39594 maxblk 39594


For the single column index I get this.


Leaf block dump
===============
header address 146500188=0x8bb6a5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8024=0x1f58
kdxcoavs 7986
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 40 76 fa 00 02
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 1 minblk 39602 maxblk 39602


So it appears that the answer to whether nulls are indexed or not depends to some extent on the number of columns in the index, (note that *all* null columns still aren't stored in the multiple column index) and the behaviour of multi-column unique indexes may not be quite what you expect.