In the previous article in the joins series we compared query performance between a third normal form schema and the same schema denormalized to second normal form. We then extended it the example so our denormalized schema was in just first normal form.
Funny that oracle can easily cast ‘nan’,’inf’,’infinity’,’-inf’,’-infinity’ to corresponding binary_float_infinity,binary_double_nan, but there is no any format models for to_char(binary_float_infinity,format) or to_binary_***(text_expr,format) that can output the same as to_char(binary_float_infinity)/to_binary_float(‘inf’) without format parameter:
Continuing the series on joins, I’m going to look at denormalization. This process reduces the number of joins necessary to return results for a schema.
One of the big arguments against normalizing data is “for performance”. The process of normalization creates new tables as relations are decomposed according to their functional dependencies. This means (more) joins are necessary to return the same results.
In previous posts about caching mechanism of determinstic functions I wrote that cached results are kept only between fetch calls, but there is one exception from this rule: if all function parameters are literals, cached result will not be flushed every fetch call.
Little example with difference:
It’s possible to get an error after granting privileges to an external file system. One of those errors is tedious to resolve until you understand the rules governing Java NIO file permissions.
You grant privileges to external file systems as the
sys user with the
grant_permission procedure of the
dbms_java package, like
x ^ y = y ^ x.
(x ^ y) ^ z = x ^ (y ^ z).
x ^ (x v y) = x.
x v y = y v x.
(x v y) v z = x v (y v z).
x v (x ^ y) = x.
Joins appear (to me) to be getting a lot of bad press recently. In discussions I’ve had and articles I’ve read many give the position that joins are somehow inherently bad and to be avoided at all costs.
I’ve never been entirely convinced by the “joins are bad” arguments however. Partly because there’s few concrete cases actually demonstrating the (additional) cost of joins. Instead discussions tend to be hand-wavy arguments around extra CPU cycles used or something to that effect.
On March 17, 2014, I became an employee of Oracle Corporation for the second time. My first round with Oracle started in August 1987. My second son, Eli, was less than a year old. I'd been incredibly bored with my consulting gig, which consisted of babysitting a reporting system on a DEC10 "mainframe", based on a flat-file database – but a database.
So I checked the Help Wanted pages (no Internet, no smartphones, no LinkedIn) and came across an ad from Oracle Corporation. It contained the word "database", so I figured: "Why ?"
The following update forms the basis of a DIY connection pool. It is intended to allocate an unused session to whoever requests it:
A few years ago, Alberto Dell’Era blogged about product prices with overlapping date ranges; “on an overlapping range, the strongest priority (lower number) wins.” His analysis, explanation and solution are all excellent. I’m revisiting the subject to suggest a few improvements (and mention MATCH_RECOGNIZE yet again). To explain more easily, Alberto uses letters (a,b,c,d,e) instead […]