My old arch-nemesis, the in-line if ($q = $q == $a ? $b : $c; ) reared its ugly little head again. This time, it was in context of an web page that displayed some form values, something like this:CGI::textfield({name => 'price_dollars', width => '5', value => ($mode eq 'Edit' ? $line_item[$count]->price_dollars() : '0') });CGI::textfield({name => 'price_cents', value => ($mode eq 'Edit' ? $line_item[$count]->price_cents(): '00' });This carried on for 15 other fields on the form. So, we have 17 if else statements all checking to see if the form is in ‘Edit’ mode. If there were, say, ten line items on the form . . .  well no need to go any further, other than to say that is a whole lot of if statements.While this does not take up much space, this multiplicity of ifs is not really necessary or even good, since to the compiler, an inline if and a bracketed one are the same. The inline is only a shorthand to make our code more readable.We could of course just declare 17 scalars at each iteration, and then a use single if statment to set these scalars, like this . . .  more »

The dbms_metadata package has a few convenient functions and procedures that allow you to generate the SQL to recreate parts of your database. The best-known function is probably dbms_metadata.get_ddl(), but every example of its use that I’ve seen on the Internet seems to use a “select from dual”, e.g. more »

If you are experimenting with gathering system statistics it might be helpful to archive your current settings and any intermediate settings you come up with along the way. All the system stats info is held in the sys.aux_stats$ table. Since the format is a little wacky, I came up with the following table to hold stats and the following insert statement to populate it after every gathering of system stats. more »

In 2009 I have presented "Alles Over Groeperen" twice in the Netherlands and the English version "All About Grouping" also twice, once at the Oracle OpenWorld Unconference and once at UKOUG. If you haven't been able to visit one of these presentations, and you are interested in the subject, then you can catch up by reading the paper that I just finished. more »

Just a quick follow-up to my previous post on investigating bad plans using AWR & ASH.In that article, I mentionedthat it was a shame that the ACCESS_PREDICATES and FILTER_PREDICATES columns were not populated (how this cannot be classified as a bug is beyond comprehension) andthat I was using DBA_HIST_SQLBIND to investigate various peeked binds for that particular SQL based on the LAST_CAPTURED columnHowever, I should also have mentioned that, from 10gR2 onwards, populated in both V$SQL_PLAN and DBA_HIST_SQL_PLAN, there is a very useful column called OTHER_XML. more »

Before the year ends I’d like to share some good stuff… I have never seen a huge compilation of SQL tuning tips or rewrite scenarios (with test cases) and got them only on OracleFans forum… ooops… so you can’t read Chinese? try this translated version, whew.. good thing Google has this translate service that I am able to read in Chinese.. more »

How many hard parses would you see for the session, and how many child cursors for the SQL statement will be in the library cache more »

Since many of my customers are fairly hot on security I’m often banned from plugging USB drives into local PCs, or getting my laptop onto the local network, or downloading from the Internet, so more »

Some time ago I wrote a post about how COUNT(*) and COUNT(column) are semantically different things (link). Such queries may return different results if the column counted has NULLs in it. And the difference comes from that NULL is not a value, it’s rather a state which says “value unknown” or “no value entered”.So, you better understand how NULLs interact with your SQL constructs if you call yourself a DBA or a database developer ;-) more »

2009 is drawing to a close so I just took a gander at the year-end search term statistics for my blog. It?s interesting to see the search terms that send readers to this blog. Some are surprising, others not so surprising. So, for trivial pursuit sake, the following list shows the top 50 search terms more »

I posted a little holiday quiz – timed to appear just before midnight (GMT) on 24th December – that asked about the number of rows sorted and the memory used for queries like:select sortcodefrom more »

I have a table with one million rows, there are no indexes on the table. The table has a column called sortcode which has no nulls, and has been generated in a highly random way so that no value appears more than four times. Consider the following queries: more »

In a recent ‘philosophy’ post I focused on the critical mental image that should be adopted when comparing B-tree and bitmap indexes. I was a little surprised, however, to discover that the idea I proposed needed further explanation. So here’s a note that expands on the original comment.The point that caused confusion was simple: more »

I’ve just added Charles Hooper?and Joze Senegacnik?to my list of useful blogs – so this is a temporary note to draw attention to the change; and if you hadn’t noticed it, Kyle Hailey is a fairly recent addition. more »

Each time you upgrade the Oracle server?(even with a patch release), you may find that some strange things happen to a few execution paths. Every release carries some changes to the optimizer code – sometimes enhancements, sometimes bug fixes more »

Right now I’m desperately struggling to catch up on my email, blogging and posting from the last two weeks – so I’ve just got time to let you know that I’ve just had confirmation from ODTUG that they’ve accepted all three of my abstracts for Kaleidoscope 2010. See Public Appearances for a list of the more »

From time to time I get asked if it’s possible to index a partitioned table so that recent partitions have different (local) indexes from older partitions. The answer is “not really, but there’s a couple of dirty tricks which aren’t very nice and aren’t very stable“. more »

Here’s an interesting post and test case from Gregory Guillou (WeDoStreams blog). It features an SQL statement that is re-optimised the second time you run it.Since it’s running on 11.2.0.1 your first thought is likely to be more »