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?

Otherwise, I can only think of using PL/SQL to run this query against
every single column.

Comments embedded.
On Jan 21, 6:29*am, noon <Nun... (AT) gmail (DOT) com> wrote:
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.

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

Comments embedded.
On Jan 21, 6:29*am, noon <Nun... (AT) gmail (DOT) com> wrote:
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.

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

Comments embedded.
On Jan 21, 6:29*am, noon <Nun... (AT) gmail (DOT) com> wrote:
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.

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

Comments embedded.
On Jan 21, 6:29*am, noon <Nun... (AT) gmail (DOT) com> wrote:
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.

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

On Jan 21, 9:17 am, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:
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.

On Jan 21, 9:17 am, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:
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.

On Jan 21, 9:17 am, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:
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.

On Jan 21, 9:17 am, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:
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.

On Jan 21, 11:00*am, noon <Nun... (AT) gmail (DOT) com> wrote:
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 --