Chunking tables 6: JOIN solution

At last, here is the first complete solution to the problem of dividing a table into 12 chunks with equal numbers of blocks. James Su and I proposed this solution on OTN, but without the "filtered_extents" optimisation.

Would You Like to Load a Data Vault like a Master?

If you are about to embark on a Data Vault project this year, and not quite sure the best way to load your data into a Data Vault efficiently, I have a great opportunity for you. Dan and Sanjay have been hard at work re-vamping the Learn Data Vault site and are now ready to […]

What the SQL? (History)

Oracle SQL Developer has 2 client-side logging mechanisms for capturing SQL that is executed:

  1. SQL History
  2. Statements
I tend to keep both panels hidden or closed until I need them. The recording happens whether they are visible or not on the desktop.

I tend to keep both panels hidden or closed until I need them. The recording happens whether they are visible or not on the desktop.

SQL History

This is the SQL that you have executed in a SQL Worksheet. It’s also any scripts you have executed in a SQL Worksheet. Ran something the other day and forgot to (more...)

Chunking tables 5: chunk boundaries

We still want to split a table into 12 chunks with equal numbers of blocks, so we can access each chunk using a rowid range. We have the extents we need and now we need to calculate the exact chunk boundaries.

Chunking tables 4: Histograms

After "only" 3 posts on chunking tables, we now have the raw materials for breaking up a table into 12 chunks with equal numbers of blocks, and for getting ROWID ranges that let us access the data. Now let's chunk!

Chunking tables 3: working with blocks

So far we've decided to access a table chunk by chunk, each chunk containing one twelfth of the tables's blocks. But first, how do we access data in a chunk of blocks? How do we even know what blocks a table has?

Show all views including a specific string

Seems to be a simple problem and easy to fix. Actually it is not because if you try one of these examples then you will fail:
Mehr »

Chunking tables 2: Requirement

As I mentioned in my previous post, the basic idea is to divide a table into 12 "equal chunks", each "chunk" being a rowid range that covers the same number of blocks (give or take 1). What does this mean? Why do this? Why divide by blocks and not rows? Here are my thoughts.

Primarys Keys and their supporting indexes

Sometimes things just happen which makes you realise that stuff you thought all experienced DBA’s knew about isn’t common knowledge. I suppose it’s a side effect of working with the same evolving product for over 25 years at dozens of clients, when the colleague you are talking to has a mere decade of experience at fewer companies (and therefore less diversity of challenge).

Today I noticed that a release was creating a table, then an (more...)

Oracle Spatial (Teil 3) – Entfernung von Punkten berechnen

Im heutigen Blogpost möchte ich kurz anhand eines Beispiels aufzeigen, wie Sie die Entfernung von SDO_GEOMTRY Punkten berechnen können. Hierbei gehe ich auch konkret auf das Problem des Verbindens von zwei Datenmengen anhand einer definierten Entfernung ein.
Mehr »

ORA_ROWSCN – When Was My Record Commited

I was going to do a follow-up post to my post on USERENV(‘COMMITSCN’) just to describe the slightly better known but still overlooked (and possibly more useful) ORA_ROWSCN – but I don’t need to!

As Neil Chandler has done this excellent post on it

Go and look at that post, it has all the information and detail you were actually looking for.

However, for any of you search-engine-seekers who can’t face the pain of following (more...)

When did I update that row?

I had a requirement the other day to understand when some data had been changed, but there was no column on the table which showed this.

So how do I find out? Well I could go off mining redo and lots of other time consuming and exotic things, but you can use the Oracle Pseudocolumn ORA_ROWSCN. This gives the SCN assocaited with each row. Well, actually it usually doesn’t. It does not show when the (more...)

Joining Temporal Tables 4: ranges

If you have temporal tables with date ranges, and you are sure those date ranges cannot overlap, then you can join them using the technique in my previous post - with a few important changes!

Getting Your Transaction SCN – USERENV(COMMITSCN)

A few days ago I was introduced (or re-introduced) to USERENV(‘COMMITSCN’) by Jonathan Lewis. This is an internal function that allows limited access to the SCN of your transaction.

I was trying to find a way to get the actual commit SCN easily as it struck me that Oracle would have it to hand somewhere and it would be unique to the change and generated very efficiently. I could not find anything to do it (more...)

Joining Temporal Tables 3: Gaps

This is my third post about temporal tables that don't allow overlaps. I'm going to add support for temporal gaps, which will also allow objects to stop existing at some point.

Importing XML file with invalid character 22 (U+0016)

I have to import a set of XML files from time to time. Most of those XML files can be imported with out any problems. But at least one file includes a special character U+0016 which occurs randomly some where inside the file.

When I try to import that file I get this ORA- error message:
ORA-31011: XML-Parsing nicht erfolgreich
ORA-19202: Fehler bei XML-Verarbeitung
LPX-00217: Ungültiges Zeichen 22 (U+0016)
Error at line 39409 aufgetreten

Mehr »

Joining Temporal Tables 2: handling nulls

In my previous post, I showed a way to implement and join temporal tables when there are no gaps, overlaps or null values. Let's see what happens when I allow null values.

Snowflake SQL: Making Schema-on-Read a Reality (Part 2)

n this post I will show you how to access an array of data within the JSON document and how we handle nested arrays. Then finally I will give you an example of doing an aggregation using data in the JSON structure and how simple it is to filter your query results by referring to values within an array.

How an Oracle error can send you the wrong way…

The Twelve Days of SQLT: Day Four: Nobody Did It

Previous installment: Day Three: Just a Mess Without a Clue

I know a funny little man,
As quiet as a mouse,
Who does the mischief that is done
In everybody’s house!
There’s no one ever sees his face,
And yet we all agree
That every plate we break was cracked
By Mr. Nobody.

—Unknown author

The purpose of a SQLT report is to collect all the information that could possibly shed light on a problem (more...)