I found that my Account.Id is looping because that is a calculated value (multiple properties combined into one).

You are correct, it is more WHERE conditions.  In the original query I did have those values as more WHERE, but I am attempting to speed up the processing.

I started looking at the data, and found the query was doing A LOT of process/retrieving of data that was later thrown on the floor.  If I could make the query use the Account.Type first, I would eliminate extra time joining other (large) tables.

You are correct, that where clause is not exactly right (as this is not my real SQL, but a representation of what I was doing)

Should've been:

select Account.Name, Account.State, Transaction.Amt, Transaction.Date, Transaction.Service
from Transaction 
left join Account 
    on Account.Id = Transaction.Account  
where Transaction.Account in (
    Select Account.Id
     from Account
     where Account.Type is not null
     and Account.Id>123456789
     and Account.Id<=323456789
)
and Transaction.Date >= ?
and Transaction.Date <= ?

I attempted to use '%NOFLATTEN', which changed the 'show plan', but the range is not acknowledged in the plan, showed  'looping on'.

I also ran a real SQL and watched via JOBEXAM and the process started with 0.

Hello Alexander

Thanks for the response.

So by default, all other process are NOT running 'READ COMMITTED' (I assume b/c of the overhead).  Is there a global setting that can be changed so that all process are in 'READ COMMITTED' mode to PREVENT dirty reads (instead of having to set that command on every process)?  

That is the problem, I can't get myFlag to that method.

I attempted that earlier, but the Trigger does not fire until 'before the save'. 

As I have a ' objectgenerator' my code and it is calling a legacy function, where the index is set.  As I step through the code, the legacy operations is performed and then the trigger is fired.  While I can 'see' the myFlag variable, within the trigger, the index has already been set.