2 Replies Latest reply: Feb 6, 2013 7:26 AM by mpatzwahl RSS

    Parsing which columns used in a query

    mpatzwahl
      Hi,

      i try to write my own advisor :-) I´m looking for a package that can help me to find out which columns a used in query in a codition

      Example:
      select 1 from emp
      where deptno=10
      and job='CLERK';

      => deptno and clerk
      I worked with col_usage$, but it seems not always correct, and it´s not updated when you zuse explain plan ...

      Does anybody know, how Oracle´s dbms_advisor/dbms_sqltune finds out which columns are used in a condition ?

      Thanks
      Marco
        • 1. Re: Parsing which columns used in a query
          6363
          mpatzwahl wrote:

          i try to write my own advisor :-)
          Oh dear, that is completely insane. Why not use the one provided?

          You would need to write a SQL parser.

          The language definition is here

          http://docs.oracle.com/cd/E11882_01/server.112/e26088/toc.htm

          And don't forget that a function could be used in the condition so you will need to parse PL/SQL also

          http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm

          Try it out on something simple like this and see how far you get

          {message:id=1908539}
          SQL> create or replace function f1 return varchar2 as
            2  l_dummy dual.dummy%type;
            3  begin
            4    select dummy into l_dummy from dual;
            5    return l_dummy;
            6  end;
            7  /
          
          Function created.
          
          SQL> edi
          Wrote file afiedt.sql
          
            1  create or replace function f2 return varchar2 as
            2  begin
            3    return 'X';
            4* end;
          SQL> /
          
          Function created.
          
          SQL> create or replace view v as
            2  select
            3      dummy a,
            4      (select dummy from dual) b,
            5      f1 c,
            6      f2 d,
            7      'X' e
            8  from dual;
          
          View created.
          
          SQL> select * from v where c = 'X';
          
          A B C D E
          - - - - -
          X X X X X
          • 2. Re: Parsing which columns used in a query
            mpatzwahl
            Why not use the one provided?
            because i have a standard edition.

            Yes it sounds complecated, but the first approach would be to use col_usage$

            Thanks
            Marco