Metacode Gone Wrong

Following on from my reply about code comments at AskTom, here is a lovely example from The Daily WTF of how not to do it. It looks like we have something to learn from both ends of the spectrum.

Does nobody do code reviews any more?

Back in the Saddle

Well, I have been called out of my blessed retirement and cajoled into working for a living again. Rats. Just as I was getting used to life as a Country Gentleman of Leisure.

It was all so unexpected as well — yesterday at 2pm I casualy extended a finger and pressed an “Apply for this Job” button, and by 7pm I’d been interviewed and had signed a contract. Huh … actually a total of 3 hours between click and being accepted, with the rest just being paperwork.

Interesting stuff, anyway. “Business Intelligence Architect”, designing a system for external and internal reporting of network alerts and alarms for a major telecommunications company. The really interesting part is that the duties are not just on the Oracle data warehouse design side, but also include the reporting environment and ETL functionality. It is so much more satisfying to mentally flick between “Ah hah … so this metric is required …” into “Well it’ll have to be stored in such-and-such a way” on to “In that case the ETL wil have to …”. A real synthesis of thought processes.

Also, that’s a heck of a lot of paperwork I’ll be spitting out.

The actual development will be offshore and the designs will be subject to approval by the client of course. It should be a wild ride.

Curiously, the consultancy for the work is the UK branch of the company I was a full-time consultant to from 2002 through 2006, when I was in Ohio and Colorado. Coincidence, but another interesting facet.

Not withstanding my new employment status, I’ll still be off to Spain for two weeks at the end of August. I feel like I’ll be ready for it by then.

The Colour of Software Development

I was intrigued by this article in the New York Times. In brief, it reviews some tests on the relationship between colour and cognitive performance, and highlights the ways in which the brain is affected by colour in the environment. For example studies seem to suggest that exposure to the colour red promotes accuracy, attention to detail, and fact recall, whereas a blue environment promotes creative thinking and imagination.

Yellow seems to make people eat more food. Go figure.

I have a weak spot for studies like this. I recall reading of one that demonstrated quite convincingly that creativity in finding an elegant solution to a software development problem was stifled by the playing of music through headphones. How many developers do we know who do just that very thing, albeit it in many cases to drown out the babble of the marketing department that they are colocated with? That’s workplace creativity being strangled, that is.

What I find interesting about this colour study is that two desirable aspects of the software development process — creativity and accuracy — are apparantly promoted by different colours, and these aspects are also desirable at different stages in the development cycle.

Designing an ETL process? Creativity … get yourself in the blue room, and those intuitive leaps that suddenly simplify the whole process will flow naturally to your pen. Implementing the design as code? Red room for you, and you’ll be focused on syntax and detail, with fewer bugs and faster development.

And keep yellow out of the work place. The sedentary office life and donuts in the morning are already our worst enemy.

42

Well, 2009 is here, and it’s a time of change for sure.

There’s that whole US election thing of course, which made me a happy non-participant in the democratic process, but more importantly it’s a time of change in the Sponge household. After 10 (is that right? maybe more) years of life in the United States, experincing Dayton OH, Colorado Springs CO, and Fairfax VA, we’re heading back to the Old Country at the end of March or thereabouts. Not out in some gods-forsaken wilderness either, but just a shade west of London. Ten years ago I’d had enough of London, and maybe I still couldn’t take living there and commuting into the city five days a week, but it’s a fine place to be near and I’m looking forward to be back close enough for a stroll around Hampstead Heath, or Greenwich, or the Inns of Court.

Mixed feelings in the house about it of course. Nervousness for the kids (5, 7 and 9), who still miss the mountains and sunshine of Colorado. Despite the inherent dangers of the area (yes Jennifer, “escaped lion”  makes me react with “it’s a little concerning” also), Numbers 2 and 3 sons have decided to go to college there and Number 3 will live in Number 2’s house. I’m not sure that I planned ahead that far when I was their ages, but it’ll be nice to have somewhere sunny for me to visit when I’m in my dotage.

Well, it’s been hard to be so far away from family for all these years and it’ll be good to be back in England, just a drive away from siblings and their own families, and a short flight away from parents. I smell an extended summer vacation  for three lucky boys and four mixed-fortune grandparents! If they need me, I’ll be windsurfing or summit.

Anyhoo, in retrospect it was a simple business to move over here, though it didn’t seem so at the time. There was the paperwork and bureaucratic waiting game of the visa process for me, but that aside there was just some furniture, some boxes, and an airplane ticket. In ten years it seems that you can pick up a lot of possessions — a cat, a couple of cars, a TV or two, three kids, an artificial Christmas tree … the list seems to be endless. The tree aside they all require their own confusing set of paperwork or other accoutrements. Importing a car is no easy matter, and anything electrical requires transformers and converters and whatnot. We have to find a school for the cat, get the kids vaccinated and into quaranteen kennels, and get new passports for both. It’s a confusing business that will take our organisational skills to a whole new level.

Plus, we’ll be missing out on several years’ worth of East Enders when we move, but with the local PBS station only broadcasting two episodes a week we weren’t catching up. Just to give you an idea of how far behind we are, Tom just died trying to save whats-his-name from the fire in the Slaters’ house that Little Mo accidentally started after what-his-name doused the place in petrol with the intention of scaring Little Mo into staying with him. The bastard. Still, Tom had that tumor so he wasn’t going to be around much longer. And that new manager at the Old Vic is obviously a bad sort.

Trevor, that was his name. The guy with the petrol.

So, there is enough to be done with this move that I didn’t feel that I could juggle personal and work commitments for the next three months, so I left XM Satellite Radio almost on the last day of the year and I now kid myself that I will be a Gentleman of Leisure for a while. XM and Sirius satellite radio companies are now but a single entity so there’s a certain amount of merging and sorting and whatnot to be done, and many challenges ahead that I really couldn’t have put all of my energies into. It’s been interesting to see how two companies that were pretty much the only ones of their kind in the country are similar in some ways and poles-apart in others. I can certainly say that there were many organisational and philisophical differences in the two IT organisations, which were almost complete opposites of each other in many aspects. And that’s all I have to say about that :)

I’ll miss much of it, of course. I worked with some people there who I hope to be in touch with for many years, some of them in strange foreign countries full of poison frogs and monkeys. Oh, they’d deny it if you asked them straight, but if you work ‘em for 40 hours straight they’ll confess it all. Really, we’d lure people to lovely Washington DC with promises of an easy couple of weeks getting to know us and helping out with a few things here and there, then work them relentlessly every single day and return a wornout husk of a person to their next of kin — a mere hollow shell. “On the way out I passed Henrique at Sao Paolo airport — I thought he was a homeless guy! What did you do to him?”. A real and unsolicited testimonial from one of our wonderful developers in Curitiba.

Good times. I’ll miss those crazy Brazilians and the special Anglo-Saxon vocabulary we used on a daily basis to keep ourselves sane.

Moving along … aside from that whole “relocating to a different continent” thing, I have a number of other pursuits to keep me entertained.

Firstly, I have a little list of technical topics to write about. Some parallelism things,  some SQL stuff. I’d also like to get more familiar with Oracle’s other data warehousing technologies, so I’ll be fiddling with them as well. I had a brief and not very technical encounter with OBIEE a year or so ago (the same project that destroyed poor Henrique, I think) and the architecture was very appealing (Henrique might differ). It’s one of those “why yes, that seems like it would work very nicely” ways of doing things that I’m keen to know more about. Also, after around ten years of working with Informatica it will be interesting to see how Oracle’s own data warehouse ETL toolset handles. I dipped my toe in that water many years ago but at that stage I think that I was looking at a less than mature application, and I’ve been hearing good things about itfor a while now. Time to take that plunge.

Secondly, there’s that whole  ”gained X pounds in weight while living in America” thing to deal with. Giving up the smokes, working too many sedentary hours, and the dining-out culture have really done a number on my waistline. A sparkly new membership at the enormous local fitness club makes me wonder why I didn’t do this before, and hopefully will help me get to grips with that. I’m no fan of that sort of thing (“what is this strange salty fluid exuding from my pores?”), but it has to be done. It’s been OK so far. You know, bearable.

Lastly, the kids will be out of day care and under Direct Parental Supervision. Now that should really be interesting.

However before all of that begins I’m off to the UK and Spain for a couple of weeks first, reminding friends and family what it will be like to have us back within unexpected-visit range and giving them a last chance to change names and move to a different city. I like to think of it as a Mental Health Vacation for me to decompress from a couple of years of rather hard and stressful work, and to get me refreshed for the next few months of what will doubtless be one of my more interesting years of family life.

“Happy New Year”, everyone.

Has Everyone Forgotten “Keep It Simple, Stupid”?

OK, we all love a clever piece of code to do something a little bit tricky, but has everyone lost sight of the benefits of simplicity?

My case study for the day, a thread on the Oracle forums in which various all_objects, user_objects, connect by, MODEL, xmltable, and pipelined function techniques are suggested for generating a list of days.

Well, my comments are in the thread. Just use a table … that’s why we have them. Keep … it … simple.

 

The “OVERLAPS” Predicate

I was browsing a pretty interesting “e-book” this evening: “Developing Time-Oriented Database Applications in SQL” By Richard T. Snodgrass. I quietly bemoaned the missing functionality of Oracle in some regards, for instance the rather neat OVERLAPS predicate that returns true when two intervals … well, overlap. We tend to jump through hoops a little to achieve this sometimes, I felt.

But you never know, things have moved on since the Oracle 8, so I searched the 10g and 11g documentation to see if I’dbeen negligent in not reading the New Features Guide. Alas, nothing in the SQL Reference, but I did turn up this interesting entry:

ORA-30085: syntax error was found in overlaps predicate
Cause: A syntax error was found during parsing an overlaps predicate.
Action: Correct the syntax.

“How”, I asked myself, “can there be an error message relating to a non-existent feature”?

“What if”, I continued, “said feature is not missing, but merely undocumented?”

So I tested it.

And it works.

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 12 23:14:03 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Enter user-name: XXX/XXX@XXX
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security and Data Mining Scoring Engine options
SQL> select * from dual
  2  where          (date '2007-01-01', date '2008-01-01')
  3        overlaps (date '2005-01-01', date '2006-01-01')
  4  /
no rows selected
SQL>
SQL> select * from dual
  2  where          (date '2007-01-01', date '2008-01-01')
  3        overlaps (date '2005-01-01', date '2007-01-02')
  4  /
D
-
X
SQL>
SQL> select * from dual
  2  where          (date '2007-01-01', interval '5' year)
  3        overlaps (date '2005-01-01', date '2007-01-02')
  4  /
D
-
X
SQL>
SQL> select * from dual
  2  where          (date '2007-01-01', interval '5' year)
  3        overlaps (date '2005-01-01', interval '10' year)
  4  /
D
-
X
SQL>
SQL> select * from dual
  2  where NOT         (date '2007-01-01', date '2008-01-01')
  3           overlaps (date '2005-01-01', date '2006-01-01')
  4  /
D
-
X
SQL>
SQL>
SQL> with test_data as
  2  (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all
  3   select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all
  4   select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all
  5   select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all
  6   select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual)
  7  select *
  8  from   test_data t1,
  9         test_data t2
 10  where  (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt)
 11  /
FROM_DT   TO_DT     FROM_DT   TO_DT
--------- --------- --------- ---------
01-JAN-07 01-DEC-07 01-JAN-07 01-DEC-07
01-JAN-07 01-DEC-07 01-APR-07 01-AUG-07
01-JAN-07 01-DEC-07 01-JUL-07 01-AUG-07
01-JAN-07 01-DEC-07 01-OCT-07 01-FEB-08
01-APR-07 01-AUG-07 01-JAN-07 01-DEC-07
01-APR-07 01-AUG-07 01-APR-07 01-AUG-07
01-APR-07 01-AUG-07 01-JUL-07 01-AUG-07
01-JUL-07 01-AUG-07 01-JAN-07 01-DEC-07
01-JUL-07 01-AUG-07 01-APR-07 01-AUG-07
01-JUL-07 01-AUG-07 01-JUL-07 01-AUG-07
01-OCT-07 01-FEB-08 01-JAN-07 01-DEC-07
FROM_DT   TO_DT     FROM_DT   TO_DT
--------- --------- --------- ---------
01-OCT-07 01-FEB-08 01-OCT-07 01-FEB-08
01-OCT-07 01-FEB-08 01-JAN-08 01-DEC-08
01-JAN-08 01-DEC-08 01-OCT-07 01-FEB-08
01-JAN-08 01-DEC-08 01-JAN-08 01-DEC-08
15 rows selected.
SQL>
SQL>  with test_data as
  2  (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all
  3   select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all
  4   select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all
  5   select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all
  6   select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual)
  7  select *
  8  from   test_data t1,
  9         test_data t2
 10  where  NOT (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt)
 11  /
FROM_DT   TO_DT     FROM_DT   TO_DT
--------- --------- --------- ---------
01-JAN-07 01-DEC-07 01-JAN-08 01-DEC-08
01-APR-07 01-AUG-07 01-OCT-07 01-FEB-08
01-APR-07 01-AUG-07 01-JAN-08 01-DEC-08
01-JUL-07 01-AUG-07 01-OCT-07 01-FEB-08
01-JUL-07 01-AUG-07 01-JAN-08 01-DEC-08
01-OCT-07 01-FEB-08 01-APR-07 01-AUG-07
01-OCT-07 01-FEB-08 01-JUL-07 01-AUG-07
01-JAN-08 01-DEC-08 01-JAN-07 01-DEC-07
01-JAN-08 01-DEC-08 01-APR-07 01-AUG-07
01-JAN-08 01-DEC-08 01-JUL-07 01-AUG-07
10 rows selected.
SQL> explain plan for
  2  with test_data as
  3  (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all
  4   select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all
  5   select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all
  6   select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all
  7   select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual)
  8  select *
  9  from   test_data t1,
 10         test_data t2
 11  where  (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt)
 12  /
Explained.
SQL> set linesize 200
SQL> select * from table(dbms_xplan.display)
  2  /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 1640239735
----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     1 |    36 |    22  (37)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |       |
|   2 |   LOAD AS SELECT           |                             |       |       |            |       |
|   3 |    UNION-ALL               |                             |       |       |            |       |
|   4 |     FAST DUAL              |                             |     1 |       |     2   (0)| 00:00:01 |
|   5 |     FAST DUAL              |                             |     1 |       |     2   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
----------------------------------------
|   6 |     FAST DUAL              |                             |     1 |       |     2   (0)| 00:00:01 |
|   7 |     FAST DUAL              |                             |     1 |       |     2   (0)| 00:00:01 |
|   8 |     FAST DUAL              |                             |     1 |       |     2   (0)| 00:00:01 |
|   9 |   NESTED LOOPS             |                             |     1 |    36 |    12   (0)| 00:00:01 |
|  10 |    VIEW                    |                             |     5 |    90 |     2   (0)| 00:00:01 |
|  11 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6602_EE7BE7EC |     5 |    90 |     2   (0)| 00:00:01 |
|* 12 |    VIEW                    |                             |     1 |    18 |     2   (0)| 00:00:01 |
|  13 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6602_EE7BE7EC |     5 |    90 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
----------------------------------------
---------------------------------------------------
  12 - filter((INTERNAL_FUNCTION("T1"."FROM_DT"),INTERNAL_FUNCTION("T1"."TO_DT"))OVERLAPS(INTERNAL
              _FUNCTION("T2"."FROM_DT"),INTERNAL_FUNCTION("T2"."TO_DT")))
26 rows selected.

So not only does it let you check two pairs of dates to see if they overlap, it lets you check if they do not overlap (pretty simple stuff), and it also accepts a date and interval expression in place of date pairs.

It doesn’t work for pairs of numbers though, it seems:

SQL> select * from dual
  2  where          (1,5)
  3        overlaps (3,8)
  4  /
where          (1,5)
                *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL>

Still, very cool! Undocumented, but still cool.

Does it make it more cool if it is undocumented?

A Fix for Check Constraints That Harm Cardinalities

I wrote a while ago about the harmful effect of check constraints on query optimization. I’ll pause while you catch up on that …

Anyway, this appears to have been addressed in 10.2.0.4, and it’s now safe to go back into the water on applying check constraints. Bug 5891471.

The bug description also notes that you can set event 10195 to disable transitive predicate generation using check constraints.

I haven’t tested any of this — I will if I get time. Unless someoneelse wants to …

 

Testing a No-statistics Environment: Part II

Following on from the previous post, a little glitch in the plans: the instance appeared to crash late last night during the load. Hopefully not some exotic bug associated with dynamic sampling.

I modified the delete-and-lock strategy yesterday after remembering that we have a number of indexes that are created with the “compute statistics” option. Since DBMS_STATS.LOCK_TABLE_STATS is intended to prevent the modification of any statistics associated with a table I ran a quick test to see if compute statistics can be specified for an index created against a statistics-locked table, and indeed it cannot.

SQL Error: ORA-38029: object statistics are locked
38029. 00000 -  “object statistics are locked”
*Cause:    An attept was made to modify optimizer statistics of the object.
*Action:   Unlock statistics with the DBMS_STATS.UNLOCK_TABLE_STATS procedure
           on base table(s). Retry the operation if it is okay to update statistics.

Well, it was probably safer not to anyway for a first run.

Now, whatever happened to that database instance?

 

Testing a No-statistics Environment.

I am absolutely sick and tired of dealing with issues where misleading partition statistics (for example) cause ETL queries to go crazy and try to sort-merge tables with tens of millions of rows each.

Since we’re about to run a test load on production data I’m going to drop all table and index statistics and lock them. OK, actually I’m going to back the statistics up first, and then I’m going to drop and lock them.

We’ll see how we do on a 100% dynamic sampling-based system …

An Empassioned and Meaningless Debate

“See-qwel” or “S-Q-L”? Have your say on this highly important matter here, and help shape the future of our industry.