IQ 16 SP8, Question on Row Id

[Former Member]

SAP IQ does not guaranty that row ids will be sequential at the storage level for a table (gap can exists).

But, Can new rows inserted in the same table use the row ids in the gaps?

Example:

If I insert in a table 37,000 rows and the rows ids are from 1 to 8000 and from 600,001 to 628,000 (37,000 rows).

And later I insert (add) another 40,000 rows to the same table:

Does IQ guaranty that every new row inserted will be assigned a row id bigger that the last row ID recorded in the table?

Or, can the numbers in the gaps of the existing row ids be assigned to new rows added (ie.: If I add 40,000 new rows, can the new rows ids (or at least some row ids) be in the range 8,001 to 600,000)?

Thanks a lot,

Uvernes

[markmumy · Accepted solution]

There is no guarantee on reusing of ROWIDs in IQ 16 now.  It goes back to our other discussion on data ordering upon input.  The same parallelism to give us performance means that we can't start allocating ROWIDs upon insert and do it in a way that guarantees reuse of ROWIDs.

Pre IQ 16 we had the ability to append data to the end of the table (Append_Load=ON) for performance.  We could also disable appending and have the load engine reuse those earlier vacated ROWIDs.

All of that was overhauled in IQ 16 to give us the quantum leap in load performance.

Can I ask why we want to reuse ROWIDs?  Is this related to the other post about the temp tables and ORDER BY statement?

Mark

[Former Member · Accepted solution]

Hi Mark,

Thanks a lot for the explanation.

Yes, this is related to our previous discussion about temp tables and Order by (We solved that using the number function as you recommended).

We do not want to reuse row IDs, but to be sure that any new row added will have a row id bigger than the last row id on the table.

Let me explain; we have a staging table (S) that is often receiving new rows, we also have a process (P) that clean, transforms and move the rows from the staging table to the corresponding data warehouse table (DWT).  Until now, each time that P execute, it use the following query to identify the rows to be processes:

select fistRow = min(rowid(S)), lastRow = max(rowid(S)) from stageUser.S

After all the rows in the range are processes, P delete the rows where rowid is in the range (firstRow – lastRow).

If new rows are inserted in the staging table while P is executing and the row ids of those new rows are not guaranty to be bigger that lastRow, then we will need to modify the process P.

Thanks a lot,

Uvernes.

[markmumy · Accepted solution]

On a tangential note, ROWIDs are not always static.  Check out this note and how RLV can affect them: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01840.1600/doc/html/dhe13...

I don't think that we can assume that ROWIDs will always been incrementing or always placed at the end of the table.  They never were in the past, either, where it depended heavily on the Append_Load option setting.  Your code above would have only worked if Append_Load was set to ON pre-IQ 16.

Anyway, in tests that I have done (where I've paid attention to ROWID), it would appear that new rows do show up at the end of the table.  But those are not definitive tests and certainly not something I would rely on.

I would recommend opening a case with support to get the proper answer.  I've got too much speculation going on for my liking.

Mark

[Former Member · Accepted solution]

Thanks a lot Mark.

I'd a ticket open on this subject. 

To play safe we will not relay on row ids, but create a surrogate key (sequential) on the staging table.

Thanks a lot for your help.

Uvernes.