Re: reading random records with JDBC?

#1

Ð¹ÐµÐ½Ñ Ð¼Ð°Ñ€Ñ‚Ð¸Ð½ ÑˆÐ»Ð°Ñ‚Ñ‚ÐµÑ€ wrote:

Quote:

From a huge table, I want to read random records via JDBC to make some
statistic things. How do I best and fastest afford this?
Just like this: "select * from ... where rowid=?" or are there better
ways?

JMS

Use the SAMPLE clause
http://www.psoug.org/library.html
scroll down to "SAMPLE CLAUSE."
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#2

Quote:

Use the SAMPLE clausehttp://www.psoug.org/library.html
scroll down to "SAMPLE CLAUSE."

Thank you very much for your good idea!
But I did not tell the whole truth: I want to read random records from
two tables and compare them. So I read record 883 from table1 and
record 883 from table2 and compare the fields. Then I continue with
another record.

Martin

#3

MfwEwkCtzVem (AT) spammotel (DOT) com wrote:

Quote:

Use the SAMPLE clausehttp://www.psoug.org/library.html
scroll down to "SAMPLE CLAUSE."

Thank you very much for your good idea!
But I did not tell the whole truth: I want to read random records from
two tables and compare them. So I read record 883 from table1 and
record 883 from table2 and compare the fields. Then I continue with
another record.

Martin

Then use DBMS_RANDOM to generate random numbers between 1 and the
number of rows in the smallest of the two tables.

http://www.psoug.org/reference/dbms_random.html

alternatively DBMS_CRYPTO.RANDOM_INTEGER
http://www.psoug.org/reference/dbms_crypto.html
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#4

Would it make sense to create 10 rowids in java and then do a query
like that:

select * from table where rowid in
("2378","98989","127489","100323"....)

Thanks,
Martin

#5

MfwEwkCtzVem (AT) spammotel (DOT) com wrote:

Quote:

Would it make sense to create 10 rowids in java and then do a query
like that:

select * from table where rowid in
("2378","98989","127489","100323"....)

Thanks,
Martin

It might unless one were to consider that ROWID is a reserved word in
Oracle with a very specific meaning.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#6

Quote:

It might unless one were to consider that ROWID is a reserved word in
Oracle with a very specific meaning.

Huuu...right...
I tried select * from (select rownum rn from table where
rownum<=recno) where rn=recno,
but with 100.000.000 rows its awfully slow!

Martin

#7

On Nov 5, 4:27 am, <MfwEwkCtz... (AT) spammotel (DOT) com> wrote:

Quote:

Use the SAMPLE clausehttp://www.psoug.org/library.html
scroll down to "SAMPLE CLAUSE."

Thank you very much for your good idea!
But I did not tell the whole truth: I want to read random records from
two tables and compare them. So I read record 883 from table1 and
record 883 from table2 and compare the fields. Then I continue with
another record.

Martin

There are no records in a database, only rows.

So exactly what is the key that makes the rows comparable? Use that.
IOW, select a sample from table 1 and fectch the rows with the same
keys from table 2, and then compare.

HTH,
ed

#8

Quote:

There are no records in a database, only rows.

I just checked the official oracle docu. It's not called "rows", it's
called "zeilen"!

Quote:

So exactly what is the key that makes the rows comparable? Use that.
IOW, select a sample from table 1 and fectch the rows with the same
keys from table 2, and then compare.

I want to compare the tables physically, by rows, and not by key.
I don't know the keys, the program deals with different tables.

JMS

#9

On Nov 7, 4:08 pm, <MfwEwkCtz... (AT) spammotel (DOT) com> wrote:

Quote:

There are no records in a database, only rows.

I just checked the official oracle docu. It's not called "rows", it's
called "zeilen"!

Bad translation? The originals are in English, so check those.

Quote:

So exactly what is the key that makes the rows comparable? Use that.
IOW, select a sample from table 1 and fectch the rows with the same
keys from table 2, and then compare.

I want to compare the tables physically, by rows, and not by key.
I don't know the keys, the program deals with different tables.

JMS

exactly what is the purpose? What business requirement is this?

I have always tried to follow the tenet "there are no stupid
questions" when I read newsgroups, but this thread is pushing my
limits.

Just how would you select the row from the second table?

Think about what you seem to be attempting to do. Consider this
outside the context of Oracle. Let say you were comparing two files
(then record is the correct term). You somehow randomly select records
out of the first file, then what would be the corresponding record in
the second file? By i-node? note that file one might be contiguous on
the filesystem what file two is scattered into several blocks.

I'm trying to point out that what you seem to be asking is very
unusual, and likely not useful at all. It might be analogous to
comparing two bags of groceries by randomly selecting items from each
bag and comparing them. Not a very successful method of testing is it.

Ed
Reminds me somehow of the old observation about two watches. One that
runs fast and one that is completely stopped. Which one is better? The
second one because while the first one never shows the right time, the
second one is correct twice a day!

#10

On Nov 27, 4:21 am, "ÊÅÎÓ ÍÁÒÔÉÎ ÛÌÁÔÔÅÒ" <MfwEwkCtz... (AT) spammotel (DOT) com>
wrote:

Quote:

I have always tried to follow the tenet "there are no stupid
questions" when I read newsgroups, but this thread is pushing my
limits.
Just how would you select the row from the second table?
Think about what you seem to be attempting to do. Consider this
outside the context of Oracle. Let say you were comparing two files
(then record is the correct term). You somehow randomly select records
out of the first file, then what would be the corresponding record in
the second file?

I compare the row 1 in table 1 with row 1 in table 2, then row 100 in
table 1 with row 100 in table 2. Nothing spectacular. My program
works, but is very slow.

JMS

Please define row 100.

Keep in mind that in a relational database, the tables contain SETs of
data. There is no order to a set. (recall your grade school
mathematics class, with Venn Diagrams for example)

So I guess you are basically doing this
open SELECT * from tableA
open SELECT * from tableB
loop
fetch rowA
fetch rowB
compare and report
end loop

so you get a report on Monday. Monday night, the DBA migrates the DB
to a new server, or maybe just reorganizes the file structure. No data
is changed. rerunning you program on Tuesday gives different results.
(Do you know why?)

This report tells you nothing really. So you are doing this
because...?

Given that you are doing full scans BY ROW (whatever that means to
you), then there is no speed up possible. With no criteria for row
selection, you cannot get any better than linear search. You are done.
game over.

Ed
Again, this sounds like busy work or an insane assignment to me. It
makes no sense.