1 2 Previous Next 19 Replies Latest reply on Jan 16, 2014 9:04 AM by Jonathan Lewis Go to original post
      • 15. Re: Collecting stats takes forever after adding new index - why?
        Hemant K Chitale

        I actually see the avg_row_len increasing -- as if the computed value is stored with the table.

         

        SQL> create table hkc_test_fbi_vc as select * from dba_objects where rownum < 100;

        Table created.

        SQL> exec dbms_stats.gather_table_stats('','HKC_TEST_FBI_VC');

        PL/SQL procedure successfully completed.

        SQL> select num_rows, avg_row_len, blocks
          2  from user_tables
          3  where table_name = 'HKC_TEST_FBI_VC';

          NUM_ROWS AVG_ROW_LEN     BLOCKS
        ---------- ----------- ----------
                99          76          5

        1 row selected.

        SQL> create index hkc_test_fbi_vc_ndx on hkc_test_fbi_vc(rpad(owner,100,'X'));

        Index created.

        SQL> exec dbms_stats.gather_table_stats('','HKC_TEST_FBI_VC');

        PL/SQL procedure successfully completed.

        SQL> select num_rows, avg_row_len, blocks
          2  from user_tables
          3  where table_name = 'HKC_TEST_FBI_VC';

          NUM_ROWS AVG_ROW_LEN     BLOCKS
        ---------- ----------- ----------
                99         177          5

        1 row selected.

        SQL>

         

        As expected, the new column, being virtual, doesn't appear in a describe of the table.

        SQL> desc hkc_test_fbi_vc
        Name                                                                                Null?    Type
        ----------------------------------------------------------------------------------- -------- ---------------

        OWNER                                                                                        VARCHAR2(30)
        OBJECT_NAME                                                                                  VARCHAR2(128)
        SUBOBJECT_NAME                                                                               VARCHAR2(30)
        OBJECT_ID                                                                                    NUMBER
        DATA_OBJECT_ID                                                                               NUMBER
        OBJECT_TYPE                                                                                  VARCHAR2(19)
        CREATED                                                                                      DATE
        LAST_DDL_TIME                                                                                DATE
        TIMESTAMP                                                                                    VARCHAR2(19)
        STATUS                                                                                       VARCHAR2(7)
        TEMPORARY                                                                                    VARCHAR2(1)
        GENERATED                                                                                    VARCHAR2(1)
        SECONDARY                                                                                    VARCHAR2(1)
        NAMESPACE                                                                                    NUMBER
        EDITION_NAME                                                                                 VARCHAR2(30)

        SQL>

        SQL> select column_name, column_id from user_tab_columns
          2  where table_name = 'HKC_TEST_FBI_VC'
          3  order by 2;

        COLUMN_NAME                     COLUMN_ID
        ------------------------------ ----------
        OWNER                                   1
        OBJECT_NAME                             2
        SUBOBJECT_NAME                          3
        OBJECT_ID                               4
        DATA_OBJECT_ID                          5
        OBJECT_TYPE                             6
        CREATED                                 7
        LAST_DDL_TIME                           8
        TIMESTAMP                               9
        STATUS                                 10
        TEMPORARY                              11
        GENERATED                              12
        SECONDARY                              13
        NAMESPACE                              14
        EDITION_NAME                           15

        15 rows selected.

        SQL>

         

        SQL> select leaf_blocks, blevel, distinct_keys
          2  from user_indexes
          3  where index_name = 'HKC_TEST_FBI_VC_NDX';

        LEAF_BLOCKS     BLEVEL DISTINCT_KEYS
        ----------- ---------- -------------
                  2          1             1

        1 row selected.

        SQL>

        (which is OK because all 99 rows have the same OWNER value SYS)

        SQL> select distinct owner from hkc_test_fbi_vc;

        OWNER
        ------------------------------
        SYS

        1 row selected.

        SQL>

         

        Hemant K Chitale

        • 16. Re: Collecting stats takes forever after adding new index - why?
          I actually see the avg_row_len increasing -- as if the computed value is stored with the table.

          . . .

          As expected, the new column, being virtual, doesn't appear in a describe of the table.

          That's because that data is sourced from ALL_TAB_COLUMNS which filters hidden columns.

           

          It shows in ALL_TAB_COLS with an AVG_COL_LEN of 101. Sum up all of those and you get 178.

          • 17. Re: Collecting stats takes forever after adding new index - why?
            Hemant K Chitale

            This was with reference to Jonathan's assertion "Oracle doesn't store the value of the function in the table for each row, it simply stores the description of the defining function as the column definition in the data dictionary"

            I believe that the  actual values derived from the function are stored with each row.

             

             

            Hemant K Chitale


            • 18. Re: Collecting stats takes forever after adding new index - why?
              This was with reference to Jonathan's assertion "Oracle doesn't store the value of the function in the table for each row, it simply stores the description of the defining function as the column definition in the data dictionary"

              I believe that the  actual values derived from the function are stored with each row.

              If you dump a table block you will see that the virtual data isn't really stored. The value is, of course, stored in an index along with the ROWID of the corresponding row as Jonathan said.

               

              An easier way to see it isn't really stored is to modify your example a bit to make the hidden column REALLY big and then see how many rows you can fit into a block:

              CREATE TABLE TEST_FBI_VC1
              (
                OWNER           VARCHAR2(30 BYTE)
              )

              create index hkc_test_fbi_vc_ndx1 on test_fbi_vc1(rpad(owner,3000,'X'));

              INSERT INTO TEST_FBI_VC1 VALUES ('ABC')

              exec dbms_stats.gather_table_stats('','TEST_FBI_VC1');

               

              SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS
              FROM ALL_TABLES
              WHERE TABLE_NAME = 'TEST_FBI_VC1'

               

              NUM_ROWS,BLOCKS,EMPTY_BLOCKS

              540,5,0

               

              Hmmm - 540 rows of 3003 bytes doesn't seem like it would fit in only 5 blocks (the initial allocation).

              • 19. Re: Collecting stats takes forever after adding new index - why?
                Jonathan Lewis

                Hemant K Chitale wrote:

                 

                I actually see the avg_row_len increasing -- as if the computed value is stored with the table.

                 

                 

                 

                Hemant,

                 

                That's a good point, and worth making.

                 

                It's an example of how any new feature that appears in Oracle has a number of little bits of related code that have not been brought up to date. There's some generic code that calculates avg_row_len as sum(avg_col_len), and doesn't exclude the virtual columns.

                 

                Arguably, of course, it's the correct thing to do since (if I recall correctly) the optimizer actually uses avg_row_len in its volume (bytes) calculations when your SQL includes select * from base_table" - maybe avg_row_len has always meant "length of in-memory image of row" rather than "length of on-disc size of row".

                 

                If you want an example of another anomaly you just have to look at LOBs. If you store an empty_clob() in a column Oracle reports its column size as 86 bytes, even though the on-disc size is 35 bytes (or thereabouts).  Presumably the in-memory size of a LOB locator is the size of a statically declared structure that has a maximum size of 86 bytes.

                 

                 

                (Tip: try experimenting with ANALYZE on a table, then add a virtual column - ANALYZE uses the on-disc size as the row length)

                 

                Regards

                Jonathan Lewis.

                1 2 Previous Next