On 3/24/19 3:05 AM, Frank wrote:
On 2019-03-24 9:25 AM, Ron wrote:
On 3/24/19 1:42 AM, Frank wrote:
Hi all
As I understand it, a general rule of thumb is that you should never
create a physical column if the data could be derived from existing
columns. A possible reason for breaking this rule is for performance
reasons.
I have a situation where I am considering breaking the rule, but I am
not experienced enough in SQL to know if my reason is valid. I would
appreciate it if someone could glance at my 'before' and 'after'
scenarios and see if, from a 'gut-feel' point of view, I should proceed.
[snip]
Sure the second query joins a lot of tables, but is pretty straightforward.
What REALLY worries me is whether or not the query optimiser would look
at the WHERE CASE, run away screaming and then make it use sequential
scans. Thus, even query #1 would be slow.
I had not realised that. I hope someone else chimes in on this.
In every DBMS that I've used, the lside (left side) needs to be static (not
"a" static) instead of variable (like a function).
For example, this always leads to a sequential scan:
WHERE EXTRACT(DAY FROM DATE_FIELD) = 5
Is this a historical data set that's never updated, or current data
that's constantly added to?
It is the latter - current data constantly added to.
Frank
--
Angular momentum makes the world go 'round.
