Thanks for the question, Daniel.

Asked: September 04, 2008 - 11:38 am UTC

Last updated: August 28, 2013 - 5:47 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi Tom,

Thanks for taking this question.

Quite a straight forward question, by the way:
Does merge with append hint causes table fragmentation?

I have table and its data is inserted only with the merge operation, using append hint (and certainly updated later).

I use this select to estimate the table size and its possible size:

select b.partition_name, a.partition_size, b.possible_size, (b.possible_size/a.partition_size)*100 percentage
from
(select partition_name, bytes/1024/1024 partition_size from user_segments where segment_name='TABLE_NAME') a,
(select partition_name, avg_row_len, num_rows, avg_row_len*num_rows/1024/1024 possible_size from user_tab_partitions 
where (table_name like 'TABLE_NAME')) b
where
a.partition_name=b.partition_name
order by b.partition_name;

Statistics up to date, of course.

And here's one of the partitions (sorry for the crappy output):
PARTITION_NAME PARTITION_SIZE POSSIBLE_SIZE PERCENTAGE
P08 ############### 6545 ############# 279,136 ######## 4,26

And if I move the partition within the same tablespace, to coalesce it, it gets really smaller.

I mean, when a I look at this, I'm almost sure that merge+append cause fragmentation... but why?

Does append makes merge faster?

and Tom said...

... Quite a straight forward question, by the way:
Does merge with append hint causes table fragmentation? ...

hah, define fragmentation for me first.

when you use append, new rows are added ABOVE the high water mark and no existing free space under the current high water make is used. So the updates would be the only thing using existing free space, all new rows would go above the high water mark.

So, if you have free space, insert /*+ APPEND */ (or merge resulting in insert /*+APPEND*/) - it will NOT use it. Because you told us not to.

If on the other hand, there is no free space for the inserts, it would be about the same.

So, you must have existing free space, have told us to not use it.

Rating

(9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Daniel Stolf, September 04, 2008 - 6:12 pm UTC

Hi Tom, thanks a lot for your help.

But does it explain the table occupying 20 times more than it should?

It was my understanding that when an insert append starts, it gets the first empty block and starts inserting data into it until it gets full. Then when it get fulls, the process gets the next empty block etc. until there's nothing else to insert and there will be one half-full block left.

By that reasoning, at the end of an insert with parallel hint and degree 4, I should be with 4 half-full blocks that will never be used again for an insert operation. Am I wrong?

At the end of an insert with parallel hint and degree 4, I should be with 4 half-full blocks that will never be used again for an insert operation.

Am I wrong? (I have the felling I am, but I'd like to see your explanation :)

September 05, 2008 - 8:48 am UTC

https://www.oracle.com/technetwork/issue-archive/2007/07-may/o37asktom-101781.html

probably you have locally managed tablespace with uniform extents, read that article and let me know if it applies, if it doesn't - then we'll need all of the details on the tablspace, table and the method used to populate to explain what is happening.

Daniel Stolf, September 04, 2008 - 6:13 pm UTC

erm... sorry for writing the same sentence twice, i should have click preview before submitting it...

merge append into partitioned

A reader, September 08, 2008 - 1:33 pm UTC

it seems that every merge statement executed against a partitioned table allocates at least one new extent. If next extents are big you will have a lot of wasted space. I tried on 10.2.0.2 doing merge to insert 1 row at a time. After each merge, a new extent was allocated.

September 08, 2008 - 4:27 pm UTC

parallel insert /*+ APPEND */ does that - that article demonstrated that - that is why system allocated extents - so we can do extent trimming - are useful

Daniel Stolf, September 08, 2008 - 5:20 pm UTC

Hi Tom,

You were right, as usual...

Here's the tablespace configuration:

Block size: 32Kb
Initial Extent: 1Mb
Next Extent: 1Mb
Min Extents: 1
Min extlen: 1Mb
Extent Management: Local
Allocation Type: Uniform

In fact the tablespace is using UNIFORM SIZE allocation type and the extent size is 1 Mb.

What is Oracle exactly doing? Every line gets its own 1Mb extent?

Best regards.

September 09, 2008 - 7:23 am UTC

check out that article I linked to above - I wrote about this extensively - that article describes exactly what happens.

each parallel execution server allocates its own extents to load into - yes.

and when you use uniform sizes - they cannot trim them.

if you use system allocated extents - they can and will trim them

Daniel Stolf, September 09, 2008 - 9:23 am UTC

Tom, thanks a lot for your help...

I'll have to re-create the tables anyway, to change the partition key format... so I cant take this chance to change the allocation type at table level, right?

Again, thanks a lot for your help, keep up the good work :)

September 10, 2008 - 9:11 am UTC

I didn't understand that paragraph - "so I can't"? If you have to re-create the tables anyway, it would be a good opportunity in fact to change the extent allocation method by creating them in a new tablespace

Daniel Stolf, September 10, 2008 - 11:59 am UTC

i meant to write CAN, not CANT

since i will have to recreate the tables anyway, i will have the chance to change te allocation type.

sorry for the typo

Why is this happening?

Zilvinas Vidmantas, August 21, 2013 - 1:41 pm UTC

I have table tbl_dst with 14x2Mb extents on uniform 2M tablespace.
It is just loaded from table tbl_src.
After merge into tbl_dst using tbl_src@dblik
My table grows to 45x2Mb extents.
All new extents are absolutely empty.
Merge should not change tbl_dst at all. It is already merged.
ALTER TABLE tbl_dest DEALLOCATE UNUSED does nothing.
If I do merge once more nothing changes.
Can you explain what is happening?

August 28, 2013 - 5:45 pm UTC

example from start to finish please.

I'd like to see all sql involved.

including sql you are using to show the extents are absolutely empty.

is parallel query involved?

why are you using uniform extents? system allocated are typically 'better'.

I suspect this:
http://www.oracle.com/technetwork/issue-archive/2007/07-may/o37asktom-101781.html

view update does almost the same

Zilvinas Vidmantas, August 21, 2013 - 2:45 pm UTC

In my research I created local table
CREATE TABLE tab_src as select * from tab_src@dblink
That not helped. So this is no dblink realted issue.
The I tried

UPDATE (SELECT d.Field_1 df, s.Field_1 sf
FROM tab_dst d, tab_src s
WHERE s.Cl_Id = d.Cl_Id)
SET df = sf

This time I got 28x2M extents. This is exactly twice.
And all new 14 extent are empty.
There could be explanation that every updated row chained to new block, but rows were updated to the same value they had before besides ALTER TALBE MOVE solves issue.
Can you explain this behaviour?

wasted space

Zilvinas Vidmantas, August 21, 2013 - 2:52 pm UTC

In my previous post I forgot to tell that my tbl_dst is compressed using basic compression.
Can update of field to the same value it has before decompress table?
... seems it is exactly the the case
this talbe should be tuncated and direct loaded or use OLTP compression. I'm I right?

August 28, 2013 - 5:47 pm UTC

absolutely.

if you update basic compressed data (which only stays compressed for DIRECT PATH operations - never for an update) - it will uncompress it and chain it.

you do not want to update compressed data really.

you want to CREATE TABLE AS SELECT or INSERT /*+ APPEND */ to preserve compression.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

merge append into partitioned

Why is this happening?

view update does almost the same

wasted space

More to Explore

Hints

Does merge with append hint causes fragmentation?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

merge append into partitioned

Why is this happening?

view update does almost the same

wasted space

More to Explore

Hints