Pages

Wednesday, March 31, 2010

Matt Penny posted about extracting a crib-sheet for the DBA_ views from the online documentation.

I can't find a comment link on his article, but he could have got most of that information from the database:

select * from all_tab_comments
where comments is not null
and table_name like 'DBA_%';

Yes, you can but comments on views as well as tables and columns. In fact in 11gR2 you can comment on editions, operators (but not procedures and functions) materialized views, index types and 'mining models' (though I have no idea what the latter is).

Wednesday, March 24, 2010

Consider a table of people, with separate indexes on First_Name and Last_Name.

I issue : DELETE FROM people WHERE Last_Name = 'Holmes';

It will use the index on Last_Name to find the rows to delete. It finds "Mr Holmes" and deletes the row, then deletes the index entry from the Last_Name index. It also has to delete the entry from the First_Name index though, and first it needs to find that entry in the segment. It has to get the First_Name value from the table for that row, then use that to probe the index to find the entry for that value (and ROWID) so it can delete it.

Rather than using the index to find a row in a table, it actually uses the table to find the row in the index.

Saturday, March 20, 2010

http://www.penno.com.au/I've been in Australia over ten years now, and there are still some things that just seem wrong.

Take football. I mean the round-ball game, which some people call soccer. A couple of weeks ago, Sydney lost the major semi-final to Melbourne, while Wellington won over Newcastle. It's a bit odd that Wellington, which is in New Zealand, is in the Australian national competition, the A-League. It is even stranger because New Zealand isn't even in the same continent as Australia, at least as far as FIFA is concerned. Australia is part of Asia, and New Zealand is in Oceania. So even if New Zealand were to win the A-League, they wouldn't get the Asian Champions League place that would normallly go to the winner.

Then we have this concept of major and minor semi-finals. Back in England, it all seemed pretty simple. You'd have eight teams in the quarter-finals, four in the semis and two in the final. Here we had six out of the ten teams in the competition go into the finals series. The top two, Sydney and Melbourne, played their major semi-final over two legs. Then third played sixth and fourth played fifth in an elimination final. The winner of those two matches, Wellington and Newcastle, then played the minor semi-final.

Given that Sydney lost their semi-final, an Englishman would generally assume it is all over for them. Not so, as they went on to play Wellington in something called the preliminary final. Sydney won that, and this weekend they go to play Melbourne in the Grand Final. By chance, the last round of the pre-finals competition also had Sydney playing Melbourne which actually decided what is called the 'minor premiership', (ie who actually finished top of the table), won by Sydney. So Sydney played Melbourne on Feb 14th (and won), 18th (lost), March 7th (drawn) and now March 20th..

Sydney secured their Asian Champions League spot by becoming minor premiers. With Sydney already having their spot, and Wellington ineligible, Melbourne were guaranteed the second spot as soon as Newcastle lost.

If you think that is confusing, the pre-finals competition isn't much simpler. With only ten sides in the competition, each played the other three times rather than having one home and one away leg. I believe Perth were complaining that, for the second year running, they had to put up with more away games than home.

That is still simpler than the AFL though. A few years back I was working for a firm that sponsored one of the Melbourne based sides. That side played half its home games in Tasmania (a different state of Australia), and didn't actually come to Sydney at all since that year didn't include an away leg against the Sydney side. The NRL Rugby League competition is pretty similar with the main competition being too short for all teams to play the all others both home and away.

As the top-flight soccer competition draws to an end, those players hoping for World Cup berths need to find somewhere to play for a few weeks so they don't get too rusty. Portsmouth perhaps ? Meanwhile, for my two kids, the soccer season is yet to start, but they've both started training for their appearances in the Green and Gold of Penno.

Wednesday, March 10, 2010

Tom Kyte is on record as wanting the abolition of "WHEN OTHERS", "Autonomous Transactions" and "Triggers". I think he's also mention COMMIT in procedures too.

For today's rant, I'm going up against the humble concatenation operator. Yup the double pipe ( || ), or concat if you want to be 'portable'.

Not entirely, of course. I'm not a zealot.I recognize that sometimes you'll need to join a State to a Postcode (or Zip code) when outputting an address.

But they should only be in the top-most SELECT. Not in a subselect, or a predicate. And definitely not if you are inserting the combined value into another column. The first rule about normalisation is you don't talk about normalisation. No, sorry that's Fight Club. But First Normal Form does include Atomicity, which means you shouldn't be gluing fields together. This is a data model, not an Airfix model.

If you do "column_a||column_b", you'll get confused about whether 'abc' was 'ab'||'c' or 'a'||'bc' and be lost.

Even if you are smart and stick some delimiter in there (column_a||'-'||column_b), you've lost your columns. Can't use them for Referential Integrity. Can't gather stats on them. The optimizer is going to get lost. If the original columns were numbers or dates, you've added datatype conversion into the mix. If both the original values were null, you've got nothing but a delimiter in the new field. Yuck.

But mostly, at some time in the future some poor blighter is going to have to come along and break them apart with obscure regular expressions. It may be me, and I won't be happy. And then you'll find out what I can do with a double pipe !

Saturday, March 06, 2010

Thanks to David Edwards for giving me the opportunity to host edit a Log Buffer.

I'll confess to a bias first off, as my experience is in Oracle (and as a developer), so my apologies if I misread the significance of activity in other areas. Comments are open, so feel free to correct and add.

As solid-state storage pours on the speed, new bottlenecks turn up for Vadim Tkachenko from the MySQLPerformance blog, who points the finger at some InnoDB mutex's. From the same blog, the key-cache hit is autopsied by Baron Schwartz with a followup from Pythian's Sheeri .Similar discussions have absorbed many Oracle DBAs for several years.

With more immediately useful items, Thinkdiff.net's Mahmud demonstrates how incrementing variables within a mySQL select statement can be used to replace complex ranking function and Ronald Bradford is teaching Oracle DBAs about MySQL and shows us that READ COMMITTED doesn't mean the same in both worlds.