Is there any possibility of additional filters you can put on the column of
the encrypted - tab_encrypt table so as to restrict the joined record set
over there?
On Mon, Jan 30, 2023 at 10:33 AM Pap <oracle.developer35@xxxxxxxxx> wrote:
Thank you Jonathan. We are finally able to push the appdev team to make
the sha512 column data type changed from the Varchar2(which was storing the
HEX value) to RAW to store the SHA512 without conversion,
Considering different clients/drivers to oracle (like JDBC, ODBC, Native),
etc will yield the same result on the RAW value and even version upgrade
won't change these values for specific inputs.
As a short term solution with existing ~3billion rows with same data
types/data in the encrypted table which is getting joined with ~2-3milion
rows from the transaction tables rows, is there any possible solution to
make it better? And also the latest sql monitor reports says the execution
is ~10minutes vs earlier ~30minutes and sometimes it goes till ~1hrs too,
so we were unable to understand if it's just because of the timing i.e. say
the IO subsystem slowness or anything else is playing a role say e.g bloom
filter effectiveness varying time to time for different input sets so we
should set the non bloom filtered plan like you suggested ?
On Sat, Jan 28, 2023 at 12:34 AM Pap <oracle.developer35@xxxxxxxxx> wrote:
Understood the hex and raw thing now. Thank you so much.
On Fri, 27 Jan, 2023, 7:02 pm Jonathan Lewis, <jlewisoracle@xxxxxxxxx>
wrote:
Just one quick answer, I'll get back to the rest of the posting later
today (I hope).
For the storage of the raw vs. hex, the length() and lengthb() will
implicitly convert to hex before calculating the length.
For internal storage size try:
SQL> select sys_op_opnsize(c1), sys_op_opnsize(c2) from t1_encrypt;
SQL> select vsize(c1), vsize(c2) from t1_encrypt;
SQL> select dump(c1,16), dump(c2,16) from t1_encrypt;
The first two are the newer and older (respectively) calls used in
gather_table_stats() to get internal column lengths
The last is a way to dump the actual bytes store, in this case in base
16, reportung the column type and length at the start
You'll see the 64 vs. 128 in all three.
Regards
Jonathan Lewis
