Re: Help with Slow select query

Note the position of the sort in the explain plan: After the rows are reduced from 12177 to 3, the remaining 3 are sorted.

Is this really what you want?

Mark Foley wrote:

> 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 - 15:26:02 CST