Problems with tables with more than 4.2 billion rows

Hi All,

I'm wondering if anyone else is having problems I'm experiencing w/ a table with greater than 2^32 (4,294,967,296) rows. I have a table of scientific data with over 5 billion rows and I'm seeing some strange behaviors like:

• I cannot seem to export ("exp") this table. Export seems to die at 4.2 billion rows
• Queries involving ROWNUM seem to "ignore" all the rows beyond row number 4,294,967,295

For example, my table CELLFEATURE has over 5 billion rows, and when I do

   SELECT * FROM ( SELECT rownum AS rn, CF.* FROM cellfeature CF )    WHERE rn >= 4294960000;

My output looks like:

             RN     CELLID     TYPEID     VALDBL
     ---------- ---------- ---------- ----------
     4294960000  266512478      14663          0
     4294960001  266512478      14664          0
     4294960002  266512478      14665          0
     ... [many rows omitted] ...
     4294967271  266503794      14622 337.633508
     4294967294  266503794      14645          0
     4294967295  266503794      14646          2

     7296 rows selected.

     SQL>

I.e., it "stops" just before getting to row number = 2^32

Also, when I do "SELECT count(*) FROM <table>" it does give me the correct
number of rows, about 5.1 billion.

I can deal w/ the export issue since I can at least cold-backup the files, or dump text files, but the above SQL seems just incorrect, and that concerns me more.

FYI *all* of my CPU, O/S, and Oracle software (client and server version are 10.2.0.1.0) are 64-bit.

As it happens, I do OCI programming and I notice in the OCI spec the attribute OCI_ATTR_ROW_COUNT, which gives "number of rows processed", is of type "ub4" which I take to be unsigned-4-byte-int which in turn would have its max value less than 2^32. Thus it would seem simply impossible to do any operation on an Oracle table that involves more than 4.2 bill rows; or am I missing something?

I've googled extensively and I can't seem to find any info about this limit if it exists. Anyone out there w/ a similarly large table that could verify this behavior? It would be greatly appreciated.

Thanks,

        JH

---
John Hinsdale, Alma Mater Software, Inc., Princeton Junction, NJ, USA
hin@alma.com | http://www.alma.com/staff/hin | +1 609 716 6308