Oracle Sponge -- Now Moved To Wordpress

Moving to WordPress

2006-06-12T15:31:00.000-06:00

Blogger irritates me. Let's see if Wordpress makes me happy.

http://oraclesponge.wordpress.com/

I've used the supplied import functionality to move over posts and comments, and it seems to have gone pretty well. Internal links need to be resolved to the new UR, and some text formatting has gone astray, but I can work my way through that.

The category functionality is great, and the interface is much more snappy, so I'll probably stick with it.

Modelling of Condominiums

2006-06-02T13:45:00.000-06:00

A condominium in this case being a geographical area of which two of more sovereignties agree to share dominium, but that title ought to do wonders for my statistics.

Anyway, I was thinking about the mapping between cities and zip codes today, which is a many-to-many relationship, and the thought popped into my head, "I wonder if there are any cities which belong to multiple countries?" In other words, are there any cities which are part of a condominium?

So I went looking for one, not expecting to find any, and I was right -- there appear to be none. Probably a good job too.

In fact the only decent example of a condominium currently in effect seems to be that of Pheasant Island, which is a condominium of France and Spain. It apparantly is shared on a six-monthly basis between the two nations, which leads to an interesting modelling problem -- a cyclic time-dependent relationship between geography and sovereignty. In other words, it's a time-share! (Stats again).

Anyway, I'll leave that as "an exercise for the reader".

It also raises anther question: back when one needed a passport to cross between the two countries, was a crime commited if a passport-less person was present on the island at the time that sovereignty changed? Perhaps none -- perhaps the condominium agreement allowed people of either nationality to be present on the island at any time, regardless of which state currently was in charge of it. But if the agreement did not allow this then could you charge a person with crossing a border if they were stationary at the time the offence occured?

These thoughts usually turn-up on Fridays, oddly enough. Must be something to do with blood sugar.

The Prime Number Few

2006-05-22T15:08:00.000-06:00

I was chasing links through Wikipedia the other day, which always leads somewhere interesting.

In this case I fell into the deep waters of linguistics and learned about garden path sentences. They caught my eye in particular because the article includes one of my favourite jokes, albeit with a slightly different construction to my usual one.

"If time flies like an arrow, do fruit flies like a banana?"

Now that I've read a deconstruction of the joke, it seems a little less funny though.

The Three Pillars of Oracle Data Warehousing

2006-05-18T07:00:00.000-06:00

Introduction

This is a basic topic for Oracle data warehousing beginners, based on some ideas that I'm hoping will stop buzzing round in my head if I commit them to virtual paper.

There are three Oracle features that provide a foundation for successful data warehousing:

Partitioning
Parallelism
Psummary Tables (the "p" is silent)

Here are the benefits that they bring to the system.

Partitioning

In a data warehouse we are interested in accessing large numbers of rows that are identified through some common attribute. Very often the same small numbers of attributes are used over and over again to filter the data. The most common is some form of date filter ("give me sales for this January"), and there are often others that are used very commonly ("give me inventory levels for repairable items", "give me backorders for California").

In an OLTP system we would use an index or a full tablescan to get this subset of data, but that can be inefficient if we are selecting a large number of rows representing a subset of the total table data because of the high number of logical i/o's required and the single block reads that are used to access the table.

In a data warehouse we can partition according to attributes commonly used as filters by the users, or according to logical child attributes of them (eg. users commonly filter by month but we partition by day). This achieves two important aims.

Rows of data that are commonly used together are physically co-located.
The optimizer can treat each partition as a table and can perform fast scans of multiple partitions to access the data ("partition pruning").

We can also use multicolumn or composite partitioning to partition by multiple attributes, so that we can partition both by date and by location and get partition pruning on either or both.

There are also benefits to manipulating bulk data -- we can use partition-based DDL operations to load and unload data from a table (a partition exchange) or to delete old data (partition drop or truncate).

Parallelism

In a parallel query operation the physical locations in which the required rows are held are subdivided into a number of ranges, each of which is then scanned by a different process
(a parallel query slave). The processes that read the data then pass it on to either a single query coordinator process or to another set of slaves which themselves pass the results to the query coordinator.

Insert, update and delete operations can also be parallelized to provide faster bulk data changes.

One of the key differences between serial and parallel queries are that the parallel queries read data directly from disk instead of checking for the required blocks in the SGAs block buffer area, and pass the result set directly to the user's PGA, thus completely bypassing the SGA. Read consistency is maintained through the query coordinator requesting a checkpoint to have commited dirty buffers of the scanned object written to disk before the PQ slaves start reading.

The coordination required in creating and controlling query slaves leads to an overall increase in resource usage which can easily overload a system, but when correctly implemented spare system resources can be used to improve the response time of queries that access or manipulate a large amount of data.

You probably need a lower degree of parallelism than you expect.

Psummary tables

OK, "summaries" then.

The static nature of data warehouse data allows the results of some frequently executed queries to be pre-computed and cached as summary tables. These are often created through the Oracle materialized view object type, although the use of an MV is not mandatory.

The query rewrite function of the cost-based optimizer can use the metadata stored in materialized view definitions or through declarations of query equivalence made through the DBMS_ADVANCED_REWRITE package (10g+ only) to redirect queries against large base tables (typically data warehouse fact tables) to the smaller summary tables. In most cases the summary table does not have to be an exact match for the query.

The major challenge in using summary tables is in finding an efficient mechanism for maintaining consistency between the summary and its base table(s).

The major benefit is that almost any end-user query can be executed extremely quickly with the correct use of summary tables.

PSummary

Partitioning allows efficient access to relatively large subsets of data and efficient bulk manipulation of data.
Parallelism allows the work of a single user process to be shared among multiple slave processes, leading to faster completion time but higher resource usage.
Psummaries provide extremely fast performance, but their maintenance and monitoring can require non-trivial efforts.

Now, hopefully these thoughts will leave my head alone.

Strange Thing To Carry

2006-05-18T06:00:00.000-06:00

My kids (3, 4 & 6) intercepted and detained a pedestrian passing our house the other evening on the grounds that he was in Public Possession of Dogs, and they needed to subject him to the usual line of questioning: "Names?", "Ages?", "Boys or Girls?", "Do they bite?" etc..

Strangely none of them noticed what I saw immediately, which was the holstered handgun on his hip. I'm sure he had a license to do so, but what he was expecting might happen to him in our Nice Suburban Neighborhood is anyone's guess.

On the other hand one of my wife's co-workers reported a sighting of a mountain lion about two miles southeast of us a couple of years ago. I'm inclined to wonder whether *ahem* alcohol was a factor in that case because we're definitely on the wrong side of the city for that kind of wildlife -- antelope, deer, foxes and the occasional coyote maybe, but how a lion would pass unnoticed around Colorado Springs from the mountains on the west to the plains on the eastern side is a mystery to me.

However the mountains to the west of us aparantly have the highest density of mountain lions throughout the Rockies, which leads to some basic precautions when hiking with the kids -- don't let them straggle or walk too far in front is all it really amounts to. (That link contains other interesting information on bubonic plague, hanta virus, avalanches etc. by the way). Sightings and other less fortunate encounters seem to be pretty common up around Boulder though.

Here's a prime example of the kind of plump, tender morsel that a lion would enjoy most.

It's amazing how fast those legs will carry him if he's told that there's a lion behind him!

Getting the (Sub)Partition Name for a Row

2006-05-11T11:14:00.000-06:00

Adapted from a response to a question posted on Oracle-l, here are three methods for finding out what partition or subpartition a table row is stored in, or which partition or subpartition a row will be stored in.

Logical Inference

For range-based or list-based partitioning or for range-list composite partitioning it is often feasible to infer the partition or subpartition name from the values of the partition and subpartition key columns, as long as a sensible partition naming convention has been chosen.

For example, for a range partitioned table defined in part by ...

Create table T (col1 date ...)
Partition By Range (col1)
(
Partition Y2006_M01 values less than (date '2006-02-01'),
Partition Y2006_M02 values less than (date '2006-03-01'),
Partition Y2006_M03 values less than (date '2006-04-01'),
...
)

...we can use a SQL function to provide the partition name:

To_Char(col1,'"Y"YYYY"_M"MM')

Note the use of double-quotes in the above formula to identify literal strings in the date format.

This is a fast and simple method, although an historically varying granularity on the partitions or a complex partitioning scheme could make it difficult to maintain.

DBMS_MView.PMarker

In cases where it is difficult or impossible to do perform the logical inference (hash partitioning, for example, or with complex multicolumn range/list partitioning) there are a couple of other techniques that you can use.

In the DBMS_MVIEW package there is a PMARKER function that returns the data_object_id for the object in which the row resides, and you can join to user/dba/all_objects using that.

Here's a funky example script using variable numbers of hash subpartitions ...

drop table t
/

create table t (col1 , col2 , col3 )
partition by range (col1)
subpartition by hash (col2)
(
partition p1 values less than (2) subpartitions 2,
partition p2 values less than (3) subpartitions 4,
partition p3 values less than (4) subpartitions 8
)
as
select mod(rownum,3)+1,
      floor(dbms_random.value(1,256)),
      floor(dbms_random.value(1,10))
from   all_objects
where rownum < 101
/

with   obj as
      (select --+ materialize
              data_object_id,
              subobject_name
       from   user_objects
       where  object_name = 'T' and
              object_type = 'TABLE SUBPARTITION')
select subobject_name,
      col3
from   T,
      obj
where  data_object_id = DBMS_MView.PMarker(t.rowid)
order by 1
/

My unsubstantiated guess is that this uses the file#/block# of the rowid to perform a look-up on the extent that contains the row. It therefore also ought to be possible to "hand-roll" a similar method based on extracting the file# and block# from the rowid's and joining to the dba/user/all_extents view (or a materialized subset of it, for performance reasons) to get the segment properties. I don't know if I'd care to go that route myself.

TBL$OR$IDX$PART$NUM()

The TBL$OR$IDX$PART$NUM() function gives you the appropriate partition number for a table value or set of values. It's an undocumented (except through metalink, if that counts) function with a couple of magic numbers in it, but the general format to use is ...

TBL$OR$IDX$PART$NUM("PARTITIONED_TABLE_NAME", 0, d#, p#, "COLUMN_NAME")

The easiest way to get the appropriate format and magic numbers to use for this function is to run a trace on a "with validation" partition exchange against the table of interest, and you'll pretty much get the complete SQL that you need.

So you might end up with something similar to ...

with   utp as
      (select --+ materialize
              partition_position,
              partition_name
       from   user_tab_partitions
       where  table_name = 'MY_TABLE')
select utp.partition_name,
      last_name,
      first_name
from   my_table,
      utp
where  utp.partition_position = TBL$OR$IDX$PART$NUM("MY_TABLE", 0, 0,
65535, "PART_COL")
/

This method has the advantage that you can just supply it with arbitrary values for the partition key column(s) and you will get the appropriate partition number/name. Therefore you can use it to answer the question "Which partitions will these rows go into?". If you use partition exchanges "without validation" then you can also use it to run periodic checks on whether your ETL process has been putting rows in the wrong partitions.

The disadvantage is that it is undocumented and has those pesky magic numbers, or course.

Summary

My preference is to use the method of logical inference, if possible.

The other two are evidently based on very different methodologies -- one being based on the physical location of the row and the other on values of the partition key column(s), so they can be applied to different situations. The TBL$OR$IDX$PART$NUM method is rather more amenable to performance tuning by pre-aggregating the table data based on distinct partition keys prior to applying the function call.

Machine Music

2006-05-03T09:28:00.000-06:00

Oooh, this is nice.

http://video.google.com/videoplay?docid=-5503582578132361295

A Day Without Immigrants

2006-05-01T06:39:00.000-06:00

So today is apparantly "A Day Without Immigrants" and I just realised that this means me as well, technically speaking. Does it not seem strange that so immigrants should be held to be the source of so many problems in America, of all countries? And people in the southern border lands appear to be particularly prone to that finger-pointing, when they are living on land that was originally part of a Mexico too weakened by its own war for independence to be able to defend it from the young states to the north.

I'm coming up to the eighth anniversary of my "stepping off the boat" as it were -- it's easy to work out as I just add on one year and a couple of days to my wedding anniversary, which is how long it took for my permanent resident visa to come through, plus a couple of weeks to get the last things packed and get a flight. I missed my first wedding anniversary by just five days I think. From the visa (and later job) interview questions I was asked I was apparantly suspected of being an economic migrant, which made the enormous paycut that I took to move from London, UK to Dayton, Ohio rather ironic.

On the other hand, I don't really think of myself as an immigrant. I'm think I'm really someone who is just hanging out here for a while ... seeing the sights, doing a bit of shopping, being married, having kids etc.. On the other hand I've only been back to England once in all that time, for my brother's wedding in Cambridge last year. With my parents living in Spain and my parents-in-law living near Rome we tend to pay fleeting visits to Heathrow every now and then just to see if they've finished building it yet (which they haven't) and to stock-up on the few goodies that are available there and not from our local Fine Imported Goods emporium -- ie. the commissary at Peterson AFB. They sell Flakes, Crunchies, Milk Chocolate Hobnobs etc, although they fly off the shelves because everyone panic-buys them apparantly.

So anyway I get itchy feet everytime I see Oracle blogsters writing of their travels, and we're regularly overcome by an urge to move back to Europe. Any part of it at all will do. We were recently thwarted in an attempted move to Slovenia or Romania, and now we're trying for Germany. My brother tells me we should be moving to Denmark -- in fact a friend of a friend ended up there after some kind of incident involving rowing to Iceland in a replica of a Viking longboat, during which voyage he apparantly had an affair with the wife of the group leader which must have involved either extraordinary levels of discretion or some extraordinarily tense mealtimes -- but frankly it's probably Hobson's choice where we land. If the USAF doesn't have positions for a Developmental Engineer at least available then it's not on the list.

So where was I?

Ah yes, the immigrant day thing. Well it turns out that most of my work colleagues will be in a Customer Acceptance Test for most of the day so they wouldn't notice me not being here (here being 1,200 miles from them) anyway. However, I applaud the priniciple and I may go down the Monica's Taco Shop for a breakfast burrito to show support for the cause.

Welcome

2006-04-29T08:10:00.000-06:00

Welcome to the Dark Side, Mark!

Whoops, security breach.

2006-04-27T22:47:00.000-06:00

Well it turns out that thanks to "Homeland Security" your complete driver's license information may be on the Internet ... although the good news is that you can get it taken off. It seemed like such a good idea that I've taken mine off.

Go here, search for it, and go through the "Hide my Records" procedure if it's there. What is wrong with these people?

I already checked for George Bush of Texas, but I suppose there's a few of them and the "real" one isn't listed. If you're quick though (ie. quicker than him) you can get yourself a copy of the license of Mr Thomas Kyte of Leesburg, Virginia before he gets the records removed ... that's quite the photo!

A Dose of Stupidity

2006-04-26T10:28:00.000-06:00

Natural selection is a fine thing, but when a person's stupidity and arrogance takes someone else's life we can only hope that there will be some serious repurcussions.

http://www.kcsg.com/story.aspx?id=682

No helmet, no protective gear, and 85mph in a 35mph zone on a new machine? At the very least I hope this guy got skinned well enough that he'll need a helmet to get women to look at him in future.

More Security For You Sir.

2006-04-26T07:44:00.000-06:00

http://www.computerworld.com/securitytopics/security/story/0,10801,110880,00.html

Looks like some kind of security administrator role being introduced. $20,000 per CPU or $400 per user seems a littlesteep to me though.

Soon Time to Move Again

2006-04-19T08:00:00.000-06:00

It's that time again, when the wife and I have to fill in forms and wishlists to try and influence our next posting. Last time we got pretty lucky - Colorado Springs wasn't on our list but it turned out pretty well.

The eternal problem is that wherever there are Air Force bases, all commerce other than the defence industry tends to flee. It's a trend that is continuing, accelerating even, with the consolidation of Air Force units into the larger bases with plenty of room for growth -- ie. in the middle of a God-forsaken wilderness with a 60 mile commute to the nearest habitation.

That isn't to say that they aren't good places to live -- Eglin AFB in Florida is next to one of the best beaches in the world, and Vandenburg AFB is nicely positioned on the California coast. Still they are not exactly major centres of commercial high-tech activity. In Colorado Springs I landed on my feet with a work-at-home contract with a longterm client, but that sort of thing wears thin after a while. Pretty quickly actually, especially in conjunction with a move to a new town.

The current hot prospect looks like Hanscom AFB, just outside Boston, or maybe the Pentagon. I think I like the idea of Boston more. Actually I like the idea of Europe even more than that, with some interesting vacancies at Ramstein to the south-west of Frankfurt -- I'll have to brush up on the non-existant German that I didn't learn from either the Turks I worked with in Munich or the girlfriend from Manchester I was living with at the time.

Early days yet though, and the whole process just epitomises the story of the best-laid plans of mice and men often going astray. If the worst comes to the worst and we do end up in the middle of nowhere then I'll be hoping for at least some nearby reasonable-sized airport, and I'll do the road-warrior thing for a few years. Or we'll just find the wife a new employer who doesn't unreasonably insist on moving it's employees every three years or so, regardless of the diverse other opportunities in the area. Tut! Maybe we'll move to New Zealand instead.

Space Requirement for Compressed Row Updates

2006-04-18T12:21:00.000-06:00

It seems that when you update rows within compressed blocks the data is temporarily decompressed then recompressed, possibly causing the table to grow.

Here I create an uncompressed copy of DBA_OBJECTS, which takes 788 blocks to store. The compressed copy is then shown to take 256 blocks.

On updating all of the rows the table has grown to 1038 blocks, approximately 256+788, yet as the DBMS_ROWID-based queries show the rows have not moved. It seems that an additional approximately-788 blocks of space have been added to move the table's high water mark.

Curioser and curioser.

SQL> drop table t1
 2 

Table dropped.

SQL>
SQL> create table t1
 2  nocompress
 3  pctfree 0
 4  as
 5  select * from dba_objects
 6  /

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
      788            0

SQL>
SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1610346    1611133        788

SQL>
SQL> update t1 set owner = rtrim(owner)
 2  /

60120 rows updated.

SQL> commit
 2  /

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
      788            0

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1610346    1611133        788

SQL>
SQL> drop table t1
 2  /

Table dropped.

SQL>
SQL> create table t1
 2  compress
 3  pctfree 0
 4  as
 5  select * from dba_objects
 6  /

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
      256            0

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1611146    1611401        256

SQL>
SQL> update t1 set owner = rtrim(owner)
 2  /

60121 rows updated.

SQL> commit
 2  /

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
     1038            0

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1611146    1611401        256

SQL> update t1 set owner = lower(owner)
 2  /

60121 rows updated.

SQL> commit
 2  /

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
     1038            0

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1611146    1611401        256

Introspection time

2006-04-18T08:38:00.000-06:00

I wandered over to Similar Minds to try their Jung personality test.

ISTP - "Engineer". Values freedom of action and following interests and impulses. Independent, concise in speech, master of tools. 5.4% of total population.

Free Jung Personality Test (similar to Myers-Briggs/MBTI)

Apparantly it means this. some of those observations seem to be pretty spot-on -- ie. the one's that reflect well on me. Just ignore all the others, they're not true. Also the "athletic" thing, pay no attention to that.

Careerwise I seem to be doing OK. Here are some suggestions from the website:

Police and Detective Work
Forensic Pathologists
Computer Programmers, System Analysts and Computer Specialists
Engineers
Carpenters
Mechanics
Pilots, Drivers, Motorcyclists
Athletes
Entrepreneurs

Well, #3 there is obviously on target ... I also built a very nice fence and gate, and next I'm putting up some trellises so that's #5 ... I have a Masters degree in Aeronautical Engineering and an HNC in Marine Engineering, so that's #4 ... I ride a 2005 Triumph Tiger, #7. I really ought to work harder on #8 though.

OK, back to work people.

Bad Form

2006-04-16T19:12:00.000-06:00

I just finished filling in the thirteen-pages of forms required to transfer Number Three Son to a new day care. Here are some statistics on the number of times the forms required particular information

His name: Seven
His date of birth: Four
His current age: Two
His doctor's name: Three
My address: Seven
My phone number: Ten
My signature: Eighteen

Things I have now lost as a result:

Use of my writing hand
Memory of my social security number
Last trace of sympathy for de-normalisation for performance

And my will to live is fading fast ... there must be a beer around here somewhere ...

In Praise of ISO 8601

2006-04-13T07:00:00.000-06:00

As a UK ex-patriate and US resident I'm very sensitive to date formats. Moving from the DD/MM/YYY format to the MM/DD/YYYY format was a traumatic experience, and after eight years I still get a nervous twitch when I need to place the month before the day. What kind of a twisted mind thought that up?

Probably the same one who decided that in my first job in the US I'd be dealing with a system for which the preferred date display formats were to be YYDDD and YYYYDDD. There can't be many people who are instinctively aware that the first of October is day 274 of a non-leap year, but if you find someone who is then I can tell you who they work for. Furthermore, they may belive that this data format is known as the Julian Date, and they're wrong about that of course. Further-furthermore it's surprising how many database designers believe that a requirement to display a date in YYYYDDD format means that it is a great idea to store it as a NUMBER(7), leading to it being displayed by default in many tools as "2,006,274.00". Actually it's not surprising, it's just irritating for the hopefully-obvious reasons.

The other exception to the adherence to the MM/DD/YYYY format was the Immigration Service, who seemed to prefer DD/MM/YYYY on their forms. Thus I was lulled into a false sense of security through the whole visa process, and only ambushed with the MM/DD/YYYY abomination when I was fully commited to the process.

This is why I have such a liking for ISO 8601, which specifies the standard date format to be YYYY-MM-DD, with a reduced precision option of YYYY-MM and an optional time component of what we Oracleers would represent as hh24:mi:ss. I could lay out a complete set of reasons why this makes more sense but it is done very well here, and the display of week numbers is also nicely handled.

Now then, do you suppose that if the massed hordes of database developers and administrators rose up against the MM/DD/YYYY and the less-objectionable DD/MM/YYYY and DD-Mon-YYYY formats in favour of YYYY-MM-DD then we might make a meaningful impact on this scourge? I'd hope so. If we all started a "Movement Against Date Display Stupidity" (MADDS) by claiming that there was a fatal bug in the system when displaying anything other than YYYY-MM-DD then we might deceive our way to virtue, and all mankind would benefit.

It'd do me a power of good, anyway.

A Quick Materialized View Performance Note

2006-04-12T07:00:00.000-06:00

I was asked in an email this morning about how to improve materialized view refresh performance, and it's something that appears in Google hits for the blog quite frequently. So I thought I'd write a quick-and-dirty posting to summarise some of the research (and the resulting practical applications) that I've been fiddling with recently.

Here are some bullet points:

The materialized view fast refresh mechanism is a one-size-fits-all solution, and is probably not efficient for 99% of summary table maintenance operations.
The join of the aggregated change data to the MV is function-based, as the columns of both relations are wrapped in the Sys_Op_Map_NonNull() function that allows "null = null" joins. I think that it is extremely unlikely that anyone has nullable attribute columns in their fact or summary tables, so this (and the composite function-based index required to support it) are a waste of resources.
Because of the nature of the join it seems to be extremely unlikely that partition pruning of the summary table could take place.
The join mechanism promotes nested loop joins, where a hash join is probably more efficient (that's technically an outer join in the merge, of course).
The refresh mechanism assumes that a merge will be required, when sometimes an insert is not only possible but is very much more efficient.

If performance (and robustness, IMHO) are an issue for you then I would advise that you do the following:

Use materialized views only for enabling query rewrite (which means creating them on a prebuilt summary table, and unless you are loading to the summary by partition exchange then you have to drop the MV, refresh the summary table, and recreate the MV). In 10g it is much more easy to use the DBMS_Advanced_Rewrite package instead of MV's.
Write your own refresh code, based on the usual principles of writing good SQL. If you don't need a merge then don't use it. If you don't need to join to dimension tables to get higher attributes then don't do it.
Leverage different levels of aggregation to help produce higher levels. For a series of hierarchical summaries, multi-level aggregations can be extremely beneficial.
Consider storing the refresh and MV definition SQL in CLOB columns of a summary management table, so they can be tuned and edited without needing to open up package code to do so.
Consider using a complete refresh, either through MV's or manually, for higher aggregation levels, particularly when you can reference another summary table to do so.

My practical experience of taking this path delivered a reduction in the refresh time of a set of seven materialized views based on a single fact table, from a couple of hours down to six minutes. The publication of this is an example of what my client's technical lead calls "polishing the crown" :D

Anyway, this is all a sort-of abstract from a whitepaper that I'm working on right now, which will include all sorts of proofs and demonstrations of the above, plus some process and risk management advice, but it doesn't look like it'll be ready very soon. Feel free to comment or ask questions on the points raised above though -- I don't want anyone to be kept in suspenders 'till the paper is finished.

My Dishwasher is List Partitioned

2006-04-07T15:37:00.000-06:00

My dishwasher has two levels in it. The bottom level is for plates and Large Things, the top level is for cups and Small Things, and there is a removable cutlery partition attached to the door.

If I had a spare cutlery partition then I could keep one of them in use by the dishwashing system while the other one is treated as a regular cutlery holder, and it could be filled with dirty cutlery as the items become available. When it was full I could perform a cutlery partition exchange between the one with clean items and the one now full of dirty items.

If I had spare levels then they could be treated in a similar manner. I believe that commercial dishwashers have exactly that configuration, thus they operate with lower downtime because of this exchange mechanism, although the overall configuration requires more space.

Within the cutlery partition there are six subpartitions. I like to fill each one with a single type of cutlery -- one for knives, two for spoons (they don't stack as well), a couple for forks, and one for other items. Although it is more work to separate the items into these subpartitions it has the advantage of physically clustering all the spoons together and I can access all items of one type without having to scan the complete cutlery partition.

For the upper and lower levels similar principles apply, although they are not really subpartitioned in the same way. Instead the large plates are clustered in a single contiguous range -- the small plates, the glasses and the mugs each have their own place. Again it is more work to insert the items like this, but the advantage of faster retrieval is similar because I don't have to scan the complete level to pick similar items out from dissimilar ones.

That is all.

The State of The Sponge

2006-04-06T11:53:00.000-06:00

I notice that the blog just passed it's 80,000 page load since I started using Statcounter to monitor it, and that's just one day short of its first anniversary so it's time for stats.

Looking at the statistics for the last 1000 visits, one-third of them come from Google searches -- most seem to be to do with materialized views, oddly enough.

186 www.google.com   
77  tkyte.blogspot.com   
23  www.google.co.uk   
23  www.bloglines.com   
20  oracledoug.blogspot.com   
19  www.google.co.in   
14  www.google.com.au   
10  www.google.ca   
9   www.google.fr   
8   thinkoracle.blogspot.com   
7   blogs.oracle.com   
7   orawin.info   
6   www.google.de   
6   oradot.com   
6   www.rittman.net   
5   www.google.es   
5   www.blogger.com   
5   www.petefinnigan.com   
4   my5.statcounter.com   
4   www.google.be
4   www.google.it   
4   search.blogger.com   
4   www.newsgator.co.uk   
4   www.netvibes.com   
3   www.google.com.tr   
3   www.google.fi   
3   uk.my.yahoo.com   
3   www.edpadgett.com   
3   ora-dev.blogspot.com   
3   search.msn.com   
3   www.google.se   
2   www.google.com.sg   
2   www.google.co.il   
2   www.oracle-base.com   
2   www.google.ru   
2   www.google.ro   
2   www.google.ch   
2   search.yahoo.com   
2   pjs-random.blogspot.com   
2   www.google.ie
2   bloglines.com   
2   www.google.com.ph   
2   www.google.nl   
2   oracle-base.com   
2   discuss.joelonsoftware.com   
2   www.google.com.sg   
1   blogsearch.google.com   
1   www.dbasupport.com   
1   my2.statcounter.com   
1   oramossoracle.blogspot.com   
1   www.nomad8.com   
1   www.google.co.za   
1   my.yahoo.com   
1   www.google.com.tw   
1   www.google.com.hk   
1   www.google.pl   
1   www.google.co.kr   
1   tkyte-test.blogspot.com   
1   www.google.is   
1   www.google.cl
1   idyller.blogspot.com   
1   www.google.com.ar   
1   www.google.co.jp   
1   peec2006.blogspot.com   
1   www.google.com.br   
1   www.google.gr   
1   portal.umbrialistens.com   
1   www.google.co.ve   
1   kdsatplay.blogspot.com   
1   www.google.dk   
1   www.google.com.eg   
1   oraqa.com   
1   www.google.com.co   
1   ambigramania.blogspot.com   
1   www.google.pt   
1   www.surfwax.com   
1   www.google.com.mx   
1   www.alltheweb.com

Fifty-two countries are represented, if we ignore the unknowns ...

402 41.66%   United States
132 13.68%   United Kingdom
80   8.29%   Canada
60   6.22%   Lithuania
39   4.04%   Netherlands
37   3.83%   India
26   2.69%   Australia
20   2.07%   Germany
17   1.76%   France
15   1.55%   Switzerland
13   1.35%   Spain
11   1.14%   Unknown   -
9    0.93%   Russian Federation
8    0.83%   Belgium
7    0.73%   Turkey
7    0.73%   Japan
6    0.62%   Norway
6    0.62%   Denmark
5    0.52%   Portugal
4    0.41%   United Arab Emirates
4    0.41%   Italy
4    0.41%   Sweden
4    0.41%   Israel
3    0.31%   Colombia
3    0.31%   Greece
3    0.31%   Singapore
2    0.21%   Romania
2    0.21%   Philippines
2    0.21%   Poland
2    0.21%   Austria
2    0.21%   Ireland
2    0.21%   Serbia And Montenegro
2    0.21%   Finland
2    0.21%   Latvia
2    0.21%   Bolivia
2    0.21%   Uruguay
2    0.21%   Argentina
2    0.21%   South Africa
2    0.21%   Puerto Rico
1    0.10%   Pakistan
1    0.10%   Bulgaria
1    0.10%   Estonia
1    0.10%   Kenya
1    0.10%   Chile
1    0.10%   Andorra
1    0.10%   Ukraine
1    0.10%   Iceland
1    0.10%   New Zealand
1    0.10%   Egypt
1    0.10%   Brazil
1    0.10%   Slovenia
1    0.10%   Korea, Republic Of
1    0.10%   Hong Kong

Good to see the Netherlands up there this time! Readership seems to be up to 13 Dutch people, unless some of them are cheating and using multiple computers.

The most frequently occuring city for Sponge readers is London, UK, then Calgary, Ontario, Arlington, Nieuwegein ...

The most popular article is "Writing Good SQL". It's a perennial favourite with the Google crowd.

That's all -- back to work people!

A Story of Database FUD

2006-04-06T08:12:00.000-06:00

The sorry tale here ... http://discuss.joelonsoftware.com/default.asp?joel.3.328948.11

Bad Advice On Materialized View Deferrable Constraints

2006-04-05T15:30:00.000-06:00

An article by Jonathan Lewis on constraint problems with materialized views set off a little bell in my head about a metalink document on the subject, Note 284101.1.

The note protests the use of unique, foreign key and primary key constraints on materialized views, and includes the following interesting phrases ...

"It is not a good idea to create unique (unique indexes ) constraints , primary key or Foreign key constraints on the snapshot base table ... There should not be any Unique/PK constraints at Mview base table ... Drop the Unique/PK constriants on Mview Base Tables."

Then one of the workarounds is "You can have Deferred Constraints". The document doesn't identify why having deferred constraints might be a bad idea, or why not having integrity constraints on the table at all is preferable to having them deferred .. I can't think of a reason why that might be so, and I can think of several reasons why it might not be so (query optimization being the prime one).

All this misdirection and ambiguity, combined with the abbreviation of an error code from "ORA-00001" to "ORA-1" in the title of a help document (does that make sense to anyone?) make this a pretty poor piece of work. It's not the Most Useless Metalink Article Ever, but it's close.

By the way, if you read the title and the first few words of this article, and thought "Dave's going to say that Jonathan Lewis gave bad advice!" before realising I wasn't ... the effect was intentional :D If you didn't think that then my plan failed.

What's Wrong With Opera?

2006-04-05T07:16:00.000-06:00

Not the musical form, obviously there's nothing wrong with that at all.

The browser is very nice, but I can't read the Oracle forums with it. If I go here then clicking on any link takes me here. Opera also doesn't display the little images that are scattered all around the page, and I get a bunch of rectangles with text in them. Internet Explorer doesn't have this problem, nor the issues that I wrote about in Firefox the other day (heresy, I know).

Why is reading HTML and following links so difficult? You'd imagine that they'd have it close to working by now. Well, maybe there's some configuration thing that needs to be done to the browser, but I'm not sure why I should have to spend my time messing about with this kind of crap.

OK, I promise to write something later this week about a technology that I do like. Um ....

Development and Tuning

2006-04-04T19:15:00.000-06:00

I had a pithy thought earlier today.

1. Development is the business of making the database do work
2. Tuning is the business of reducing the amount of work the database does.

Therefore, tuning is the opposite of development. It is "anti-development"!

What's Wrong With Firefox?

2006-04-03T08:49:00.000-06:00

A number of things.

On one of my machines it consistently refuses to open particular web sites. http://www.dbdebunk.com/index.html is one of them -- IE has no problem, Opera has no problem. No error message, nothing. It just seems to pretend that I haven't asked it do do anything.
It's a resource hog. Memory usage often tops 150Mb, and can only be reduced by stopping and restarting it. If my machine startys running slowly then I know where to look -- Firefox is sitting and chewing up CPU cycles to no apparant effect.
It's unstable. I generally get four or five crashes a week, and they're not associated with having a lot of windows or tabs open either.

It didn't used to be this bad, and it seems to be getting worse. AdBlock has been the only reason to keep using it, but that's not enough anymore. I'd actually rather have adverts on the page than put up with the all of Firefox's crap .