can I find out which columns are being used or not in a table?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: can I find out which columns are being used or not in a table?

  1. #1
    Join Date
    Oct 2000
    Posts
    90

    Unhappy

    Hello, is the a way that I can find out which columns are being used in a table. I have a very huge table and I know that there are some of the columns that are not being used, but I don't know the exact ones. Is there any way that I can find out. If there are, can anybody tell me the exact way to find out?

    thanks,
    dorothy

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hm what do u mean if itīs being used, a null column?

  3. #3
    Join Date
    Oct 2000
    Posts
    90
    Okay let me try and explain it further. I have a huge table with a lot of columns. There are some front end applications that query some of the columns and fields in the database. Not all the columns are being querried. i.e. some of these columns are just there and application are not using them.
    So I am trying to find out which of these columns are being used (i.e. selected from ) and which of them are not being used?
    Are there any SQL queries that can let me do this? OR are there any tools that can help me find which of the columns are being used or not?

    thanks,
    dorothy

    [Edited by dorothy on 12-16-2000 at 12:47 PM]

  4. #4
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    You can try to look in v$sqlarea an get all the statements
    that refer to your table.

    Gert

  5. #5
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    Dorthy

    You could do something like the following

    select column_name
    from dba_tab_columns
    where table_name = <table of interest>
    and num_distinct = 0

    The tables will have had to be analyzed first in order for the statistics to be there though.

    Joe
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  6. #6
    Join Date
    Oct 2000
    Posts
    90
    Hi joe,
    So is this query going to tell me exactly what columns that the applications query from? I want to be able to determine which of the columns that are used by the applications for query purposes and which ones are not being used.
    i.e. which columns get used and the ones that do not get used

    thanks,
    dorothy

  7. #7
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    Dorthy,

    No, I misunderstood your question. That will tell you which columns in a particular table are not populated. If you need to find out which columns are being accessed by your applications, I would suggest doing something like turning Auditing on for a couple of days and going through the trace files or something of that nature. The problem you are going to run into probably though is that some of the columns might only be accessed during batch processing, monthly loads, etc.. etc.. Some might almost never be used at all except for special circumstances.

    Is there some larger issue that you are looking at. Maybe if we understood exactly what the issue is we'd be able to offer some better advice.

    Thanks,

    Joe
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  8. #8
    Join Date
    Oct 2000
    Posts
    90
    Thanks Joe,
    Let me tell you the whole scenario. The whole scenario is that, we are going to build a new database(called Database1) from the scratch. There is an old Database (called Database2) that has very huge tables. There are some applications that query some of the columns from Database2(but not all the columns). We do not know the names of these columns that these applications query from. So we are trying to find out those columns.

    The purpose of finding these columns is to put the exact columns that are used by the applications into the new tables of the database that we are going to create(i.e. database1). By knowing the exact columns, we will not put columns that are not needed into the new tables of the new database.

    How exactly do you do tuning auditing? Is there any specific documentation that I can read? But before that can you tell me in a nutshell how it's done and how it works?


    thanks for your time,
    dorothy

  9. #9
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    Dorthy,

    I'll ponder the issue more and try to come up with a more efficient method but right now the only things I can think of in the absence of going through the source code is to turn auditing on for the whole database by setting the parameter SQL_TRACE = TRUE inside of your initialization file. This will create a separate trace file for each session in the directory defined by USER_DUMP_DIR. This is going to create a huge number of traces though if you have any real activity going on throughout the day and it's also going to slow down your database.

    After you have the trace files you'd have to run them through something like tkprof and examine the sql that was executed and note which columns were referenced. Once again, you're going to run into the issue of tables/columns that are very infrequently accessed.

    Can anyone else think of a better method than turning auditing on or going through the v$sqlarea statement by statement?

    Joe

    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  10. #10
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    The better way to do it to look into the code of application and user queries. You should also consider the dependency of the front end applciation which is inserting/updating data into that table. After looking into the applications come out with the list of column not referenced by application. There may be some columns referenced by application but of no relavence or obsolete. In that case you should modify the application first and then modify the database.

    What do the expertssay?


    Sanjay

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width