Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Tuning the SQL Query

Re: Tuning the SQL Query

From: <mamatha.lucas_at_gmail.com>
Date: 31 May 2006 15:25:12 -0700
Message-ID: <1149114312.177778.165920@f6g2000cwb.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US