Re: vendor sql tuning

Chris,

I know you can't change the sql, so you can not use the no_unnest hint, but you could try setting _unnest_subquery to false, at least at the session level, and see if that helps.

Chris Stephens wrote:

> on the one hand you're correct. on the other hand, i see know reason
> why the optimizer should be full scanning the index. ...and i also
> see know reason why it's not possible to convince the optimizer to use
> the index as a lookup. ...the last thing i tried was setting
> optimizer_index_cost_adj = 1 ...still no luck.
>
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> Parse 1 0.01 0.01 0 0 0 0
> Execute 1 0.00 0.00 0 0 0 0
> Fetch 1 136.30 333.73 367616 17004541 0 0
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> total 3 136.31 333.74 367616 17004541 0 0
>
> ---------------------------------------------------------------------------------------
> | Id | Operation |
> Name | Rows | Bytes | Cost |
> ----------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT |
> | 281K| 22M| 826 |
> |* 1 | FILTER |
> | | | |
> | 2 | TABLE ACCESS BY INDEX ROWID | CASE_ID
> | 281K| 22M| 826 |
> |* 3 | INDEX FULL SCAN | CASE_ID_IDX1
> | 2889K| | 26 |
> |* 4 | TABLE ACCESS BY INDEX ROWID | ADDRESS
> | 1 | 25 | 2 |
> |* 5 | INDEX UNIQUE SCAN | ADDRESS_PK1
> | 1 | | 1 |
> | 6 | NESTED LOOPS |
> | 1 | 41 | 3 |
> |* 7 | INDEX RANGE SCAN |
> ASSOCIATED_ADDRESS_PK | 1 | 16 | 2 |
> |* 8 | TABLE ACCESS BY INDEX ROWID | ADDRESS
> | 1 | 25 | 1 |
> |* 9 | INDEX UNIQUE SCAN | ADDRESS_PK1
> | 1 | | |
> ----------------------------------------------------------------------------------------
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l