oracle优化查询语法速度,oracle setFetchsize() 优化查询速度

搜索热词

实践分析：

最开始在statement 上面设置fetchsize的原因是想增加查询结果返回的速度，因为这样一下子就可以返回更多的数据，但是实际上当我们加上这个条件的时候，没有起到效果，反而慢了下来，原因不知道。对于fetchsize的用法参考官方说明：

Fetch Size

By default,when Oracle JDBC executes a query,it receives the result set 10 rows at a time from the database cursor. This is the default Oraclerow-prefetch value. You can change the number of rows retrieved with each trip to the database cursor by changing the row-prefetch value (see"Oracle Row Prefetching"for more information).

JDBC 2.0 also allows you to specify the number of rows fetched with each database round trip for a query,and this number is referred to as thefetch size. In Oracle JDBC,the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries executed through that statement object.

Fetch size is also used in a result set. When the statement object executes a query,the fetch size of the statement object is passed to the result set object produced by the query. However,you can also set the fetch size in the result set object to override the statement fetch size that was passed to it. (Also note that changes made to a statement object's fetch size after a result set is produced will have no affect on that result set.)

The result set fetch size,either set explicitly,or by default equal to the statement fetch size that was passed to it,determines the number of rows that are retrieved in any subsequent trips to the database for that result set. This includes any trips that are still required to complete the original query,as well as anyrefetchingof data into the result set. (Data can be refetched,either explicitly or implicitly,to update a scroll-sensitive or scroll-insensitive/updatable result set. See"Refetching Rows".)

Setting the Fetch Size

The following methods are available in allStatement,PreparedStatement,CallableStatement,andResultSetobjects for setting and getting the fetch size:

void setFetchSize(int rows) throws sqlException

int getFetchSize() throws sqlException

To set the fetch size for a query,callsetFetchSize()on the statement object prior to executing the query. If you set the fetch size to N,then N rows are fetched with each trip to the database.

After you have executed the query,you can callsetFetchSize()on the result set object to override the statement object fetch size that was passed to it. This will affect any subsequent trips to the database to get more rows for the original query,as well as affecting any later refetching of rows. (See"Refetching Rows".)

Use of Standard Fetch Size versus Oracle Row-Prefetch Setting

Using the JDBC 2.0 fetch size is fundamentally similar to using the Oracle row-prefetch value,except that with the row-prefetch value you do not have the flexibility of distinct values in the statement object and result set object. The row prefetch value would be used everywhere.

Furthermore,JDBC 2.0 fetch size usage is portable and can be used with other JDBC drivers. Oracle row-prefetch usage is vendor-specific.

See"Oracle Row Prefetching"for a general discussion of this Oracle feature.

Note:

Do not mix the JDBC 2.0 fetch size API and the Oracle row prefetching API in your application. You can use one or the other,but not both.

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。