Re: Table analysis

On Jan 21, 11:00 am, noon <Nun..._at_gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Be aware that if you have a two-value column where almost all the rows will have one value and where you often want all the rows with the lesser value that NULL can be effectively utilizied as representing the most common of the two values. With a normal index Oracle does not index nulls so you end up with a sparse index (only non-null rows are indexed). In an OLTP environment where a bitmap index may not be appropriate this can provide a very effective indexed access path.

So just because a column value is NULL does not mean the value is unimportant or meaningless to the application.

HTH -- Mark D Powell -- Received on Mon Jan 21 2008 - 11:55:49 CST