RE: help with a SQL/view problem
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <wbfergus@xxxxxxxxx>, "oracle-l-freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Feb 2010 13:00:16 -0500
Bill,
Well your first mistake is running on windoze. As for the
second, first I need to know what the definition of deposits_base is.
Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bill Ferguson
Sent: Monday, February 15, 2010 3:29 PM
To: oracle-l-freelists
Subject: help with a SQL/view problem
First off, in case of any differences, I'm running 11.1.0.7 on Windows
Server 2003.
Project 'management' wants a view to see the population of a certain
schema, with the population percentage of each (data) field in a table
and also the population percentage of that field in relation to to the
'parent' table.
Here is what I have so far:
CREATE OR REPLACE FORCE VIEW PERCENT_FILLED
(
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
IS_REQUIRED,
DEFAULT_VALUE,
NUM_DISTINCT,
NUM_NULLS,
LAST_ANALYZED,
NUM_ROWS,
"Table Pct Populated",
"DB Pct Populated"
)
AS
SELECT a.TABLE_NAME,
a.COLUMN_NAME,
a.DATA_TYPE,
DECODE (a.NULLABLE, 'N', '*', 'Y', NULL) is_required,
a.DATA_DEFAULT DEFAULT_VALUE,
a.NUM_DISTINCT,
a.NUM_NULLS,
a.LAST_ANALYZED,
b.num_rows,
ROUND ( ( (b.num_rows - a.num_nulls) / b.num_rows) * 100)
"Table Pct Populated",
ROUND (
( (b.num_rows - a.num_nulls)
/ ( SELECT COUNT (*) FROM deposits_base))
* 100)
"DB Pct Populated"
FROM dba_tab_cols a
LEFT OUTER JOIN
dba_tab_statistics b
ON (a.owner = b.owner AND a.table_name = b.table_name)
WHERE a.owner = 'USGS' AND (a.table_name LIKE '%_BASE')
AND a.column_name NOT IN
('INSERT_DATE', 'INSERTED_BY', 'UPDATE_DATE',
'UPDATED_BY')
AND a.column_name NOT LIKE 'SYS%'
AND a.table_name NOT LIKE 'MLOG$_%'
AND a.table_name NOT LIKE 'RUPD$_%'
-- and column_name not in ('MAS_ID', 'MRDS_ID')
ORDER BY a.table_name, a.column_id;
So, obviously in this case 'USGS' is the schema and all of my 'data'
tables that I need to report on all end with '_BASE'. I'm filtering
out a few additional columns and tables as well, so I only report on
the actual 'data' fields. My parent table is called DEPOSITS_BASE and
my primary key field is called DEP_ID.
For the most part, the view seems to work pretty well, but I am having
problems getting an accurate number for the last column ("DB Pct
Populated"). The number being reported is actually the percentage of
that column populated based upon the total number of rows in the
DEPOSITS_BASE table, not the percentage of that column populated based
upon distinct DEP_ID's.
It seems like it should be pretty straightforward, but for the life of
me I haven't been able to figure it out yet (to long of a weekend?).
Can somebody help me out here please? It should only require a couple
of changes to run similarly on any schema you want to run it against.
Thanks.
--
-- Bill Ferguson
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
