Re: Inserting with billion of rows faster

Out of curiosity the database is in a dataguard configuration?

On Mon, Mar 29, 2021, 21:30 Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Lok,
>
> I am afraid exchange partition will generally hardly help in your case.
> Of course from the point of manageability a table of this size should be
> range partitioned by date.
> In addition I rather wonder about the usefulness of the list partitioning
> if one partition holds 18billion rows out of total ~20billion.
> 700 million rows however are not that much. When I was RWPG member I had
> once the opportunity to load 2 billion rows and a standard hardware. Took
> 15 minutes.
> As Tim mentioned, it was likely not a direct path load in your case.
> Can you shared an execution plan of the insert?
>
> Thanks
>
> Lothar
>
>
> Am 29.03.2021 um 20:11 schrieb Lok P:
>
> Thank you Tim. Actually we have the target table holding ~20billion rows
> which is list-hash composite partitioned. It has two list partitions and
> each list partition has a ~2048 hash subpartition. And we want to insert
> into that target table around ~700+ million rows which is failing with
> ora-01624.
>
> Now I am unable to fully understand how we can utilize partition
> exchange method here to load those ~700million rows. As because , I see
> even all of those rows are meant for one of the list partitions(which
> itself holds ~18billion rows out of total ~20billion) but were spread
> across ~2048 hash subpartitions. So do you mean to say, we should load
> ~18billion rows into a similar structure stage table(without index with
> same list-hash composite partition) and also load those additional
> ~700million those are missing (and it should succeed as it wont have
> indexes now so zero UNDO with APPEND INSERT) and then do the truncate of
> the list partition and then do the partition exchange with stage table?
>
> On Mon, Mar 29, 2021 at 2:45 AM Tim Gorman <tim.evdbt_at_gmail.com> wrote:
>
>> Lok,
>>
>> >> Ora-01628 max extent 32765 reached for rollback segment
>>
>> If you are running out of space on your rollback segments, then you are
>> not actually using direct path insert, APPEND or APPEND_VALUES hint
>> notwithstanding. The only undo you should be generating is for the DDL,
>> not the DML, and that should not max out anything.
>>
>> Long story short: you really can not effectively perform direct-path
>> APPEND insert directly to that "live" table or partition with indexes. In
>> your case, you need to perform the direct-path insert to a hash-partitioned
>> table of the same logical "shape" as the list partition you are targeting,
>> then build indexes to match the main table when you are done loading, and
>> then finally use ALTER TABLE ... EXCHANGE PARTITION to swap it all with the
>> "live" partition(s) in the main table. If it helps, HERE
>> <http://evdbt.com/download/presentation-scaling-to-infinity-partitioning-data-warehouses-on-oracle-database/>is
>> a presentation on this technique and HERE
>> <http://evdbt.com/download/paper-scaling-to-infinity-partitioning-data-warehouses-on-oracle-database-2/>is
>> a corresponding white paper on the technique. Also, HERE
>> <http://evdbt.com/download/exchpart-sql/>is a PL/SQL package called
>> EXCHPART which contains procedures to automate the moves mentioned in the
>> white paper and presentation. Please don't consider EXCHPART as anything
>> more than a template, it is not intended as working code, particularly when
>> it must be adapted for your local coding culture and standards. Hopefully
>> it is a good start?
>>
>> Oh also, there is no such thing as a NOLOGGING hint; nologging is an
>> attribute on the table or partition which is effective only with
>> direct-path insert operations, never with conventional-path INSERT, UPDATE,
>> or DELETE operations.
>>
>> Hope this helps...
>>
>> Thanks!
>>
>> -Tim
>>
>>
>> On 3/27/2021 10:37 PM, Lok P wrote:
>>
>> Hi Listers, we have one process in which we load ~50million daily (using
>> direct path insert.. INSERT APPEND ) to a target table which holds
>> ~20billion rows(~1TB in size)) and is list -hash composite partitioned. ~2
>> list partition and ~2048 hash subpartitions. It has 4 indexes out of those
>> one is a composite primary key comprising of 5 columns.We have that data
>> load job failing since ~15-20 days without notice and it thus accumulated
>> ~billion rows and the load process is now failing with (Ora-01628 max
>> extent 32765 reached for rollback segment).
>>
>> So we thought of running it in a bulk collect method(append_values hint)
>> and commit in chunks of ~1million. But during that process we endup seeing
>> the data load is significantly slower , it was inserting ~1million rows in
>> ~40minutes. And we were not able to understand the reason but the wait
>> events were all showing "cell single block physical read" and the object
>> was the target load object. Means it was the INSERt which was struggling.
>> So we now endup having partial data loaded to the target i.e. around
>> ~40-50million loaded to the target. And as it has a primary key , we have
>> to delete the ~40-50million rows from the target table and then reload it.
>>
>> I was thinking if we should do the delete in bulk method with a chunk of
>> 1million , but again that will happen in a single thread and will be
>> significantly slow. So what is the best way to have those ~40-50million
>> data deleted from the target table(which holds a total ~20billion rows)?
>>
>> And then I was also thinking , if we could make the index unusable and
>> perform the delete and then data load(which would happen with almost zero
>> UNDo in absence of INDEXes) , but then in that case it seems that DELETE
>> will need the PK index to fetch the rowids so we cant get rid of the PK
>> index then. So what is the best approach to go for the delete and data
>> load here without breaking data consistency in the target table?
>>
>> So wanted to understand what is the best/faster approach to go for delete
>> and data load in this situation?
>>
>> Regards
>> Lok
>>
>>
>>
>

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