What is an unbounded query?

Is an unbounded query a query without a WHERE param = value statement?

Apologies for the simplicity of this one.

Answer

An unbounded query is one where the search criteria is not particularly specific, and is thus likely to return a very large result set. A query without a WHERE clause would certainly fall into this category, but let’s consider for a moment some other possibilities. Let’s say we have tables as follows:

CREATE TABLE SALES_DATA
  (ID_SALES_DATA      NUMBER PRIMARY KEY,
   TRANSACTION_DATE   DATE NOT NULL
   LOCATION           NUMBER NOT NULL,
   TOTAL_SALE_AMOUNT  NUMBER NOT NULL,
   ...etc...);

CREATE TABLE LOCATION
  (LOCATION  NUMBER PRIMARY KEY,
   DISTRICT  NUMBER NOT NULL,
   ...etc...);

Suppose that we want to pull in a specific transaction, and we know the ID of the sale:

SELECT * FROM SALES_DATA WHERE ID_SALES_DATA = <whatever>

In this case the query is bounded, and we can guarantee it’s going to pull in either one or zero rows.

Another example of a bounded query, but with a large result set would be the one produced when the director of district 23 says “I want to see the total sales for each store in my district for every day last year”, which would be something like

SELECT LOCATION, TRUNC(TRANSACTION_DATE), SUM(TOTAL_SALE_AMOUNT)
  FROM SALES_DATA S,
       LOCATION L
  WHERE S.TRANSACTION_DATE BETWEEN '01-JAN-2009' AND '31-DEC-2009' AND
        L.LOCATION = S.LOCATION AND
        L.DISTRICT = 23
  GROUP BY LOCATION,
           TRUNC(TRANSACTION_DATE)
  ORDER BY LOCATION,
           TRUNC(TRANSACTION_DATE)

In this case the query should return 365 (or fewer, if stores are not open every day) rows for each store in district 23. If there’s 25 stores in the district it’ll return 9125 rows or fewer.

On the other hand, let’s say our VP of Sales wants some data. He/she/it isn’t quite certain what’s wanted, but he/she/it is pretty sure that whatever it is happened in the first six months of the year…not quite sure about which year…and not sure about the location, either – probably in district 23 (he/she/it has had a running feud with the individual who runs district 23 for the past 6 years, ever since that golf tournament where…well, never mind…but if a problem can be hung on the door of district 23’s director so be it!)…and of course he/she/it wants all the details, and have it on his/her/its desk toot sweet! And thus we get a query that looks something like

SELECT L.DISTRICT, S.LOCATION, S.TRANSACTION_DATE,
       S.something, S.something_else, S.some_more_stuff
  FROM SALES_DATA S,
       LOCATIONS L
  WHERE EXTRACT(MONTH FROM S.TRANSACTION_DATE) <= 6 AND
        L.LOCATION = S.LOCATION
  ORDER BY L.DISTRICT,
           S.LOCATION

This is an example of an unbounded query. How many rows will it return? Good question – that depends on how business conditions were, how many location were open, how many days there were in February, etc.

Put more simply, if you can look at a query and have a pretty good idea of how many rows it’s going to return (even though that number might be relatively large) the query is bounded. If you can’t, it’s unbounded.

Share and enjoy.