Snip of just the top of bounced message, sigh, lazy old guy forgot to snip
before:
From: Mark W. Farnham [mailto:mwf@xxxxxxxx] ;
Sent: Sunday, November 28, 2021 6:35 AM
To: 'Andy Sayer'
Cc: 'Pap'; 'Oracle L'
Subject: RE: Variable execution time of sql with same plan
By all means, if you can improve SFE_IX1 as Andy suggests, then it should
directly prune to far fewer rows to later filter. You'll pay maintenance
costs for the extra columns, and it may change the behavior of other
queries, but adding Andy's suggested columns probably gets this query to
return in time better expressed as seconds rather than minutes. Looking up a
few tens of thousands of rows by an index instead of looking up 100 million
to over a billion should definitely be an enormous win. The execution may
remain highly variable by bind value, but you won't care.
IF you cannot modify the indexes for whatever reason, then 91 million is
fewer rows to filter than any of your presented cases, and doing that by
creating just the columns you need for those 91 million rows via that index
will need to be done with a sledge hammer to convince the CBO to use it.
Ergo the inline view suggestion: presented initially with only ptcode as a
predicate the CBO will use that index. That changes no indexes. It should
get you fairly consistent timing results, slightly faster than your
presented best case. But it still does an enormous amount of work that
wouldn't be needed at all with an improved SFE_IX1.
Changing the join order structurally is also probably a consistent win, but
if you can improve SFE_IX1 to only pull tens of thousands of rows in the
first place it is not going to matter. If you put the transitive additional
equality in the query it probably gives the CBO a better chance of seeing
whether it is a win, and that is a trivial code change. Only do it
structurally if you are sure it is always a win or at least a tie.
mwf
From: Andy Sayer [mailto:andysayer@xxxxxxxxx] ;
Sent: Saturday, November 27, 2021 7:21 PM
To: Mark W. Farnham
Cc: Pap; Oracle L
Subject: Re: Variable execution time of sql with same plan
Pap,
My suggestion remains to index the SFE table using enough of the columns
you're filtering on so you do less work.
