Re: Inserting with billion of rows faster

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_at_gmail.com> 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.
>
>
>

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