This discussion is archived
7 Replies Latest reply: Dec 4, 2013 9:11 AM by NightWing RSS

simple question about block splitting

NightWing Journeyer
Currently Being Moderated

Hello Experts,

 

My question is, aren't the following DML performing 90-10 block split?? Because it has been said that the first one performs 50-50 block splitting.

 

SQL> CREATE TABLE album_sales_IOT(album_id number, country_id number, total_sals number, album_colour varchar2(20),

     CONSTRAINT album_sales_iot_pk PRIMARY KEY(album_id, country_id)) ORGANIZATION INDEX;

 

Table created.

 

1

 

SQL> BEGIN

 

  2    FOR i IN 5001..10000 LOOP

  3      FOR c IN 201..300 LOOP

  4        INSERT INTO album_sales_iot VALUES(i,c,ceil(dbms_random.value(1,5000000)), 'Yet more new rows');

  5      END LOOP;

  6    END LOOP;

  7    COMMIT;

  8  END;

  9  /

 

PL/SQL procedure successfully completed.

 

 

1

2

3

4

5

6

7

8

9

10

11

 

SQL> BEGIN

 

  2    FOR i IN 1..5000 LOOP

  3       FOR c IN 101..200 LOOP

  4          INSERT INTO album_sales_iot

  5          VALUES(i,c,ceil(dbms_random.value(1,5000000)), 'Some new rows');

  6       END LOOP;

  7    END LOOP;

  8    COMMIT;

  9  END;

10  /

PL/SQL procedure successfully completed.

  • 1. Re: simple question about block splitting
    StefanKoehler Explorer
    Currently Being Moderated

    Hi NightWing,

     

    > Because it has been said that the first one performs 50-50 block splitting.

    Who said it? .. However it can easily be answered with Snapper by Tanel Poder.

     

    First PL/SQL procedure

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------

        SID, USERNAME  , TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------

          8, SYS       , STAT, leaf node splits                                          ,          2427,      12.14,         ,             ,          ,           ,

          8, SYS       , STAT, leaf node 90-10 splits                                    ,          2427,      12.14,         ,             ,          ,           ,

          8, SYS       , STAT, branch node splits                                        ,             4,        .02,         ,             ,          ,           ,

          8, SYS       , STAT, root node splits                                          ,             1,        .01,         ,             ,          ,           ,

     

    Second PL/SQL procedure

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------

        SID, USERNAME  , TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------

          8, SYS       , STAT, leaf node splits                                          ,          2179,       10.9,         ,             ,          ,           ,

          8, SYS       , STAT, branch node splits                                        ,             8,        .04,         ,             ,          ,           ,


    Regards

    Stefan

  • 2. Re: simple question about block splitting
    NightWing Journeyer
    Currently Being Moderated

    According to these articles by Richard Foote,

     

     

    In the previous post, we saw how 50-50 index block splits on the ALBUM_SALES_IOT IOT table caused rows to move to new leaf blocks, resulting in a degradation in the PCT_DIRECT_ACCESS value of the associated ALBUM_SALES_IOT_TOTAL_SALES_I secondary index, which in turn resulted in poorer performance when using this index. We had to rebuild the secondary index (or update block references) to make all the “guess” components accurate and the index efficient again and so point to the correct locations within the parent IOT.

     

    IOT | Search Results | Richard Foote's Oracle Blog

     

    IOT Secondary Indexes – The Logical ROWID Guess Component Part I (Lucky) | Richard Foote's Oracle Blo…

  • 3. Re: simple question about block splitting
    NightWing Journeyer
    Currently Being Moderated

    In addition, When I look these DML's I can say that they are inserted sequential, So, can't I said that both of them perform 90-10 block splitting?

  • 4. Re: simple question about block splitting
    EdStevens Guru
    Currently Being Moderated

    please do not cross post

     

    simple question about block splitting

  • 5. Re: simple question about block splitting
    NightWing Journeyer
    Currently Being Moderated

    EdStevens wrote:

     

    please do not cross post

     

    simple question about block splitting

    I did'nt do it on purpose. Sorry about that.

  • 6. Re: simple question about block splitting
    StefanKoehler Explorer
    Currently Being Moderated

    Hi NightWing,

    well you missed the initial data set creation here (and mixed something up in-between): http://richardfoote.wordpress.com/2012/01/10/index-organized-tables-an-introduction-of-sorts-pyramid-song/

     

    That is the same stuff as i did with your provided (first) PL/SQL procedure (my first run with 'Yet more new rows'). You can see 90-10 block splits only. However 50-50 block splits occur by running the second PL/SQL procedure (with 'Some new rows') afterwards. That's the same as Richard mentioned in his second blog post, but in my case the initial data set was different based on your provided information: http://richardfoote.wordpress.com/2012/04/26/iot-secondary-indexes-the-logical-rowid-guess-component-part-i-lucky/

     

    However he executed the PL/SQL procedure (with 'Yet more new rows') as a third run (http://richardfoote.wordpress.com/2012/05/08/iot-secondary-indexes-the-logical-rowid-guess-component-part-ii-move-on/) and in this / his case the PK values are increasing monotonically based on his previously created data sets. So no 50-50 block splits (as he also stated) - everything logical and all right.

     

    > My question is, aren't the following DML performing 90-10 block split?? Because it has been said that the first one performs 50-50 block splitting.

    He never said this. He said the following about the one with 'Yet more new rows' (in your post the first PL/SQL procedure):

    We notice that the PCT_DIRECT_ACCESS value remains unchanged. So, no 50-50 block split, no PCT_DIRECT_ACCESS degradation with regard the secondary indexes.

     

    You can cross-check this on your own by a query on session statistics or using Snapper by Tanel Poder.

     

    Regards

    Stefan

  • 7. Re: simple question about block splitting
    NightWing Journeyer
    Currently Being Moderated

    StefanKoehler wrote:

     

    Hi NightWing,

    well you missed the initial data set creation here (and mixed something up in-between): http://richardfoote.wordpress.com/2012/01/10/index-organized-tables-an-introduction-of-sorts-pyramid-song/

     

    That is the same stuff as i did with your provided (first) PL/SQL procedure (my first run with 'Yet more new rows'). You can see 90-10 block splits only. However 50-50 block splits occur by running the second PL/SQL procedure (with 'Some new rows') afterwards. That's the same as Richard mentioned in his second blog post, but in my case the initial data set was different based on your provided information: http://richardfoote.wordpress.com/2012/04/26/iot-secondary-indexes-the-logical-rowid-guess-component-part-i-lucky/

     

    However he executed the PL/SQL procedure (with 'Yet more new rows') as a third run (http://richardfoote.wordpress.com/2012/05/08/iot-secondary-indexes-the-logical-rowid-guess-component-part-ii-move-on/) and in this / his case the PK values are increasing monotonically based on his previously created data sets. So no 50-50 block splits (as he also stated) - everything logical and all right.

     

    > My question is, aren't the following DML performing 90-10 block split?? Because it has been said that the first one performs 50-50 block splitting.

    He never said this. He said the following about the one with 'Yet more new rows' (in your post the first PL/SQL procedure):

    We notice that the PCT_DIRECT_ACCESS value remains unchanged. So, no 50-50 block split, no PCT_DIRECT_ACCESS degradation with regard the secondary indexes.

     

    You can cross-check this on your own by a query on session statistics or using Snapper by Tanel Poder.

     

    Regards

    Stefan

     

     

    As you mentioned, I missed the initial data set creation. Thanks a lot.

Legend

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