This discussion is archived
11 Replies Latest reply: Sep 11, 2013 7:39 PM by rp0428 RSS

table size increased after truncate & insert

Pradeepan Newbie
Currently Being Moderated

Hi,

We have a table in 9.2.0.6  64 bit database running on  AIX5.1.  This table is populated through triggers.  We truncated the table & insert the data using same code from the trigger (insert /*+ APPEND */  .. select from ..) with append option. We found huge difference between both versions of this table.

 

Before truncate

Size  -  8.5 GB

Blocks - 1103488

Extents - 8621

Avg_row_len  - 270

 

After truncate & insert

Size  -  15 GB

Blocks - 1977984

Extents - 15453

Avg_row_len  - 456

 

The size of  all extents, number of rows are same on both.

The tablespace is dictionary managed & segment management manual

 

I want to know the reason for such difference . The only thing i suspect is the initial table pctfree & pctused were modified in the past, but i cannot confirm this as i am new to this environment & nobody has the knowledge of the history on this table

 

Thanks

Prad

  • 1. Re: table size increased after truncate & insert
    sb92075 Guru
    Currently Being Moderated

    >Avg_row_len  - 270

    why such discrepancy in AVG_ROW_LEN?

    >Avg_row_len  - 456


  • 2. Re: table size increased after truncate & insert
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    > insert the data using same code from the trigger (insert /*+ APPEND */  .. select from ..) with append option.

    Were there multiple INSERT calls or a single INSERT call ?

    A trigger can't commit so how did you commit the direct path insert ?

     

    Was PCTFREE set to a high value ?

     

    Hemant K Chitale


  • 3. Re: table size increased after truncate & insert
    Pradeepan Newbie
    Currently Being Moderated

    We have triggers on multiple tables which insert/delete/update data to this particular table (say table t1). This trigger code does not have "append" hint.

    For application upgrade purpose, this database is duplicated to a test instance and then the table t1 is truncated, inserted with data from those tables by

    insert /*+ APPEND */ into t1 select  from   t2,t3,t4 where ...

    commit;

    insert /*+ APPEND */ into t1 select  from   t2,t3 where ...

    commit

    ..

    and so on..

     

    At the end we have same number of rows & same data on both tables.

    The PCTFREE wasn't changed during this exercise

     

    Thanks

    Prad

  • 4. Re: table size increased after truncate & insert
    sb92075 Guru
    Currently Being Moderated

    >At the end we have same number of rows & same data on both tables.

    but the new rows have about extra 200 bytes which account for the increase in table size

     

    How can each row of "same data" consume an additional 200 bytes per row?

  • 5. Re: table size increased after truncate & insert
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    if average row length is different then how can the data be same? Obviously something has changed -- perhaps some strings that were empty now are filled with spaces or something. Even if PCTFREE changed, it still wouldn't be the answer because it shouldn't have affected average row length (it would only affect the way data is packed into blocks). If you cannot restore the old vesion of the table using flashback or backup, then I guess there's no way to find out for sure.

     

    Best regards,

    Nikolay

  • 6. Re: table size increased after truncate & insert
    Pradeepan Newbie
    Currently Being Moderated

    Hi all,

     

    We found the reason, it was due to the way the trigger handles the null values with respect to char & varchar2  columns. The vendor has not followed the same logic.

     

    Thank all for valuable comments

     

    Regards

    Prad

  • 7. Re: table size increased after truncate & insert
    rp0428 Guru
    Currently Being Moderated

    Thanks for following up.

     

    But you haven't provided enough information about the problem and solution to help anyone else that may run across it.

     

    Exactly what does 'the way the trigger handles the null values' mean?

     

    What does 'vendor has not followed the same logic' mean?

     

    Please explain this in more detail so that others may be helped.


  • 8. Re: table size increased after truncate & insert
    Pradeepan Newbie
    Currently Being Moderated

    Hi,

     

    The base tables contain multiple char data type columns  (most of them  are null ) & the target table contains the same columns but with datatype as varchar2. The triggers on the base tables apply RTRIM to those columns when writing to the target table. Vendor did not apply the RTRIM in their code.

     

    Regards

    Prad

  • 9. Re: table size increased after truncate & insert
    rp0428 Guru
    Currently Being Moderated
    The base tables contain multiple char data type columns  (most of them  are null ) & the target table contains the same columns but with datatype as varchar2. The triggers on the base tables apply RTRIM to those columns when writing to the target table. Vendor did not apply the RTRIM in their code.

     

    Thanks for the additional information.

     

    If a CHAR is NULL then it doesn't contain a value at all. If it is not NULL then it will be padded to the full length. So a NULL CHAR column will result in a NULL VARCHAR2 column. And a non-null CHAR column will result in the full length being transferred to the VARCHAR2 column. The RTRIM would TRIM any of the excess pad characters from the value.

     

    But the question you posted didn't have anything to do with a vendor:

    This table is populated through triggers.  We truncated the table & insert the data using same code from the trigger (insert /*+ APPEND */  .. select from ..) with append option. We found huge difference between both versions of this table.

    You say you used the same code from the trigger. So if you use the same code as the trigger and both sets of code apply RTRIM then why would the result be different just because you ran that code manually?

  • 10. Re: table size increased after truncate & insert
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    The RTRIM would also remove the padded blanks when copying from the CHAR to the VARCHAR2.

     

     

    SQL> create table hkc_test_10 (id_column number, data_char_col char(10));

    Table created.

    SQL> insert into hkc_test_10 select rownum, 'X' from dual connect by level < 11;

    10 rows created.

    SQL> select id_column, vsize(data_char_col) from hkc_test_10 order by 1;

    ID_COLUMN VSIZE(DATA_CHAR_COL)
    ---------- --------------------
             1                   10
             2                   10
             3                   10
             4                   10
             5                   10
             6                   10
             7                   10
             8                   10
             9                   10
            10                   10

    10 rows selected.

    SQL> create table hkc_test_20 (id_column number, data_vc2_col varchar2(10));

    Table created.

    SQL> insert into hkc_test_20 select * from hkc_test_10;

    10 rows created.

    SQL> select id_column, vsize(data_vc2_col) from hkc_test_20 order by 1;

    ID_COLUMN VSIZE(DATA_VC2_COL)
    ---------- -------------------
             1                  10
             2                  10
             3                  10
             4                  10
             5                  10
             6                  10
             7                  10
             8                  10
             9                  10
            10                  10

    10 rows selected.

    SQL> delete hkc_test_20;

    10 rows deleted.

    SQL> insert into hkc_test_20 select id_column, rtrim(data_char_col) from hkc_test_10;

    10 rows created.

    SQL> select id_column, vsize(data_vc2_col) from hkc_test_20 order by 1;

    ID_COLUMN VSIZE(DATA_VC2_COL)
    ---------- -------------------
             1                   1
             2                   1
             3                   1
             4                   1
             5                   1
             6                   1
             7                   1
             8                   1
             9                   1
            10                   1

    10 rows selected.

    SQL>

     

    Hemant K Chitale

  • 11. Re: table size increased after truncate & insert
    rp0428 Guru
    Currently Being Moderated
    The RTRIM would also remove the padded blanks when copying from the CHAR to the VARCHAR2.

    Ummm - yes it would. Which is just what I said above:

    And a non-null CHAR column will result in the full length being transferred to the VARCHAR2 column.The RTRIM would TRIM any of the excess pad characters from the value.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points