Saturday, June 21, 2008

For Want of a %s...

Today's post involves a three-way grudge match among usability, security, and inertia.

One of the basic tenets of usability is to answer user questions clearly and concisely. One concrete technique is to use placeholder strings liberally in messages (typically coded in C-like languages as "%s" for strings or "%d" for numbers).

This notion pops into my head whenever I'm doing anything significant in SQL*Plus. Here's a condensed excerpt from a typical log file of mine. The questions that run through my mind aren't that deep -- what table is being updated? What index, package, procedure, etc. is being created? It seems redundant for me to have to put that information into PROMPT commands.

34094 rows updated.
Commit complete.
Index created.
Table truncated.
Package created.
Package body created.
No errors.
Package created.
No errors.
Function created.
No errors.
Procedure created.
No errors.

I never pushed too hard though to get a change like this into SQL*Plus, because just imagine how many test cases and automated builds expect to see some exact sequence of output that would suddenly be different. I expect it would require some sort of SET option within SQL*Plus, which in turn would involve new documentation, a long adoption cycle, scripts that used the new SET option would throw errors for older versions of SQL*Plus... all of a sudden it's not so clean.

Another possibility for this usability technique is the set of database error messages. How many messages say that some limit has been exceeded, or there's a problem with some object, where the erroneous thing that was encountered, or the correct thing that was expected, is never specified?

Again, test cases are difficult to change. Extensive error messages are also tricky for translation, because maybe the placeholder needs to be moved around because of different sentence structures in different languages. Maybe the value of the placeholder causes a ripple effect elsewhere in the message, as with the "le / la" distinction in French depending on whether a noun is considered masculine or feminine.

The lesson here is that these little touches are important to bake in from the beginning.

Security is another consideration that wasn't so obvious when the usability bibles were being written. If someone can induce an application to print a < character where one isn't expected, and the output is written to a web page, presto an attacker can construct a string to be echoed in an error message with a <script>, <iframe>, or other tag that can change the contents of the page as part of a cross-site scripting attack. So there's an extra level of scrutiny for every message string, to ensure that it doesn't have disallowed characters, that the substitution text isn't too long, and so on.

Saturday, June 7, 2008

The Goals of a Computer Science Education

I'm far enough along in my UC Berkeley Comp Sci refresher podcasts -- 61A (Structure and Interpretation of Computer Programs), 61B (Data Structures), and most of 162 (Operating Systems) -- that it feels like time to make some blog posts about computer science in general.

After all, one of the perennial topics for argum^H^H^H^H^H discussion is whether it's better to hire a Comp Sci major, a naturally talented coder, or someone with a lot of vocational training and experience. What anyway are the goals of a Comp Sci degree?

IBM has a multidimensional measure of software quality and/or customer satisfaction called CUPRIMD [that link is a PDF file]. Standing for Capability, Usability, Performance, Reliability, Installability, Maintainability, and Documentation. Never one to leave well enough alone, IBM later extended this acronym to CUPRIMDSO by adding measurements for Support and Overall. I contend that the goal of computer science is really all about the CPR in that acronym: capability, performance, and reliability.

For example, CS 61A focuses on capability (how do you write a program to accomplish X?) and reliability (what practices are needed to ensure the answer is right and cover all the possible inputs?). CS 61B focuses on capability (how do you sort, find the highest/lowest value, or transform a data structure?) and performance (is it faster to sort this way or that, what are the best and worst cases to do such-and-such a transformation on this type of data structure?). And CS 162 focuses on reliability (how do we keep a process from writing over another's memory, how do we avoid deadlock?) and performance (how do we keep address translation, resource tracking, et al from grinding the system to a halt?).

I know that all the while, students are exhorted about documentation ("write good comments", "learn Javadoc notation"), and later on, depending on the institution, there might be optional branches off into specialties for technical writing and usability. But those are a bit squishy for the core Comp Sci goals. The best technical writers put their deep knowledge aside to write things that are mostly correct and comprehensive, in words that an eight-grader can understand. The questions "is Ruby on Rails usable?" and "is this ATM touchscreen usable?" lead off in very different directions.

The goal of teaching someone who can make the most reliable, fastest, and fullest-featured program or system runs into some conundrums in the real world.

Today, if you want to enable your company to do something it couldn't do before, you might do a "View Source" on a web page to see how to do a certain effect in CSS, or adopt a new language or code library that solves your toughest problem in one line of code. So capability is not really such a distinguishing factor between people with higher education, natural skill, or on-the-job training.

That leaves us with performance and reliability. That's where the real paradoxes come in.

You might call in someone with a CS degree, who turns an O(N**3) solution into O(N  log N) and so saves untold hours of waiting. Or they might reduce memory requirements so that the underpowered machine devoted to an application suddenly runs like a champ. Or they might network together several servers to turn formerly idle time into productive work.

On the other hand, if you have something that works most of the time but occasionally gives a wrong answer or hangs, the Comp Sci solution may add quite a lot of complexity and overhead to make things 100% reliable. UCB's operating systems course really puts that into perspective, taking me down memory lane from the Commodore 64 days of gleefully smashing the stack, to Motorola 68000 and the Amiga 1000 where there was no paging or swapping and we dreamed of the 68020 with MMU, to the world of today with robust multitasking and memory management all accelerated by hardware. Not a day goes by that I don't say to myself, things were a lot faster in the '80s. :-)

I see echoes of these CS goals in the "tough tech interview"-style word problems that people post from time to time. (C'mon, admit it, you've looked at them.) Hey, this one is really recursive unwinding, that one's a merge sort, I'll bet the clock algorithm would work here, and so on. For the most part, all drawn from those early fundamental CS courses. As an employer, you want your recent grads to really have internalized those lessons, rather than deciding that this theory stuff isn't important in the real world. And you want your industry hires to not have completely forgotten the basics after years of dealing with COTS ("commercial off-the-shelf") software and office politics.

Tuesday, June 3, 2008

How PL/SQL Could Be More Like PHP

This is really a trick subject line -- PL/SQL already has the capability to be very much like PHP. PL/SQL Server Pages lets you write template HTML files including PL/SQL parameters, expressions, and blocks, and turn them into stored procedures that produce HTML output using HTP.P() procedure calls. The angle-bracket syntax around the expressions and blocks is very similar to that of PHP. And a PL/SQL coder has a headstart when it comes to optimizing DB performance and avoiding security problems.

PL/SQL Server Pages was the first web-related feature that I documented at Oracle. When I started using it myself, eating my own dogfood so to speak, that's when the potential functionality enhancements sprang to mind.

PHP is so popular because it's all based around text files, you can modify your source with any kind of HTML tool, and you don't have to do anything special to get the code to run, just put the files in the right places.

PL/SQL Server pages are also based around text files, you can also run them through HTML Tidy, and edit them in DreamWeaver or what have you. However, to use them requires the loadpsp command. With this command, you specify the user ID and password on the command line. That's not the ideal mode of loading code if you normally sit at the SQL*Plus command prompt, already logged in, and load new/changed stored procedures via '@script_name'.

With PHP, the way to load code into different parts of the application structure is to put the files in different subdirectories. The equivalent for PL/SQL Server Pages would be to use a template file to generate a packaged subprogram, an internal procedure, or a method for an object type. But, to my knowledge, loadpsp can only create a top-level procedure, which best practice says you should keep to a minimum.

Now, all of this could be worked around if loadpsp could generate the source code for the procedure and stop there, leaving the file behind instead of loading it into the database. You could set up a makefile to transform each .psp file into a .sql file. You could load the .sql files into the database from inside SQL*Plus without doing a separate login each time. You could set up a script using a SQL*Plus HOST command to go through both steps. Perhaps that would incite the SQL*Plus dev team to create a new directive similar to '@' that would run loadpsp and then '@' the resulting output file. If the output file could start with 'PROCEDURE ... IS', leaving off the initial 'CREATE OR REPLACE', you could use my favorite trick of:

package body foo is
@procedure1;
@procedure2;
...

to load PSP-generated pages as packaged procedures or object methods.

I never quite bought into the idea that with PHP, coders and designers would happily edit the same files, passing them back and forth and limiting their edits to either the HTML tags or the code blocks depending on their role. In my experience, every expert tends to have ingrained assumptions that don't work well with top-down planning. The designer tasked with making a more attractive-looking page will go off and write HTML that's structured very differently from the existing page, that PL/SQL blocks and widgets can't be neatly dropped into. The PL/SQL coder tasked with adding some dynamic elements to the page will chafe at working inside markup produced by some HTML authoring tool they disdain; sometimes, it's too painful even to read past the DOCTYPE tag. :-)

Thursday, May 29, 2008

How PL/SQL Could Be More Like Perl

An idea has been rattling around in my head for a while now. I know, I know, there's a lot of empty space so it makes a lot of noise. :-) Could PL/SQL become more popular and easier to use by taking a couple of points from the Perl playbook?

I read a blog post about how scripting languages are or aren't gaining on Java. I think supporting curly-brace syntax would be a nifty way to encourage PL/SQL adoption among both scripters and Java coders. But maybe that's just me.

My first impressions of Ada and then PL/SQL were somewhat negative: hey, this verbose BEGIN/END, IF...THEN...END IF syntax reminds me of Pascal. I first programmed in Pascal on the Commodore 64, and it was deadly slow to compile and run, owing to the whole UCSD P-Code business. Then at university, it was the first language for coursework, and even though it ran really fast on UNIX boxes, I always felt that behind the scenes, more deadly slowness was occurring.

These days, 95% of the languages I code in use curly braces for blocks:

if ... { ... }
function ... { ... }
for ... { ... }

Imagine if PL/SQL did the same. Suddenly, brace matching in vi/vim would work. (Hit % while cursor is on a brace character to find the matching brace at the other end of the block.) It would be unlikely to introduce logic errors; in my experience, mis-nesting of blocks is not a frequent source of errors in Perl or Java, and in deeply nested PL/SQL, even today a careless coder can just add END IF statements until the syntax error goes away. I sometimes write little comments after the END IF, END LOOP, etc. to remind myself of which block each one closes.

On the documentation end, the PL/SQL User's Guide and Reference, aka PL/SQL Language Reference in 11g, has never devoted reference topics specifically to "BEGIN Statement", "END Statement", "END IF Statement", etc. Instead there are generic topics like "Block Declaration", or a section on "LOOP Statements" that doesn't split out the "END..." part as a separate item. That's always made it hard for me to write auto-linking pretty printers for PL/SQL (i.e. no "BEGIN" topic to link from the "BEGIN" keyword), but it would make syntax enhancements slot right into the docs.

From a parsing standpoint, I think it's pretty straightforward. If you leave out a THEN or put an END IF where an END LOOP is expected, PL/SQL gives you a straightforward error message. It knows what block is open and what the expected closing symbol is. So I imagine that it's practical to parse syntax like:

procedure foo is
x number;
{
if 1 > 0
{
for bar in (select * from t1)
{
x := 3.1;
declare
x varchar2(64);
{
x := 'hello';
}
}
}
}


{ takes the place of THEN, LOOP, and BEGIN. If a { is seen where one of those tokens is expected, treat it like the equivalent block beginning keyword. When a } is seen, treat it like whatever block closing keyword is valid at that spot. When a { is seen with nothing special preceding it, treat it like a nested BEGIN.

When I think about it more deeply, I see potential problems around ELSE and WHEN clauses, where those keywords act as both the end of one block and the beginning of another, but anyway this whole idea is probably just a fantasy on my part.

One other Perlism that really struck home lately. Perl lets you get away with a comma after the last item within an array-style declaration. This seems like a minor point, but when writing programs that generate other programs (or anyway JSON-style data structures), it is sometimes difficult to give special treatment to the first or last data item. For example, with SQL or PL/SQL it's simple to write out 1000 INSERT statements, each ending with a semicolon. But let's say you have an XSLT transform with a FOR-EACH loop that produces an initialization statement for a data structure. The output goes

'item1',
'item2',
'item3',
...
'itemN',

and then you're left with that final trailing comma that you need to get rid of somehow. I'll often put the comma at the beginning of each line for just that reason, and to allow easier reordering of parameters and such. But then the first item is a special case. Think of how many times you have to introduce a Boolean variable to make the output slightly different for the first or last pass through a loop.

I used to think Perl's optional no-op trailing comma was just a flippant design choice, but now I think it was a very shrewd way to make dynamic programming easier. One that some other languages could emulate.

Tuesday, May 27, 2008

Doubleplusgood vi Shortcut for PL/SQL Coders

One thing that bugs me (no pun intended) about PL/SQL syntax is the lack of the ++ and similar operators. If you use long variable names, you're penalized by having to type them twice in a simple increment operation:

rows_processed := rows_processed + 1;

I use vim, a cross-platform vi clone, for editing PL/SQL code. vim has a lot of extensions that the old-school vi user might never realize. One such is Ctrl-P when in insert mode. It looks backward ("previous") for the last matching word, and fills it in. For example, you could write the line above by entering:

rows_processed := <Ctrl-P>

vim fills in the variable name again, then you continue entering "+ 1;".

If the variable name you want is not the very last word, you can enter one or more letters first and vim will fill in the last word that starts with that prefix. For example:

toggle_switch := initial_value - t<Ctrl-P>;

produces:

toggle_switch := initial_value - toggle_switch;

The matching isn't limited to the current line, so you could fill in variable, procedure, etc. names from much earlier in the file.

Vim has a whole set of such extensions. Some of them like 'ab' are familiar from the original vi days, but you can boost your productivity by brushing up on the newer techniques.

Wednesday, May 14, 2008

Query vs. CREATE TABLE AS SELECT

Half the time, I want to know some answer that can be determined with a query. The other half of the time, I want to start from that answer and use it as an intermediate step towards some larger solution. (That's not even considering the third half... :-)

So, I have a bunch of .sql files that do SELECT statements. Sometimes, I end up editing those .sql files into new ones that do CREATE TABLE AS SELECT... using the same query. I don't like the proliferation of .sql files that are slight variations of each other. That's the aspect of SQL coding that I would most like to see be object-oriented, the many SQL scripts that differ from each other only in terms of connection string, table name, or other minor detail.

Now, I know the WITH clause is there for pulling out a query to reuse its results as part of a bigger query. But sometimes the natural-feeling technique is to do CREATE TABLE AS SELECT colname... and then several other queries with clauses WHERE colname IN SELECT * FROM newly_created_table.

I had a sense there must be some shortcut to turning the original query into a CTAS construct. And there is! Remember how SQL*Plus will happily substitute the contents of a script anywhere you start a line with an '@' sign? Well, turns out you can use this technique to substitute a script as the last half of a SQL statement:

- Create a .sql file that has a single SELECT statement.
- Issue a CTAS statement like so:

create table t1 as
@script_with_select_statement

The trick is to make sure the SQL script has no blank lines, as they will cause SQL*Plus to stop processing the command prematurely. '--' comments are OK, so you can comment and format the script using -- lines where you would normally use blank lines.

CTAS is a bit of a blunt instrument, especially if the same situation comes up again and again. I may adapt this technique to use with CREATE GLOBAL TEMPORARY TABLE t1... and INSERT INTO t1 SELECT... constructs.

Monday, April 21, 2008

A CASE of Functional Programming

Chris mentioned in response to my post on functional programming that the CASE construct is useful in such situations. You can use CASE expressions either in PL/SQL subprograms or in SQL queries.

I agree, although I'm not sure if my agreement is more because of developer convenience or the pragmatic (performance) aspect. When you use a CASE expression, you skip the step of putting the IF/THEN test into its own function, so a purist might say it's bad that you end up duplicating code if you use the same CASE in several different queries. However, I have found CASE to be faster than I expected in queries producing big result sets, so I use it sometimes in preference to setting up a real function and making a function-based index.

As Chris mentioned, CASE lets you turn a PL/SQL function into essentially a one-liner, just a RETURN statement that covers all the possible bases. CASE expressions force you to use the ELSE clause, so you're guaranteed to return a value no matter how strange the input arguments. [See correction/digression at end.]

In a SQL query, CASE can take the place of a function call, either for transformation of the output results:

select
case region_code
when 'NL' then 'Newfoundland'
when 'ON' then 'Ontario'
when 'CA' then 'California'
else 'Some other place'
end
from ...

or for a condition in the WHERE clause:

select ...
where vacation_days >
case
when emp_type = 'VP' then 10
when years_service > 5 then 5
else 2
end
...

or (my favorite) in some clause where you wouldn't normally think of it, such as putting a dynamic ORDER BY clause into a query inside a PL/SQL procedure:

for item in (select ... where ...
order by case
when param = 'by_date' then date_added
when param = 'by_name' then last_name
else credit_card_number
end
)


Correction



As Boneist pointed out, my original assertion that CASE forces you to cover all possibilities is not correct for CASE expressions. For example:

select nvl(case when 1 = 0 then 'foo' end, 'bar') from dual;

returns 'bar', demonstrating that if the CASE expression "falls through" without matching any WHEN clauses, the result is null.

It is true for a CASE statement rather than an expression:

begin
case
when 1 = 0 then null;
end case;
end;

That will throw an exception, because of the lack of an else clause. I can never remember whether that behavior is for the statement or the expression, because it's the opposite of what I would like. If I'm selecting actions, I'd like a default ELSE condition of "do nothing", so I could leave it out. But if I'm selecting values, I'd prefer to be forced to consider all cases.

I fretted about the CASE statement vs. expression difference when I first wrote it up in the PL/SQL User's Guide. Is there enough cross-linking so that people will see both forms? Is there too much cross-linking so that people will get the expression and the statement mixed up?

Also, do we really need to use a special term "searched case" for one variety of syntax? I suppose it is a long-standing term, since I see it used in SQL Server and MySQL contexts too. I just can't imagine going into a code review and arguing with someone that they should or shouldn't use "searched case", without having to define it and clarify whether that meant it just dives into the WHEN clauses, or starts with CASE something.

Anyway, knowing about the statement as opposed to the expression does have some point in the functional programming environment. Let's say you wanted to rewrite the COMPARE function from my earlier post using CASE, but if something went wrong, it should raise an exception rather than return a value. Instead of coding that as a single statement starting "RETURN CASE ...", you'd write:

function compare(x number, y number) return number is
begin
case
when x > 0 then return 1;
when x < 0 then return -1;
when x = 0 then return 0;
-- If there is some unexpected combination of parameters, NULLs or NaNs or
-- what have you, need to handle that case also.
else raise some_package.weird_math_error;
end case;
end;

Sunday, April 20, 2008

A Word about Functional Programming

In all the back-and-forth about procedural programming and object-oriented programming, don't forget about another possibility: functional programming. It offers valuable lessons that can help with both hardcore SQL database operations, and the most modern and hip OO coding styles.

In some quarters, functional programming has a bit of a stigma from the Lisp days. In its purest form, functional programming does away with variables, turns even the most complicated program into a one-liner (with tons of brackets to nest properly), and has performance that varies depending on the characteristics of the language compiler.

These days, the introductory Comp Sci course at UC Berkeley (61A) uses Scheme as the teaching language. It's more accessible than Lisp, but still I found it more pleasant to follow along at home doing the exercises in Javascript.

On a pragmatic level, the upside of functional programming goes something like this. Once you write a function that can reliably read the color value for the pixel at coordinates (x,y), and another that can reliably write a color value for the pixel at (x,y), writing Photoshop just boils down to some data structures and a zillion calls to these functions. In a web development context, if you can make a popup window appear reliably for Firefox, Internet Explorer, Opera, Safari, etc., it doesn't really matter how unappealing the code underneath is, you can build more and more layers that call the same few functions. You can see the downside on many popular web sites; once people learned how to make ads appear and dance across pages, or pull code from other sites to track people's browsing behavior, they built on top of those functions without considering the ultimate performance hit or memory consumption to do all that for hundreds of browser pages/tabs at a time.

If you just want to introduce a bit of functional programming into your everyday life, you can do that without switching languages. Here are some ways you could use its precents to improve PL/SQL or other database code.

With functional programming, every program unit is a function (i.e. has a return value). This lets you map the outline of a program using essentially pseudo-code style. Instead of:

if x > y then x := 0; end if;

you would write something like:

if exceeds_limit() then reset_value(); end if;

When you've tried this before, you might have found it cumbersome to pass all the input values as parameters -- it's not any better to write is_greater_than(x,y) rather than x > y. But PL/SQL lets you write nested subprograms that access the variables of the parent subprogram, so you can make calls without parameters and let the subprograms get and set the values they need.

OK, it's true that reset_value() above is a procedure, not a function. But that's why I said full-on functional programming isn't strictly required. In traditional FP style, instead of doing assignments, you'd issue RETURN statements, and all the assignments would happen at outer layers of the program.

These pseudo-code outlines make it easier to implement bug-free versions of the named subprograms, since they're generally dead simple to write. You're also protected if you need to write several different variations of the main program, that call different combinations of subprograms or do things in a different order; you don't wind up duplicating all the code for the detailed logic.

Functional programming does instill a certain discipline to consider all the possible cases of execution:

function compare(x number, y number) return number is
begin
if x > 0 then return 1; end if;
if x < 0 then return -1; end if;
if x = 0 then return 0; end if;
-- If there is some unexpected combination of parameters, NULLs or NaNs or
-- what have you, need to handle that case also.
return null; -- Or raise an exception...
end;

When you're comfortable handling all the "halting conditions" for straightforward functions like that, it gives you confidence to tackle recursive calls. Typically, a recursive call will do a little bit of manipulation or testing to see if the function has enough information to return a final value, and if not, will call itself with a slightly modified (simpler) version of the input parameter. For example, N-1 for an integer parameter, or SUBSTR(param,2) for a string parameter. Something that trends towards zero, the empty string, the empty list, etc.

PL/SQL can do recursion, but its more complex data structures don't jump out as being ideally suited to recursive calls. For example, if you have a collection, it's not a simple matter to construct a new collection consisting of elements 2-N, to chop off the first one, or to "concatenate" collections to pass the results back through recursive calls. That's a common operation in doing parsing operations, and other scenarios often tackled through recursion. Perhaps with the enhancements to collection operations over the last couple of releases, recursive operations involving collections are now straightforward. If so, I'd love to hear from people who have done so.

SQL coders have been dealing with performance implications of functions for some time now, so they could swap war stories over beer with Lisp coders. If you aren't careful, a SQL query can make astronomical calls to expensive functions. But if you are careful, then you can be like one of those Lisp programmers who looks at any problem and says, "I could solve that in one line, since 1985!".

Oracle subselects and inline views are very good for doing "composition of functions", that is, producing a set of values and passing those values through a series of functions.

select line from
(
select howmany || ': ' || label line from
(
select howmany, label from
(
select count(*) howmany, title label from t1
group by title
)
order by howmany desc
)
)
where rownum < 11
;

In the example above, we're producing a large number of values in the innermost section, and in the outer layers we're sorting the results, concatenating different fields into a single string, and finally limiting the number of results that come out at the end. Along the way, we're renaming some of the expressions and columns of the inner layers to be more descriptive and generic.

Performance-wise, it's best to push the most expensive operations and the limiting step as far inside as possible. If the innermost query only produces 10 rows, it doesn't matter how much transforming and sorting we do in the outer parts, because those steps only happen 10 times.

For example, consider these variations on the same idea:

select distinct(substr(lower(colname),2)) from t;
select distinct(substr(lower(distinct(colname)),2));

Let's say the table has 10,000 rows. The first query is relatively inefficient, because we're running LOWER 10,000 times, then running SUBSTR 10,000 times on the results, then finally doing DISTINCT which might prune the final results down to 1000. The second query runs DISTINCT right at the beginning (the innermost DISTINCT), which prunes the results let's say to 1500; then LOWER gets run 1500 times, and SUBSTR gets run 1500 times, and then the second DISTINCT brings the final results from 1500 rows down to 1000 rows.

I actually ran the first query above in a moment of Lisp-like "see, I can do this in one line", and it processed 22,000 rows in a fraction of a second. In a real business situation, the functions might be things like IS_GOOD_CREDIT_RISK() or DESERVES_A_RAISE_THIS_YEAR(), things that might even query other tables, and anyway take a long time to compute for every row. In which case it really is important to make the result set small in the inner layers, and save the expensive calls for the outer ones.

A whole class of Oracle features go into the idea of optimizing queries that call functions.

There is the function-based index, where the database pre-computes the results of a function for each row, and stores the results in the index rather than as a separate column. The function must be declared DETERMINISTIC, that is, it is guaranteed to give the same result every time when passed the same input parameters. (The database already knows that built-in functions like LOWER() and SUBSTR() are deterministic, so you can build function-based indexes that incorporate calls to those functions.) This approach can be better than having a separate column, because what if the logic breaks down and that column doesn't get updated when the other columns do? The function results will get out-of-date. If you only need the function results for use in WHERE clauses, the function-based index protects against that case. It actually protects a little too well in my experience, because when you recompile the function, the index becomes invalid and prevents DML operations on the table. So you need to separate out the code for that function so it doesn't get recompiled except when you really change it. Either that or add an ALTER INDEX...REBUILD statement after the CREATE OR REPLACE statement for the function.

In 11g, there is the function result cache, which devotes some memory to the results from functions. The idea being that if you have a function that translates 'CA' to 'California', 'IA' to 'Indiana', etc. the database should be smart enough to keep those 50 or so values cached in memory and skip the actual call to your function. That way, you could query large amounts of data without displaying cryptic verbatim column values, and without paying a performance penalty for the transformations. I haven't personally tried the result cache, but I'm itching to -- got those DETERMINISTIC keywords locked in and ready to go!

When you make a nested expression like regexp_replace(lower(substr(to_char(...)))), that's known as "composition of functions", and although that seems like a straightforward notion, there are things you can do with it. If you take that whole expression and make it into a function:

procedure transform_it(what varchar2)
return varchar2
deterministic
is
begin
return regexp_replace(lower(etc. etc.

then you can reference this transformation from many queries; if you later realize there is a faster way to do it, you can rearrange the nesting and all those queries will speed up; and you can use a function-based index or the 11g function result cache to supercharge it.

Saturday, April 12, 2008

Deleting Data, Part 2

In a previous post, I listed a few different techniques for deleting Oracle data. Just to recap:

DELETE statement in SQL (or PL/SQL).
FORALL loop construct wrapped around a DELETE statement in PL/SQL.
TRUNCATE TABLE statement.
Temporary tables.

Different themes emerge. Some ways are more flexible (DELETE statement), some are faster for large amounts of data (FORALL, TRUNCATE), and some are more convenient (temporary tables).

As I thought about it some more, I realized there must be 50 ways to... delete your data. Here are some of the others. I'm sure there are plenty more that I haven't had direct experience with.

ALTER TABLE DROP PARTITION falls in between a normal DELETE and a TRUNCATE. You've specified some criteria to divide up the values in a large table, for example by year or by area code, and with one statement you can get rid of all the data in one of those buckets. Perhaps every Jan. 1, you remove the data for the 5-years-ago year. Dropping a partition simplifies the behind-the-scenes bookkeeping for the physical storage and the indexes, as opposed to issuing a DELETE in a regular table.

If you add a foreign key constraint to a column, you can include the ON DELETE CASCADE clause. The foreign key references another table, meaning you can't insert data unless the value in that column matches one of the key values in a different table. ON DELETE CASCADE means that when data is deleted from the other table, the matching key values are deleted from your table also, saving you from having to issue multiple sets of DELETE statements for interrelated tables.

You can add a foreign key constraint via the CREATE TABLE statement or the ALTER TABLE statement. Here's where it becomes difficult to dream up different "tasks" for different ways to achieve the same end. Putting a foreign key constraint in a CREATE TABLE statement suggests you are planning ahead and have constructed a detailed database design. Putting a foreign key constraint in an ALTER TABLE statement could mean that you are troubleshooting a problem once the database is in operation; perhaps it was overoptimistic to assume that every programmer would remember to DELETE from all the right tables. Or it could just mean that you were writing a setup script, found it easier to guess the right constraint syntax for ALTER TABLE than for CREATE TABLE, and so wrote a simple vanilla CREATE TABLE statement immediately followed by one or more ALTER TABLEs.

Personally, I use foreign key constraints a little less than I might like. In a data warehousing situation, such as with a search engine, I might be fiddling with or reloading data to get everything just right, and want to avoid unexpected side-effects in other tables. For example, if you reload the set of US state abbreviations into a 50-row table, you wouldn't want all the customer data for California to disappear because the 'CA' row was removed for a moment.

Another space-saving technique that applies to PL/SQL only is the SERIALLY_REUSABLE pragma. It lets the database free up memory space used by package variables, more frequently than happens by default. Then you don't need to feel so guilty about having your package code load the entire contents of a table into a PL/SQL collection data structure. But the memory gets freed up so often that the pragma seems only useful for specialized situations with long-running sessions.

Let's broaden the objective a little bit. Some of these techniques make data disappear without requiring action on the programmer's part, keeping bad old data out of query results and freeing up space on disk. Taking off the DBA hat and putting on the developer one, perhaps we wouldn't care quite as much about the disk space. What about techniques to make data disappear from queries, even if it doesn't really disappear from the database?

The CREATE VIEW statement can create a rolling window based on dates or some other value in the data stream. For example, you might use a condition WHERE date_col >= SYSDATE - 7 to only examine the last 7 days worth of data. Because Oracle dates include a time component, that would mean everything from 1:25 PM last Saturday up to 1:25 PM this Saturday, using the example of my own clock as I type this. If you wanted only full-day periods, you would use a condition such as WHERE date_col BETWEEN TRUNC(SYSDATE) - 7 AND TRUNC(SYSDATE). That would include everything from the start of last Saturday, up to the start of today, and would give you consistent results if you ran the same query again during the same day.

Other views might restrict queries to the last 1000 web page views or financial transactions. I think of such views broadly under the "deleting data" umbrella, because just based on the passage of time or activity within an application, older data stops appearing in query results without any inconvenience to the programmer.

Another technique along the same lines is the CREATE MATERIALIZED VIEW statement. This has 2 distinct uses: either you have a table normally accessed through a database link that is so slow you just want to have a local copy that you can query, or you have such an expensive/complicated query against a big table that you want to keep the query results in their own table so you can retrieve or subset them much faster. The "deleting" part comes because the materialized view can be automatically refreshed, either on a schedule or whenever the data in the underlying table changes. So as with other views, you can sit tight and old data will stop showing up in your queries.

I've started making some headway with materialized views, but am not yet a master of the refreshing techniques. That seems to require perusing both the SQL Reference and the Data Warehousing Guide. All those Data Warehousing examples with quarterly sales figures etc. don't resonate much with me when I'm learning about MVs, analytic functions, or partitioned tables.

I think those are all the related techniques that I use or consider using for such purposes. Of course, you can always manufacture other situations or add layers on top of these techniques. You could set up a scheduled job that deleted old data, you could have a trigger that acted like a foreign key constraint by deleting from a related table -- the possibilities are endless!

Tuesday, April 8, 2008

Unlikely Combos: Deleting Data

One of the challenges I see in understanding how the Oracle Database works is when features for similar tasks don't fall into neatly organized categories. It's obvious that this feature should be documented over here, and that one over there, but where can you discuss them side-by-side? Maybe the features are from different functional areas, and it's tough to find a single reviewer who can verify. Maybe the feature is being used in ways not originally intended, which prevents a technique from getting officially endorsed.

Well, that's where a blog comes in. Personal opinions all the way!

I'll write some posts with features that I think of as addressing similar tasks, falling along a spectrum of convenience, capacity, and complexity. First up: deleting data.

The basic method for deleting data is the SQL DELETE statement. Well duh! You can delete one or a zillion rows in a table, or a subset of rows based on simple or complicated conditions. Everyone knows that.

delete from the_table;
delete from the_table where col_value > 10;
delete from the_table where col_value in (select some_other_col from some_other_table);

But as the volume of data grows or the conditions get more tangled, other techniques start to look attractive.

Next up is the PL/SQL FORALL statement. You can use this loop-like technique in situations where normally you would issue a sequence of regular DELETE statements, because it's too complicated to construct an IN list or what have you. You'd put the construct inside an anonymous block, stored procedure, or trigger.

forall i in 1..the_count
delete from the_table
where col_value = collection_element(i);

In real life, your WHERE condition would probably be more complicated, with different clauses referencing the i'th value of different collections.

forall i in 1..the_count
delete from the_table
where col_value between lower_bound(i) and upper_bound(i)
and col_string not like '%' || string_pattern(i) || '%';

When I would normally write a little Perl script to spit out thousands of DELETE statements, instead I make the script generate an anonymous block that sets up one or more big PL/SQL collections, then runs a little FORALL-DELETE loop at the end. FORALL batches all the requests, so there's a lot less network traffic and other overhead. You can use the same technique to speed up large groups of related INSERT or UPDATE statements too.

Both DELETE and FORALL do all the nice Oracle behind-the-scenes bookkeeping that we all know and love. But sometimes, all you want is for a lot of data to be gone, ASAP. For example, in a data warehousing scenario, you might want to keep emptying a table and immediately re-filling it. That's where the TRUNCATE statement comes in. It skips some of the bookkeeping, so for example index statistics can go stale and the database doesn't reclaim space like it could. But it's fast.

truncate table the_table;

The opposite scenario is when you only have a little data to delete, but you want to delete it frequently. Maybe a stored procedure needs a little work area that isn't needed after the COMMIT, or after a web session finishes generating an HTML page and disconnects. That's when the temporary table comes in. It empties itself with no fuss, no muss, either when the session commits or disconnects, your choice.

Oracle temporary tables are faster to populate and clear than normal tables, although they don't totally avoid behind-the-scenes overhead. The key thing to remember, if you're a SQL Server expert, is that creating and dropping them is just as expensive as for a regular table, so you want to create a single temporary table as part of application setup, and let it live forever. Oracle takes care of deleting the data when you're through with it, and also keeps different sessions from seeing each other's data. So several sessions can be messing with the same temporary table at once, and each only sees the data it put there.

-- To clear the table as soon as the transaction ends...
create global temporary table t1 (scratch_value number) on commit delete rows;
-- To clear the table as soon as the session ends...
create global temporary table t2 (key_value varchar2(16)) on commit preserve rows;

OK, back to TRUNCATE. Sometimes, you want a lot of data to disappear quickly, but then the lack of bookkeeping bites you later. I've had tables where I truncated a couple of hundred thousand rows before loading new data, and then anything involving the new data was slow. I could go through the process of generating new stats, but then the whole operation takes as long as using DELETE for the whole table. Sometimes -- again usually in data warehousing situations where nobody else is using a table -- the practical thing to do is DROP TABLE followed by CREATE TABLE to recreate it.

Another kind of table that you can think of like a temporary table is the external table. The data comes from an outside text file, so you can imagine that the data ceases to exist (within the Oracle sphere of control, anyway) as soon as you finish querying it. It takes up space on the filesystem, but not in the normal tablespace and datafile regime. I haven't personally used external tables, but I've considered it for data that I thought of as "read once and forget".