Determining Deterministic
December 16, 2010 1 Comment
This is a bit weird.
I’m putting together a couple of articles on DETERMINISTIC functions – this is a landing page for those – but it’s a feature I never use unless I’ve got a function-based index.
Hopefully, I’ll go over some of the reasons why.
But by crickey it’s really highlighted to me again how hard it is to come up with isolated, consistent, simple demonstrations. It’s like opening Pandora’s box as you come across little oddities and influencing factors.
Anyway… for what it’s worth:
- Determining Deterministic I – Introduction: Some blurb about it first being for FBIs only
- Determining Deterministic II – Simple Performance Benefits: Behold! it can reduce function calls
- Determining Deterministic III – Not So Simple Performance Benefits: Suprising influencing factor, different from scalar subquery caching?
- Determining Deterministic IV – Again Not So Simple: Strange variations in function call reductions
- …More to come (placeholders below otherwise i’ll forget…
- …revisit part III – fetchsize plus Order by…
- …revisit part IV – hash collisions?…
- …in relation to Virtual Columns…
- …vs Scalar Subquery Caching…
- …vs Result Cache…
- …Just rewrite the logic inline anyway …
- …Read Consistency oddities…
- …So it’s only really good for FBIs then…
Pingback: Log Buffer #210, A Carnival of the Vanities for DBAs | The Pythian Blog