Hi, I am quite new to PL/SQL and Oracle. Right now I am trying to scramble all the data fields within a few columns (i.e. shuffling them, treat all these columns as a tuple)
What I did are these
1. Create a temp tableA with 2x number of columns and an id column as index
2. Copy all these columns from the original table to the temp tableA (label them col1before, col2before, ...)
3. Create a temp tableB with an id column as index
4. Sort all these columns from the original table ordered by dbms_random.value() and copy to the temp tableB
5. Copy all the columns from tableB to tableA (label them col1after, col2after, ...) where tableA.id = tableB.id
6. Check if col1before+col2before+... == col1after+col2after+...
7. If yes, redo from 4.
8. If no, copy all the col1after, col2after, ... from tableA back to the original table where col1 = col1before and col2 = col2before and ...
So far, I can achieve what I want, but I believe there must be some more efficient way such that for example
a. I dont have to create an extra table tableB (i.e. no step 3, 5) and all the steps can be done using only tableA
b. even better is I dont have to rely on the random sort and then check and then redo if some rows are still in place (i.e. I can just shuffle the columns in the original table)
Please let me know any expert way to do such simple task