Explain Plan *itself* running slowly
There is an anchor table with hundreds of columns. There are three projections:
1) A super projection with an equivalent number of columns.
2) A projection with a few columns (deliberately) missing from the anchor tables, along with several expression-based columns.
3) A projection similar to #2, but it is also a top-k
The sample data is 1 million rows long.
The super projection has statistics collected on all columns. #2 and #3 have no statistics, as it is one of the listed restrictions in the manual (no stats possible on projections with expressions).
A simple count() against the anchor table runs in under a second. The explain reveals that the super projection (#1) is used.
A simple count() against projection directly (#3) takes 45 seconds. The explain plan ITSELF takes 44+ (!!!) seconds to run (and reveals that obviously that specific projection is being used because it was specified directly). When not using expression columns, this problem goes away.
Several trials in different order of the above scenarios exhibits very little variation from the times I've provided.
In other RDBMSs, there is the concept of tuning how much effort is spent on the planning stage itself. However, the magnitude of the planning effort here doesn't seem appropriate to describe 44+ seconds of planning. Explain VERBOSE adds detail of no consequence that I can see.
Do you have either:
1) Ideas to describe this (apparent) massive planning overhead
2) Ideas for how to provide insight into where the delay is.
Have people actually used expression columns successfully without incurring massive planning overhead?
Thanks in advance,
Alex
Comments
Do you have support contract ? if yes please open a case. If not, can you please share file with table defination and projection defination with me . You can send it to me as a message by clicking on my name tag.