Re: Unique index access path seems very slow

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