Re: Help with Slow select query

Hi Mark,

Regrettably I can't yet provide you with the final solution, because I don't know the definition of the indexes used, yet it is more or less clear for me what happens.
The concatenation is being caused by at least one OR series. This forces the optimizer to handle the query in three separate branches, which is exactly what we see here. The usual tric is replace that by IN (cond1,cond2 etc), however that may (doesn't need to) result in a full table scan. If that is a full table scan on the driving table, no problem. Apparently of_accts_general is the driving table. If you are using the rule based optimizer (optimizer mode is choose in init.ora and no statistics on the table), the driving table should be the last in the from clause, as the parser operates backwards from the ; Also it should be B.key = A.key

Hth,

Sybrand Bakker, Oracle DBA

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 - 09:11:57 CST