Home » SQL & PL/SQL » SQL & PL/SQL » select count of nulls thru EXECUTE IMMEDIATE
select count of nulls thru EXECUTE IMMEDIATE [message #279015] Tue, 06 November 2007 11:16 Go to next message
jwa6
Messages: 4
Registered: November 2007
Junior Member

this is what I would like to do

I am getting all the fields name for a table 'GRT2007_STG1'
from user_TAB_COLUMNS

cursor c_col_name is
select
COLUMN_NAME
from
user_TAB_COLUMNS
where
TABLE_NAME = 'GRT2007_STG1';
r_col_name c_col_name%ROWTYPE;


-- then I look for a nulls count for each field

v_isnull char(1) := null;


FOR r_col_name IN c_col_name LOOP
v_COLUMN_NAME := r_col_name.COLUMN_NAME;




EXECUTE IMMEDIATE
'select
count(*)
from
GRT2007_STG1
where ' || v_COLUMN_NAME||' = :j' INTO null_cnt USING v_isnull ;

-- with the above I look for a count of null values in each of the fields in the table

there are 263 fields in table GRT2007_STG1

this doesnt work as the count is always 0

also

I woukld like to get all values from each field( nulls too)
using a group by( to see whats coming to us in the file)


EXECUTE IMMEDIATE
'select '||v_COLUMN_NAME||' from GRT2007_STG1 group by ' ||
v_COLUMN_NAME;

but how to code for a undetermended result set?



value1 90
value2 1
value3 1600

etc.

jim


Re: select count of nulls thru EXECUTE IMMEDIATE [message #279019 is a reply to message #279015] Tue, 06 November 2007 11:23 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>there are 263 fields in table GRT2007_STG1
So you are going to make 263 Full Table Scans (FTS) of table GRT2007_STG1 to obtain complete results.
IMO, that is not very efficient.
With a small PL/SQL procedure only a single FTS would be done.
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279021 is a reply to message #279019] Tue, 06 November 2007 11:29 Go to previous messageGo to next message
jwa6
Messages: 4
Registered: November 2007
Junior Member
well
thanks for that

I think
jim
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279025 is a reply to message #279015] Tue, 06 November 2007 11:50 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member

09:49:16 SQL> create table sample(id number, col1 number,col2 number);

Table created.

09:49:17 SQL> insert into  sample values(1,1,1);

1 row created.

09:49:17 SQL> insert into  sample values(2,2,2);

1 row created.

09:49:17 SQL> insert into  sample values(3,3,null);

1 row created.

09:49:17 SQL> insert into  sample values(4,null,4);

1 row created.

09:49:17 SQL> select count(id) , count(col1), count(col2) from sample;

 COUNT(ID) COUNT(COL1) COUNT(COL2)
---------- ----------- -----------
         4           3           3
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279028 is a reply to message #279025] Tue, 06 November 2007 12:06 Go to previous messageGo to next message
jwa6
Messages: 4
Registered: November 2007
Junior Member
yes,
I can do that

select
(SELECT
count(*)
From
GRT2007_STG1
where Test_Date is null),
(SELECT
count(*)
From
GRT2007_STG1
where Student_Last_Name is null)
from dual;



Im doing it for 263 fields ...I was hoping to ''automate' the process.

jim
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279034 is a reply to message #279015] Tue, 06 November 2007 12:19 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You can write SQL to write SQL which contains all 263 columns.
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279037 is a reply to message #279034] Tue, 06 November 2007 12:36 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
example...
SELECT reslt
  FROM (SELECT   *
            FROM (SELECT 'select' reslt, ROWNUM rn, 1 ord
                    FROM DUAL
                  UNION ALL
                  SELECT    DECODE (ROWNUM, 1, NULL, ',') || 
				  '(abc '|| column_name|| CHR (10) || ' XYZ)', ROWNUM, 2
                    FROM user_tab_columns
                   WHERE table_name = 'EMP'
                  UNION ALL
                  SELECT 'from my_tab' || CHR (10) || 'where...', ROWNUM, 3
                    FROM DUAL)
        ORDER BY ord, rn)


select
(abc EMPNO
 XYZ)
,(abc ENAME
 XYZ)
,(abc JOB
 XYZ)
,(abc MGR
 XYZ)
,(abc HIREDATE
 XYZ)
,(abc SAL
 XYZ)
,(abc COMM
 XYZ)
,(abc DEPTNO
 XYZ)
from my_tab
where...


[added rownum, order by to make sure it's robust]

[Updated on: Tue, 06 November 2007 12:41]

Report message to a moderator

Re: select count of nulls thru EXECUTE IMMEDIATE [message #279044 is a reply to message #279037] Tue, 06 November 2007 13:12 Go to previous messageGo to next message
jwa6
Messages: 4
Registered: November 2007
Junior Member
Ill have to try this out.

Its a little over me ol bald head - Ill play w/ it a bit - then have a double malt!.

thanks for the suggestion...its a different take from what I was trying to do w/
immediate exec

jim
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279084 is a reply to message #279015] Tue, 06 November 2007 20:13 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
maybe you can reverse the values with a decode. Make null = 1 and everything else = null.

select count(decode(col1,null,1,null)) count1
      ,count(decode(col2,null,1,null)) count2
      ,count(decode(col3,null,1,null)) count3
from yourtable
/


I do this stuff all the time when checking data in tables for percentages null of columns.

kevin
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279117 is a reply to message #279084] Wed, 07 November 2007 01:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Good advice, Kevin! I was about to post something similar when I read your post. I'd combine your DECODE with Andrew's and Ana's hint of SQL generating the statement.

MHE
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279121 is a reply to message #279084] Wed, 07 November 2007 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think "count(*)-count(colX)" will be faster than "count(decode..."

Regards
Michel
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279124 is a reply to message #279121] Wed, 07 November 2007 01:14 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Now you got me curious, Michel. Why would it be faster?

I set up a small test case like this before I read Kevin's reply:
CREATE TABLE yourtable( col1 NUMBER
                      , col2 NUMBER
                      , col3 NUMBER
                      , col4 NUMBER
                      , col5 NUMBER
                      , col6 NUMBER
                      );
                      
INSERT INTO yourtable VALUES (NULL,1   ,1   ,1   ,1   ,1   );
INSERT INTO yourtable VALUES (1   ,1   ,1   ,1   ,1   ,1   );
INSERT INTO yourtable VALUES (NULL,1   ,NULL,1   ,1   ,1   );
INSERT INTO yourtable VALUES (1   ,1   ,NULL,1   ,1   ,1   );
INSERT INTO yourtable VALUES (1   ,1   ,NULL,1   ,1   ,1   );
INSERT INTO yourtable VALUES (1   ,1   ,1   ,1   ,1   ,1   );
INSERT INTO yourtable VALUES (NULL,NULL,1   ,1   ,1   ,1   );
INSERT INTO yourtable VALUES (1   ,1   ,1   ,1   ,1   ,1   );
INSERT INTO yourtable VALUES (1   ,1   ,NULL,1   ,1   ,1   );
INSERT INTO yourtable VALUES (1   ,1   ,NULL,NULL,1   ,1   );
INSERT INTO yourtable VALUES (1   ,1   ,NULL,NULL,1   ,1   );
INSERT INTO yourtable VALUES (1   ,1   ,NULL,1   ,1   ,1   );
INSERT INTO yourtable VALUES (1   ,1   ,NULL,1   ,1   ,NULL);
INSERT INTO yourtable VALUES (1   ,1   ,1   ,1   ,NULL,1   );

COMMIT;


var yourtable VARCHAR2(30)
exec :yourtable := 'YOURTABLE'
/
SET HEAD OFF
SET FEEDB OFF
SPOOL exec_count.sql

SELECT    CASE ROWNUM
             WHEN 1
                THEN 'SELECT '
             ELSE ', '
          END
       || 'SUM(NVL2('
       || column_name
       || ', 0, 1 )) cnt_col'||ROWNUM x
FROM   user_tab_columns
WHERE  table_name = :yourtable
UNION ALL
SELECT 'FROM '||:yourtable||';' FROM dual
/
SPOOL OFF
SET HEAD ON
SET FEEDB ON
@exec_count

DROP TABLE yourtable PURGE
/
Apart from the NVL2, it is pretty much what Kevin was explaining.

MHE
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279148 is a reply to message #279124] Wed, 07 November 2007 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just logic.
Count(*) is already there when you execute count(col1) this is just one more slot in memory.
"count(decode..." needs algorithm calculated each time, Oracle does not know you just swap null and value.
"SUM(NVL2..." may be better (I really don't know) but slower than count: "inc" instruction is far faster than "add" and you still have to "calculate" nvl2 even if it is optimized.

Regards
Michel
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279289 is a reply to message #279015] Wed, 07 November 2007 10:51 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
this was a neat test.

the count using decode

Elapsed: 00:00:05.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=300 Card=1 Bytes=1297)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'AIMS_ALL_DATA' (TABLE) (Cost=300 Card=13088 Bytes=16975136)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      24246  consistent gets
          0  physical reads
          0  redo size
      24412  bytes sent via SQL*Net to client
      33960  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 

the count useing count(*)-count(column)

Elapsed: 00:00:01.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=300 Card=1 Bytes=1297)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'AIMS_ALL_DATA' (TABLE) (Cost=300 Card=13088 Bytes=16975136)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      24246  consistent gets
          0  physical reads
          0  redo size
      24412  bytes sent via SQL*Net to client
      26307  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 


this table has 625 columns in it and 13,000 rows so I constructed two giant sql queries to do the count of nulls in each column. Timing results are via AUTOTRACE and are real interesting because they show no difference between the two query variations neither by query plan nor by statitcs related to each run. So... where is the time going?

A good question for Tom Kyte maybe? Or does someone here have the deep understanding to explain why the time difference is so great.

These timings were consistent no matter how many times I ran each query.

select count(*) rowcount
,count(decode(ASSESSMENT_ACTIVE,null,1,null)) ASSESSMENT_ACTIVE
,count(decode(ASSESSMENT_AID,null,1,null)) ASSESSMENT_AID
...
from aims_all_data
/


select count(*) rowcount
,count(*)-count(ASSESSMENT_ACTIVE) ASSESSMENT_ACTIVE
,count(*)-count(ASSESSMENT_AID) ASSESSMENT_AID
...
from aims_all_data
/

Kevin
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279388 is a reply to message #279289] Thu, 08 November 2007 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, thanks for the test.
There is no difference in explain plan because same plan is used.
The difference is in the code executed and it is cpu time (I think).
You would have a better picture of it with a sql trace (or 10046) instead of autotrace.

Regards
Michel
Re: select count of nulls thru EXECUTE IMMEDIATE [message #279458 is a reply to message #279015] Thu, 08 November 2007 10:27 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes thanks, I just wanted to point out to everyone that we use autotrace to look at the plans and statistics of a set of queries as our way of evaluating the efficiency of these queries against each other.

But these still only tell part of the story. Sometimes there are significant differences but they don't readily show up.

Kevin
Previous Topic: Error handling when a function in a package is not found
Next Topic: Multi packages or single package? Which is good?
Goto Forum:
  


Current Time: Thu Dec 08 18:11:28 CST 2016

Total time taken to generate the page: 0.26083 seconds