Suggestion: view
December 28, 2007 - 1pm Central time zone
Reviewer: Stew Ashton from Paris, France
In addition to Tom's answer, to alleviate your concerns about dynamic SQL and having two sets of queries, you could put the "full mode" query in a view. That would catch syntax errors, simplify your code and allow you to make future changes in one place.
use case
December 31, 2007 - 7am Central time zone
Reviewer: Karthick from India
and last_update_date >= :l_last_run_date
can be writern as
and last_update_date >= case MODE when 'FULL' then last_update_date else :l_last_run_date end
Followup January 1, 2008 - 6pm Central time zone:
you missed the point.
they WANT two different plans - one that uses an index (when a date is supplied) and one that does not (when all of the data will be accessed)
if you use a single statement - you'll get a single plan... (that is what shared sql is all about)
Re-parse may not be needed
January 2, 2008 - 9am Central time zone
Reviewer: Mike from Cleveland, OH USA
We have had luck with a technique like this, where in_ACCT_NBR represents an 'optional' filter condition:
1 explain plan for
2 SELECT *
3 FROM ACCT_TB
4* WHERE ACCT_NBR = nvl(:in_ACCT_NBR, ACCT_NBR)
09:27:49 > /
Explained.
Elapsed: 00:00:01.52
09:27:52 > SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 3506087093
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1958K| 1701M| 178K (2)| 00:35:43 |
| 1 | CONCATENATION | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | ACCT_TB | 1958K| 1701M| 178K (2)| 00:35:43 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| ACCT_TB | 1 | 911 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | ACCT_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:IN_ACCT_NBR IS NULL)
3 - filter("ACCT_NBR" IS NOT NULL)
4 - filter(:IN_ACCT_NBR IS NOT NULL)
6 - access("ACCT_NBR"=:IN_ACCT_NBR)
Note
-----
- 'PLAN_TABLE' is old version
25 rows selected.
Elapsed: 00:00:00.37
With this technique, either (step 3) or (steps 5+6) will be executed - not both.
One caveat: the column you are filtering on should be NOT NULL; otherwise NULL values will be excluded even if the input is null - by the predicate that resolves to (NULL = NULL).
Followup January 2, 2008 - 11am Central time zone:
yes, that is called nvl or expansion
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7555175291763
and works on simple queries however the above poster said:
... The interface involves dozens of large queries that pull data from various sources in the Oracle e-Business 11i data model. ...
so the odds of it coming into play are near.... zero%
To Mike from Cleveland, OH USA
January 2, 2008 - 10am Central time zone
Reviewer: A reader from London
"One caveat: the column you are filtering on should be NOT NULL; otherwise NULL values will be
excluded even if the input is null - by the predicate that resolves to (NULL = NULL)."
NULL = NULL won't return any rows... as NULL can only be checked with "IS NULL" or "IS NOT NULL"
clause.
HDH
NULL=NULL - yes, that was my point
January 2, 2008 - 11am Central time zone
Reviewer: Mike from Cleveland, OH USA
The 'expected behavior' here is that a null input does not filter.
If you provide a null input (implying no filtering) but you have a null column value, that will
have the undesirable result of being filtered out - because you will end up with a predicate that
resolves to NULL=NULL which is always false.
If your columns is defined as NOT NULL then you can be confident you will not fall into this. If it
may be null, then you have to be more careful and more verbose in the query. And your
more-elaborate query may or may not be able to get the nice dual-purpose plan I showed here.
New idea for this
June 16, 2008 - 7am Central time zone
Reviewer: Matthew McPeak
What about putting logic in like this:
SELECT <system-wide optimizer_index_cost_adj setting>
INTO x;
IF FULL_MODE THEN
execute immediate 'alter session set optimizer_index_cost_adj = ' || x;
ELSE
execute immediate 'alter session set optimizer_index_cost_adj = ' || x+1;
END IF;
Would this not force the optimizer to come up with two plans -- one for full mode and one for our
incremental mode? (Please don't bother to comment on the lack of bounds checking for when X=100.)
Thanks in advance for any comments!
Followup June 16, 2008 - 12pm Central time zone:
If this is true:
For the incremental mode, we want the queries to include a predicate like:
and last_update_date >= :l_last_run_date
And we want the full mode to omit all such predicates.
We would like to avoid using dynamic SQL (since we find that hard to read and hard to maintain
since you don't catch syntax errors until runtime). We would also like to avoid having 2 sets of
queries (one with the predicate and one without).
then this will be my answer:
if ( cond1 )
then
open c for select .....;
else
open c for select .....;
end if;
loop
fetch c bulk collect ....;
it is not dynamic sql, it omits the where clause when you want it omitted. It is straightforward, it relies on no tricks, it would be in my estimation the way to go
Thanks!
June 16, 2008 - 1pm Central time zone
Reviewer: Matthew McPeak
Thank you. I think I understand what you are saying about being straight-forward. I've seen (and
sadly committed) many examples of the fact that "clever" != "smart".
But the "open c for select ...." that gets nearly duplicated is actually 2,200 lines of cursor code
and I have to think that a 5 line "trick" is going to wind up being more straight-forward and
maintainable than 2,200 lines of near duplicate SQL.
Anyway, even though I was hoping to hear something more along the lines of "go for it", I really
appreciate your assistance. Thank you.
Followup June 16, 2008 - 1pm Central time zone:
.... and I have to think that a 5 line "trick" is going to wind
up being more straight-forward and maintainable than 2,200 lines of near
duplicate SQL.
...
sounds like you have a view and a view with a predicate
if
open c for select * from v;
else
open c for select * from v where date...;
I don't like tricks, tricks get lost in history - no one knows why they are there. what happens when the optimizer decides in 11.1.0.17 that a difference of one on the index cost adjust is not meaningful - they are the same (I'm forecasting what might happen)
Food for thought
June 16, 2008 - 2pm Central time zone
Reviewer: Matthew McPeak
It would be more like 18 views, but I see your point. Thank you again!
|