SQL

From: Vishal <mr.v.shah_at_gmail.com>
Date: Thu, 30 Jun 2005 17:28:52 +0530
Message-ID: <33fcbdce0506300458431fa269@mail.gmail.com>

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 Received on Thu Jun 30 2005 - 08:04:22 CDT