Clustering_Factor = 0

I looked at clustering_factor in the previous post and wondered what might cause it to be well below the number of blocks in the table. Chris Saxon suggested it might be due to rows with null values. I decided to look first at the two indexes with a clustering_factor of zero in the same Oracle 11.2 database and checked which columns they were on. Then I looked to see how many of the rows had null values in the columns concerned. The table had > 100 million rows and I found it much quicker to see if any rows had not null values instead. In both cases, there were none at all i.e. all rows in the table had null values in the columns in question:

 

SQL> select owner, index_name

  2  from dba_indexes

  3  where table_owner = 'UIMSMGR'

  4  and table_name = 'UABOPEN'

  5  and clustering_factor = 0

  6  /

 

OWNER                     INDEX_NAME

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

TFE_INDEXES               TFE_UABOPEN_COPA_CODE

UIMSMGR                   UABOPEN_COURT_CASE_INDEX

 

SQL> select column_position, column_name

  2  from dba_ind_columns

  3  where index_owner = 'TFE_INDEXES'

  4  and index_name = 'TFE_UABOPEN_COPA_CODE'

  5  order by 1

  6  /

 

COLUMN_POSITION COLUMN_NAME

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

              1 UABOPEN_COPA_CODE

 

SQL> select column_position, column_name

  2  from dba_ind_columns

  3  where index_owner = 'UIMSMGR'

  4  and index_name = 'UABOPEN_COURT_CASE_INDEX'

  5  order by 1

  6  /

 

COLUMN_POSITION COLUMN_NAME

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

              1 UABOPEN_COURT_CASE_NUMBER

 

SQL> select count(*)

  2  from uabopen

  3  where uabopen_copa_code is not null

  4  /

 

  COUNT(*)

----------

         0

 

SQL> select count(*)

  2  from uabopen

  3  where uabopen_court_case_number is not null

  4  /

 

  COUNT(*)

----------

         0

 

SQL>

 

As for the indexes with clustering_factor > 0 but less than the number of blocks in the table, I guess that some but not all of the rows had null values in the indexed columns. This is something I will leave you to check for yourselves if you feel so inclined.

Clustering_Factor

I attended the UKOUG conference early in December. While I was there, I went to a presentation by Chris Saxon, where he explained how Oracle decides when to use an index. The example below, which I ran in an Oracle 11.1 database, is based on what I learnt.

 

I used to believe that if a where clause only returned a small percentage of the rows in a table, Oracle would use an index if it could. Then I would be surprised whenever Oracle did not follow this rule. However, I now know that it is not as simple as this; it also depends on the clustering factor.

 

I ran the query below to look at the DBA_SEGMENTS view and show the number of rows for segments owned by SYSTEM, the total number of rows and the percentage of rows for segments owned by SYSTEM. The important point for this example is that SYSTEM owned less than 2% of the segments:

 

SQL> l

  1  select

  2  (select count(*)

  3   from dba_segments

  4   where owner = 'SYSTEM') as system,

  5  (select count(*)

  6   from dba_segments) as total,

  7  (select count(*)

  8   from dba_segments

  9   where owner = 'SYSTEM') /

 10  (select count(*)

 11   from dba_segments) * 100

 12  as percentage

 13* from dual

SQL> /

 

    SYSTEM      TOTAL PERCENTAGE

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

       434      23153 1.87448711

 

SQL>

 

I copied 3 columns from every row in this view into a table:

 

SQL> create table tab1

  2  as select owner, segment_name, bytes

  3  from dba_segments

  4  /

 

Table created.

 

SQL>

 

Based on what I used to think, if I queried this table to find all the rows for segments belonging to SYSTEM, Oracle would use an index if it had one. The problem with this idea is that even with an index, Oracle needs to read every block containing a row belonging to SYSTEM. So, once I had created the table, I counted the number of blocks with one (or more) of these rows. The rows were in several blocks as they were scattered throughout the table:

 

SQL> select count

  2  (distinct dbms_rowid.rowid_block_number(rowid))

  3  from tab1

  4  where owner = 'SYSTEM'

  5  /

 

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))

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

                                                 84

 

SQL>

 

… then I counted the number of blocks in the table:

 

SQL> select blocks

  2  from user_segments

  3  where segment_name = 'TAB1'

  4  /

 

   BLOCKS

----------

       128

 

SQL>

 

Although less than 2% of the table’s rows were for segments belonging to SYSTEM, even with an index in place, Oracle would have to read 84/128x100 = (almost) 66% of the blocks to get to them.

 

I created an index on the owner column:

 

SQL> create index ind1 on tab1(owner)

  2  /

 

Index created.

 

SQL>

 

I checked the clustering_factor of the index. If this is close to the number of BLOCKS in the table, the data is well clustered (in relation to the index in question). If the clustering_factor is higher, approaching the number of ROWS in the table, the data is not well clustered. In this case, the rows for segments belonging to SYSTEM were all over the place so the data was not well clustered:

 

SQL> select clustering_factor

  2  from user_indexes

  3  where index_name = 'IND1'

  4  /

 

CLUSTERING_FACTOR

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

             1087

 

SQL>

 

I gathered statistics for the table. I found that if I did not do this, Oracle had to do some dynamic sampling when querying the table and I want to look at this in a future post, not now:

 

SQL> exec dbms_stats.gather_table_stats(-

> ownname=>'ANDREW',tabname=>'TAB1');

 

PL/SQL procedure successfully completed.

 

SQL>

 

I traced my session and ran a test query, which calculated how much space the segments belonging to SYSTEM occupied:

 

SQL> alter session set sql_trace = true

  2  /

 

Session altered.

 

SQL> select sum(bytes)

  2  from tab1

  3  where owner = 'SYSTEM'

  4  /

 

SUM(BYTES)

----------

  52953088

 

SQL> alter session set sql_trace = false

  2  /

 

Session altered.

 

SQL>

 

… then I ran the trace file through tkprof and found that Oracle had not used the index when running the query. It had to read well over half of the table’s blocks so a full table scan was much quicker:

 

********************************************************************************

 

select sum(bytes)

from tab1

where owner = 'SYSTEM'

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0        120          0           1

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

total        4      0.00       0.00          0        120          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 1698  (ANDREW)

 

Rows     Row Source Operation

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

      1  SORT AGGREGATE (cr=120 pr=0 pw=0 time=0 us)

    434   TABLE ACCESS FULL TAB1 (cr=120 pr=0 pw=0 time=6 us cost=11 size=5655 card=435)

 

 

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   SORT (AGGREGATE)

    434    TABLE ACCESS   MODE: ANALYZED (FULL) OF 'TAB1' (TABLE)

 

********************************************************************************

 

I repeated the test but this time I sorted the data on the owner column first so the rows for segments belonging to SYSTEM would be together:

 

SQL> create table tab2

  2  as select owner, segment_name, bytes

  3  from dba_segments

  4  order by owner

  5  /

 

Table created.

 

SQL>

 

These rows were only in 3 blocks:

 

SQL> select count

  2  (distinct dbms_rowid.rowid_block_number(rowid))

  3  from tab2

  4  where owner = 'SYSTEM'

  5  /

 

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))

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

                                                  3

 

SQL>

 

… but (obviously), the total number of blocks was the same as in TAB1:                                                

 

SQL> select blocks

  2  from user_segments

  3  where segment_name = 'TAB2'

  4  /

 

   BLOCKS

----------

       128

 

SQL>

 

… so, to read all the rows for segments belonging to SYSTEM, Oracle would only need to access 3/128x100 = (just over) 2.3% of the blocks in the table (assuming it had an index to tell it where to go):

 

SQL> create index ind2 on tab2(owner)

  2  /

 

Index created.

 

SQL>

 

The clustering_factor was very close to the number of BLOCKS in the table so, as expected, the data was well clustered for the index:

 

SQL> select clustering_factor

  2  from user_indexes

  3  where index_name = 'IND2'

  4  /

 

CLUSTERING_FACTOR

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

              116

 

SQL> exec dbms_stats.gather_table_stats(-

> ownname=>'ANDREW',tabname=>'TAB2');

 

PL/SQL procedure successfully completed.

 

SQL> alter session set sql_trace = true

  2  /

 

Session altered.

 

SQL> select sum(bytes)

  2  from tab2

  3  where owner = 'SYSTEM'

  4  /

 

SUM(BYTES)

----------

  52953088

 

SQL> alter session set sql_trace = false

  2  /

 

Session altered.

 

SQL>

 

… and the trace file showed that Oracle used the index when it ran the query against TAB2:

 

********************************************************************************

 

select sum(bytes)

from tab2

where owner = 'SYSTEM'

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0          6          0           1

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

total        4      0.00       0.00          0          6          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 1698  (ANDREW)

 

Rows     Row Source Operation

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

      1  SORT AGGREGATE (cr=6 pr=0 pw=0 time=0 us)

    434   TABLE ACCESS BY INDEX ROWID TAB2 (cr=6 pr=0 pw=0 time=3 us cost=5 size=5499 card=423)

    434    INDEX RANGE SCAN IND2 (cr=3 pr=0 pw=0 time=0 us cost=2 size=0 card=423)(object id 219070)

 

 

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   SORT (AGGREGATE)

    434    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'TAB2'

               (TABLE)

    434     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'IND2' (INDEX)

 

********************************************************************************

 

The only problem with this is that my tables were only queried on one column. In real-life, tables are often queried on several columns. You can add indexes to speed up these queries but the data can only be stored one way round. It is therefore unlikely that all of the indexes will have an optimal clustering_factor. To illustrate this, I found a table with 21 indexes in an Oracle 11.2 production database:

 

SQL> select count(*) from dba_indexes

  2  where table_owner = 'UIMSMGR'

  3  and table_name = 'UABOPEN'

  4  /

 

  COUNT(*)

----------

        21

 

SQL>

 

I checked how many blocks and rows it had:

 

SQL> select blocks, num_rows from dba_tables

  2  where owner = 'UIMSMGR'

  3  and table_name = 'UABOPEN'

  4  /

 

    BLOCKS   NUM_ROWS

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

   4606999   99046543

 

SQL>

 

I checked the clustering_factor of each index:

 

SQL> select clustering_factor from dba_indexes

  2  where table_owner = 'UIMSMGR'

  3  and table_name = 'UABOPEN'

  4  order by 1

  5  /

 

CLUSTERING_FACTOR

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

                0

                0

             1324

             1640

          1284785

          4319498

          4431395

          5113131

         10048510

         11847679

         14469614

         14808490

         20604699

         21757869

         22821251

         23166573

         23336072

         24103639

         24306402

         25662626

         26071543

 

21 rows selected.

 

SQL>

 

The first five indexes have a clustering_factor well below the number of blocks in the table. I’m not sure what this means so I will try to look at it in a future post.

 

The next three indexes have a clustering_factor close to the number of blocks. The data is well clustered for these indexes.

 

However, the final 13 indexes have a higher clustering_factor. The data is not well clustered for these indexes so Oracle is less likely to use them.

Oracle Live SQL

I had the opportunity to attend the UKOUG Conference recently. While I was there, I went to a Roundtable run by Shakeeb Rahman from Oracle. He told us about an Oracle tool which allows you to run SQL online without installing Oracle yourself. All you need is an account on My Oracle Support. I logged onto it here. It’s immediately obvious how it works so, once I had connected, I decided to see which version of Oracle I was using (Oracle 12.1.0.2). As usual, click on the image to enlarge it:

I do not have access to an Oracle 12 database right now so this tool will help me to learn some new features. I will post the results in due course.

A Simple Example of an Index Organised Table Without Overflow

For a long time I have had a note on my task list to learn about index organized tables. I never got round to doing it because I thought I would never see one. However, I came across several in a 3^rd party application recently. An index organized table is a kind of index and table combined. You can see how they work in the example below, which I tested in an Oracle 11.2 database:

 

First I created a sequence. You don’t need a sequence to create an index organized table. I just used it to ensure that the index always contained unique values:

SQL> create sequence seq1

  2  /

 

Sequence created.

SQL>

I read that an index organized table cannot contain a LONG column. When I tried to do this, Oracle returned an ORA-02160:

SQL> create table iot1

  2  (owner varchar2(30),

  3   object_name varchar2(30),

  4   seq_no number,

  5   column_not_allowed long,

  6   constraint iot1_pk

  7   primary key (owner, object_name, seq_no))

  8  organization index

  9  /

organization index

             *

ERROR at line 8:

ORA-02160: index-organized table can not contain

columns of type LONG

 

SQL>

Without the LONG column, the index organized table was created successfully. This was an index organized table without overflow. I will try to look at overflow in a future post:

SQL> create table iot1

  2  (owner varchar2(30),

  3   object_name varchar2(30),

  4   seq_no number,

  5   constraint iot1_pk

  6   primary key (owner, object_name, seq_no))

  7  organization index

  8  /

 

Table created.

 

SQL>

After creating the index organized table, it had an IOT_TYPE of IOT. The IOT_NAME column was empty as the index organized table did not have overflow:

SQL> select iot_type, nvl(iot_name,'NULL')

  2  from user_tables

  3  where table_name = 'IOT1'

  4  /

 

IOT_TYPE             NVL(IOT_NAME,'NULL')

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

IOT                  NULL

SQL>

I added data to the index organized table like this:

SQL> begin

  2   for i in 1..15 loop

  3    insert into iot1

  4    select owner, object_name, seq1.nextval

  5    from dba_objects;

  6   end loop;

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL> select count(*) from iot1

  2  /

 

  COUNT(*)

----------

    996060

SQL>

The index organized table had an entry in DBA_TABLES:

SQL> select count(*) from dba_tables

  2  where table_name = 'IOT1'

  3  /

 

  COUNT(*)

----------

         1

SQL>

... but it did not appear in DBA_SEGMENTS:

SQL> select count(*) from dba_segments

  2  where segment_name = 'IOT1'

  3  /

 

  COUNT(*)

----------

         0

SQL>

... and its TABLESPACE_NAME entry in DBA_TABLES was empty: 

SQL> select nvl(tablespace_name,'NULL')

  2  from dba_tables

  3  where table_name = 'IOT1'

  4  /

 

NVL(TABLESPACE_NAME,'NULL')

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

NULL

SQL>

The index associated with the index organized table had an entry in DBA_SEGMENTS so I checked how big it was:

SQL> select bytes from dba_segments

  2  where segment_name = 'IOT1_PK'

  3  /

 

     BYTES

----------

  83886080

SQL>

Then I deleted several rows from the index organized table:

SQL> delete from iot1

  2  where owner = 'SYS'

  3  /

 

460995 rows deleted.

SQL>

... and checked that this deleted some leaf rows from the index:

SQL> analyze index iot1_pk validate structure

  2  /

 

Index analyzed.

 

SQL> select name, lf_rows, del_lf_rows

  2  from index_stats

  3  /

 

NAME          LF_ROWS DEL_LF_ROWS

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

IOT1_PK        848331      313266

 

SQL>

When this happens, you can usually just rebuild the index but when I tried to do this to the index for the index organized table, Oracle returned an ORA-28650:

SQL> alter index iot1_pk rebuild

  2  /

alter index iot1_pk rebuild

*

ERROR at line 1:

ORA-28650: Primary index on an IOT cannot be rebuilt

 

SQL>

So I moved the table instead:

SQL> alter table iot1 move online

  2  /

 

Table altered.

SQL>

... then I analyzed the index again:

SQL> analyze index iot1_pk validate structure

  2  /

 

Index analyzed.

SQL>

... and saw that the deleted leaf rows had disappeared:

 

SQL> select name, lf_rows, del_lf_rows

  2  from index_stats

  3  /

 

NAME          LF_ROWS DEL_LF_ROWS

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

IOT1_PK        535065           0

SQL>

... and the index was much smaller:

SQL> select bytes from dba_segments

  2  where segment_name = 'IOT1_PK'

  3  /

 

     BYTES

----------

  25165824

SQL>