Ajuda Query - Urgente

Question

 

Amigos estou fazendo uma união de uma query com uma outra subquery, porém quando consulto a quantidade de registros para essa operação aparece um valor maoir de 170 milhões de registros. Como isso é possível visto que nas duas tabelas consultas não possui mais de 9 milhões de registros?

 

Ah uma outra pergunta como faço um join no Oracle? Quando tento jogar a mesma sintaxe do SQLSERVER não funciona.

 

Segue a consulta que utilizo.

SELECT COUNT(*) FROM (Select ILA.INVOICE_ACCOUNT_ID CONTARBOR, ITSI.INVOICE_ACCOUNT_ID,
     ITSI.SERV_INST_NUMBER,
     ITSI.SERV_INST_STATUS_ID,
     Max(ITSI.SERV_INST_STATUS_DATE) SERV_INST_STATUS_DATE,
     Max(ITSI.SERV_INST_RATEPLAN) SERV_INST_RATEPLAN
 from ICS_T_SERV_INST ITSI,
   ICS_L_ACCOUNT ILA
where ITSI.INVOICE_ACCOUNT_ID = ILA.INVOICE_ACCOUNT_EXTERNAL_ID
    and ITSI.SERV_INST_STATUS_ID = 'Ativado'
    --and ITSI.INVOICE_ACCOUNT_ID = 2743261813
group by ILA.INVOICE_ACCOUNT_ID, ITSI.INVOICE_ACCOUNT_ID, ITSI.SERV_INST_NUMBER, ITSI.SERV_INST_STATUS_ID

 UNION ALL
 
 Select SubQ2.CONTARBOR,
     SubQ2.INVOICE_ACCOUNT_ID,
     SubQ2.SERV_INST_NUMBER,
     SubQ2.SERV_INST_STATUS_ID,
     SubQ2.SERV_INST_STATUS_DATE,
     SubQ2.SERV_INST_RATEPLAN
 from
  
  (Select ILA.INVOICE_ACCOUNT_ID CONTARBOR, ITSI.INVOICE_ACCOUNT_ID,
      ITSI.SERV_INST_NUMBER,
      ITSI.SERV_INST_STATUS_ID,
      Max(ITSI.SERV_INST_STATUS_DATE) SERV_INST_STATUS_DATE,
      Max(ITSI.SERV_INST_RATEPLAN) SERV_INST_RATEPLAN
  From ICS_T_SERV_INST ITSI,
    ICS_L_ACCOUNT ILA
  Where ITSI.INVOICE_ACCOUNT_ID = ILA.INVOICE_ACCOUNT_EXTERNAL_ID
     and ITSI.SERV_INST_STATUS_ID = 'Ativado'
     --and ITSI.INVOICE_ACCOUNT_ID = 2743261813
  Group By ILA.INVOICE_ACCOUNT_ID, ITSI.INVOICE_ACCOUNT_ID, ITSI.SERV_INST_NUMBER, ITSI.SERV_INST_STATUS_ID) SubQ1,
  
  
   (Select ILA.INVOICE_ACCOUNT_ID CONTARBOR, ITSI.INVOICE_ACCOUNT_ID,
      ITSI.SERV_INST_NUMBER,
      ITSI.SERV_INST_STATUS_ID,
      Max(ITSI.SERV_INST_STATUS_DATE) SERV_INST_STATUS_DATE,
      Max(ITSI.SERV_INST_RATEPLAN) SERV_INST_RATEPLAN
  from ICS_T_SERV_INST ITSI,
    ICS_L_ACCOUNT ILA
  where ITSI.INVOICE_ACCOUNT_ID = ILA.INVOICE_ACCOUNT_EXTERNAL_ID
     and ITSI.SERV_INST_STATUS_ID = 'Desativado'
     --and ITSI.INVOICE_ACCOUNT_ID = 2743261813
  group by ILA.INVOICE_ACCOUNT_ID, ITSI.INVOICE_ACCOUNT_ID, ITSI.SERV_INST_NUMBER, ITSI.SERV_INST_STATUS_ID ) SubQ2
  
 Where SubQ1.INVOICE_ACCOUNT_ID = SubQ2.INVOICE_ACCOUNT_ID
    and SubQ1.SERV_INST_NUMBER <> SubQ2.SERV_INST_NUMBER
   
 Group by SubQ2.CONTARBOR,
    SubQ2.INVOICE_ACCOUNT_ID,
    SubQ2.SERV_INST_NUMBER,
    SubQ2.SERV_INST_STATUS_ID,
    SubQ2.SERV_INST_STATUS_DATE,
    SubQ2.SERV_INST_RATEPLAN)