I have to ask...how did you check that the table has over 308 million records?  A simple count(*) or did you check a catalog table?

Hi Mike.

First, [Query]->[Info]->[status] gives me that number.

But w/o running dbaccess, I have my trusty script fragments.sh (published on IIUG repository like 9+ years ago) to keep track as well.

-- Jacob S

I have to ask...how did you check that the table has over 308 million records?  A simple count(*) or did you check a catalog table?

Gerd:

In a partitioned (fragmented) table the actual physical ROWID is not available simply because there would be duplicates, that is why if you need the functionality of a rowid for a partitioned table you have to create it WITH ROWID which adds an indexed SERIAL type column named rowid, so your comment is incorrect. The fact that Jacob can query the ROWID indicates that either the table is not partitioned or was created WITH ROWID. Given that he is testing for negative rowids I strongly suspect that the table is not partitioned.

Partitioned tables, indeed all tables, do, however, also support the IFX_ROW_ID pseudo-column which is the phyical address of the row and does include the partnum of the partition that holds the row along with the page number and slot number. Unfortunately IFX_ROW_ID is a string not an integer. 

Art

Art and everyone elase telling me how useless rowid is on a partitioned table:

This table is not partitioned.  I was actually looking for a way to split the task of copying 300+ million rows into a partitioned copy of this table into its partitioned twin.  Thus, rowid is (or should be) a valid way to count rows in each "equivalence class" or rowids.

As I type this reply, I am running the qury supplied by both Mike and Gerd. And timing it, just for jollies.  Here's the result, with the table name censored, of course:

select
    case
        when rowid <= 0 then '(0:Gone negative)'
        when rowid <= 500000000  then '(1: Under 500 Million)'
        when rowid <= 1000000000 then '(2: 500 mil - 1 billion)'
        when rowid <= 1500000000 then '(3: 1 billion - 1.5 billion)'
        when rowid <= 2000000000 then '(4: 1.5 billion - 2 billion)'
        else                          '(5: Over 2 billion)'
    end category,
    count(*) frquency
  from <mytable>
 group by 1
 order by 1 ;

The query ran for 19 minutes and then returned:

category                             frquency

(1: Under 500 Million)              308752270

1 row(s) retrieved.

What a colossal joke on me, espececially after I had previously selected max(rowid) and gotten a number in the 2-billion range.

Conclusion (which I had previously concluded): Rowid is interpreted in a funny way internally.  It can not be used as a class of rows.  Fortunately, the table has indexed columns that can be used for splitting the task.

Surprising numbers but I'd call this the end of the thread.  Lesson learned: Don't use rowid as an integer.  (How does FourGen get away with it?)

------------------------------
+-----------------------------------------------------------+
| I am pleased to report that I had no problems today. |
| I had only issues, opportunities, challenges and valuable |
| learning experiences. |
+------------------------------------------ Jacob S --------+
------------------------------


Original Message:
Sent: 07/08/26 06:07 AM
From: Art Kagel
Subject: Row count by rowid does not add up to actual rows

Gerd:

In a partitioned (fragmented) table the actual physical ROWID is not available simply because there would be duplicates, that is why if you need the functionality of a rowid for a partitioned table you have to create it WITH ROWID which adds an indexed SERIAL type column named rowid, so your comment is incorrect. The fact that Jacob can query the ROWID indicates that either the table is not partitioned or was created WITH ROWID. Given that he is testing for negative rowids I strongly suspect that the table is not partitioned.

Partitioned tables, indeed all tables, do, however, also support the IFX_ROW_ID pseudo-column which is the phyical address of the row and does include the partnum of the partition that holds the row along with the page number and slot number. Unfortunately IFX_ROW_ID is a string not an integer. 

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------



Original Message:
Sent: 07/08/26 02:05 AM
From: Gerd Kaluzinski
Subject: Row count by rowid does not add up to actual rows

How wide are this table's rows? And how many pages has it got allocated? (oncheck -pt <db>:<tab>   ->    first (DATA) partition would have both these info points)

Rowid 500000000  = 0x1dcd6500, so page 0x1dcd65 = 1953125  -  if this partition has below these nearly 2mio pages allocated or, technically, if the last row is on a page below this limit, the result of this CASE query would be plausible.

If not, though, something's indeed fishy here...

Forgot to mention:  what's the partition's page size?

------------------------------
Andreas Legner
Informix Dev
HCL Software
------------------------------


Original Message:
Sent: 07/08/26 12:41 PM
From: Jacob Salomon
Subject: Row count by rowid does not add up to actual rows

Art and everyone elase telling me how useless rowid is on a partitioned table:

This table is not partitioned.  I was actually looking for a way to split the task of copying 300+ million rows into a partitioned copy of this table into its partitioned twin.  Thus, rowid is (or should be) a valid way to count rows in each "equivalence class" or rowids.

As I type this reply, I am running the qury supplied by both Mike and Gerd. And timing it, just for jollies.  Here's the result, with the table name censored, of course:

select
    case
        when rowid <= 0 then '(0:Gone negative)'
        when rowid <= 500000000  then '(1: Under 500 Million)'
        when rowid <= 1000000000 then '(2: 500 mil - 1 billion)'
        when rowid <= 1500000000 then '(3: 1 billion - 1.5 billion)'
        when rowid <= 2000000000 then '(4: 1.5 billion - 2 billion)'
        else                          '(5: Over 2 billion)'
    end category,
    count(*) frquency
  from <mytable>
 group by 1
 order by 1 ;

The query ran for 19 minutes and then returned:

category                             frquency

(1: Under 500 Million)              308752270

1 row(s) retrieved.

What a colossal joke on me, espececially after I had previously selected max(rowid) and gotten a number in the 2-billion range.

Conclusion (which I had previously concluded): Rowid is interpreted in a funny way internally.  It can not be used as a class of rows.  Fortunately, the table has indexed columns that can be used for splitting the task.

Surprising numbers but I'd call this the end of the thread.  Lesson learned: Don't use rowid as an integer.  (How does FourGen get away with it?)

------------------------------
+-----------------------------------------------------------+
| I am pleased to report that I had no problems today. |
| I had only issues, opportunities, challenges and valuable |
| learning experiences. |
+------------------------------------------ Jacob S --------+
------------------------------


Original Message:
Sent: 07/08/26 06:07 AM
From: Art Kagel
Subject: Row count by rowid does not add up to actual rows

Gerd:

In a partitioned (fragmented) table the actual physical ROWID is not available simply because there would be duplicates, that is why if you need the functionality of a rowid for a partitioned table you have to create it WITH ROWID which adds an indexed SERIAL type column named rowid, so your comment is incorrect. The fact that Jacob can query the ROWID indicates that either the table is not partitioned or was created WITH ROWID. Given that he is testing for negative rowids I strongly suspect that the table is not partitioned.

Partitioned tables, indeed all tables, do, however, also support the IFX_ROW_ID pseudo-column which is the phyical address of the row and does include the partnum of the partition that holds the row along with the page number and slot number. Unfortunately IFX_ROW_ID is a string not an integer. 

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

For sure if the table is partitioned the originally shared query will fail to return the correct number of rows as it doesn't take into account the duplicate rowid's.  If the UNION's where UNION ALL it would likely work.  That being said the Suggested use of the Case and Group By could work too (cleaner to read).  

Hope one of these solutions finds you well. 

Eric Rowell

Eric:

While Jacob has solved his original problem differently, I have to respond to you and the others who commented on ROWIDs for partitioned tables. Let me repeat my previous post:

THERE ARE NO ROWIDs AVAILABLE FOR A PARTITIONED TABLE! At least not "real" ROWIDs simply because of the duplicates you cannot query the actual ROWID for a partitioned table, you would get an error if you tried. If and only if the partitioned table was created WITH ROWID it will have a hidden column named rowid, however, that is NOT a ROWID in the sense of a physical row location, but rather a simple SERIAL type column with an index, so there will be no duplicate values!

Also, Jacob did state that he was trying to find a way to copy smaller numbers of rows from a non-partitioned table to a new partitioned version and had been attempting to see if he could use ranges of ROWIDs to copy subsets of the data.

I probably should have just reminded Jacob that this is exactly what my dbcopy utility is all about!

Art

For sure if the table is partitioned the originally shared query will fail to return the correct number of rows as it doesn't take into account the duplicate rowid's.  If the UNION's where UNION ALL it would likely work.  That being said the Suggested use of the Case and Group By could work too (cleaner to read).  

Hope one of these solutions finds you well. 

Eric Rowell

Again, this is a segue from the original question but to reply to Art:

I indeed needed the reminder about dbcopy but not every host in my environment has the CSDK installed so  can compile it.  That said, I came up with a scheme to:

1. Use a query to generate insert into <target> select from <source> where <date-clause>; statements, date-by-date.  Something like 12,000 SQL commands
2. Run that monster scripts.  Here's what some of that output looks like; it's running now on a test server

insert into <target> select sti.* from <source> where <join condition> and stx.post_date = '2010/10/20';

33662 row(s) inserted.

insert into <target> select sti.* from<source> where <join condition>  and stx.post_date = '2010/10/21';

36805 row(s) inserted.

insert into <target> select sti.* from <source> where <join condition> and stx.post_date = '2010/10/22';

21219 row(s) inserted.

Each insert is a singleton transaction so no worries about long transactions.  But the log-backup is certainly being kept busy!

BTW, This is zooming through and thousands of rows per sec.  I would have liked to pipe the <inserted> messages (in stderr?) through a filter that inserts a time stamp at the start of each line, so that I can really gauge rows/second. (fcopy-table.pl does this to the microsecond but that was built in when I had more time to develop it.) Does dbcopy provide this luxury?  I think I'll see if I can compile it on an application development host and copy it where it's needed.

In any case, I think it's time to put this thread to bed. 

• Jacob S

Eric:

While Jacob has solved his original problem differently, I have to respond to you and the others who commented on ROWIDs for partitioned tables. Let me repeat my previous post:

THERE ARE NO ROWIDs AVAILABLE FOR A PARTITIONED TABLE! At least not "real" ROWIDs simply because of the duplicates you cannot query the actual ROWID for a partitioned table, you would get an error if you tried. If and only if the partitioned table was created WITH ROWID it will have a hidden column named rowid, however, that is NOT a ROWID in the sense of a physical row location, but rather a simple SERIAL type column with an index, so there will be no duplicate values!

Also, Jacob did state that he was trying to find a way to copy smaller numbers of rows from a non-partitioned table to a new partitioned version and had been attempting to see if he could use ranges of ROWIDs to copy subsets of the data.

I probably should have just reminded Jacob that this is exactly what my dbcopy utility is all about!

Art

For sure if the table is partitioned the originally shared query will fail to return the correct number of rows as it doesn't take into account the duplicate rowid's.  If the UNION's where UNION ALL it would likely work.  That being said the Suggested use of the Case and Group By could work too (cleaner to read).  

Hope one of these solutions finds you well. 

Eric Rowell