A reader, September 01, 2009 - 12:09 am UTC
Hello Tom,
Who about load/replace table every month? you still suggest to keep index enable on the underline table?
Thanks
Regard's
September 01, 2009 - 11:27 am UTC
if you load as a REPLACEMENT - probably truncate + disable + parallel load + parallel rebuild (probably - it can definitely make sense to maintain indexes even in this case since the 'merge' at the end will be merging something with nothing)
will the load take longer if you leave the indexes in place? Yes.
But what you are interested in is the time to reset the tables + load + index - and it could be that maintaining the indexes during the load is faster (you cannot skip redo generation if you maintain them during the load is one other factor to consider)
Thanks a lot
A reader, September 01, 2009 - 2:52 am UTC
Thanks a lot. Very clear explanation
David Aldridge, September 01, 2009 - 6:10 am UTC
>> a) we write table data above the high water mark (table is LOCKED, no other transaction will modify it)
Just as an "enhancement" to the answer, I believe that if a direct path insert is performed explicitly against a single partition of a table (ie. INSERT /*+ APPEND */ INTO table PARTITION (partition) ... ) then the exclusive lock is taken at the partition level, not at the table level. Presumably (meaning "I have no idea but it seems logical") this extends to subpartitions also.
Thus multiple concurrent serial direct path inserts can be made into partitioned table as long as each operation directly addresses a partition that no other process addresses.
September 01, 2009 - 11:36 am UTC
I should have said segment(s) instead of table. Yes, we lock the segments we know at execute time (before we see the data) might be touched - and if you use the partition extended name - only that partition is locked as we know at execute time that only that partition will be loaded into.
so, yes, correct - it is all done at the segment(s) level, not the logical container that is the table necessarily.
A reader, September 01, 2009 - 9:19 am UTC
This is exactly what I am doing
insert /*+ append */ into localtab partition(p)
(x,y,z)
select x,y,z
from table@dblink distab
where not exists (select null
from localtab
where localtab.pk = distab.pk);
My problem is that I have several similar inserts and I need to COMMIT after each insert in order to avoid
ORA-12838: cannot read/modify an object after modifying it in parallel
I like to commit only when I need and mainly at the end of the transaction. But in this situation, I am obliged to COMMIT after each Insert.
Is it the sole solution to avoid ORA-12838 in my situation?
Thanks very much
September 01, 2009 - 11:41 am UTC
if you want to direct path and if you want to do the "do it yourself" parallel - yes, you would need to commit if you need to read it afterward.
A reader, September 10, 2009 - 3:35 am UTC
Dear Tom,
I would like to explain you the observation I have in order to know your opinion about.
I have a stored procedure DB1_ProcA in database DB1
Within this procedure I have this
INSERT /*+ APPEND */
INTO DB1_TABLE_1
SELECT FROM DBLINK@DB2_TABLE_1
I am inserting into DB1 database with data coming from DB2 database via a dblink.
I benchmarch this insert when I run the stored procedure(in fact when I run the insert as I am connected into DB1) and it took 4 minutes during several days.
However, when It comes that this DB1_ProcA is launched from a third database DB3, then the insert took 29 minutes the first day and 1hour 46 minutes the second day.
Is it correct that a stored procedure that has been created into DB1 when you execute it from DB1 will be faster then when you execute it from DB3?
Could you please let me know your opinion about this
Thanks a lot for your answer
September 14, 2009 - 10:44 am UTC
... Is it correct that a stored procedure that has been created into DB1 when you
execute it from DB1 will be faster then when you execute it from DB3?
...
no, not necessarily, you do not give us much to go on here - if the insert /*+ append */ it going into a table with indexes on it and the table it growing and growing - I would anticipate that could add to the runtime over time.
Append Hint in Insert
A reader, January 04, 2011 - 11:47 am UTC
Hi Tom,
If I use append hint in insert statement that means I am inserting data directly above the HWM. Is that the only reason it doesn't create any undo. And also if I start inserting data using append from two terminals in the same table then how it works in real scenario.\
Thanks
Neeraj Ranjan Rath
January 04, 2011 - 11:54 am UTC
the direct path load of data above the high water mark is what allows us to skip undo generation for that table during that load - yes.
only one transaction at a time may perform a direct path load into a given segment. If you had two sessions attempting to do the direct path load at the same time - one of them would block on the other.
Parallel query is not subject to that - you can direct path load in parallel. So, you would use parallel DML - not "do it yourself" parallelism to load into a single segment in parallel using direct path.
Same Title
Neeraj Ranjan Rath, January 05, 2011 - 10:48 am UTC
Excellent ,Thanks Tom.
I have another doubt on the following. In any case We kill the process of direct path loading in the middle then in that case We will have to live with the data which is loading till the point We loaded n killed as it won't create undo so it can't rollback.
Thanks
Neeraj Ranjan Rath
January 05, 2011 - 10:55 am UTC
no one ever said it cannot be rolled back.
It can and will be rolled back.
All we said is we don't have to store UNDO for the *table data* - because we load above the high water mark. To rollback that insert all we do is reset the high water mark. The high water mark is a dictionary change - the change we make to the dictionary is protected by undo and hence can be rolled back.
You haven't lost transaction consistency here - rollback still most certainly works.