Select big table

dmitry_n

New Member
Hello. I am interested in the following question. I am connecting to the progress version 11 database using the Squirell sql 4.0.0 client. I have a large table with 8 billion rows. When I select to the table, I get values that are repeated periodically. Moreover, they are repeated every 4294967296 lines. That is, Squirell shows these lines, and then starts showing everything again from the first line. All this happens in one select.
Why can this happen?

Thanks for the help.
 

tamhas

ProgressTalk.com Sponsor
You might try showing the query and the table indices so that we have at least a little information to go on.

One might also ask what use is a query that returns over 4 billion rows...
 

dmitry_n

New Member
The query is just select * from table.
A query is needed to unload the full table into a file.
In the file, I see how after 4294967296 rows the rest of the rows begin to repeat from the very first row in the table. The process takes a long time and doesn’t seem to want to stop.
 

tamhas

ProgressTalk.com Sponsor
While this doesn't answer your question, I wonder why you would use a SQL query to do what a dump already does.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
If you want to dump the contents of a table, e.g. to load them into a table in some other database, this isn't the way to do it.

Instead of describing the details of the problem you're having with your solution, please describe the details of the business problem you are trying to solve. Include information about the target database and the OpenEdge version (more specific than "progress version 11").
 

tamhas

ProgressTalk.com Sponsor
Indeed, sounds like a call to support is indicated, provided the OP is on support. Further reason to do it a different way.
 

TomBascom

Curmudgeon
It doesn't sound like a db problem to me. To me it seems more like Squirrel is treating something (an internal row counter?) as an unsigned 32 bit integer which is actually larger than that (or which needs to be larger than that). The alleged "select * from table" doesn't have a row counter. So where is that coming from? If there is a field in the row that is a sequence and that field was setup as a plain old integer it should have rolled over at 2^31 -1, not 2^32. If it is an int64 field then it is possible that whatever is being used to set it has a 2^32 limit. But none of that is a db problem per se.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
The number of rows returned at one time by a query is controlled by the SQL client application, not by the SQL engine or JDBC or ODBC drivers.

If it is the client that is the limiting factor, and you are determined to dump these records with a SQL query, try sqlexp and see whether the results are different from squirrel.
 

TomBascom

Curmudgeon
Or maybe a bit more information about what this "row counter" actually is and where it is coming from. The actual query being used in Squirrel would be a good start. It would also be helpful to know the complete schema definition of the records in question. Including little details like indexes and trigger definitions.

Playing hide and seek with the details does not amuse me. If our poster would like help I suggest that he or she provide all of the requested follow-up details.
 
Top