select c "char", s sum_position from your_table model return updated rows partition by (rowid) dimension by('A' c) measures(your_column,0 s) rules iterate(20) until(iteration_number + 1 = nvl(length(your_column['A']),1)) ( s[substr(your_column['A'],iteration_number + 1,1)] = nvl(s[substr(your_column['A'],iteration_number + 1,1)],0) + iteration_number + 1 ) /
Vi wrote:No doubt it will be slow. You're taking individual characters from strings, summing up their positions within the grouping of that string to generate new rows of data, and you're doing that for 60,000 rows of strings. That's undoubtably generating a lot of new rows (probably in the millions) and taking a lot of cpu time to to process all those characters and do the summing up.
Is there any other way to achieve this?? the query performance is very low :-(
Vi wrote:You have posted just one string and what you want to get.
I have tried the query, still it's taking more than half hour to execute. I have 60,000 records in my table
Edited by: Vi on Dec 26, 2012 11:05 PM