| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How to get the data from a huge table efficiently?
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.
>
>
>
>
>
 |
 |