SQL

hi guys,

well this might sound funny but we had this one query for which the
execution time is going on a linear curve upwards as the number of
records in the corresponding tables.

SELECT (-1) * SUM(A.POSTING_AMT) FROM ACC_POSTINGS A , AC_INTERNAL_ACCOUNTS C
WHERE A.INTERNAL_ACCOUNT_ID = C.INTERNAL_ACCOUNT_ID
AND C.SUBFACTOR_1_VAL = :B2
AND C.ACCOUNT_CATEGORY_CODE = '2111200001'
AND A.EFFECTIVE_DATE <= :B1
HAVING SUM(POSTING_AMT) < 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.60     176.25      29240      38728          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.61     176.25      29240      38728          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5386

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  FILTER
      1   SORT AGGREGATE
  34686    TABLE ACCESS BY GLOBAL INDEX ROWID ACC_POSTINGS PARTITION:
ROW LOCATION ROW LOCATION
  34688     NESTED LOOPS
      1      TABLE ACCESS BY INDEX ROWID AC_INTERNAL_ACCOUNTS
  46042       INDEX RANGE SCAN IDX_AC_INT_ACC_SUBVAL1 (object id 96138)
  34686      PARTITION RANGE ITERATOR PARTITION: KEY KEY
  34686       INDEX RANGE SCAN ACC_P_IX_1 PARTITION: KEY KEY (object id 67385)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                     29240        0.25        166.62
  global cache cr request                     18041        0.02          6.13
  SQL*Net message from client                     2      101.67        101.67
  row cache lock                                  1        0.00          0.00

this is using all the appropriate indexes and everything.. is there
something or can you suggest something which will help me to improve
this?

thanks..
--
http://www.freelists.org/webpage/oracle-l

Other related posts: