Re: tuning problem

Hello Stefan,

maybe a function-based index on SUBSTR ( t_03.pobject_uid , 1 ,14 ) would help you?

Regards,

Martin

stefan keller wrote:
>
> hello folowing question, please anwer also to: stefan.keller_at_rieter.com
>
> i have a table (PPOM_STUB) containing 300000 recs
>
> there is following sql from a third company (i cant change the source code)
> SELECT DISTINCT t_01.rsecondary_objectu, t_01.rsecondary_objectc
> FROM
> PIMANRELATION t_01 , PIMANTYPE t_02 WHERE ( ( ( ( t_01.rprimary_objectu =
> :1
> ) AND ( t_01.rrelation_typeu = t_02.PUID ) ) AND ( t_02.ptype_name =
> 'IMAN_master_form' ) ) AND NOT EXISTS ( SELECT t_03.PUID FROM PPOM_STUB
> t_03 WHERE ( t_01.rsecondary_objectu = SUBSTR ( t_03.pobject_uid , 1 ,
> 14 ) ) ) )
>
> this "NOT EXISTS ( SELECT t_03.PUID FROM PPOM_STUB
> t_03 WHERE ( t_01.rsecondary_objectu = SUBSTR ( t_03.pobject_uid , 1 ,
> 14 )" is a killer subselect:
> tkprof says:
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> Parse 291 0.64 0.67 0 0 0
> 0
> Execute 291 0.02 0.02 0 0 0
> 0
> Fetch 291 319.27 320.28 80 473827 0
> 163
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> total 873 319.93 320.97 80 473827 0
> 163
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: 17
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 SORT UNIQUE
> 0 NESTED LOOPS
> 0 TABLE ACCESS BY INDEX ROWID PIMANRELATION
> 2 INDEX RANGE SCAN (object id 2350)
> 201126 INDEX FULL SCAN (object id 2575)
> 0 TABLE ACCESS BY INDEX ROWID PIMANTYPE
> 0 INDEX UNIQUE SCAN (object id 2358)
>
> any suggestions?
>
> db facts
> db_block_size = 8192
> db_file_multiblock_read_count = 32
> db_block_buffers = 125000
> SHARED_POOL_SIZE = 30000000
> SHARED_POOL_RESERVED_SIZE = 15000000
> SHARED_POOL_RESERVED_MIN_ALLOC = 5000
>
> optimizer is choose
Received on Wed Sep 04 2002 - 03:53:40 CDT