This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Nov 29, 2012 1:21 PM by ji li RSS

alter table move (containing clobs) to clean up chained rows....

ji li Pro
Currently Being Moderated
Hello colleagues,

I am running Oracle 11.2.0.2 EE on a Solaris 10 platform. (in DG configuration)

My question is regarding chained rows.

I have a slightly large table of approx. 3 Million records (16G in size).

According to dba_tables, my chained_cnt = 2221064.

Wow! That is excessive.

Now, I'm assuming the easiest and quickest way to resolve the chained rows is to do an "alter table ... move" command (using same tablespace).
From my research, this appears easy enough, and I've done this on tables in the past.

Two caveats are that I need to be sure to drop and rebuild all indexes immediately after I'm done, and gather fresh statistics (including indexes).

The question I have is regarding that this table contains about 20 CLOB fields, and 20 LOB indexes.

Will I be able to 'move' the table if it has clob datatypes?
Next question is how do I rebuild the lob indexes? (assuming I need to).
The table also has one Function Based index. Do I need to rebuilt it as well after doing the alter table move?

Thanks in advance for your guideance.
  • 1. Re: alter table move (containing clobs) to clean up chained rows....
    Marcus Rangel Journeyer
    Currently Being Moderated
    Do you really want to do that ? Have you read Tom Kyte's considerations about the subject ? Maybe it is just not worth your trouble...

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:358341515662
  • 2. Re: alter table move (containing clobs) to clean up chained rows....
    ji li Pro
    Currently Being Moderated
    Thanks Marcus for pointing out that article.

    As it turns out, our table has 517 columns, which according to Tom's article, would imply that all rows would be internally chained.

    So, now the question is whether chained_cnt in dba_tables refers to internally chained rows, or both chained (internally or externally), and migrated rows.

    The application this database supports is REMEDY, which of course is vendor provided, so we can not change the number of columns.

    Any suggestions? It is obvious at this point that we have a performance problem on this table, even though it says we are using full index scans and no full table scans.

    Explain plan for simple query is:

    SELECT STATEMENT
    SORT GROUP BY NOSORT
    TABLE ACCESS BY INDEX ROWID
    INDEX FULL SCAN

    but yet the cost is 304K and takes a long time to return a query for a table that only contains 3 to 5 million records.
  • 3. Re: alter table move (containing clobs) to clean up chained rows....
    Marcus Rangel Journeyer
    Currently Being Moderated
    It seems to me that you simply don't have a good filter (column(s) with good selectivity) to query this table. Is this the nature of you query, does it need to access the whole table to get the result ? Because if it is not, maybe an additional index or a change in the query can solve your performance issue. For instance, what is this simple query that needed to perform an index full scan ?
  • 4. Re: alter table move (containing clobs) to clean up chained rows....
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    It is obvious at this point that we have a performance problem on this table
    It is not that obvious for us here.

    HOW TO: Post a SQL statement tuning request - template posting
    HOW TO: Post a SQL statement tuning request - template posting
  • 5. Re: alter table move (containing clobs) to clean up chained rows....
    ji li Pro
    Currently Being Moderated
    Actually, most queries are using an index range scan or index full scan.
    But, just for the sake of this discussion, here is the simple query I mentioned.
    Note that my concern is of the chained or migrated rows, and how to resolve them.
    But if my table contains 520 columns, how can I get around intra-block chaining?

    The question also still remains how can I tell the difference between row chaining, row migration, and intra-block chaining and which is it that is showing up in dba_tables.chained_cnt?

    simple query:

    SELECT C536870916,COUNT(T2179.C536881135)
    FROM aradmin.T2179
    WHERE ((T2179.C536871037 = 'Trouble') AND ((T2179.C536870944 = 'New')
    OR (T2179.C536870944 = 'Assigned') OR (T2179.C536870944 = 'On Hold')))
    GROUP BY C536870916
    ORDER BY C536870916

    Explain plan for simple query is:

    SELECT STATEMENT
    SORT GROUP BY NOSORT
    TABLE ACCESS BY INDEX ROWID
    INDEX FULL SCAN

    Also, on this table of 520 columns, there are already 61 indexes, of which 20 are LOB indexes, and one FB index. All others are normal indexes.
    My thinking on moving the table is that I would have to rebuild each and every index, however, I'm not sure how to rebuild LOB or FB indexes.



    So, back to my original question: can I safely "move" a table containing several CLOB columns and also how do I rebuild the LOB indexes?


    Thanks.
  • 6. Re: alter table move (containing clobs) to clean up chained rows....
    Martin Preiss Expert
    Currently Being Moderated
    an INDEX FULL SCAN is a sorted read with single block I/O - so for a big result set it is in almost every case slower than a FULL TABLE SCAN that uses multiblock I/O.

    Regards

    Martin
  • 7. Re: alter table move (containing clobs) to clean up chained rows....
    sb92075 Guru
    Currently Being Moderated
    ji li wrote:



    So, back to my original question: can I safely "move" a table containing several CLOB columns and also how do I rebuild the LOB indexes?


    Thanks.
    I give up.
    Can you?

    Do you realize & understand that any INDEX does not change the content of any result set?
    So you will obtain the correct answer whether INDEX exists or not.
    It is just a function for how long it will take to return the result set.
    so either course of action is "safe".
  • 8. Re: alter table move (containing clobs) to clean up chained rows....
    ji li Pro
    Currently Being Moderated
    Very interesting...

    I know this is either a Richard Foote or Jonathan Lewis question, but in that case, is it considered better to have larger block sizes for indexes to read in more data (single block at a time as you noted). In that case, I'd like to rebuild the indexes into a separate tablespace with a larger block size (16K).

    What is your recommendation regarding this? I believe I saw a posting once that said larger block sizes for indexes were not necessarily beneficial.
  • 9. Re: alter table move (containing clobs) to clean up chained rows....
    ji li Pro
    Currently Being Moderated
    Hi SB...

    I'm not sure if you understand what i was asking.

    So, my intention is that i plan to "move" the table to rebuild the chained rows.
    The indexes which currently exist all point to rowids that are aligned with the current blocks containing those rows.
    Once I move the table, then obviously those rowids will no longer be valid and I will need to rebuild "all" indexes using rowids.

    First, not knowing if CLOB columns w/LOB indexes even use rowids, and also knowing that CLOBs and LOBs require different handling, this is why I am asking how to do this.

    Any help you can offer would be appreciated.

    I have researched this already and found this one web page that is somewhat helpful, but still not sure how this will work in our case since our LOBs are stored in the same tablespace.

    http://halimdba.blogspot.com/2011/01/how-to-rebuild-lob-index-in-oracle.html
  • 10. Re: alter table move (containing clobs) to clean up chained rows....
    Martin Preiss Expert
    Currently Being Moderated
    indeed a Richard-Foote-question :) -> http://richardfoote.wordpress.com/2009/02/18/larger-block-tablespace-for-indexes-revisited-part-i-the-tourist/: "I’ve previously discussed the various issues and myths relating to the so-called benefits of creating a separate, larger block tablespace for indexes and why it’s not recommended and generally a bad idea"

    Regards

    Martin
  • 11. Re: alter table move (containing clobs) to clean up chained rows....
    ji li Pro
    Currently Being Moderated
    interesting article indeed. I seem to remember reading something similar, and all the discussion below it between Don and Richard.

    Still, however, I'm rather confused on the subject, even after reading it, especially when Oracle's note states: - Larger blocksizes provides less fragmentation (row chaining and row migration) of large objects (LOB, BLOB, CLOB).

    For now, I'm going to forget about the larger block sizes until I have a better understanding of its benefit (or not).

    So, for this table, containing 520 columns, 60 indexes, and columns and indexes as CLOBs/LOBs, I'm hesitant to even attempt a 'move' operation - although I will test it in our test environment. My concern is still regarding all the LOB indexes on the CLOB columns. How do I rebuild them? Or, do I even need to since the CLOBs are not in the actual table anyway?
  • 12. Re: alter table move (containing clobs) to clean up chained rows....
    Martin Preiss Expert
    Currently Being Moderated
    I think chained rows and migrated rows are both counted in chained_cnt - at least that's what Jonathan Lewis (http://jonathanlewis.wordpress.com/2009/04/30/analyze-this/: "either of which will be reported as a chained row") and Tanel Poder (http://blog.tanelpoder.com/2009/11/04/detect-chained-and-migrated-rows-in-oracle/: "even though Oracle calls them all chained rows internally, as the chaining mechanism is the same for both cases") tell us.

    We can also check it:

    -- row too big for a block
    -- 11.2.0.1
    -- blocksize 8K
    create table test_chaining2 (
      col1 number
    , col2 varchar2(4000)
    , col3 varchar2(4000)
    );
    
    insert into test_chaining2
    select 1, lpad('*', 4000, '*'), lpad('*', 4000, '*') from dual;
    
    select table_name, chain_cnt from user_tables where table_name = 'TEST_CHAINING2';
    
    TABLE_NAME                      CHAIN_CNT
    ------------------------------ ----------
    TEST_CHAINING2                          1
    I also made a test with too many columns (> 254) - but don't want to add the code here ... For a table with 300 columns and 10000 rows I got a CHAIN_CNT of 667: but at the moment I don't have the time to find a good interpretation for that number ...

    Regards

    Martin
  • 13. Re: alter table move (containing clobs) to clean up chained rows....
    ji li Pro
    Currently Being Moderated
    Thanks for your help Martin.

    I've tried creating a simple table as select * from 'trouble_table' where rownum < 11, then gathered stats on the table, but the CHAIN_CNT was 0.

    Maybe this is an indication that the CHAIN_CNT I have on the actual table is from real row migration or chaining rather than intra-chaining due to too many columns.

    Oddly enough, when I did the CTAS it created the same table structure, and only the LOB indexes. It did not create any of the 'normal' indexes or the one FB index. Is this normal behavior?

    So, when I tried to rebuild one of the LOB indexes, it failed with:

    SQL> alter index "SYS_IL0000259013C00027$$" rebuild;
    alter index "SYS_IL0000259013C00027$$" rebuild
    *
    ERROR at line 1:
    ORA-02327: cannot create index on expression with datatype LOB


    So, back to my original question, do I need to rebuild the LOB indexes, and if so, how do I do that?
  • 14. Re: alter table move (containing clobs) to clean up chained rows....
    sb92075 Guru
    Currently Being Moderated
    ji li wrote:
    Thanks for your help Martin.

    I've tried creating a simple table as select * from 'trouble_table' where rownum < 11, then gathered stats on the table, but the CHAIN_CNT was 0.

    Maybe this is an indication that the CHAIN_CNT I have on the actual table is from real row migration or chaining rather than intra-chaining due to too many columns.
    post SQL that was used to "gather stats".

    post SQL that populates CHAIN_CNT column.
1 2 Previous Next

Legend

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