Home » SQL & PL/SQL » SQL & PL/SQL » COUNT NULL ROWS
COUNT NULL ROWS [message #267973] Mon, 17 September 2007 00:36 Go to next message
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 #267974 is a reply to message #267973] Mon, 17 September 2007 00:42 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Try this:

SELECT COUNT(*) FROM table_name WHERE colX IS NULL;

DELETE FROM table_name WHERE colX IS NULL;
Re: COUNT NULL ROWS [message #267983 is a reply to message #267973] Mon, 17 September 2007 01:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #267996 is a reply to message #267985] Mon, 17 September 2007 01:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Mon, 17 September 2007 08:29
(for instance, what do you consider NULL rows?

Re: COUNT NULL ROWS [message #268005 is a reply to message #267973] Mon, 17 September 2007 02:10 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #268043 is a reply to message #267973] Mon, 17 September 2007 05:23 Go to previous messageGo to next message
ram_ocp
Messages: 49
Registered: December 2005
Location: Karnataka
Member


Otherwise can you let me know how to list all the empty table in a database ie tables without any rows

Ram
Re: COUNT NULL ROWS [message #268050 is a reply to message #267973] Mon, 17 September 2007 06:02 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
After gathering statistics:
select table_name from user_tables where num_rows=0
Re: COUNT NULL ROWS [message #268055 is a reply to message #268050] Mon, 17 September 2007 06:36 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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



Re: COUNT NULL ROWS [message #268334 is a reply to message #267973] Tue, 18 September 2007 04:04 Go to previous message
ram_ocp
Messages: 49
Registered: December 2005
Location: Karnataka
Member
Hi....

Thanks to all !!!
Previous Topic: sql%found problem
Next Topic: Cursor as out parameter
Goto Forum:
  


Current Time: Sat Dec 03 18:33:10 CST 2016

Total time taken to generate the page: 0.10958 seconds