cruel
Programmer
- Aug 6, 2001
- 131
Hi, Oracle has a function that allows one to sample a large table. can I do sampling in Informix? Thanks a lot!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
sampling
- Thread starter cruel
- Start date
- Status
- Thread starter
- #3
cruel
Programmer
- Aug 6, 2001
- 131
Some tables are huge. Instead of pulling out the whole table, you randomly select 2% of the total rows? One way you can do it in Oracle is using 'sample (2)' after from statement.
While we are at it, is there also a feature in Informix that is similar to 'where rownum<n' in Oracle?
I have checked online documents and personal referrence, it was not there. Thanks
While we are at it, is there also a feature in Informix that is similar to 'where rownum<n' in Oracle?
I have checked online documents and personal referrence, it was not there. Thanks
cruel:
I don't know much about Oracle. Thanks for the update. The closest thing I can think of using FIRST. Beginning with Online 7, Informix provides a FIRST keyword. So if you wanted to the first 10 rows, it's
SELECT FIRST 10 col1, col2 FROM table_name WHERE ....
For the Informix 4GL programmers, in order to use FIRST, declare the select as a cursor.
Informix also has a rowid. It's a guaranteed unique integer value and does not change for the life of the row. So you can do:
SELECT * FROM table_name WHERE rowid = 45678
If you're using fragmented tables, rowid works a little different. I've really not had fragment tables, but I think you have to delcare the rowid column.
Regards,
Ed
Schaefer
I don't know much about Oracle. Thanks for the update. The closest thing I can think of using FIRST. Beginning with Online 7, Informix provides a FIRST keyword. So if you wanted to the first 10 rows, it's
SELECT FIRST 10 col1, col2 FROM table_name WHERE ....
For the Informix 4GL programmers, in order to use FIRST, declare the select as a cursor.
Informix also has a rowid. It's a guaranteed unique integer value and does not change for the life of the row. So you can do:
SELECT * FROM table_name WHERE rowid = 45678
If you're using fragmented tables, rowid works a little different. I've really not had fragment tables, but I think you have to delcare the rowid column.
Regards,
Ed
Schaefer
- Thread starter
- #5
- Status