Re: Unique index access path seems very slow

Thank You Andy and Mladen.

I am little confused on the RAW VS HEX. As i see even i simply calculate the SHA512 value for a normal string as below, its still giving 128bytes as shown below without the hex function. So how the RAW value will help in minimizing the size of the column even we remove the RAWTOHEX conversion function here or I am missing anything here?

Also as i tested , the conversion or the function(RAWTOHEX) call for ~3million rows itself is finishing in ~40 seconds in the inline view part, so as far as the RAWTOHEX function evaluation is concerned for this query, its a minimal percentage of the overall query execution time here.

select (STANDARD_HASH (TO_CHAR (TRIM ( '225057')),'SHA512')) from dual

57AAF4B657557BAD68054B38C40149E2D8BD1EEB46A346DD6AC248228F55330B0832E8A1997855782811F0FA4C363FAB36597824157C3E10EAFFB0F07F61B666 select (RAWTOHEX(STANDARD_HASH (TO_CHAR (TRIM ( '225057')),'SHA512'))) from dual

57AAF4B657557BAD68054B38C40149E2D8BD1EEB46A346DD6AC248228F55330B0832E8A1997855782811F0FA4C363FAB36597824157C3E10EAFFB0F07F61B666 I am struggling to understand below point. Do you mean to say adding 'order by COL1_SHA_512' to the inline view query itself? can you please explain a bit.

" *I wonder if you were to place an extra order by subquery in there to join from. This might at least get you as good as you can get from the index."*

I tried third suggestion of Jonathan, regarding turning off the bloom filter and not seeing much difference. Below is the sql monitor for both with and without bloom filter.

https://gist.github.com/oracle9999/9b9a2d5da832e7b55cfa77abba8d0276

The first two suggestion from Jonathan, are based on the design change of the transaction table tran_tab. And this table is having size ~25TB now and is having ~210 daily range partitions. As its being used across many applications queries , so would be difficult to go for this change. So we were looking if anything can be done by tweaking the design of this encrypted table TAB_ENCRYPT rather.

We were thinking if its possible by someway, we can tie/generate certain number(say new sequence number) and maintain that additional column value for each of those encrypted value as reference, so that the length of this reference column will be minimal and index on this new column will be smaller too and also joining on that column will be efficient. Also this may not be easy as the same sequence number has to be referred and passed in all the up/downstream systems.

On Wed, 25 Jan, 2023, 9:07 pm Andy Sayer, <andysayer_at_gmail.com> wrote:

> I forgot about the impact of rawtohex, that would definitely give you a
> lot of bang for your buck. It sounds to me like the impact of changing this
> on your users is being exaggerated, you can easily make it presented in the
> existing way if you needed to.
>
> Of course, if you do insist on keeping the full hex data stored, you can
> use a function based index on hextoraw to create a smaller index.
>
> I don’t see what list partitioning is going to get you. You have
> completely random data and you have no apparent way of doing anything about
> that. Even if you were to eliminate it to just use one of those partitions
> (which is 1/16^3 million chance) you’re still going to be reading the index
> in a very random order.
>
> Saying this, I wonder if you were to place an extra order by subquery in
> there to join from. This might at least get you as good as you can get from
> the index.
>
> You should still go ahead with the smaller index. Partitioning is a no
> from me.
>
> Thanks,
> Andy
>
> On Wed, Jan 25, 2023 at 5:31 AM, Mladen Gogala <gogala.mladen_at_gmail.com>
> wrote:
>
>> On 1/25/23 03:45, yudhi s wrote:
>>
>> Regarding your teams thought of list partition the encrypted table column
>> with the last letter. You would end up in 16 list partitions. However as
>> your data or say SHA512 value will be distributed across so you will end up
>> relying only on luck to eliminate some table/index partition scans of your
>> encrypted table, which I think would not be a good strategy.
>>
>> Pap, you should ask yourself one question only: do I feel lucky? Well, do
>> you?
>>
>> --
>> Mladen Gogala
>> Database Consultant
>> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>>
>>

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