We count the number of rows in a table – and it’s four.
We add a constraint to restrict the values for a certain column – and every column survives the check.
We use the corresponding predicate to count the number of rows that match the check constraint – and we’ve lost a row !

Why ?

A predicate returns a row if it evaluates to TRUE.
A constraint allows a row if it does not evaluate to FALSE - which means it is allowed to evaluate to TRUE or to NULL.

I have one row in the table where v1 is null, and for that row the check constraint evaluates to NULL, which is not FALSE. So the row passes the check constraint, but doesn’t get returned by the predicate. I think it’s worth mentioning this difference because from time to time I see production systems that “lose” data because of this oversight.

To make the constraint consistent with the predicate I would probably add a NOT NULL declaration to the column or rewrite the constraint as (v1 is not null and v1 in (‘a’,'b’,'c’)).

Lindsay passed me a link to an article about the recent Oracle Firewall public release and also the recent partnering with F5 . The part that interests me most is the Oracle firewall and the fact that Oracle has stated....[Read More]

Just a super-quick post to highlight the availability of Graham Wood's Hidden Features presentation that I enjoyed at UKOUG 2010. I felt it was one that lots of people could pick up one or two tips or reminders from, just from the slides, but couldn't track it down. Graham's been kind enough to let me host the RMOUG version.

Hopefully more stats posts to come now I'm free of the stats paper and (hopefully) a few support nightmares.

I recently got involved with a performance investigation for an Oracle 9.2 database. The process of investigation threw up some interesting information for me regarding the accuracy of statistics collection in Oracle. It also highlights how different defaults in different versions of Oracle can lead to remarkably different statistics and hence execution plans. Finally, it [...]

If you are near Vienna or Tallinn - you have an opportunity to attend an excellent two day seminar hosted by Oracle University - but delivered by Richard Foote. If you really want to know all about indexing in Oracle - this seminar will deliver.

I know Richard really knows his stuff - and I've seen him present many times before. This will be a seriously good event. The linked to page has linked to the registration pages.

I’m not really a fan of social network sites. I use them from time to time, but if they dropped of the face of the earth tomorrow it wouldn’t make much difference to my life. I have email addresses and phone number of everyone I want to talk to. Nuff said.

With that in mind I really didn’t give a crap about watching The Social Network when it was at the cinema. I was visiting friends last night and they both wanted to watch it, so I was forced into a corner. As it turned out it was a good film. If it had been about some company other than Facebook I would have enjoyed it even more. The film portrayal of the characters is as follows:

Mark Zuckerberg: A socially retarded, completely untrustworthy and annoying uber-geek thief. If he’s like this in real life I would definitely not want to meet him and putting a single penny in his pocket is bottom of my list of desires.

Sean Parker: A serial screw-up, style over substance, paranoid and incredibly annoying wannabe uber-geek. Once again, if he is like this in real life he’s not on my list of people I would like to meet.

Eduardo Saverin: An honest, if a little naive, guy who was surrounded by prize-winning sh*ts who were all out to screw him.

The fact the film is based on a book whose content is mostly drawn from interviews with Eduardo Saverin prior to the completion of his court case against Mark Zuckerberg suggests that maybe the view of the characters is “a little bit biased”.

Anyway, get it out on DVD and try to forget it is about Facebook and I think you will like it.

Just received an email from Oracle University stating that numbers are very tight for my Index Internals and Best Practices Seminars scheduled next month for Vienna and Tallinn. Unfortunately, due to the travel time, it will be a case of them both running or neither of them going ahead. With cancelled events in the past, it’s [...]