Re: How to get the data from a huge table efficiently?

Jet Mah wrote:
> Hi, Steve
>
> First of all, it's my mistake. The huge table is about 30 million rows,
> and the "CallableStatement..." line should be deleted.
> I mean if you want to fetch the data from a huge table, what should you
> do? I just show my way. :)
> Thanks.
>
> Regards,
> Jet
>
>
> Steve Howard wrote:
> > Jet Mah wrote:
> > > Hi,
> > > I want to get the data one by one from a huge table(about 3 thousand
> > > rows) efficiently, this is my way as below:
> > >
> > > //--------------------------------------------------------------------------
> > >
> > > int queryEachTime = 1000;
> > > int queryFrequency = 5000;
> > >
> > > Connection conn = null;
> > > try {
> > >
> > > while(true) {
> > >
> > > if(conn == null){
> > > // "dbPool" is a connection pool
> > > conn = dbPool.getConnection();
> > > }
> > >
> > > CallableStatement qstmt = conn.prepareCall(sql,
> > > ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
> > > String sql = "SELECT ROWNUM, id, name, read " +
> > > "FROM member " +
> > > "WHERE age > 20 AND ROWNUM<=" + queryEachTime + "";
> > > Statement qstmt =
> > > conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
> > > ResultSet.CONCUR_UPDATABLE);
> > > ResultSet rs = qstmt.executeQuery(sql);
> > > // If have new data
> > > boolean haveData = false;
> > > // put the data into memory
> > > Vector v = new Vector();
> > > while (rs.next()) {
> > > RequestBean rb = new RequestBean();
> > > // get data
> > > rb.setId(rs.getBigDecimal("id"));
> > > rb.setName(rs.getBoolean("name"));
> > >
> > > // 放到内存中
> > > v.addElement(rb);
> > >
> > > // update row
> > > rs.updateInt("read_stat", 1);
> > > rs.updateRow();
> > >
> > > haveData = true;
> > > }
> > > // close
> > > rs.close();
> > > rs = null;
> > > qstmt.close();
> > > qstmt = null;
> > > // release connection
> > > conn.close();
> > > conn = null;
> > >
> > > for(int i = 0; i < v.size(); i ++){
> > > RequestBean rb = (RequestBean)v.get(i);
> > > // do something
> > > }
> > > v.clear();
> > >
> > > /* Have finished! */
> > > if(!haveData) {
> > > if(LOG.isDebugEnabled())
> > > LOG.debug("Finished!");
> > > }
> > >
> > > /* Frequency */
> > > try {
> > > Thread.sleep(queryFrequency);
> > > } catch (InterruptedException e) {
> > > }
> > >
> > > }
> > > } catch (ProxoolException e) {
> > > LOG.error("",e);
> > > } catch (SQLException e) {
> > > LOG.error("",e);
> > > } finally {
> > > if (conn != null) {
> > > try {
> > > conn.close();
> > > } catch (SQLException e) {
> > > }
> > > conn = null;
> > > }
> > > }
> > > //--------------------------------------------------------------------------
> > >
> > > But when the application run for a while, It will get the
> > > OutOfMemoryException. Any ideas? Thanks.
> >
> > Hi Jet,
> >
> > My guess is you meant something larger than 3000 rows? 3000 rows is
> > not very large.
> >
> > I am surprised this code even compiles, as you have qstmt defined
> > twice. Also, there are issues (ORA-01446) with most Oracle JDBC
> > drivers when updating a ResultSet. Is "member" a view with an INSTEAD
> > OF trigger?
> >
> > Also, it looks like you always select the first 1000 rows, so may want
> > to just embed that in the sql string.
> >
> > Lastly, can you run the sql itself in SQL*PLUS after setting autotrace
> > on? This would enable you to get a query plan to see why it is slow.
> > If this doesn't sound familiar, talk to your DBA, or read up on it at
> > tahiti.oracle.com.
> >
> > Regards,
> >
> > Steve

How many rows does age > 20 return in total out of the 30 millions rows? Is it indexed?

Have you run the trace I suggested above? If you don't have access to the command line utilities like sql*plus (I know J2EE guys don't sometimes), you can place the following in your code...

      String sqlplan = "select * from table(dbms_xplan.display)";
      qstmt.execute( "explain plan for " + sql);
      ResultSet rsplan = qstmt.executeQuery(sqlplan);
      while (rsplan.next()) {
        System.out.println(rsplan.getString(1));
      }

Run that and post the output.

Regards,

Steve Received on Sat Jun 03 2006 - 11:44:31 CDT