ROWNUM is a pseudocolumn in Oracle that assigns a unique number to each row fetched by a query. It is often used in pagination queries to limit the number of rows returned and display specific page results.

The query mentioned above demonstrates the usage of ROWNUM in a pagination query. The inner query selects all columns from the table "table_name" and assigns a unique row number to each row using the ROWNUM pseudocolumn. The WHERE clause filters the result set to only include rows where the ROWNUM is less than or equal to 110.

The outer query then further filters the result set to only include rows where the "rn" column (the alias for the ROWNUM) is greater than 100. This effectively skips the first 100 rows and returns the remaining rows, which can be considered as the result set for the desired page.

The reason for using such a query structure is to achieve pagination functionality. When dealing with large datasets, it is often necessary to retrieve data in chunks or pages to improve performance and enhance the user experience. By using ROWNUM in this manner, the query can efficiently retrieve the required page of data without fetching the entire dataset.

To use ROWNUM for pagination, the inner query should be modified with the desired criteria such as sorting, filtering, or joining tables. The WHERE clause in the inner query should specify the desired page limits by setting the ROWNUM condition accordingly. The outer query should then filter the result set based on the desired page number and the number of rows per page.

By using this approach, developers can easily implement pagination in their queries and retrieve data efficiently in smaller chunks. This improves query performance and reduces the load on the database server, resulting in a better user experience.

In conclusion, ROWNUM is a powerful pseudocolumn in Oracle that is commonly used in pagination queries. By understanding how ROWNUM works and how to incorporate it into queries, developers can efficiently retrieve data in a paginated manner, improving query performance and user experience.

问题的出现原因：在Oracle 12中，如果需要进行分页查询，可以使用ROWNUM来实现。然而，很多人可能对ROWNUM的工作原理不太清楚，导致在使用过程中出现一些问题。

解决方法：为了解决这个问题，可以使用以下的SQL语句来实现分页查询：

select owner, object_name, object_id
from t
order by owner, object_name
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

通过以上的SQL语句，我们可以实现每页显示5条记录，跳过前5条记录并显示接下来的5条记录。这样就可以实现分页查询的功能了。

ROWNUM在分页查询中的工作原理是一种限制结果集的方法。ROWNUM是在查询的谓词阶段之后，排序或聚合之前为每一行分配的一个值。ROWNUM仅在被赋值之后递增，这就是为什么以下查询永远不会返回任何行的原因：select * from t where ROWNUM > 1;因为ROWNUM > 1对于第一行来说是不成立的，所以ROWNUM不会递增到2，因此没有任何ROWNUM的值能够大于1。

在Oracle 12c及以上版本中，可以使用新的Top-n行限制功能来实现类似于Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound的查询。例如，以下查询将以升序返回从第四高薪水到第七高薪水之间的员工：

SELECT empno, sal

FROM emp

ORDER BY sal

OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

如果不想在结果中显示r列，可以在外部查询中列出所需的列名，而不是使用select *。此外，在SQL*Plus中可以使用NOPRINT命令来实现。同时，正确编写分页查询可以确保正确的分页。

需要注意的是，分页查询需要使用order by来保证结果的排序，如果没有order by，则结果是随机的。如果排序的列具有重复的值，可能会出现重复的数据在不同页中显示的情况。可以通过将第一个where子句移到外部where子句中并使用rnum作为条件来解决这个问题。

总之，ROWNUM在分页查询中的工作原理是通过为每一行分配一个值来限制结果集，并且需要正确使用order by来保证结果的排序和正确的分页。