System views in all_tab_comments

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).

Turning things on their head

Deletes are odd.

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.

Told you they were odd.

Added:

I figured it is worth adding a demonstration:

create table persons 
  (first_name varchar2(30), last_name varchar2(30));

insert into persons values ('Sherlock','Holmes');

create index pers_ln_ix on persons (last_name);

create or replace function dummy_func (i_val in varchar2) 
return varchar2 deterministic is
begin
  dbms_output.put_line('exec dummy_func');
  return i_val;
end;
/

create index pers_fn_ix on persons (dummy_func(first_name));

delete from persons
where last_name = 'Holmes';

If you run that, you'll see that the delete fires the dummy_func function, as it needs to obtain the return value so it can find the entry in the index.

Go Sydney FC

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.

Developers Against Inappropriate Concatenation

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 !

Log Buffer #181: a Carnival of the Vanities for DBAs

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.

I'm going to start with a few items on backup and recovery.

Bill Graziano at Sqlteamwebblogs learns and shares lessons from a SAN failure. Always useful to hear about real-life recoveries where things aren't as neat as a simulation or rehearsal. Oli Sennhauser on Shinguz asks whether you are trusting your backup and recommends practicing restores.
Buck Woody reminds us about the artifacts outside the database which we rely on , when he tells us not forget to backup your Master Keys.
Ronald Bradford shows how he ensures he doesn't lose information from his MySQL error log, while from his SQL Server blog Jonathan Kehayias is automating setting up his logs .


Looking to the future now and mySQL and noSQL were in the news as Twitter looks to a new architecture.
At HighScalability, Ted Hoff looks toward the end of the dominance of mySQL and memcache while Mark Callahan at mysqlha describes the pluses and minuses of mySQL, SQL and noSQL.

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.

On a broader, and longer term note, OptimalDBA Daniel Fink looks at whether software is (still) a good career path


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.

In the world of Postgres, Hubert Lubaczewski at depesz proposes using Text over VARCHAR(n) but anticipates less than whole-hearted acceptance. He gets support from David Fetter with VARCHAR(n) considered harmful but not from Leo Hsu and Regina Obe who write in its defense.

At LessThanDot, Ted Krueger is discussing mirroring SQL Server databases across the continent.

Still on SQL Server (but applicable to Oracle too) Brent Ozar thinks we should understand more about the Optimizer and stop hitting it with a big stick. Tuning is also on the cards for Charles Hooper as he details the intricacies of DBMS_XPLAN options.

Guy Harrison has been looking at memory management on VMWare, which will interest those turning towards virtualisation.

Finally, remember sharing is good, so I'll finish with Thomas Roach at OracleRant doing an un-rant like sharing of Grid Control scripts.