Oracle Scratchpad

June 26, 2012

Philosophy 17

Filed under: Oracle,Philosophy — Jonathan Lewis @ 5:17 pm BST Jun 26,2012

You need to understand the application and its data.

A recent request on OTN was for advice on making this piece of SQL run faster:

delete from toc_node_rel rel
where   not exists (
                select  *
                from    toc_rel_meta meta
                where   rel.rel_id = meta.rel_id
        )
;


Here’s a very short list of questions to focus the mind on possible solutions. There is a column called rel_id in both tables; columns with “id” in the name tend to be a little bit special, so are these columns:

a) the primary key of one table and foreign key to the other (if so which way round)
b) the primary keys of both tables
c) both foreign keys to a shared primary key table

Until you know the answer to these questions you can’t really make progress in working out the best way to implement the requirement. And even when you have the answers that’s still only one more step in the right direction, and the precursor to the next set of questions – like “have the constraints actually been declared and enabled, are any foreign key constraints allowed to include nulls, are any primary key constraints enforced by non-unique indexes”, and we still haven’t got around to absolute data volumes, clustering patterns, and expected volume deleted.

Footnote:
You may want to argue about whether the possibilities listed about should or shouldn’t exist in a properly designed system. Feel free to do so; just because something is wrong in theory doesn’t stop it from happening in practice.

Footnote 2:
For a more concrete impression for the short list of questions:

For the PK/FK option (a) – imagine a very simple order processing model, are we trying to delete products for which there are no orders, or orders for which we don’t stock the product (which shouldn’t have got into the system anyway if we had implemented it properly).

For the PK/PK option (b) – imagine our simple order processing system has a seperate delivery table which clones the PK of the order table, are we trying to delete deliveries for which there is no order (again they should not exist, but who said this system had been designed and implemented well – see (a)).

For the shared FK option (c) – imagine a different order processing system that allows multiple order lines per order and clones the PK of the order line to produce a row in a deliveries table, are we trying to delete deliveries where there are no order lines for the corresponding order (yet another possibility from a badly designed and badly implemented system – but I’m sure I’m not the only one to have seen systems and code like this).

1 Comment »

  1. [...] You need to understand the application and its data. The undeniable philosophy by Jonathan Lewis. [...]

    Pingback by Log Buffer #276, A Carnival of the Vanities for DBAs | The Pythian Blog — June 29, 2012 @ 6:01 am BST Jun 29,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,528 other followers