#dbms_comparison

[tsworman]

Bulk Collecting and pipe lining rows.

I'm not sure what to call the following code other than elegant. It sits inside a loop over a cursor, inside a pipelined function. The result of this is cast to a table and then used inside another procedure (follow me?). The queries being built and run are dynamic and produce a result based off a dynamic data set. Doing this ensures that as tables and their indexes change, we won't need to change our code to find new or updated rows. 

v_query := 'select distinct' || v_new_p1.come_column || ' from some_schema.' || v_new_p1.some_table|| ' where ' || v_new_p1.index_column '=''' || v_new_p1.index_value || ''''; execute immediate v_query bulk collect into t_temp_values; for v_temp_values in t_temp_values.first .. t_temp_values.last loop                    pipe row(v_temp_values); end loop;

I never thought I'd need to use execute immediate, bulk collect, a custom type and pipelined function combined to produce something. They are all neat features on their own. Together it seems a bit crazy.

[tsworman]

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

[tsworman]

Oracle DBMS_Comparison and importance of timing

To say that DBMS_Comparison and I are only friends would be an understatement. The package and I share our tables...

Today I learned an important lesson. Timing is everything and DBMS comparison can't have your back everywhere. It would really excel at dumping people. I imagine as a companion, DBMS_Comparison would happily tell me that "things have changed (and I can tell you which rows!)" and also "this isn't going to work out, your indexes are different than mine".

Now that I've made my bpf (best package forevs) into a person... lets walk through today's events and see where I went wrong. I'm male, this makes me the one who is always wrong. I also can't read it's mind. I didn't grant it select on mine so I'll assume it has no idea what I intended for it to do.

Ideally we would have done the following: 1) Compared between remote and local database tables 2) Taken the new rows identified by comparison from remote database and processed 3) DBMS_Comparison and Tyler high five! (M-F are chest bump days)

Today this really happened: 1) Compared between remote and local database 2) Waited a few hours during which time the remote database changed 3) Queried "new" rows from remote database based on a now invalid comparison 4) Made a frown face (example: :/) because the "new" rows no longer existed in many cases as they have been updated 5) Realization that: "that just happened! Whoa!" 

Solution: Timing is everything! Never run DBMS comparison if you can't process results all the way through before remote database changes... or pull over the identifier for the changed rows immediately when the comparison is done. Even if you merge and keep a local copy of the database on your side, the remote row_ids are not valid on your new copy. If you need to identify new rows and do so by selecting "where remote_row_id is not null and local_row_id is null", then you need to pull the key immediately so you can later identify the row on your side. http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_3107.htm  - This identifies the index_value column which I need to look into more. My hope is that the data it contains will allow me to identify rows after the merge takes place. If so, I can avoid the need to reference the remote database across our dblink. People that could have fixed this for us: Bill and Ted (Would have been excellent!) Marty Mcfly Doctor Who