Re: Inserting with billion of rows faster

Thank you Jonathan.

Yes all the indexes are local in this case. And we are not very sure of how the data movement happens across active/inactive partitions , we will try to check with the dev team and also if possible will engage experts to have a detailed analysis around this.

And i was initially not able to understand your point fully though, but now i re-read it and your point is to try loading just those 700million into a blank stage table with exactly the same partitioning structure but without any indexes. And then loop through each of those non zero hash sub partitions of the stage table and do a "insert"/*+append*/ into actual_table partition().. select from stage_table partition();" . We will try this option , but as i mentioned in past, we tried making the data load happen in a chunk of 1million through FORALL insert, and that time we saw the insert were very slow, and the waits(cell single block physical read) were all around the indexes, so it might be that the order of the incoming data really making difference. And i think your point is , possibly this subpartition to subpartition load may help us going faster as it makes those ordering of data better. Correct me if wrong.

Creating a new UNDO tablespace or dropping and creating an existing undo tablespace is something that should help us here to get rid of those large numbers of small extents. We are evaluating that option too. But before that we also want to have a final try to load those ~700million rows at one shot (using insert into.. select from..) just to see if we are lucky enough to get some rollback segment for this transaction which must not be having a large number of small extents in it.

Regards
Lok

On Tue, Mar 30, 2021 at 6:04 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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