This discussion is archived
2 Replies Latest reply: Feb 6, 2013 5:26 AM by mpatzwahl RSS

Parsing which columns used in a query

mpatzwahl Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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