Re: When to store data that could be derived

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.

> 
> 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