Re: Unique index access path seems very slow

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_at_gmail.com> 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_at_gmail.com> wrote:
>
>> Understood the hex and raw thing now. Thank you so much.
>>
>> On Fri, 27 Jan, 2023, 7:02 pm Jonathan Lewis, <jlewisoracle_at_gmail.com>
>> 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
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l