Re: Table analysis

From: noon <>
Date: Mon, 21 Jan 2008 08:00:13 -0800 (PST)
Message-ID: <>

On Jan 21, 9:17 am, "" <> wrote:
> Comments embedded.
> On Jan 21, 6:29 am, noon <> wrote:
> > What I am trying to accomplish is to get an understanding of how much
> > columns are being used in a table. We are working on creating a data
> > warehouse, and I need to know which columns are important and being
> > used and vice versa. My plan is to get a number of how many nulls /
> > not nulls there are for each record in the column. Is there a tool
> > that can provide this kind of table analysis?
> Not that I know of. You realise that simply having non-Null values
> doesn't necessarily indicate that a column is being used as the column
> could have a default value assignment, making it populated but not
> necessarily indicating any usage.
> You could audit these tables for activity and peruse the associated
> sql text to see what is being used. But, without any Oracle version
> information one can only speculate.
> > Otherwise, I can only think of using PL/SQL to run this query against
> > every single column.
> I can't understand what that would do for you; as I stated earlier
> simply being populated doesn't mark the column as being used.
> Provide the Oracle version you're using (all four or five numbers).
> You can't get a reasonably usable answer without this information.
> David Fitzjarrell

I actually ended up using the following SQL which worked well for what I wanted.

SELECT table_name, column_name, num_nulls, last_analyzed FROM user_tab_columns

I see the point you make about default value assignments and it is important. I realize this may seem quite trivial and while I won't disagree with that, it's just a quick and dirty first-glance type of overview. Received on Mon Jan 21 2008 - 10:00:13 CST

Original text of this message