You said:
*"If I get it correctly , you said partition wise Join would have been
possible if we would have made the source table partitioned in the same way
as the target table i.e. list-hash composite partition."*
That is not repeating what I said. I ASKED if it was possible to partition
the source tables in a way that would make a partition-wise join and insert
possible. If you think that that would help it's up to you to work out (a)
if it is possible (b) what it will take to achieve and (c) how much benefit
it might give you.
You asked: "But how would that also help in making the data load faster or
say get rid of Ora-01628? I am somehow not able to relate the partition
wise join to the data load benefits". The order in which data is inserted
and the index maintained can make a difference to the volume of undo
generated whether this difference would appear in your case is something I
cannot predict. I don't recall whether you said anything about the indexes
being local or global - and that could be an important factor.
Creating a temporary table of 18B rows and indexing it in order to inserts
a few hundred million rows sounds like a terrible idea -- especially since
the low-cost, low-risk strategy is working out why you are getting the
ORA-01628 error and stopping it from happening. Amongst other problems with
this approach is the question of what you're going to do when the data is
twice the size, or three times, or 10 times - which won't take long if
you're on 20B and loading 500M per day.But maybe you're not telling us
about how data is transferred from the ACTIVE partition to the INACTIVE
partition, or how it's deleted on a very regulary basis (which means any
idea that sound wonderful for solving your current problem may turn out to
be a disaster for the task you have to deal with next month).
To get a well-rounded analysis of EVERYTHING that you might need to
consider I suggest you call in a poper consultant rather than trying to
solve your problems piece by piece on a list server.
Regards
Jonathan Lewis
P.S. You haven't commented on my suggestion of loading an empty,
unindexed, table with the new data then insert from each sub-partition in
turn as a separate transaction. Nor have you commented on the approach of
creating new undo tablespaces when the current one becomes so overloaded
with small segments that an ORA-01628 seems likely.
On Tue, 30 Mar 2021 at 09:01, Lok P <loknath.73@xxxxxxxxx> wrote:
Yes Jonathan, there is an ACTIVE/INACTIVE concept for those two list
partitions and data loaded into those alternately. If I get it correctly ,
you said partition wise Join would have been possible if we would have made
the source table partitioned in the same way as the target table i.e.
list-hash composite partition. So that would have made the Join faster as
the data may not have been spilled to temp then while reading from the
source table, the hash joins happen fully in memory. But how would that
also help in making the data load faster or say get rid of Ora-01628? I am
somehow not able to relate the partition wise join to the data load
benefits.
Again considering immediately we may not be able to go for the design
change for the source table to partition then as per target , so it seems
the only option is to go for the monolith approach. Initially i was
hesitating to go for this creating a ~18billion rows temp table considering
the number is too huge and i have never created a ~18billion rows on the
fly using CTAS even its without any index :) so was hesitating, but it
seems from the discussion it would be faster approach if we create the temp
table(with exact list-hash partition structure) with ~18billion rows at one
shot using CTAS and then create indexes on them using parallel degree and
then swap/exchange it with the actual list partition. Correct me if my
understanding is wrong.
