Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Tuning the SQL Query
Hello:
I tried changing the query as you suggested it doesnt bring th right amount of records.
I did not write this query, somebody else did and I'm trying Performance Tuning on the SQL Query.
I see that there are lot of self joins & outer joins, I was wondering if changing the outer joins to FULL OUTER JOIN ( we are on 9.2) will help improve the performance.
Thanks for all your help & suggestions.
Mamatha
carla_rj_at_imagelink.com.br wrote:
> I took a look on your query and don't understood why you used the IN
> expression.
>
> If you replace this part:
>
> AND meter.row_id IN
> (
> SELECT /*+ INDEX(meter2 S_ASSET_EM_UNIVID) INDEX(arsm2
> S_ASSET_REL_F1) */
> meter2.row_id
> FROM siebel.s_asset_rel_at_MUDR2SEBL arsm2,
> siebel.s_asset_at_MUDR2SEBL meter2
> WHERE arsm2.x_rel_status = 'Active'
> AND arsm2.relation_type_cd = 'SDP-METER'
> AND meter2.row_id = arsm2.asset_id
> AND meter2.data_src = 'JEA'
> AND meter2.type_cd = 'Meter'
> AND meter2.x_universal_id LIKE TRANSLATE(
> '25343590','*', '%' )
> AND rownum <= ROUND( 50 * 1.5 )
> )
>
> by this one, your query may improve the performance.
>
> ( meter.data_src = 'JEA'
> AND meter.type_cd = 'Meter'
> AND meter.x_universal_id LIKE TRANSLATE(
> '25343590','*', '%' )
> )
>
> Regards,
>
> Carla
Received on Wed May 31 2006 - 17:25:12 CDT