Help with Slow select query

From: Mark Foley <foleym_at_meftechno.com>
Date: 28 Feb 1999 07:00:24 PST
Message-ID: <7bblm8$kd@chronicle.concentric.net>

I am not by any stretch of the imagination a SQL or Oracle expert. I am just now
starting to tune my application and understand how to improve my database performance.

In the case of the following Select, in which I want only 4 rows returned, the resulting select
logic seems much more complicated than seems necessary.

Why does this Select result in all of the Nesting and subsequent poor performance?

How can I rewrite it to return the same result set, but more efficiently?

Thank you!

SELECT A.ACCOUNTNUM,A.Status,A.Ptype,A.Stype,A.RAU,A.AAU,A.OFFILE,A.BOFILE,   A.REGCNTRY,B.NAME,B.PTYPE,B.ADDR1,B.ADDR2,B.ADDR3,B.ADDR4,B.ADDR5,B.ADDR6,   B.CITY,B.STATE,B.ZIPCODE,B.SSN,B.SDNINFO,B.ACCOUNTNUM FROM
 of_accts_general A,of_accts_namaddr B WHERE A.ACCOUNTNUM=B.ACCOUNTNUM AND   A.Ptype='TRIC' AND B.PTYPE='TRIC' AND (A.Status='U' OR A.Status='M' OR   A.Status='N')AND (A.OFFILE='1' OR A.BOFILE='1' OR A.REGCNTRY='1')AND ROWNUM
  < 4 ORDER BY A.ACCOUNTNUM

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.36 0.38 1 0 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 5.67 24.52 2126 63167 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 3 6.03 24.90 2127 63167 1 3

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 12 (OFAC)

Rows Execution Plan
------- ---------------------------------------------------

      0  SELECT STATEMENT   GOAL: CHOOSE
      3   SORT (ORDER BY)
      3    COUNT
      0     CONCATENATION
      0      FILTER
      0       NESTED LOOPS
      0        TABLE ACCESS (BY ROWID) OF 'OF_ACCTS_GENERAL'
      1         INDEX (RANGE SCAN) OF 'NI_OF_ACCTS' (NON-UNIQUE)
      0        TABLE ACCESS (BY ROWID) OF 'OF_ACCTS_NAMADDR'
      0         INDEX (RANGE SCAN) OF 'NI_OF_NAMADDR' (NON-UNIQUE)
      0      FILTER
      0       NESTED LOOPS
      0        TABLE ACCESS (BY ROWID) OF 'OF_ACCTS_GENERAL'
      1         INDEX (RANGE SCAN) OF 'NI_OF_ACCTS' (NON-UNIQUE)
      0        TABLE ACCESS (BY ROWID) OF 'OF_ACCTS_NAMADDR'
      0         INDEX (RANGE SCAN) OF 'NI_OF_NAMADDR' (NON-UNIQUE)
  12177      FILTER
  12177       NESTED LOOPS
   7761        TABLE ACCESS (BY ROWID) OF 'OF_ACCTS_GENERAL'
   7762         INDEX (RANGE SCAN) OF 'NI_OF_ACCTS' (NON-UNIQUE)
  12177        TABLE ACCESS (BY ROWID) OF 'OF_ACCTS_NAMADDR'
  19938         INDEX (RANGE SCAN) OF 'NI_OF_NAMADDR' (NON-UNIQUE)

****************************************************************************

---

Received on Sun Feb 28 1999 - 09:00:24 CST