Parsing strings
I've spent much of my week playing with splitting strings. This is normally a simple task but I'm attempting to pivot out the split columns at the same time. This is happening against a system view that has ~1.5 million rows. I did the math and then wrote a perl script that does something similar in a short time to verify. So why can't I get my database to do it as efficiently? It will produce ~6 million rows when complete.
I'd like to do this in one nice query, and return back exactly what I need. It definitely can be done, I've just not found a very efficient way.
My table looks like this: 'A', 'B', 'D', 'Tyler,is,awesome' 'A', 'B', 'G', 'Tyler,rocks'
I'd like to get back the following in a single query (fast!): 'A', 'B', 'D', 'Tyler' 'A', 'B', 'D', 'is' 'A', 'B', 'D', 'awesome' 'A', 'B', 'G', 'Tyler' 'A', 'B', 'G', 'rocks' Tried: -Various forms of 'connect by level' with regexp_split -Tokenizer function using inStr/subStr and then Cartesian-ing the results -XMLTable (cool trick where it thinks your comma separated string is a table.) -Split columns in line and then use 11g's pivot function. All work, just very slowly. I've attempted to optimize each of them but the box and permissions setup on my normal user account are thwarting me. I plan to try one of the above options as a system user once our development environment is back. My hope is that the optimizer is picking a bad plan and if I run explain plan on the query, then I can possibly hint it so it will pick a better option. The goal is to use this with DBMS_Comparison's index columns on the diff rows table. Then we don't need to select the original key values from across a database link after the comparison takes place simply to identify the new rows. The link works, but it can be unreliable as we don't control the source system. It takes 35-40 minutes to run as it exists today. The closest I've been able to get splitting the above strings is ~2 hours. In the process of doing this I found a really neat AskTom article where they discuss a cool oracle feature that allows you to bind a value to an inlist. Link:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:73830657104020