Re: Inserting with billion of rows faster

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.

Regards
Lok

On Tue, Mar 30, 2021 at 2:12 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
>
> So you've got two list partitions but the data spread is 90/10. Is this
> something like Active/Inactive?
> When you load new data does it all go into one of the partitions or is
> there a similar 90/10 split between partitions.
>
> Part of the strategy of partitioning is to break big jobs into little jobs
> - so if you're having problems inserting your 700M rows into a 20B row
> table then try to find ways of getting more out of partitions.
> a) You plan doesn't show any partition-wise activity. Is it possible to
> partition the source tables so that the join and insert can operate in a
> partition-wise way? It's possible that this MIGHT reduce the overheads of
> index maintenance on the insert (and your post about "load as select" /
> "index maintenance" with the monitor plan confirms that it was a direct
> path load, with unto (mostly) generated by index maintenance).
>
> b) If you can't optimize the monolithic join, you could create an empty,
> unindexed clone of the target table and insert into that with a direct path
> insert. Then execute a loop walks the subpartitions in turn, inserting from
> the staging table to the target table, committing after each subpartition.
> This should avoid the ORA-01628 as you will be rotating around the undo
> segments. One other thought about the ORA-01628.
>
> A separate thought about the ORA-01628. There must be something about
> your processing in the hours and days preceding the ORA-01628 that results
> in a lot of small undo segments being created and then being "robbed" of
> their initial extents by the big job. If you can't find out why this
> happens then set up a proced which, on a regular basis (e.g. every day)
> Creates a new undo tablespace, and switches the instance into the new undo
> tablespace, then (after a delay which the database will force) drops the
> old undo tablespace. If you can do this in a timely fashion your big job
> won't be stealing lots of little extents to grow, it will simply use the
> large amount of free space in the new tablespace. (It would be interesting
> to see how many extents of what size are in the problem undo segment when
> it hits the limit (select blocks, count(*) ....)
>
> Regards
> Jonathan Lewis
>
>
>

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