Re: Unique index access path seems very slow

Thank you Jonathan and Yudhi.

 Actually we are planning to get the filtered results from the range partitioned transaction table tran_tab(i.e. mainly the results from the inline view) and put it in another stage table(say stage_tran_tab) which will have selected columns only and will hold data for last couple of days only. That stage table will then be joined with the tab_encrypt table on the sha_512 column. We will range-hash partition the new table stage_tran_tab on (part_dt, sha_512) column respectively and hash partition the tab_encrypt on the sha_512 column.

So just ~256 hash partitions on both the stage_tran_tab and tab_encrypt should be fine in this above scenario. We decided this mainly considering the tab_encrypt is ~900GB in size currently and it can max grow double i.e. ~2TB in future. So with ~256 hash partitions , the avg size of one partition will be <10GB in size for the tab_encrypt table. The stage table , stage_tran_tab will be a lot smaller in size anyway as it will only hold a day or two of transaction data.

On Mon, Feb 13, 2023 at 3:47 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> Yudhi,
>
> I don't think there's a "best practice" as such. There are several details
> that might need to be balanced in any particular situation. My immediate
> response to 256 hash partition is that it sounds a little high for the
> requirement, especially since it also has to apply to the sub-partitions of
> a composite partitioned table.
>
> a) the limit on data segments per object is 2^20 - 1, so at 256
> subpartitions you have a maximum of approx. 4,000 partitions before you hit
> a hard limit, which for daily partitions means about 11 years. That's not a
> problem in this case, but I have seen someone asking why at 1,024
> subpartitions their system "broke" after 3 years.
>
> b) the larger number number of segments the more segment information has
> to be kept in the data dictionary, and the longer it may take to optimize
> some types of statement, and the most contention between concurrent
> processes.
>
> c) You have to allow for the volume of statistical information that needs
> to be held as the number of partitions grows - it can get very big
> (especially with the older synopsis mechanism and the automatic choice of
> histograms.
>
> d) If you have too many segments they may get so small that Oracle stops
> doing direct path reads, and switches back to cached reads, using more CPU
> and not taking advantage of smart scan.
>
>
> I will be writing some notes on the business of how partition size might
> affect the efficiency of the hash join - but the symptom being displayed in
> this case was more due to the optimizer's ESTIMATE of the rows in the build
> table rather than the actual number of rows, and my initial thought is that
> a much smaller number of hash partitions would still mean the Bloom filter
> could be small enough to be very useful.
>
> Regards
> Jonathan Lewis
>
>
>
>
> On Sat, 11 Feb 2023 at 04:22, yudhi s <learnerdatabase99_at_gmail.com> wrote:
>
>> And Jonathan, I am curious as OP also asked, what should be the optimal
>> number of hash partitions in this scenario for the tab_encrypt table. Is
>> there any best practice for deciding that or it's hit and trial only? Not
>> sure if you missed, OP has posted the 10104 hash join traces in one of the
>> reply.
>>
>>
>>>>>>>>>
>>>>>>>>>

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