Picking query based on parameter in Oracle PL/SQL

Question

Ok, say I have a query:

SELECT * FROM TABLE_AWESOME WHERE YEAR = :AMAZINGYEAR;

Which works very nicely. But say I want to be able to return either just those results or all results based on a drop down. (e.g., the drop down would have 2008, 2009, ALL YEARS)

I decided to tackle said problem with PL/SQL with the following format:

DECLARE
  the_year VARCHAR(20) := &AMAZINGYEAR;
BEGIN
  IF the_year = 'ALL' THEN
      SELECT * FROM TABLE_AWESOME;
  ELSE
      SELECT * FROM TABLE_AWESOME WHERE YEAR = the_year;
  END IF;
END;

Unfortunately, this fails. I get errors like "an INTO clause is expected in this SELECT statement".

I'm completely new to PL/SQL so I think I'm just expecting too much of it. I have looked over the documentation but haven't found any reason why this wouldn't work the way I have it. The query I'm actually using is much much more complicated than this but I want to keep this simple so I'll get answer quickly.

Thanks in advance :)

Answer 1

There is a real danger in the queries offered by Jim and Alex.

Assumption, you have 20 years of data in there, so a query on YEAR = return 5% of the blocks. I say blocks and not rows because I assume the data is being added on that date so the clustering factor is high.

If you want 1 year, you want the optimizer to use an index on year to find those 5% of rows.

If you want all years, you want the optimizer to use a full table scan to get every row.

Are we good so far?

Once you put this into production, the first time Oracle loads the query it peaks at the bind variable and formulates a plan based on that.

SO let's say the first load is 'All'.

Great, the plan is a Full table scan (FTS) and that plan is cached and you get all the rows back in 5 minutes. No big deal.

The next run you say 1999. But the plan is cached and so it uses a FTS to get just 5% of the rows and it takes 5 minutes. "Hmmm... the user says, that was many fewer rows and the same time." But that's fine... it's just a 5 minute report... life is a little slow when it doesn't have to be but no one is yelling.

That night the batch jobs blow that query out of the cache and in the morning the first user asks for 2001. Oracle checks the cache, not there, peeks at the variable, 2001. Ah, the best plan for that is an index scan. and THAT plan is cached. The results come back in 10 seconds and blows the user away. The next person, who is normally first, does the morning "ALL" report and the query never returns.

WHY?

Because it's getting every single row by looking through the index.... horrible nested loops. The 5 minute report is now at 30 and counting.

Your original post has the best answer. Two queries, that way both will ALWAYS get the best plan, bind variable peeking won't kill you.

The problem you're having is just a fundamental Oracle issue. You run a query from a tool and get the results back INTO the tool. If you put a select statement into a pl/sql block you have to do something with it. You have to load it into a cursor, or array, or variable. It's nothing to do with you being wrong and them being right... it's just a lack of pl/sql skills.