Saturday, May 14, 2005

Cinema Bizarre

As I turn on the TV, there is a wierd movie from ~1980 on, starring a totally suave, chain-smoking, dressed-to-the-nines Art Garfunkel.

This type of hallucination can only be explained by food poisoning...

Wednesday, May 11, 2005

Coding, Style, and Performance

At this point, my job seems to be (a) helping a group of developers implementing on-the-fly designs, and (b) writing design documents for some of our deeper issues. As part of this, I've been watching people write a fair amount of SQL. One of the great issues of the day seems to be surrounding join syntax in Oracle. Traditionally, an Oracle sql statement that joins emp and dept would look like:

SQL> select e.empno, e.ename, d.dname
2 from emp e, dept d
3 where e.deptno = d.deptno;

And, if there were employees who did not belong to a department, you would use an outer join:

SQL> select e.empno, e.ename, d.dname
2 from emp e, dept d
3* where e.deptno = d.deptno(+);

or, if you wanted to make sure to get any departments that had no employees, you would use a right outer join:

SQL>select e.empno, e.ename, d.dname
2 from emp e, dept d
3* where e.deptno(+) = d.deptno;

All well and good - easy examples, understandable code, what could be easier?
But -- and there's always a but -- when you start adding real logic to the where clause
(where ename like 'KIN%'), and perhaps add several more joins to the query, you have a festering mess, just waiting for a bug to appear.

Oracle 9 and above support ANSI join syntax, which removes the stuff from the where clause in its entirety (that's a good thing). More importantly, I think it helps people think through the questions they are asking, which can expose some of the bugs we typically see much earlier.

SQL> select empno, ename, dname
2* from emp inner join dept using (deptno);

SQL> select empno, ename, dname
2* from emp left outer join dept using (deptno);

SQL> select empno, ename, dname
2* from emp right outer join dept using (deptno);


SQL> select empno, ename, dname
2* from emp full outer join dept using (deptno);

Now I consider the new syntax to be a great improvement. It's much more clear which is the driving table, and you can put the logic in English:
Inner Join: "Fetch me the empno, empname, and corresponding dept name from these two lists; don't list anyone that doesn't belong to a department"
Left Outer Join: "Fetch me the empno, empname, and corresponding dept name if one exists from these two lists"
Right Outer Join: "Fetch me the empno, empname, and corresponding dept name if one exists from these two lists, and also list any dept names that have no employees"

It only gets better from here... but that will have to wait until tomorrow.

Tuesday, May 03, 2005

Fun with CTXCAT

Oracle provides a full text indexing type called CTXCAT, which can be thought of as a low-maintenance full text index. You don't have to worry about synchronizing, which makes updates happen in real time. It is best used for things like document title searches, product names, and the like -- the syntax is easy, but you have very little power or control to do the sexy, cool full text search that a Context index gives you.

Except -- you can actually embed a Context 'CONTAINS' clause within a CTXCAT 'CATSEARCH' clause and get the full power [well, a good chunk of it anyway]. The syntax is a little painful, but very repeatable. As an example...

Contains clause to do a fuzzy search on the word 'column' in the doc.text column:

select docid, score(1), title from doc
where contains (text, '?column', 1) > 0
order by score(1) desc;

You cannot duplicate this with a standard CATSEARCH clause. However, using a query template you can extend the catsearch clause to perform a contains search (substitute < for [ because I can't figure out how to tell it to turn off HTML processing) :

select docid, title from doc
where catsearch(title,
'[query>
[textquery grammar="context"> ?column
[score datatype="integer"/>

[/query>','') > 0;

Viola! Fuzzy searching on a CTXCAT index.

The Full Swing

Golf is best thought of as the act of trying to smack a small white ball a quarter of a mile into a four-inch cup, three tries is good, four reasonable, and anything more unacceptable.

I would really like to try that again soon, but won't be holding my breath.

Thursday, April 28, 2005

Intuition

So Tom Kyte has a post today: http://http://tkyte.blogspot.com/2005/04/things-you-know.html answering "[how] do you prioritize what "new things" you'll be learning first?".
One of the comments dealt with intuition, and it occurs to me that intuition is what you call 'good guesses'. People always extrapolate based on what they know [or think they know] -- if there's a more fundamental definition of learning, I don't know what it is.
However, are some people better extrapolators than others? I spent a couple of hours today working through a problem with a couple of people. They had been working on a new package for about a week, and were basically stuck. They had tried "everything".
So, here I am, looking at a package that I've never seen before in my life, and, after taking a little while to undo a portion of "everything" they had tried (to set the system up like I thought it was intended to be), we got it working. As I look back, most of the things they had tried were pure guesses, and had little to do with solving the problem.
I don't know exactly what to make of this, or most importantly how to help them pick a successful approach, but that's the challenge right now.

Me and My kids

Nobody Expects the Spanish Inquisition

I've been watching blogging since ... well, before it even really started. I sipped from the cup of Dave Winer when he was publishing DaveNets via email, then switched to reading them from his website, then heard him giddily begin to promote 'Weeblogs'.
I've never taken the plunge, first because I'm not sure I have anything metaphysically earth-shattering to say, but mainly because I'm not confident that I can keep writing something every day.
But Google has made the cost of entry so low that I can't help but try.
So -- here's hoping that there's something to say tomorrow. 'Till then...