Re: Table analysis

On Jan 21, 9:17 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> Comments embedded.
> On Jan 21, 6:29 am, noon <Nun..._at_gmail.com> 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