Dave, yes, the last idea would work fine actually ... an interesting idea might be to define get_path as stable recursively rather than loop up the hierarchy the way it does.
That way after the first file path calculation the recursive call to get the parent path would return the cached value ...
But still the call overheads themselves will get you to some degree.
Best is your suggestion of calling get_path on the folder before doing the folder chunk query then concatenating the folder name and file name in the big select rather than call get_path.
That would give us speed nearly identical to the 4.6.3 query I believe. And this would work for Oracle, too (which doesn't have the equivalent of "stable" attributes for PL/SQL functions that I'm aware off).
BTW the cost is mostly proportionate to the depth of the tree, not the total number of folders.
If you or Ola want to work on this that would be great! I said I'd measure it, not fix it :)
If you guys whack at it, though, I'll be more than willing to rerun my tests using whatever you guys do come up with.
Just remember it needs doing for both DBs.
