Oracle Scratchpad

Public Appearances

 

September 2022

Switzerland / Online

I’ve been asked to supply a couple of abstracts for the annual Trivadis Performance Days 2022. I’ve suggested:

3 Decades – 3 principles

I’ve been advising, trouble-shooting and giving training on Oracle-based systems for around 30 years, and time after time I’ve seen problems created, or solutions missed, because three fundamental questions haven’t been addressed properly:

    • What do the users need to do?
    • What will the data look like?
    • What can the Oracle RDBMS do?

For new systems the first question tends to be addressed fairly well, but the 2nd and 3rd are often poorly addressed. Most of the time, however, developers and DBAs spend on Oracle systems seems to be about working around the mistakes of existing systems – so the questions are more likely to be re-ordered and modified slightly to:

    • What does the data look like? (that’s an easy one)
    • What do the users really need to do? (that’s probably a lot clearer)
    • How can the Oracle RDBMS help? (maybe considered a little more carefully)

In this session I’ll start at the top level with these questions and talk about why these are the fundamental 3 things that need to be addressed – working my way from the business requirement to the mechanics of the database and the problems of scaling and concurrency as I do so, with a minimum of high-tech detail.

The Big Delete

Your database has been in operation and growing for years, and one day the management tells you that it’s time for you to delete (or archive) all the transaction data that’s more than 7 years old. Where do you go from there?

One common response is to post the question “How do I delete X million rows from my table?” on one of the Oracle forums or listservers; but there’s no generic answer to that question. The amount of background information you need before you can devise a good strategy is significant, and it’s very easy to run into nasty surprises and big mistakes because your planning didn’t allow for all the constraints imposed by the nature of “the system”.

This will be a chatty presentation covering as many issues as I can remember about “the big delete” and why, ultimately, the DELETE command is only a minor detail of the entire operation. There will be no rocket-science and no arithmetic, although there were will be a few technical terms (such as “redo”, “referential integrity”, “materialized views” that everyone should know) that will get a mention.

 

 

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: