I've not posted anything for a few days, and it looks like today is going to be spent unloading my database so I can install VectorWise 1.0. As much as I've enjoyed playing with the 0.8 release, the extra features in 1.0 are too inviting to ignore, and besides, what would be the point of familiarizing myself with the features (and limitations) of a stale version?

I'm using unloaddb in the usual way. Most of the most frequently used utilities work with VectorWise, with only a few documented exceptions. In this case unloaddb is unloading a mix of VectorWise tables and classic Ingres tables from the same database. I did hit a snag with the script generated by unloaddb, but I reckon it's alpha software and besides it was easily fixed. I logged the problem anyway.

To kill some time while I am waiting for the unload to finish I thought I'd show how I created subsets of my largest table to produce quick test cases. As I have mentioned repeatedly, one of my tables is really enormous. Although I can run some queries on it, others are infeasible owing to the lack of physical resources on the machine I'm using. Also, no matter how fast VectorWise is, there are times when I just want to prove that a sequence of steps produces a correct result so I want nearly instant response time and a dataset I can check by other means. I'm not going to go through a 3.3 billlion row table by hand to verify VectorWise's arithmetic.

I decided to take my big table and produce a series of subsets of it, each half the size of the previous one. The idea is that I can start any testing with the smallest subset (6.25 million rows). It is small enough to load into classic Ingres to run verification queries in a reasonable amount of time, and it gives nearly instant response times using VectorWise. I can then use a set twice the size to see if that works within my limited resources, and if it does, I can double the size again, and so on until I find the limit.

To preserve a realistic distribution of values I created the first subset by randomly choosing half the rows from 3.3 billion row table. Every row had an equal probability of being selected. It took a bit less than 55 minutes to create a 1.6 billion row subset like so: I repeated the procedure on that subset to get an 800 million row subset, then again on that one to get 400 million, and so on, finishing up with a 6.25 million row subset.

The subsets are subsets of my rawdata. Each subset was used to populate the tables of its corresponding star schema. It was easy to verify that this procedure had successfully chosen a representative sample preserving the relative frequency of key values so that the only difference was the size of the dataset.