Is this the largest SQL query ever written?

Before I get knocked out of the park for this, check out Tom Kyte’s advice on using SQL where high performance is required. Switching to procedural PL/SQL is an overhead and when the requirement is to price 100 calls a second on a “noughties” mid-range Solaris system, this is one solution.

I was Oracle lead at the time this was written, and there was no-one else to take on this particular critical system component.

Calls may be rated by tiered tariffs, or by an uplift multiplier and other factors on the band unit tariff.

The usge inner query block retrieves the usage, band unit tariff for the normal portion of the call and the long distance portion of the call.

Analytic functions are then used to apply the tiered tariff prices if any. It would have been possible to reduce the size of the query by not separating long-distance and normal (local) call data into separate columns in the result set.

However, the separation to makes the separate calculations more explicit and avoid potentially having a sensitive PARTITION BY clause where a small change could cause local and long distance to be aggregated too early and make the results nonsense.

An overview of the query follows.

Levels of nesting for each call follow (from the inside out – i.e. “upside down” as far as the query’s concerned):

1) Get long-distance usage and

a) Untiered tariff rates OR

b) Tiered tariff rates

2) Get normal (local) call usage

a) Untiered tariff rates OR

b) Tiered tariff rates

3) Evaluate usage against tiers or apply untiered tariff rates to normal and long-distance usage

4) Aggregate at the level of normal and long-distance usage

5) Aggregate normal and long-distance information into a single result record

The largest query ever?

Categories Oracle database
%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close