how to limit the return size of query

Posted on 2007-07-21
Last Modified: 2013-12-18
I am new to oracle. I really appreciate if having responds from you.
I am developing an application using Java for programing and Oracle for storing data. User can query the data using my application.

And to reserve the resource, the size of result set (number of rows) must be smaller than a specified number. If larger, an exception will be returned instead of the actual result.

I know that class Statement in Java provides an operation setMaxRows(int number). However, this operation just silently dropped the exceeded rows without notification. I want to ask if there is any way in Oracle to check the size of query before actually execute it.

I tried with select count(*) to get the size first, but in this way we need an additional scan of database. Therefore it is not efficient.

Is there any mechanism that I can configure the database to get an announcement when the size of a query is larger than expected? Please help! Thank you very much!
Question by:ttuyen32
    LVL 2

    Accepted Solution

    Add WHERE ROWNUM < 101 to your querey (adjust number to fit)
    if you get less than 100 rows you have the complete result set.
    If you get 100 rows you may have 100 rows, or (more likely?) you have >100 rows.
    You application only shows 99 rows (although retrieving 100), and thus it is safe for you to say that there are more than 99 rows (though you cant state if it is one more row or many more rows)


    Author Comment

    Thanks, Msquare!
    I want that Oracle returns an error or exception instead of the actual result set if the result size is greater than a specific value. With your suggestion, we just limit the size of result set and the remain rows will be silently dropped :D
    LVL 2

    Expert Comment

    Yes .... but at that point you have in your client all the info to decide what to do. I presumed you then throw your own execption or some other error/alternate handling when you know you've scanned 100 rows (and you only wanted 99). As far as doing "needless" work in Oracle you haven't wasted much, only one row. I can think of some very clumsy/difficult joins which would cause Oracle to do lot of work (simplified, work out 10000 rows before returning 100), but in most cases Oraclw will pipeline the rowretrieval/join process and the RowNum filter limit will abort all "inner" searches. Your queries may have to be tuned by including the /*+ First_rows (100)  */ hint.

    I "dont do Java" (cant know everything) but I presume the setMaxRows will do the RowNum trick "behind your back". You can still use the same approach (ie ask for one more row than you want to allow for the user) and the same error handling as I suggest

    There is one way to get Oracle not even start a query if the query is too large - this is using the Resource Manager. This will only become effective for "large" queries, ie it can throw out queries which require more than x CPU seconds. This is usually used in datawarehpuse type systems where you would work your way through billions of rows to get some summary output, and you want to avoid users asking too complicated queries in online (and allow these in batch mode only).

    You can also with Profiles lmiit a user to so many CPU seconds or Disk IO calls per SQL-query. This will start the query, burn the amount of CPU/Disk, and then abort (giving you your error) but we still will have made the atttempt (just like the RowNum limiit).

    I can think of 3rd approach. This will involve a convoluted approach at your end, and probably cost more than you save. (but it depends on the quantiies of datablocks, rows, queries etc you have). You can use the EXPLAIN PLAN on your query. The CostBasedOptimizer returns how many rows it expects the query to return. You then querey the PLAN_TABLE for that number. BEWARE! The CBO estimate is an estimate that can be very wrong. Also you must have up-to-date statiistics on all involved tables.

    And that is as close as you're gonna get to the fuction you want.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This article describes some very basic things about SQL Server filegroups.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now