Oracle Count Millions paging query total count, total bar count optimization

Oracle Count Millions paging query total number of records, total bar count optimization

Oracle Count millions query total number of records, total bar count optimization

Recently do a project, do paging, found that paging query speed is very slow, paging I do is two queries, one is the total number of queries, one is to query the paging results

/** query total number of records **/    SELECT        COUNT (ID) from the        USER order by        id/** query result set **/select        *     from        (select            row_.*,            rownum rownum_         from            (select                ID,                 user_number,                user_name,                User_password,                     sex,                registered_time,                last_login_time,                post                    from                user  U            ORDER by                U.id) Row_         where            rownum <=?        )     where        rownum_ >?

The user table records are more than 1.28 million, this is no query criteria when the query, that is, the user just entered the module query, found that the query time is 2566ms~2152ms, separate execution of each statement, found that the first execution time in more than 2000ms, in pl/ The results of the execution in SQL also confirm my judgment. Therefore, the SELECT Count statement is optimized.

On the internet to find a lot of optimization programs, mostly unsatisfactory, (the table of the way sounds good, but because the single table is a historical reason, here is not considered). Finally find a more satisfying answer. is to add/*+rowid (user) */or/*+ INDEX (user ID) */In the statement to improve the query effect.

I heard that this is the mandatory use of index statistics results? If there is any shrimp can tell me the principle, please come to more guidance!

Select  /*+rowid (user) */   count (*) from  user T or select  /*+ INDEX (user ID) */COUNT (*) from user  T

After use, a single statistic of the total number of queries around 800ms, the results of paging query basically between 900ms~950ms, basically within a second, reached the original design requirements.

Of course, this is not added query conditions, when the query conditions are added, regardless of the previous plus no forced index, the result time is between 2000ms, so if you want to make a conditional query, you need to optimize in the where condition. Pay particular attention to the order of conditional fields before and after queries.

For specific optimizations, refer to

1. Analysis of Oracle Statement optimization rules

Http://www.cnblogs.com/Automation_software/archive/2011/01/21/1940883.html

Oracle Count Millions paging query total count, total bar count optimization