Best way to select from millions of rows in Oracle DB

Question

G'day!

I have a million different words that I would like to query in a table with 15 million rows. The result of synonyms along with the word is processed after each request.

Table

as follows:

    synonym      word
    ---------------------
    ancient      old
    anile        old
    centenarian  old
    darkened     old
    distant      far
    remote       far
    calm         gentle
    quite        gentle

Here's how this is done in Java now:

....
PreparedStatement stmt;
ResultSet wordList;
ResultSet syns;
...

stmt = conn.prepareStatement("select distinct word from table");
wordList = stmt.executeQuery();

while (wordList.next()) {
    stmt = conn.prepareStatement("select synonym from table where word=?");
    stmt.setString(1, wordList.getString(1));
    syns = stmt.executeQuery();

    process(syns, wordList.getString(1));
}
...

It is incredibly slow. What is the fastest way to do such things?

Cheers, Chris

Answer 1

Two ideas:

a) How to make one request:

select synonym from table where word in (select distinct word from table)

b) Or, if a method processneeds to deal with them as a set of synonyms for a single word, why not sort them by wordand start processover every time it worddiffers? This query will look like this:

select word, synonym 
from table 
order by word

Answer 2

• , "" .

• . , , , , .

• sblundy .

Answer 3

, ? select word, synonym from table order by word, Java.

Answer 4

PreparedStatement stmt;
ResultSet syns;
...

stmt = conn.prepareStatement("select distinct " + 
                             "  sy.synonm " + 
                             "from " +
                             "  table sy " +
                             "  table wd " +
                             "where sy.word = wd.word");
syns = stmt.executeQuery();
process(syns);

Answer 5

, :

while (wordList.next()) {
    stmt = conn.prepareStatement("select synonym from table where word=?");
    stmt.setString(1, wordList.getString(1));
    syns = stmt.executeQuery();

    process(syns, wordList.getString(1));
}

:

stmt = conn.prepareStatement("select synonym from table where word=?");
while (wordList.next()) {
    stmt.setString(1, wordList.getString(1));
    syns = stmt.executeQuery();

    process(syns, wordList.getString(1));
}

, db /cache/etc, . , , , .

Answer 6

set setFetchSize . , , setFetchSize (someRelativelyHighNumberLike1000). , java 1000 , Oracle [ , , ). . /,

• 1
• 1

,

• fetch 50/100/1000
• 50/100/1000

just hold 50/100/1000 [or no matter how much you extract right away] in some array structure until you process them.

Answer 7

The problem is resolved. The important thing is that the table can be sorted by word. Therefore, I can easily iterate over the whole table. Like this:

....
Statement stmt;
ResultSet rs;
String currentWord;
HashSet<String> syns = new HashSet<String>();
...

stmt = conn.createStatement();
rs = stmt.executeQuery(select word, synonym from table order by word);

rs.next();
currentWord = rs.getString(1);
syns.add(rs.getString(2));

while (rs.next()) {
    if (rs.getString(1) != currentWord) {
        process(syns, currentWord);
        syns.clear();
        currentWord = rs.getString(1);
    }
    syns.add(rs.getString(2));
}
...