SELECT   10000 AS parentindicator, NULL AS companyname, templatetype, seqnum,
         NAME, description, beneficiaryname, trxtype, companyid AS companyid,
         user_id, accountid, lastupdatedate
    FROM templates
   WHERE nvl(accountid,-1) = -1
   		 AND companyid = '20020423000007'
		 AND templatetype IN ('2')		 
UNION ALL
SELECT  (SELECT COUNT (*)
            FROM company com
           WHERE a.accountid IS NOT NULL
             AND a.accountid = b.bankaccountid
             AND b.companyid = com.parentcompanyid) AS parentindicator,
         c.companyname AS companyname, templatetype, seqnum, NAME,		
         description, beneficiaryname, trxtype, a.companyid AS companyid,
         user_id, accountid, lastupdatedate
    FROM templates a, bankaccount b, company c
   WHERE a.accountid is not null
     AND a.accountid = b.bankaccountid
	 AND b.companyid = c.companyid      
     AND a.companyid = c.companyid      
     AND a.companyid = '20020423000007' 
     AND a.templatetype IN ('2')
ORDER BY parentindicator DESC, companyname, lastupdatedate DESC;

=================================================================================
Explain Plan
==============
Operation				Object Name		Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
-----------------------------------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=CHOOSE				3 K	 	338  	 	      	             	 
  SORT ORDER BY							3 K	309 K	253.3  	 	      	             	 
    UNION-ALL		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	EBN.TEMPLATES		3 K	306 K	66.3  	 	      	             	 
        BITMAP CONVERSION TO ROWIDS		  	 	 	 	      	             	 
          BITMAP AND		  	 	 	 	      	             	 
            BITMAP CONVERSION FROM ROWIDS		  	 	 	 	      	             	 
              INDEX RANGE SCAN		EBN.IDX_TEMP_ACCOUNT	3 K	 	10  	 	      	             	 
            BITMAP CONVERSION FROM ROWIDS		  	 	 	 	      	             	 
              INDEX RANGE SCAN		EBN.IDX_CMP_TTYPE	3 K	 	69  	 	      	             	 
      NESTED LOOPS						21  	3 K	187  	 	      	             	 
        NESTED LOOPS						21  	2 K	166  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	EBN.TEMPLATES		21  	2 K	145  	 	      	             	 
            INDEX RANGE SCAN		EBN.IDX_CMP_TTYPE	3 K	 	69  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	EBN.BANKACCOUNT		1  	16  	1  	 	      	             	 
            INDEX UNIQUE SCAN		EBN.PK_BANKACCOUNT	1  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	EBN.COMPANY		1  	32  	1  	 	      	             	 
          INDEX UNIQUE SCAN		EBN.PK_COMPANY		1