This discussion is archived
4 Replies Latest reply: Sep 28, 2013 2:14 AM by VladimirSitnikov RSS

4EA2 access and filter predicates are missing in both explain and autotrace views

VladimirSitnikov Newbie
Currently Being Moderated

Before 4EA2, "predicates branch" in settings acted as "show predicates as columns" vs "show predicates as branch in explain tree".

I always change the mode to "show prediactes as columns" since it is the only way to see/understand 50-100 lines long explain plan.

 

In 4EA2 "predicates in columns" mode is broken in both explain plan and autotrace views.

  • 1. Re: 4EA2 access and filter predicates are missing in both explain and autotrace views
    Vadim Tropashko Pro
    Currently Being Moderated

    You can collapse predicates with context menu item "collapse predicates&projection" available over any tree node. Or are you implying that you prefer reading non-formatted complex predicate expression stuffed into varchar over parsed predicate rendered as expression tree?

  • 2. Re: 4EA2 access and filter predicates are missing in both explain and autotrace views
    VladimirSitnikov Newbie
    Currently Being Moderated

    I prefer varchar over expression tree:

    0) Even after "collapse predicates&projection" each predicate node still consumes a line.

    1) Typical predicate is either simple equality or 1-2 AND/ORs. That is very readable. I see no point in expanding say "in-list iterator" predicates.

    2) Long AND/OR chains are better understood in the source SQL itself. In case of long AND/OR chains I typically locate relevant SQL and go from there (yes, views make things complex, but still the only way to go)

    3) If the tool made syntax highlighting in varchar2 view that would be even better. Our company's internal DB tool does highlight predicates (e.g. numbers and text literals with different colors)

    4) Complex expressions (e.g. condition for a functional based index) is not a penny better in expression tree view.

    5) Current computer displays tend to be wide, thus horizontal space is cheaper than vertical. I find completely flawed the design when a complete node is dedicated just to read "access predicates". For instance, "simple unique scan" would consume _two_ rows. In most of the cases you _do_ know what the access predicate for a particular unique key is and the only thing predicates are required is to get the relevant aliases.

  • 3. Re: 4EA2 access and filter predicates are missing in both explain and autotrace views
    Vadim Tropashko Pro
    Currently Being Moderated

    Those are valid concerns; however, one idea invalidates all of them:

    Predicates are relations. Therefore, consistent design would designate a tree node for each predicate the same way as a node is dedicated for table access. Predicate evaluation can be ordered the same spirit as joins are ordered, because effectively table join is the same as predicate conjunction and they can be even mixed up together.

  • 4. Re: 4EA2 access and filter predicates are missing in both explain and autotrace views
    VladimirSitnikov Newbie
    Currently Being Moderated

    Vadim,

    Are you kidding?

     

    Please note that design decisions should be made based on the real use-cases.

    Mathematic purity is not enough to justify visual layout.

     

    If the main use-case was "to split input SQL into relations" the approach of "tree node for each predicate" would make sense.

     

    However, the main purpose of using _autotrace_ and/or explain plan views is to analyze the used tables/indices and the order of resulting index/table scans.

    For that purpose all my justifications 0..5 apply.

     

    VadimTropashko wrote:

    Predicates are relations. Therefore, consistent design would designate

    Does not apply here as we are discussing engineering use-cases.

    VadimTropashko wrote:

    table join is the same as predicate conjunction and they can be even mixed up together.

    This does not apply as Oracle DB does not mix up joins and predicates.

     

     

    Please, feel free to put justifications for "tree expression" mode (as well as drawbacks of "varchar" mode), however I beg on your understanding of "sql development and/or performance engineering" as a main use-case of a current discussion.

     

    I am ready to accept "mathematicians need expression trees" as I am just not familiar with their cases.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points