A lot depends on how you are using the results. If this is a frequent query, you might consider creating a Materialized View on the data, including the rownumber as a column in the view.
With a Materialized View, you can index the columns and run very efficient queries against the view, so your pagination on the rownumber range will be very fast.
However, the tradeoff is how frequently the underlying tables are updated versus the queries, and how “current” the results need to be.
If the tables are relatively static (infrequent updates – say once a day or less), then the view could be real-time. If the tables are updated a lot throughout the day, then you might want to schedule updates to the view, instead of rebuilding the view every time an underlying row changes. If the result data didn’t need to be absolutely current, you could schedule the view to refresh once a day, for example.
Materialized Views are very simple in Oracle, and very powerful for creating indexable joined data structures. See “Create Materialized View” in your Oracle documentation.
Note – because the entire rownumber set is likely to be recomputed with each change to the underlying tables, you should probably configure this view to do a complete refresh instead of trying to do a “fast” (or incremental) refresh. A complete refresh is essentially a drop and recreate of the view, which is probably going to be faster than trying to recompute and adjust every row on a name change.
Of course, if the tables are frequently updated, and this query is run infrequently and needs to have the most current information, then your existing query may be the best solution.