How many rows?

Ok, this is a weird one.

Oracle 9.2.0.6 64-bit on Sparc-Solaris 9.

I have an external table. If I select * from table, I get 3,371 rows. If I select count(*), I get 3,631??

My guess is that count(*) just counts rows, but doesn't care if they will be rejected, whereas 'select *' actually outputs them, so, filters out rows that don't fit the correct format?

Is this documented behavior?

$ sqlplus pqsslink_at_pep1

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Nov 10 11:00:49 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> desc pqss_holdings_ext

 Name                                      Null?    Type
 ----------------------------------------- --------


----------------------------

 OPERATION                                          VARCHAR2(1)
 PMID                                               NUMBER(9)
 LIBHASH                                            VARCHAR2(20)
 DATESTARTID                                        NUMBER(9)
 DATEENDID                                          NUMBER(9)

SQL> select * from pqss_holdings_ext;

O PMID LIBHASH DATESTARTID DATEENDID
- ---------- -------------------- ----------- ----------

I      10260 AN3UC5SD2K                  5677       5460
I      10402 AN3UC5SD2K                  4185          1
I      10402 AN3UC5SD2K                  4498          1
I      10402 AN3UC5SD2K                  4516          1
I      10604 AN3UC5SD2K                  3369       5460
I      10604 AN3UC5SD2K                  1754       4759
I      10661 AN3UC5SD2K                  4458       5460
I      11396 AN3UC5SD2K                  5119          1
I      11619 AN3UC5SD2K                  3699       4103
I      11619 AN3UC5SD2K                  3720       4121
I      11635 AN3UC5SD2K                  2667       5677

...lots of data deleted here...

O PMID LIBHASH DATESTARTID DATEENDID
- ---------- -------------------- ----------- ----------

I         98 XD8BJ7HP4J                  3491          1
I         98 XD8BJ7HP4J                  4516          1
I         98 XD8BJ7HP4J                   232          1
I         98 XD8BJ7HP4J                  2749       2964
I       9921 XD8BJ7HP4J                  3020          1

3371 rows selected.

SQL> select count(*) from pqss_holdings_ext;

  COUNT(*)

      3631

Thanks!

-Mark
--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be done at all. -Peter F. Drucker, 1909-2005

--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 10 2006 - 10:10:03 CST