Table analysis

#11

On Jan 21, 11:00*am, noon <Nun... (AT) gmail (DOT) com> wrote:

Quote:

On Jan 21, 9:17 am, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:

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

#12

On Jan 21, 11:00*am, noon <Nun... (AT) gmail (DOT) com> wrote:

Quote:

On Jan 21, 9:17 am, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:

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

#13

On Jan 21, 11:00*am, noon <Nun... (AT) gmail (DOT) com> wrote:

Quote:

On Jan 21, 9:17 am, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Table analysis

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Table analysis - 01-21-2008 , 11:55 AM

Re: Table analysis - 01-21-2008 , 11:55 AM

Re: Table analysis - 01-21-2008 , 11:55 AM