Oracle group by too long time

I have this Query:

  SELECT B.BILL_BALANCE,
       B.PRIME_UID,
       home.IDENT,
       PRESENT.ACC_LOGIN,
       PRESENT.ACCOUNT_NAME2,
       eq .ACCESS_开发者_Go百科NO AS PHONE,
           adres.DISP_STREET_NAME,
           B.BILL_DT
FROM addresses adres ,
     BILL_HISTORY B
LEFT JOIN eq eq ON eq.ACCOUNT_NO = B.ACCOUNT_NO
JOIN sec PRESENT ON B.ACCOUNT_NO = PRESENT.ACCOUNT_NO
JOIN sec_text home ON home.ACCOUNT_NO = B.ACCOUNT_NO
WHERE eq.STATUS_CD = 'CU'
  AND (B.BILL_TP_CD='CB'
       OR B.BILL_TP_CD='FB')
  AND (PRESENT.bill_addr_UID=adres.PRIME_UID
       OR PRESENT.COMPANY_REG_ADDR_UID=adres.PRIME_UID)
  AND B.BILL_PAID_DT IS NULL
  AND B.BILL_DT >= to_date('2011-07-01')
  AND B.BILL_DT <= to_date('2011-07-02')
  AND rownum <=1000
GROUP BY B.PRIME_UID,
         B.BILL_BALANCE,
         home.IDENT,
         PRESENT.ACC_LOGIN,
         PRESENT.ACCOUNT_NAME2,
         eq .ACCESS_NO,
             adres.DISP_STREET_NAME,
             B.BILL_DT

I need to "group by" by B.PRIME_UID, after ths I had this error:

    [code] => 979
    [message] => ORA-00979: not a GROUP BY expression
    [offset] => 8

I added all columns to "group by" and it works.

This query works with "rownum" only.When I'm trying to remove "rownum" - Approaching data is too long..more than 50 minutes..

How can I rewrite sql without using rowcount?

Thanks.

Approximation data in table is more than 100 000.

UPDATED*

"Explain PLAN FOR" didn't work.

Array
(
    [error] => 1
    [error text] => not found
    [sql] => EXPLAIN PLAN FOR
SELECT  B.BILL_BALANCE,B.PRIME_UID,home.IDENT,PRESENT.ACC_LOGIN,PRESENT.ACCOUNT_NAME2,eq .ACCESS_NO as PHONE,adres.DISP_STREET_NAME,B.BILL_DT
FROM addresses adres ,BILL_HISTORY B 
     LEFT JOIN eq eq ON eq.ACCOUNT_NO = B.ACCOUNT_NO 
     JOIN   sec PRESENT
         ON B.ACCOUNT_NO = PRESENT.ACCOUNT_NO
     JOIN sec_text home
         ON home.ACCOUNT_NO = B.ACCOUNT_NO



WHERE  
eq.STATUS_CD = 'CU'  AND
 (B.BILL_TP_CD='CB' OR B.BILL_TP_CD='FB') 
AND  (PRESENT.bill_addr_UID=adres.PRIME_UID OR PRESENT.COMPANY_REG_ADDR_UID=adres.PRIME_UID) 
AND B.BILL_PAID_DT IS NULL 
 AND B.BILL_DT >= to_date('2011-07-01') 
 AND B.BILL_DT <= to_date('2011-07-02') 

AND rownum <=10
GROUP BY B.PRIME_UID,B.BILL_BALANCE,home.IDENT,PRESENT.ACC_LOGIN,PRESENT.ACCOUNT_NAME2,eq .ACCESS_NO,adres.DISP_STREET_NAME,B.BILL_DT
)

---

Not only do you have a cartesian product between latrep.ADDRESSES and BILL_HISTORY which is more than likely killing the performance of your query but you have also neglected to specify a date format for your TO_DATE functions Instead of: AND B.BILL_DT >= to_date('2011-07-01')

Use: AND B.BILL_DT >= to_date('2011-07-01', 'YYYY-MM-DD')

The fact that restricting the records returned by specifying rownum <= 10 returns quickly but removing it causes your performance issue tells me that it is either trying to return a vast number of records (cartesian product) or that your execution plan has some serious issues, perhaps full table (or large index range) scans of large tables.

Either way the best method for testing this if you cannot generate an explain plan is to remove the table BILL_HISTROY from your query temporarily to see if performance increases, if so you have identified the problem.

Sort out the join between these two tables and it will definitly help. Get us an explain plan and we can give you a more definite answer.

---

Without an explain plan its hard to say for sure, but I think the problem is related to this:

FROM latrep.ADDRESSES adres ,
     BILL_HISTORY B

You've got a Cartesian Product there. With no join condition, it'll join every row in ADDRESSES to every row in BILL_HISTORY, on top of all the other joins. That will make it take an eternity. You've got this later to try to narrow it down, but I suspect the OR is causing it to have to do a full table scan and cartesian product before it can match any of the criteria:

  AND (SUB.bill_addr_UID=adres.PRIME_UID
       OR SUB.COMPANY_REG_ADDR_UID=adres.PRIME_UID)

Try pulling BILL_HISTORY out of the query entirely and see if performance improves significantly. If it does, that's probably where your problem is. (And then go see your DBA to get EXPLAIN PLAN working, it's really a necessity to solve issues like this.)

---

I think there is a forgotten condition for joining

latrep.ADDRESSES adres 

and

BILL_HISTORY B

That's why it takes soo long.

And you can only have in your select clause (part of the query) directly what you have in the group by clause. If you don't want/need to group by by the other columns, you should not have them there. You should use max, min, avg, ... an aggregate function for their values, as appropriate.

e.g.

select date_in, 
       min(id) as ids_from, 
       max(id) as ids_to
  from table
 group by date_in;