|
I changed the query. Both should return the same amount of tuples. Where there were JOIN, changed to KEY JOIN (based on the concept of using the KEY JOIN). Some fields that the type CHAR that receive only numeric values have changed for the type INTEGER. (I turned off the primary and foreign keys and created again using the new fields of INTEGER). The indices were also deleted and re-created using the field of type INTEGER. The WHERE clause has not changed. Remains the same filter. I changed the query code and grabbed the implementation plans and would like to make a comparison between them.
How to correctly analyze the execution plan in ASA-9? before:
after
|
|
Trivially, we can tell you where the differences are between the graphical representations:
But without the actual plan details ("with statistics") on each node, it's difficult to comment on why this is the case, simply by looking at the graphical representation. You need to capture the SQL statements and plan with statistics. Can these full plan files (*.xml / *.saplan) be provided? Do both of these queries really return the same set of results? Or are they looking at different data sets? Did you change the actual SQL statement, or just the predicates for your WHERE clause? Well, it might be noted that it's just "taking up more relative execution time" - without knowing the timing details, we can't even tell which query is faster (which you have already made very clear, there's no doubt about that!)...
(04 Feb '13, 10:56)
Volker Barth
Replies hidden
I changed the query. Both should return the same amount of tuples. Where there were JOIN, changed to KEY JOIN (based on the concept of using the JOIN KEY). Some fields that the type CHAR that receive only numeric values have changed for the type INTEGER. (I turned off the primary and foreign keys and created again using the new fields of INTEGER). The indices were also deleted and re-created using the field of type INTEGER. The WHERE clause has not changed. Remains the same filter.
(04 Feb '13, 11:49)
Walmir Taques
|
|
Please post the plan files. To save a plan, use dbisql Plan Viewer, choose 'node and detailed statistics' ( the query will be executed), generate the plan, use Save as to save the plan in a file. The plan file contains many types of statistics, beyond your pictures above, which can be used to analyze the query execution as well as the query optimization process. |
