SELECT   trx.transactionid, trx.status, trx.batchid, trx.referencecode,
         trx.transactiontype, trx.originatoruid, trx.description,
         trx.createtime, trx.VERSION, trx.accountid, trx.amount, trx.currency,
         trx.beneficiaryname, trx.executetime, trx.ebankingrefno, trx.bankid,
         trx.companyid, trx.makerid, trx.servicereqid, trx.vertime,
         trx.relatedrefcode, trx.statuscode, MEMBER.first_name,
         MEMBER.last_name, co.companyname
    FROM TRANSACTION trx,
         companymember MEMBER,
         company co,
         transactionhistory hist,
         bankaccount ba
   WHERE trx.accountid = ba.bankaccountid
     AND ba.companyid = co.companyid
     AND trx.makerid = MEMBER.user_id
     AND hist.transactionid = trx.transactionid
     AND hist.historyid =
            (SELECT MAX (historyid)
               FROM transactionhistory ih
              WHERE trx.transactionid = ih.transactionid
                AND ih.operationdesc <> 'Copy')
     AND trx.transactiontype NOT IN ('LTAV', 'UTAV', 'TDPV', 'FCFDPV', 'LAV', 'LTPAV')
     AND trx.status IN ('Entry', 'Pending', 'P-Signed', 'F-Signed')
     AND trx.VERSION > 0
     AND (   hist.touid = '20051107084392'  OR (hist.fromuid = '20051107084392' AND hist.touid IS NULL) )
     AND co.parentcompanyid = '20031127012134' 
ORDER BY co.parentcompanyid, 
	  	 co.companyname, 
		 trx.ebankingrefno,
         trx.createtime

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	17  	 	      	             	 
  FILTER		  	 	 	 	      	             	 
    SORT GROUP BY		1  	616  	17  	 	      	             	 
      NESTED LOOPS		1  	616  	16  	 	      	             	 
        NESTED LOOPS		1  	557  	15  	 	      	             	 
          NESTED LOOPS		2  	1014  	13  	 	      	             	 
            NESTED LOOPS		2  	968  	11  	 	      	             	 
              NESTED LOOPS		2  	868  	7  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	EBN.TRANSACTIONHISTORY	3  	120  	3.25455776495451  	 	      	             	 
                  BITMAP CONVERSION TO ROWIDS		  	 	 	 	      	             	 
                    BITMAP OR		  	 	 	 	      	             	 
                      BITMAP CONVERSION FROM ROWIDS		  	 	 	 	      	             	 
                        INDEX RANGE SCAN	EBN.IDX_TRXHIS_TOUID	  	 	1  	 	      	             	 
                      BITMAP CONVERSION FROM ROWIDS		  	 	 	 	      	             	 
                        INDEX RANGE SCAN	EBN.IDX_TRXHIS_FROMUID	  	 	1  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	EBN.TRANSACTION	1  	394  	1  	 	      	             	 
                  INDEX UNIQUE SCAN	EBN.PK_EBTRANSACTION	265  	 	 	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	EBN.TRANSACTIONHISTORY	1  	50  	2  	 	      	             	 
                INDEX RANGE SCAN	EBN.IDX_HISTORY_TRXID	2  	 	1  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	EBN.BANKACCOUNT	1  	23  	1  	 	      	             	 
              INDEX UNIQUE SCAN	EBN.PK_BANKACCOUNT	1  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	EBN.COMPANY	1  	50  	1  	 	      	             	 
            INDEX UNIQUE SCAN	EBN.PK_COMPANY	14 K	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	EBN.COMPANYMEMBER	1  	59  	1  	 	      	             	 
          INDEX UNIQUE SCAN	EBN.PK_CMPMEMBER	1  	 	 	 	      	             	 

SELECT count(*)
, avg(cnt)
, max(cnt)
, stddev(cnt)
FROM (
   SELECT transactionid, count(*) AS cnt
   FROM transactionhistory hist
   WHERE hist.touid = '20051107084392'
   OR (hist.fromuid = '20051107084392' AND hist.touid IS NULL)
)

SELECT count(*),avg(cnt), max(cnt), stddev(cnt)
FROM (
   SELECT transactionid , count(*) AS cnt
   FROM transactionhistory hist
   WHERE 0=0
   AND  hist.touid = '20030325000537'
   OR (hist.fromuid = '20030325000538' AND hist.touid IS NULL)
   group by transactionid)

count(*)  | 	avg(cnt) | max(cnt) | stddev(cnt)
4.00      |     1.75	 |   3.00   |  0.96

No rows are in TRANSACTION  = 26
No rows are in transactionhistory= 16
No rows the SQL return =  around 125 ( based on the data)
currently take         = 156 msc