March 16, 2021 at 12:00 am
Comments posted to this topic are about the item The CTE Scan
March 16, 2021 at 10:24 am
I have found many who firmly believe a CTE actually creates a volatile table in tempdb and then reads from it.
i cannot figure out where they got that idea from.
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
March 16, 2021 at 11:25 am
Why would it read twice?
March 16, 2021 at 12:15 pm
Why would it read twice?
the CTE is something like a single use virtual view.
Given that the final select uses a UNION ALL, it will SELECT from the CTE, then SELECT from the same CTE again.
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
March 16, 2021 at 1:09 pm
I have found many who firmly believe a CTE actually creates a volatile table in tempdb and then reads from it.
i cannot figure out where they got that idea from.
If you are in the world I live in you can sometimes confuse what different optimizers do with this scenario. About 80% to 90% of the time, depending on the query, Oracle will read the table only once creating a virtual table in memory (or temp) and use that for each reference to the CTE. I did a quick test to do something similar to this query and the Oracle optimizer did scan the table twice, but add a where clause to the CTE and it created the virtual table.
March 16, 2021 at 2:35 pm
Fun thing - it makes no difference if you use UNION or UNION ALL. Changes the result set for sure, but the estimated execution plan remains the same. Performance with the UNION will be worse for obvious reasons.
I expected that the answer was what it was (not giving it away in case someone reads this before reading the QOTD) only because CTE's can be replaced pretty easily with nested selects. If you rewrite the query to be nested selects, it makes it easy to see what is happening and how many table lookups you need to do. CTE's are just (in my mind) a more "pretty" and easier to read version of a nested select (plus can be a lot easier to debug).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply