peformace issue on swwwihead

[Former Member]

Hi - I am facing some peformace issue on the below SELECT. Please provide some suggestions.

select wi_rh_task wi_text from swwwihead

into table it_wi_text

where wi_type in s_type and

wi_rh_task in s_task and

wi_stat in s_stat and ==> S_STAT: Considering all tasks and not passing any specific.

wi_cd in s_dtcr.

Regards

Eswar

[Former Member]

Change the order of field selection in your where clause :-

It should be as -

select wi_rh_task wi_text from swwwihead

into table it_wi_text

where wi_type in s_type and

wi_stat in s_stat and ==> S_STAT: Considering all tasks and not passing any specific.

wi_cd in s_dtcr and

wi_rh_task in s_task .

Edited by: vijetasap on Nov 2, 2009 10:14 AM

Edited by: vijetasap on Nov 2, 2009 10:16 AM

[Former Member]

Thx for the inputs.

We have to conside seconday index order here. In this case w_cd will sit in last in the where clause.

[Former Member]

Hi,

check Note 790920 which is suggesting to add a Db hint to get the correct index. Looks like you can use the same Index for your selection.

%_hints

oracle 'INDEX("&TABLE&" "SWWWIHEAD~C" "SWWWIHEAD^C" "SWWWIHEAD_C" "SWWWIHEAD_C__X")'

DB2 'USE VALUES FOR OPTIMIZATION'

DB2 '&SUBSTITUTE VALUES&'.

Cheers,

Anders