A weekend hotchpotch or is it hodgepodge?

A while back I wrote about using the row_number() analytic function and analytics in general. At the time I said that I did not use analytics often, perhaps rank() when people wanted to find their top 46789 customers - yes, sometimes they choose very odd numbers :-) This week we put dense_rank to work to create a list of the six most recent days people visited a web store (and they could visit more than once in a day) and from this generate a recent purchases to seed their next shopping basket.

Identifying the customers and shopping visits was very speedy but using this result set to select the actual items bought in those visits was quite time consuming - one of my developers spent most of the week trying to shave time off the query. He asked if he thought some bitmap indexes on the fact table foreign keys would help - I thought not because of the way we were to access it; but it is a relatively simple and reversible thing to try. He found a marginal improvement but other users had a major hit in performance. Here the bitmap allowed a star transform to happen but in Oracle 9.2 there is a bug with large partitioned tables where the lower bound on the partition key can be ignored and instead of working with a few dates it uses almost the whole table and that is a massive thing to transform in memory or more likely disk. Dropping the indexes returned things to normal.

I'm still not used to the nearness of my office, I still wake about one hour earlier than I need, get to my desk while the cleaners are still scraping yesterday's coffee spills from it and then stay for far too long. I am quite good in that I have not yet succumb to the temptations of a full cooked breakfast in the staff restaurant at 7:30 in the morning. There's hope for my figure and still being to wear that jacket at UKOUG in November.

One thing about the office that is not so good is that my swipe card no longer gets me into the DBA area, they now work in another wing of the building - still there is the phone and instant messaging and they do visit me sometimes so they haven't quite lost all those 'talk to developers' skills I taught them over the last couple of years.

Finally moving here (wordpress) seems to have worked out quite well - the pages look better; and more to the point people are finding the site and even linking to it, if anything readership has gone up (and I am not counting my blogger redirects). Still need to think about something to boost page hits to the Doug Burns level - perhaps I could write some interesting animal porn