Hi. Quick question around the runstats taken for xml columns. My scenario is this:
TableA has 60MM rows with an ID column, and an xml column housing the following sample document:
Runstats were run, the syscat knows there are 60MM rows, and the index on the ID is working great.
Now I implement some new tags turning the above into a more complex document, for example:
The SELECT looks good (full-XMLpath), the explain looks good (joins). There are around 2MM row updates per day depending on which ID comes in.
After Day 4 or so we start seeing higher cpu during the read Stored Proc which uses this new XML-document. We're presuming it's because the new document structure isn't understood, so the DB needs to scan the document. Therefore we're planning to rerun the Runstats today.
The question is:
- Does the optimiser 'know' the new document structure at a table-level (ie so all future updated rows are automatically catered for) ??
- Or is it row-by-row, and therefore I would need to run my runstats every other day (or weekend) until I'd updated all 60MM records.
Thanks in advance
PS: Here are some EXPLAIN stats which I think answers the fact that some of the document is understood by the optimiser (ie instant find) but some of it isn't (ie document-scan find)?
| | XML Doc Navigation
| | | Navigator is
| | | | /$CONTEXT_NODE$()/child::element(AcctProf)
| | | | /child::element(Lst8wkMaxAmt)(: Output Node Sequence Reference :)
| | | | | XML Doc Navigation
| | | | | | Navigator is
| | | | | | | /$CONTEXT_NODE$()(: Output Node Sequence Reference :)(: #Residual Xpath Predicates = 1 :)
| | | | | | | /unknown::unknown()