On Sun, Mar 24, 2019 at 9:05 AM Frank <frank@xxxxxxxxxxxx> 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.
There are a few other things to note here.
1 If the data is frequently used in its derivative form, putting it in a function helps, and
2. You can index the output of the function which means you run it on insert/update time and can often avoid running it on selection time if it is just a part of the where clause.
In my experience usually we have used trigger-updated functions when the field values are very large or expensive, and may need to be part of the column list, and functions with functional indexes when we don't need to ever put them in the select column list or where the columns are small and easy to calculate.
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
