COUNT NULL ROWS [message #267973] |
Mon, 17 September 2007 00:36  |
ram_ocp
Messages: 49 Registered: December 2005 Location: Karnataka
|
Member |
|
|
Hi,
I have an application running on my 9i oracle database.We have huge tables and need to do a clean up of the database.Pls let me know how to do the following
1.Count the number of NULL rows in a table/tables
2.Delete all the NULL rows in the tables
Please let me know
Thanks
Ram
|
|
|
|
Re: COUNT NULL ROWS [message #267983 is a reply to message #267973] |
Mon, 17 September 2007 01:22   |
ram_ocp
Messages: 49 Registered: December 2005 Location: Karnataka
|
Member |
|
|
Hi,
I cannot use the statement as i have to count from a group of tables having different columns names also for a single table it will be exhausting to write all column names one by one
Pls let me know how to first count and then delete all NULL rows.
Ram
|
|
|
Re: COUNT NULL ROWS [message #267985 is a reply to message #267983] |
Mon, 17 September 2007 01:29   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Generate the statements.
Now if you will provide us more details on your problem (for instance, what do you consider NULL rows? Don't you have primary keys?) then we will be able to give you more details too.
|
|
|
Re: COUNT NULL ROWS [message #267990 is a reply to message #267973] |
Mon, 17 September 2007 01:49   |
ram_ocp
Messages: 49 Registered: December 2005 Location: Karnataka
|
Member |
|
|
Hi,
Thanks for ur reply.Let me brief you what i am looking for
In simple words
I am looking for something like:
Select count(NULL) from table1,table2,table3....... ;
Instead of
Select count(*)from table1 where column1 is NOT NULL,column2 is NOT NULL, column3 is NOT NULL.......
Again
Select count(*)from table2 where column1 is NOT NULL,column2 is NOT NULL, column3 is NOT NULL.......
Objects are created by the application and at datbase level we are not permitted to insert primary keys.
Ram
|
|
|
|
Re: COUNT NULL ROWS [message #268005 is a reply to message #267973] |
Mon, 17 September 2007 02:10   |
ram_ocp
Messages: 49 Registered: December 2005 Location: Karnataka
|
Member |
|
|
Hi,
For example :
Insert into table_name values ('',' ',' ') ;
Though the above statmenet is not run but we do have lot of null rows
Ram
|
|
|
Re: COUNT NULL ROWS [message #268006 is a reply to message #268005] |
Mon, 17 September 2007 02:13   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Then use user_tab_columns to generate statements in the form of
select count(*) from the_table where column1 is null and column2 is null...
|
|
|
Re: COUNT NULL ROWS [message #268014 is a reply to message #267973] |
Mon, 17 September 2007 02:38   |
user82
Messages: 49 Registered: June 2006 Location: Romania
|
Member |
|
|
try this one:
select column_name,num_nulls from user_tab_columns where table_name='YOUR_TABLE';
it will return the number of nulls of each column...then you can delete them...also you may use dynamic SQL inside a procedure to do the job for all the tables.....
|
|
|
Re: COUNT NULL ROWS [message #268015 is a reply to message #268014] |
Mon, 17 September 2007 02:45   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
This will only give an indication of the number of null-columns per column, not whether they are all null in the same row.
|
|
|
|
|
Re: COUNT NULL ROWS [message #268055 is a reply to message #268050] |
Mon, 17 September 2007 06:36   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
This all sounds like you just started at a new job and you were given some easy assignments to see what you are capable of and how you would handle those.
If this is the case, asking around in a forum like this one, is not the way to go. You should try to find out yourself how to do these. There are several ways to get a list of tables without any rows, ranging from quite basic to 'derivated knowledge' like the statistics-method.
If you try to fool the person who handed out the assignments by using this kind of tricks, without you really understanding, you will get yourself into trouble. That person will think you have more knowledge then you have in reality, thus handing you more difficult tasks and reserving less time to teach you.
There is nothing wrong with trying to solve a problem and asking your collegues for hints.
No offence meant, but really meant as good advise.
|
|
|
Re: COUNT NULL ROWS [message #268147 is a reply to message #268055] |
Mon, 17 September 2007 14:42   |
jrich
Messages: 35 Registered: February 2006
|
Member |
|
|
One tip, completely null columns for an index are not stored in the index so a full table scan is necessary to find null values. If you tend to have very few null values but it's important to find them quickly or efficiently, you could create a function based index to only index the null values. For example:
create index nullsonly on yourtable(nvl2(col1,null,'X'));
and then a where clause like:
where nvl2(col1,null,'X') = 'X'
should use the index to find the nulls.
JR
|
|
|
|