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@xxxxxxxxx>
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@xxxxxxxxx> 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.
