database TEST;
-- generate test data
create table char_test
as
(select calendar_date - current_date as upi_id,
trim(trailing from
substring('I have a query which Hot amp Cpu/Impact on CPu is 19.6 what does it mean？It means the hot amp is doing 19.6 times as much work as the average amp. This is very skewed, but may be acceptable for a very small query (for example, less than 5 CPU seconds). You always have to consider CPU skewing in relation to the size of the query. The larger the query, the greater the impact of skew on the system.'
from random(1,500)
for random(10,256)
)
) as char_field
from sys_calendar.calendar
where upi_id between 1 and 2000
) with data
unique primary index (upi_id);[@more@]

-- one SQL with product join to get right id's
-- is generic but might not show best performance espacially for big varchar fields
-- (following sql took me 40 seconds)
select t.upi_id,
coalesce(c.id,0) as last_position,
substring(char_field from last_position + 1) as last_field,
char_field
from char_test t
left outer join
(select ci.calendar_date-current_date as id
from sys_calendar.calendar ci,
(select max(characters(char_field)) as max_len from char_test) m
where id between 1 and m.max_len
) c
on index(t.char_field,' ') > 0
and last_position between index(t.char_field,' ') and characters(t.char_field)
where (last_position is null
or last_position between index(t.char_field,' ') and characters(t.char_field)
)
qualify max(case when substring(char_field from last_position for 1) = ' ' then last_position else null end) over (partition by upi_id) = last_position or last_position = 0
order by upi_id
;

--generate sql with case
--generated sql should show better performance
select sql_text
from
(
select 99999999 as order_id,
cast('select upi_id,
case' as varchar(10000) )as sql_text
from sys_calendar.calendar
where calendar_date = current_date
union all
select (c.calendar_date - current_date) as order_id,
' when characters(char_field) > ' !! order_id !! ' and substring(char_field from ' !! order_id !! ' for 1) = '' '' then ' !! order_id as sql_text
from (select max(characters(char_field)) as max_len from char_test) as t,
sys_calendar.calendar c
where order_id between 1 and max_len
union all
select 0 as order_id,
'
else 0
end as last_position,
substring(char_field from last_position + 1) as last_field,
char_field
from char_test
order by upi_id;' as sql_text
from sys_calendar.calendar
where calendar_date = current_date
) as tmp
order by order_id desc;

-- the generated sql is:
-- (it took me 3 seconds)
-----------------
select upi_id,
case
when characters(char_field) > 256 and substring(char_field from 256 for 1) = ' ' then 256
when characters(char_field) > 255 and substring(char_field from 255 for 1) = ' ' then 255
when characters(char_field) > 254 and substring(char_field from 254 for 1) = ' ' then 254
when characters(char_field) > 253 and substring(char_field from 253 for 1) = ' ' then 253
when characters(char_field) > 252 and substring(char_field from 252 for 1) = ' ' then 252
when characters(char_field) > 251 and substring(char_field from 251 for 1) = ' ' then 251
when characters(char_field) > 250 and substring(char_field from 250 for 1) = ' ' then 250
when characters(char_field) > 249 and substring(char_field from 249 for 1) = ' ' then 249
when characters(char_field) > 248 and substring(char_field from 248 for 1) = ' ' then 248
when characters(char_field) > 247 and substring(char_field from 247 for 1) = ' ' then 247
when characters(char_field) > 246 and substring(char_field from 246 for 1) = ' ' then 246
when characters(char_field) > 245 and substring(char_field from 245 for 1) = ' ' then 245
when characters(char_field) > 244 and substring(char_field from 244 for 1) = ' ' then 244
when characters(char_field) > 243 and substring(char_field from 243 for 1) = ' ' then 243
when characters(char_field) > 242 and substring(char_field from 242 for 1) = ' ' then 242
when characters(char_field) > 241 and substring(char_field from 241 for 1) = ' ' then 241
when characters(char_field) > 240 and substring(char_field from 240 for 1) = ' ' then 240
when characters(char_field) > 239 and substring(char_field from 239 for 1) = ' ' then 239
when characters(char_field) > 238 and substring(char_field from 238 for 1) = ' ' then 238
when characters(char_field) > 237 and substring(char_field from 237 for 1) = ' ' then 237
when characters(char_field) > 236 and substring(char_field from 236 for 1) = ' ' then 236
when characters(char_field) > 235 and substring(char_field from 235 for 1) = ' ' then 235
when characters(char_field) > 234 and substring(char_field from 234 for 1) = ' ' then 234
when characters(char_field) > 233 and substring(char_field from 233 for 1) = ' ' then 233
when characters(char_field) > 232 and substring(char_field from 232 for 1) = ' ' then 232
when characters(char_field) > 231 and substring(char_field from 231 for 1) = ' ' then 231
when characters(char_field) > 230 and substring(char_field from 230 for 1) = ' ' then 230
when characters(char_field) > 229 and substring(char_field from 229 for 1) = ' ' then 229
when characters(char_field) > 228 and substring(char_field from 228 for 1) = ' ' then 228
when characters(char_field) > 227 and substring(char_field from 227 for 1) = ' ' then 227
when characters(char_field) > 226 and substring(char_field from 226 for 1) = ' ' then 226
when characters(char_field) > 225 and substring(char_field from 225 for 1) = ' ' then 225
when characters(char_field) > 224 and substring(char_field from 224 for 1) = ' ' then 224
when characters(char_field) > 223 and substring(char_field from 223 for 1) = ' ' then 223
when characters(char_field) > 222 and substring(char_field from 222 for 1) = ' ' then 222
when characters(char_field) > 221 and substring(char_field from 221 for 1) = ' ' then 221
when characters(char_field) > 220 and substring(char_field from 220 for 1) = ' ' then 220
when characters(char_field) > 219 and substring(char_field from 219 for 1) = ' ' then 219
when characters(char_field) > 218 and substring(char_field from 218 for 1) = ' ' then 218
when characters(char_field) > 217 and substring(char_field from 217 for 1) = ' ' then 217
when characters(char_field) > 216 and substring(char_field from 216 for 1) = ' ' then 216
when characters(char_field) > 215 and substring(char_field from 215 for 1) = ' ' then 215
when characters(char_field) > 214 and substring(char_field from 214 for 1) = ' ' then 214
when characters(char_field) > 213 and substring(char_field from 213 for 1) = ' ' then 213
when characters(char_field) > 212 and substring(char_field from 212 for 1) = ' ' then 212
when characters(char_field) > 211 and substring(char_field from 211 for 1) = ' ' then 211
when characters(char_field) > 210 and substring(char_field from 210 for 1) = ' ' then 210
when characters(char_field) > 209 and substring(char_field from 209 for 1) = ' ' then 209
when characters(char_field) > 208 and substring(char_field from 208 for 1) = ' ' then 208
when characters(char_field) > 207 and substring(char_field from 207 for 1) = ' ' then 207
when characters(char_field) > 206 and substring(char_field from 206 for 1) = ' ' then 206
when characters(char_field) > 205 and substring(char_field from 205 for 1) = ' ' then 205
when characters(char_field) > 204 and substring(char_field from 204 for 1) = ' ' then 204
when characters(char_field) > 203 and substring(char_field from 203 for 1) = ' ' then 203
when characters(char_field) > 202 and substring(char_field from 202 for 1) = ' ' then 202
when characters(char_field) > 201 and substring(char_field from 201 for 1) = ' ' then 201
when characters(char_field) > 200 and substring(char_field from 200 for 1) = ' ' then 200
when characters(char_field) > 199 and substring(char_field from 199 for 1) = ' ' then 199
when characters(char_field) > 198 and substring(char_field from 198 for 1) = ' ' then 198
when characters(char_field) > 197 and substring(char_field from 197 for 1) = ' ' then 197
when characters(char_field) > 196 and substring(char_field from 196 for 1) = ' ' then 196
when characters(char_field) > 195 and substring(char_field from 195 for 1) = ' ' then 195
when characters(char_field) > 194 and substring(char_field from 194 for 1) = ' ' then 194
when characters(char_field) > 193 and substring(char_field from 193 for 1) = ' ' then 193
when characters(char_field) > 192 and substring(char_field from 192 for 1) = ' ' then 192
when characters(char_field) > 191 and substring(char_field from 191 for 1) = ' ' then 191
when characters(char_field) > 190 and substring(char_field from 190 for 1) = ' ' then 190
when characters(char_field) > 189 and substring(char_field from 189 for 1) = ' ' then 189
when characters(char_field) > 188 and substring(char_field from 188 for 1) = ' ' then 188
when characters(char_field) > 187 and substring(char_field from 187 for 1) = ' ' then 187
when characters(char_field) > 186 and substring(char_field from 186 for 1) = ' ' then 186
when characters(char_field) > 185 and substring(char_field from 185 for 1) = ' ' then 185
when characters(char_field) > 184 and substring(char_field from 184 for 1) = ' ' then 184
when characters(char_field) > 183 and substring(char_field from 183 for 1) = ' ' then 183
when characters(char_field) > 182 and substring(char_field from 182 for 1) = ' ' then 182
when characters(char_field) > 181 and substring(char_field from 181 for 1) = ' ' then 181
when characters(char_field) > 180 and substring(char_field from 180 for 1) = ' ' then 180
when characters(char_field) > 179 and substring(char_field from 179 for 1) = ' ' then 179
when characters(char_field) > 178 and substring(char_field from 178 for 1) = ' ' then 178
when characters(char_field) > 177 and substring(char_field from 177 for 1) = ' ' then 177
when characters(char_field) > 176 and substring(char_field from 176 for 1) = ' ' then 176
when characters(char_field) > 175 and substring(char_field from 175 for 1) = ' ' then 175
when characters(char_field) > 174 and substring(char_field from 174 for 1) = ' ' then 174
when characters(char_field) > 173 and substring(char_field from 173 for 1) = ' ' then 173
when characters(char_field) > 172 and substring(char_field from 172 for 1) = ' ' then 172
when characters(char_field) > 171 and substring(char_field from 171 for 1) = ' ' then 171
when characters(char_field) > 170 and substring(char_field from 170 for 1) = ' ' then 170
when characters(char_field) > 169 and substring(char_field from 169 for 1) = ' ' then 169
when characters(char_field) > 168 and substring(char_field from 168 for 1) = ' ' then 168
when characters(char_field) > 167 and substring(char_field from 167 for 1) = ' ' then 167
when characters(char_field) > 166 and substring(char_field from 166 for 1) = ' ' then 166
when characters(char_field) > 165 and substring(char_field from 165 for 1) = ' ' then 165
when characters(char_field) > 164 and substring(char_field from 164 for 1) = ' ' then 164
when characters(char_field) > 163 and substring(char_field from 163 for 1) = ' ' then 163
when characters(char_field) > 162 and substring(char_field from 162 for 1) = ' ' then 162
when characters(char_field) > 161 and substring(char_field from 161 for 1) = ' ' then 161
when characters(char_field) > 160 and substring(char_field from 160 for 1) = ' ' then 160
when characters(char_field) > 159 and substring(char_field from 159 for 1) = ' ' then 159
when characters(char_field) > 158 and substring(char_field from 158 for 1) = ' ' then 158
when characters(char_field) > 157 and substring(char_field from 157 for 1) = ' ' then 157
when characters(char_field) > 156 and substring(char_field from 156 for 1) = ' ' then 156
when characters(char_field) > 155 and substring(char_field from 155 for 1) = ' ' then 155
when characters(char_field) > 154 and substring(char_field from 154 for 1) = ' ' then 154
when characters(char_field) > 153 and substring(char_field from 153 for 1) = ' ' then 153
when characters(char_field) > 152 and substring(char_field from 152 for 1) = ' ' then 152
when characters(char_field) > 151 and substring(char_field from 151 for 1) = ' ' then 151
when characters(char_field) > 150 and substring(char_field from 150 for 1) = ' ' then 150
when characters(char_field) > 149 and substring(char_field from 149 for 1) = ' ' then 149
when characters(char_field) > 148 and substring(char_field from 148 for 1) = ' ' then 148
when characters(char_field) > 147 and substring(char_field from 147 for 1) = ' ' then 147
when characters(char_field) > 146 and substring(char_field from 146 for 1) = ' ' then 146
when characters(char_field) > 145 and substring(char_field from 145 for 1) = ' ' then 145
when characters(char_field) > 144 and substring(char_field from 144 for 1) = ' ' then 144
when characters(char_field) > 143 and substring(char_field from 143 for 1) = ' ' then 143
when characters(char_field) > 142 and substring(char_field from 142 for 1) = ' ' then 142
when characters(char_field) > 141 and substring(char_field from 141 for 1) = ' ' then 141
when characters(char_field) > 140 and substring(char_field from 140 for 1) = ' ' then 140
when characters(char_field) > 139 and substring(char_field from 139 for 1) = ' ' then 139
when characters(char_field) > 138 and substring(char_field from 138 for 1) = ' ' then 138
when characters(char_field) > 137 and substring(char_field from 137 for 1) = ' ' then 137
when characters(char_field) > 136 and substring(char_field from 136 for 1) = ' ' then 136
when characters(char_field) > 135 and substring(char_field from 135 for 1) = ' ' then 135
when characters(char_field) > 134 and substring(char_field from 134 for 1) = ' ' then 134
when characters(char_field) > 133 and substring(char_field from 133 for 1) = ' ' then 133
when characters(char_field) > 132 and substring(char_field from 132 for 1) = ' ' then 132
when characters(char_field) > 131 and substring(char_field from 131 for 1) = ' ' then 131
when characters(char_field) > 130 and substring(char_field from 130 for 1) = ' ' then 130
when characters(char_field) > 129 and substring(char_field from 129 for 1) = ' ' then 129
when characters(char_field) > 128 and substring(char_field from 128 for 1) = ' ' then 128
when characters(char_field) > 127 and substring(char_field from 127 for 1) = ' ' then 127
when characters(char_field) > 126 and substring(char_field from 126 for 1) = ' ' then 126
when characters(char_field) > 125 and substring(char_field from 125 for 1) = ' ' then 125
when characters(char_field) > 124 and substring(char_field from 124 for 1) = ' ' then 124
when characters(char_field) > 123 and substring(char_field from 123 for 1) = ' ' then 123
when characters(char_field) > 122 and substring(char_field from 122 for 1) = ' ' then 122
when characters(char_field) > 121 and substring(char_field from 121 for 1) = ' ' then 121
when characters(char_field) > 120 and substring(char_field from 120 for 1) = ' ' then 120
when characters(char_field) > 119 and substring(char_field from 119 for 1) = ' ' then 119
when characters(char_field) > 118 and substring(char_field from 118 for 1) = ' ' then 118
when characters(char_field) > 117 and substring(char_field from 117 for 1) = ' ' then 117
when characters(char_field) > 116 and substring(char_field from 116 for 1) = ' ' then 116
when characters(char_field) > 115 and substring(char_field from 115 for 1) = ' ' then 115
when characters(char_field) > 114 and substring(char_field from 114 for 1) = ' ' then 114
when characters(char_field) > 113 and substring(char_field from 113 for 1) = ' ' then 113
when characters(char_field) > 112 and substring(char_field from 112 for 1) = ' ' then 112
when characters(char_field) > 111 and substring(char_field from 111 for 1) = ' ' then 111
when characters(char_field) > 110 and substring(char_field from 110 for 1) = ' ' then 110
when characters(char_field) > 109 and substring(char_field from 109 for 1) = ' ' then 109
when characters(char_field) > 108 and substring(char_field from 108 for 1) = ' ' then 108
when characters(char_field) > 107 and substring(char_field from 107 for 1) = ' ' then 107
when characters(char_field) > 106 and substring(char_field from 106 for 1) = ' ' then 106
when characters(char_field) > 105 and substring(char_field from 105 for 1) = ' ' then 105
when characters(char_field) > 104 and substring(char_field from 104 for 1) = ' ' then 104
when characters(char_field) > 103 and substring(char_field from 103 for 1) = ' ' then 103
when characters(char_field) > 102 and substring(char_field from 102 for 1) = ' ' then 102
when characters(char_field) > 101 and substring(char_field from 101 for 1) = ' ' then 101
when characters(char_field) > 100 and substring(char_field from 100 for 1) = ' ' then 100
when characters(char_field) > 99 and substring(char_field from 99 for 1) = ' ' then 99
when characters(char_field) > 98 and substring(char_field from 98 for 1) = ' ' then 98
when characters(char_field) > 97 and substring(char_field from 97 for 1) = ' ' then 97
when characters(char_field) > 96 and substring(char_field from 96 for 1) = ' ' then 96
when characters(char_field) > 95 and substring(char_field from 95 for 1) = ' ' then 95
when characters(char_field) > 94 and substring(char_field from 94 for 1) = ' ' then 94
when characters(char_field) > 93 and substring(char_field from 93 for 1) = ' ' then 93
when characters(char_field) > 92 and substring(char_field from 92 for 1) = ' ' then 92
when characters(char_field) > 91 and substring(char_field from 91 for 1) = ' ' then 91
when characters(char_field) > 90 and substring(char_field from 90 for 1) = ' ' then 90
when characters(char_field) > 89 and substring(char_field from 89 for 1) = ' ' then 89
when characters(char_field) > 88 and substring(char_field from 88 for 1) = ' ' then 88
when characters(char_field) > 87 and substring(char_field from 87 for 1) = ' ' then 87
when characters(char_field) > 86 and substring(char_field from 86 for 1) = ' ' then 86
when characters(char_field) > 85 and substring(char_field from 85 for 1) = ' ' then 85
when characters(char_field) > 84 and substring(char_field from 84 for 1) = ' ' then 84
when characters(char_field) > 83 and substring(char_field from 83 for 1) = ' ' then 83
when characters(char_field) > 82 and substring(char_field from 82 for 1) = ' ' then 82
when characters(char_field) > 81 and substring(char_field from 81 for 1) = ' ' then 81
when characters(char_field) > 80 and substring(char_field from 80 for 1) = ' ' then 80
when characters(char_field) > 79 and substring(char_field from 79 for 1) = ' ' then 79
when characters(char_field) > 78 and substring(char_field from 78 for 1) = ' ' then 78
when characters(char_field) > 77 and substring(char_field from 77 for 1) = ' ' then 77
when characters(char_field) > 76 and substring(char_field from 76 for 1) = ' ' then 76
when characters(char_field) > 75 and substring(char_field from 75 for 1) = ' ' then 75
when characters(char_field) > 74 and substring(char_field from 74 for 1) = ' ' then 74
when characters(char_field) > 73 and substring(char_field from 73 for 1) = ' ' then 73
when characters(char_field) > 72 and substring(char_field from 72 for 1) = ' ' then 72
when characters(char_field) > 71 and substring(char_field from 71 for 1) = ' ' then 71
when characters(char_field) > 70 and substring(char_field from 70 for 1) = ' ' then 70
when characters(char_field) > 69 and substring(char_field from 69 for 1) = ' ' then 69
when characters(char_field) > 68 and substring(char_field from 68 for 1) = ' ' then 68
when characters(char_field) > 67 and substring(char_field from 67 for 1) = ' ' then 67
when characters(char_field) > 66 and substring(char_field from 66 for 1) = ' ' then 66
when characters(char_field) > 65 and substring(char_field from 65 for 1) = ' ' then 65
when characters(char_field) > 64 and substring(char_field from 64 for 1) = ' ' then 64
when characters(char_field) > 63 and substring(char_field from 63 for 1) = ' ' then 63
when characters(char_field) > 62 and substring(char_field from 62 for 1) = ' ' then 62
when characters(char_field) > 61 and substring(char_field from 61 for 1) = ' ' then 61
when characters(char_field) > 60 and substring(char_field from 60 for 1) = ' ' then 60
when characters(char_field) > 59 and substring(char_field from 59 for 1) = ' ' then 59
when characters(char_field) > 58 and substring(char_field from 58 for 1) = ' ' then 58
when characters(char_field) > 57 and substring(char_field from 57 for 1) = ' ' then 57
when characters(char_field) > 56 and substring(char_field from 56 for 1) = ' ' then 56
when characters(char_field) > 55 and substring(char_field from 55 for 1) = ' ' then 55
when characters(char_field) > 54 and substring(char_field from 54 for 1) = ' ' then 54
when characters(char_field) > 53 and substring(char_field from 53 for 1) = ' ' then 53
when characters(char_field) > 52 and substring(char_field from 52 for 1) = ' ' then 52
when characters(char_field) > 51 and substring(char_field from 51 for 1) = ' ' then 51
when characters(char_field) > 50 and substring(char_field from 50 for 1) = ' ' then 50
when characters(char_field) > 49 and substring(char_field from 49 for 1) = ' ' then 49
when characters(char_field) > 48 and substring(char_field from 48 for 1) = ' ' then 48
when characters(char_field) > 47 and substring(char_field from 47 for 1) = ' ' then 47
when characters(char_field) > 46 and substring(char_field from 46 for 1) = ' ' then 46
when characters(char_field) > 45 and substring(char_field from 45 for 1) = ' ' then 45
when characters(char_field) > 44 and substring(char_field from 44 for 1) = ' ' then 44
when characters(char_field) > 43 and substring(char_field from 43 for 1) = ' ' then 43
when characters(char_field) > 42 and substring(char_field from 42 for 1) = ' ' then 42
when characters(char_field) > 41 and substring(char_field from 41 for 1) = ' ' then 41
when characters(char_field) > 40 and substring(char_field from 40 for 1) = ' ' then 40
when characters(char_field) > 39 and substring(char_field from 39 for 1) = ' ' then 39
when characters(char_field) > 38 and substring(char_field from 38 for 1) = ' ' then 38
when characters(char_field) > 37 and substring(char_field from 37 for 1) = ' ' then 37
when characters(char_field) > 36 and substring(char_field from 36 for 1) = ' ' then 36
when characters(char_field) > 35 and substring(char_field from 35 for 1) = ' ' then 35
when characters(char_field) > 34 and substring(char_field from 34 for 1) = ' ' then 34
when characters(char_field) > 33 and substring(char_field from 33 for 1) = ' ' then 33
when characters(char_field) > 32 and substring(char_field from 32 for 1) = ' ' then 32
when characters(char_field) > 31 and substring(char_field from 31 for 1) = ' ' then 31
when characters(char_field) > 30 and substring(char_field from 30 for 1) = ' ' then 30
when characters(char_field) > 29 and substring(char_field from 29 for 1) = ' ' then 29
when characters(char_field) > 28 and substring(char_field from 28 for 1) = ' ' then 28
when characters(char_field) > 27 and substring(char_field from 27 for 1) = ' ' then 27
when characters(char_field) > 26 and substring(char_field from 26 for 1) = ' ' then 26
when characters(char_field) > 25 and substring(char_field from 25 for 1) = ' ' then 25
when characters(char_field) > 24 and substring(char_field from 24 for 1) = ' ' then 24
when characters(char_field) > 23 and substring(char_field from 23 for 1) = ' ' then 23
when characters(char_field) > 22 and substring(char_field from 22 for 1) = ' ' then 22
when characters(char_field) > 21 and substring(char_field from 21 for 1) = ' ' then 21
when characters(char_field) > 20 and substring(char_field from 20 for 1) = ' ' then 20
when characters(char_field) > 19 and substring(char_field from 19 for 1) = ' ' then 19
when characters(char_field) > 18 and substring(char_field from 18 for 1) = ' ' then 18
when characters(char_field) > 17 and substring(char_field from 17 for 1) = ' ' then 17
when characters(char_field) > 16 and substring(char_field from 16 for 1) = ' ' then 16
when characters(char_field) > 15 and substring(char_field from 15 for 1) = ' ' then 15
when characters(char_field) > 14 and substring(char_field from 14 for 1) = ' ' then 14
when characters(char_field) > 13 and substring(char_field from 13 for 1) = ' ' then 13
when characters(char_field) > 12 and substring(char_field from 12 for 1) = ' ' then 12
when characters(char_field) > 11 and substring(char_field from 11 for 1) = ' ' then 11
when characters(char_field) > 10 and substring(char_field from 10 for 1) = ' ' then 10
when characters(char_field) > 9 and substring(char_field from 9 for 1) = ' ' then 9
when characters(char_field) > 8 and substring(char_field from 8 for 1) = ' ' then 8
when characters(char_field) > 7 and substring(char_field from 7 for 1) = ' ' then 7
when characters(char_field) > 6 and substring(char_field from 6 for 1) = ' ' then 6
when characters(char_field) > 5 and substring(char_field from 5 for 1) = ' ' then 5
when characters(char_field) > 4 and substring(char_field from 4 for 1) = ' ' then 4
when characters(char_field) > 3 and substring(char_field from 3 for 1) = ' ' then 3
when characters(char_field) > 2 and substring(char_field from 2 for 1) = ' ' then 2
when characters(char_field) > 1 and substring(char_field from 1 for 1) = ' ' then 1
else 0
end as last_position,
substring(char_field from last_position + 1) as last_field,
char_field
from char_test
order by upi_id;

-- drop example table
drop table char_test;