7 Replies Latest reply on Nov 11, 2019 5:11 PM by Vadim Tropashko-Oracle

    Column Alias Alignment in SQL Developer

    3974094

      Hi,

       

      SQL Developer is not formatting the column alias properly, suggest me to correct it please.

       

       

        • 1. Re: Column Alias Alignment in SQL Developer
          thatJeffSmith-Oracle

          please paste the actual code too...

          • 2. Re: Column Alias Alignment in SQL Developer
            3974094

            Actual code -

             

            select a.dbname, a.sql_id, a.execs, a.before_avg_etime, a.after_avg_etime, a.norm_stddev, max(sqlmaxb.begin_interval_time) recent_exec, a.result

            from

                    DBA_HIST_SQLSTAT  sqlmaxa,

                    DBA_HIST_SNAPSHOT sqlmaxb,

                    (

                      select

                      trim('&_dbname') dbname, sql_id, execs, before_avg_etime, after_avg_etime, norm_stddev,

                             case when to_number(before_avg_etime) < to_number(after_avg_etime) then 'Slower <<<' else 'Faster' end result

                      from (

                      select sql_id, sum(execs) execs, sum(before_execs) before_execs, sum(after_execs) after_execs,

                             sum(before_avg_etime) before_avg_etime, sum(after_avg_etime) after_avg_etime,

                             min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev,

                             case when sum(before_avg_etime) > sum(after_avg_etime) then 'Slower' else 'Faster' end better_or_worse

                      from (

                      select sql_id,

                             period_flag,

                             execs,

                             avg_etime,

                             stddev_etime,

                             case when period_flag = 'Before' then execs else 0 end before_execs,

                             case when period_flag = 'Before' then avg_etime else 0 end before_avg_etime,

                             case when period_flag = 'After' then execs else 0 end after_execs,

                             case when period_flag = 'After' then avg_etime else 0 end after_avg_etime

                      from (

                      select sql_id, period_flag, execs, avg_etime,

                      stddev(avg_etime) over (partition by sql_id) stddev_etime

                      from (

                      select sql_id, period_flag, sum(execs) execs, sum(etime)/sum(decode(execs,0,1,execs)) avg_etime from (

                      select sql_id, 'Before' period_flag,

                      nvl(executions_delta,0) execs,

                      (elapsed_time_delta)/1000000 etime

                      from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

                      where ss.snap_id = S.snap_id

                      and ss.instance_number = S.instance_number

                      and executions_delta > 0

                      and elapsed_time_delta > 0

                      and ss.begin_interval_time <= sysdate-&&days_ago

                      union

                      select sql_id, 'After' period_flag,

                      nvl(executions_delta,0) execs,

                      (elapsed_time_delta)/1000000 etime

                      from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

                      where ss.snap_id = S.snap_id

                      and ss.instance_number = S.instance_number

                      and executions_delta > 0

                      and elapsed_time_delta > 0

                      and ss.begin_interval_time > sysdate-&&days_ago

                      )

                      group by sql_id, period_flag

                      )

                      )

                      )

                      group by

                         sql_id,

                         stddev_etime

                      )

                      where

                         norm_stddev > nvl(to_number('&min_stddev'),2)

                         and (after_avg_etime - before_avg_etime) > 100

                         or max_etime > nvl(to_number('&min_etime'),.1)

                         and before_avg_etime > 1

                         and after_avg_etime > 3600

                    ) a

            where a.sql_id = sqlmaxa.sql_id

            and sqlmaxa.snap_id = sqlmaxb.snap_id

            group by a.dbname, a.sql_id, a.execs, a.before_avg_etime, a.after_avg_etime, a.norm_stddev, a.result

            --where result = 'Slower'

            order by

            8,6

            --   norm_stddev

            /

            • 3. Re: Column Alias Alignment in SQL Developer
              Vadim Tropashko-Oracle

              I see 2 bugs there. First, column aliases for function expressions are not aligned. It can be fixed in custom format like this:

               

              | [id) expr  & [id+1) 'THEN' & [id+2) expr & [scope) case_expression

              | :alignTabColAliases  & [id) column & [id) expr & [id^) select_term & [id+1) as_alias & [scope) select_clause

              | :alignTabColAliases  & [id) query_table_expression & [id+1) identifier & [scope) from_clause

               

              The output:

               

              SELECT

                  sql_id                         a,

                  'Before'                       period_flag,

                  nvl(executions_delta, 0)       execs,

                  ( elapsed_time_delta ) / 1000000 etime

              FROM

                  dba_hist_sqlstat   s,

                  x                  y

               

              Next, with leading comma there is a shift by 2 spaces. This is because the comma is aligned with the first most column and I struggle to figure out what formatting rule can do better...

              • 4. Re: Column Alias Alignment in SQL Developer
                Vadim Tropashko-Oracle

                Here is how to fix 2 space offset in version 19.2.1. Add the following to the very end of the format rules:

                 

                additionalSpaces:

                    :breaksBeforeComma & [node) select_term & ![node-1) ','

                  | :breaksBeforeComma & [node) table_reference_or_join_clause & ![node-1) ','

                -> {

                    var node = tuple.get("node");    

                    var nodeIndent = struct.getNewline(node.from);

                    if( nodeIndent != null ) {

                        struct.putNewline(node.from, nodeIndent+"  ");

                    }

                };

                 

                Output:

                 

                SELECT

                      sql_id                         a

                    , 'Before'                       period_flag

                    , nvl(executions_delta, 0)       execs

                    , ( elapsed_time_delta ) / 1000000 etime

                FROM

                      dba_hist_sqlstat   s

                    , x                  y

                 

                I have fixed the remaining misalignment of etime column for 19.4.

                • 5. Re: Column Alias Alignment in SQL Developer
                  Sentinel

                  That fixes the column list alignment issue when line breaks precede commas and follow SELECT/FROM/WHERE, now can you fix it when there is no line break after the SELECT/FROM/WHERE such that this:

                   

                  SELECT dummy
                         , 1 one  -- Indented too far
                         , MAX(2) two
                    FROM dual
                   GROUP BY dummy
                            , 1   -- Indented too far
                   ORDER BY dummy
                            , one -- Indented too far
                            , two
                  

                   

                  Gets formatted like this:

                  SELECT dummy
                       , 1 one  -- Indented Just right
                       , MAX(2) two
                    FROM dual
                   GROUP BY dummy
                          , 1   -- Indented just right
                   ORDER BY dummy
                          , one -- Indented just right
                          , two
                  

                   

                  or optionally formatted like this:

                  SELECT dummy
                       , 1 one  -- Indented Just right
                       , MAX(2) two
                    FROM dual
                   GROUP BY dummy
                       , 1   -- Indent aligns with select list
                   ORDER BY dummy
                       , one -- Indent aligns with select list
                       , two
                  
                  • 6. Re: Column Alias Alignment in SQL Developer
                    thatJeffSmith-Oracle

                    Please start new threads for new issues.

                    • 7. Re: Column Alias Alignment in SQL Developer
                      Vadim Tropashko-Oracle

                      Remove additionalSpaces rule. Use this instead:

                       

                      less2Spaces:  :breaksBeforeComma & (

                          [node+1) arg & [node) ','

                        | [node+1) prm_spec & [node) ','

                        | [node+1) select_term & [node) ','

                        | [node+1) group_by_col & [node) ','            

                        | [node+1) "ord_by_1desc" & [node) ','          

                        | [node+1) table_reference & [node) ','

                        | [node+1) par_expr_list[19,31) & [node) ','  -- in insert clause

                        | [node-1) "expr_list" & [node) ','          -- in insert clause

                      ) -> {

                          var node = tuple.get("node");    

                          var nodeIndent = struct.getNewline(node.from);

                          if( nodeIndent != null ) {

                              struct.putNewline(node.from, nodeIndent.substr(0,nodeIndent.length-2));

                          }

                      }