Re: SQL-tuning

Hi Andrei,

Can you confirm that statistics on both tables and related indexes are currently accurate.

Regards

Richard
"Andrei Romazanov" <romazanov.andrei_at_gfos.de> wrote in message news:ai94j4$12ll64$1_at_ID-70985.news.dfncis.de...
> Hello All!
>
> I cannot understand the following behavior of ORACLE-optimizer
>
> SELECT a FROM tab1 WHERE (a,b,c,d,e,f,g,h) IN
> (SELECT a,b,c,d,e,f,g,h FROM tab2);
>
> In tab1 - 300000 records, in tab2 - 0
> both tables have primary key (a,b,c,d,e,f,g,h)
>
> Result:
>
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 NESTED LOOPS
> 2 1 TABLE ACCESS (FULL) OF 'TAB1'
> 3 1 INDEX (UNIQUE SCAN) OF 'SYS_C00439582' (UNIQUE)
> ----------------------------------------------------------
> 0 recursive calls
> 4 db block gets
> 241688 consistent gets
> 222 physical reads
> 0 redo size
> 146 bytes sent via SQL*Net to client
> 309 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
> 'SYS_C00439582' - primary key in tab2
>
> If I delete any field from a,b,c,d,e,f,g,h, for example:
>
> SELECT a FROM tab1 WHERE (a,b,c,d,e,f,g) IN (SELECT a,b,c,d,e,f,g FROM
> tab2);
>
> Result is absolutely differently:
>
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 NESTED LOOPS
> 2 1 VIEW
> 3 2 SORT (UNIQUE)
> 4 3 TABLE ACCESS (FULL) OF 'īTAB2'
> 5 1 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1'
> 6 5 INDEX (RANGE SCAN) OF 'I9_TAB1' (NON-UNIQUE)
> ----------------------------------------------------------
> 0 recursive calls
> 3 db block gets
> 39 consistent gets
> 0 physical reads
> 0 redo size
> 146 bytes sent via SQL*Net to client
> 309 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
> Here index I9_TAB1 - any index in tab1 (c, d, e, f).
> I could understand that in the first case optimazer wants to take index
from
> tab2,
> because all fields are explicitly listed.
> I do not understand however, why it leads to full scan on tab1.
>
> Thank you in advance
> Andrei
>
>
>
Received on Thu Aug 01 2002 - 02:10:01 CDT