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>

ORA-01440

I was asked to run some SQL today and it generated an ORA-01440. I did not remember having seen this error before so I decided to check it out in an Oracle 11.2 database. First I created a table and added a row of data to it:

SQL> create table tab1(col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values(1)

 2  /

 

1 row created.

 

SQL>

Then I tried to change col1 to number(5), Oracle gave me an ORA-01440. The reason for this should be obvious:

SQL> alter table tab1 modify (col1 number(5))

  2  /

alter table tab1 modify (col1 number(5))

                         *

ERROR at line 1:

ORA-01440: column to be modified must be empty to decrease precision or scale

 

SQL>

 

I removed the data from the table and when I tried to modify it again, I did not get an error:

SQL> delete tab1

  2  /

 

1 row deleted.

 

SQL> alter table tab1 modify (col1 number(5))

  2  /

 

Table altered.

 

SQL>

Instead of removing the data, I could have set the value(s) to null:

 

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL> create table tab1(col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values(1)

  2  /

 

1 row created.

 

SQL> alter table tab1 modify (col1 number(5))

  2  /

alter table tab1 modify (col1 number(5))

                         *

ERROR at line 1:

ORA-01440: column to be modified must be empty to decrease precision or scale

 

SQL> update tab1 set col1 = null

  2  /

 

1 row updated.

 

SQL> alter table tab1 modify (col1 number(5))

  2  /

 

Table altered.

 

SQL>

Whichever method you choose, you should consider saving the data beforehand and reinstating it afterwards.

The Right and Wrong Ways to Add a NOT NULL Constraint

I tested these examples in an Oracle 11.2 database. The first one shows how to add a NOT NULL constraint retrospectively. You start by creating a table:

SQL> create table andrew (col1 varchar2(1))
  2  /

Table created.

SQL>

Then at some point in the future, you add a NOT NULL constraint like this: 

SQL> alter table andrew modify (col1 not null)
  2  /

Table altered.

SQL>

Doing it this way, the constraint is obvious when you describe the table:

SQL> desc andrew
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                       NOT NULL VARCHAR2(1)

SQL>

… and, if you try to add a null value, the error message is self-explanatory:

SQL> insert into andrew values (null)
  2  /
insert into andrew values (null)
                           *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ORACLE"."ANDREW"."COL1")

SQL>

The second example, which I saw recently, shows how NOT to do it. You start, as before, by creating a table:

SQL> create table fred (col1 varchar2(1))
  2  /

Table created.

SQL>

… then you add a CHECK constraint as follows:

SQL> alter table fred
  2  add constraint con1
  3  check (col1 is not null)
  4  /

Table altered.

SQL>

You cannot see this constraint when you describe the table:

SQL> desc fred
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                                VARCHAR2(1)

SQL>

… and when you try to add a null value, the error message is not very helpful:

SQL> insert into fred values (null)
  2  /
insert into fred values (null)
*
ERROR at line 1:
ORA-02290: check constraint (ORACLE.CON1) violated

SQL>

ORA-04000

I created a table in an Oracle 11.2 database. I did not specify pctfree or pctused so it was given the defaults of 10% and 40% respectively:

SQL> conn system/manager
Connected.
SQL> create table t1 (c1 number)
  2  /

Table created.

SQL> select pct_free, pct_used
  2  from user_tables
  3  where table_name = 'T1'
  4  /

  PCT_FREE   PCT_USED
---------- ----------
        10         40

SQL>

When Oracle inserts rows into a table, the pctfree specifies the percentage of space to leave free in each block for subsequent updates to the rows. This free space is used later if an extra column is added to the table or if a varchar2 column is updated to store a longer value than before. Once the pctfree in a given block falls below the specified value, no new rows can be inserted in that block so it is removed from the free list. It is then not allowed to accept new rows until the percentage of space used in the block falls below the pctused figure. When this happens, the block goes back onto the free list again. You can alter the pctfree and/or pctused settings like this:

SQL> alter table t1 pctfree 20
  2  /

Table altered.

SQL>

Going through some old notes from an Oracle 9 performance tuning course, I read that the sum of pctfree and pctused cannot be more than 100. This seemed reasonable. If you had a pctused of 40%, a pctfree of 70% and a block which was 35% full, Oracle would not know what to do with it. The pctused figure of 40% would tell Oracle to leave the block on the free list wheras the pctfree figure of 70% would tell Oracle to remove it (from the free list). In situations like this, Oracle usually has a special error message to display. In this case, it is ORA-04000, as you can see below:

SQL> alter table t1 pctfree 70
  2  /
alter table t1 pctfree 70
*
ERROR at line 1:
ORA-04000: the sum of PCTUSED and PCTFREE cannot
exceed 100

SQL>

Read Only Tables

Oracle 11 introduced a new ALTER TABLE syntax, which allows you to make a table READ ONLY, so I decided to try it out. First I created a table:

SQL> create table table_list

  2  as select table_name from dba_tables

  3  /

 

Table created.

SQL>

... then I made it READ ONLY:

 

SQL> alter table table_list read only

  2  /

 

Table altered.

... so when I tried to update it, Oracle gave me an error:

 

SQL> update table_list

  2  set table_name = 'BLAH'

  3  /

update table_list

       *

ERROR at line 1:

ORA-12081: update operation not allowed on table

"ORACLE"."TABLE_LIST" 

SQL>

I changed the table back to READ WRITE mode like this: 

 

SQL> alter table table_list read write

  2  /

 

Table altered.

SQL>

... then I was able to update it:

 

SQL> update table_list

  2  set table_name = 'BLAH'

  3  /

 

3081 rows updated.

 

SQL>

When you create a table, it is in READ WRITE mode by default. If you try to make it READ WRITE again, you get an error. Once a table is in READ ONLY mode, you get an error if you try to make it READ ONLY again. I checked this out as follows:

SQL> create table andrew (col1 varchar2(1))
  2  /

Table created.

SQL> alter table andrew read write

  2  /

alter table andrew read write

*

ERROR at line 1:

ORA-14140: table ORACLE.ANDREW is already in

read/write mode

SQL> alter table andrew read only

  2  /

Table altered.

SQL> alter table andrew read only

  2  /

alter table andrew read only

*

ERROR at line 1:

ORA-14139: table ORACLE.ANDREW is already in read-only

mode

SQL>

ORA-12988

Tables in the SYS schema are usually created by Oracle but you can create them yourself. If they are created by Oracle you should not be trying to change them. If they are created by you, you should seriously consider moving them into another schema. Either way, if you try to drop a column from a table owned by SYS, you get an ORA-12988. You can see what I mean in the example below, which I ran in an Oracle 11.2 database:

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> create table tab1

  2  (col1 number,

  3   col2 number)

  4  /

 

Table created.

 

SQL> alter table tab1 drop column col1

  2  /

alter table tab1 drop column col1

*

ERROR at line 1:

ORA-12988: cannot drop column from table owned by SYS

 

SQL>

ORA-01430

This was tested on Oracle 11.2. If you try to use a column name twice in the same table, you get an ORA-01430: 

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> alter table tab1 add (col1 number)

  2  /

alter table tab1 add (col1 number)

                      *

ERROR at line 1:

ORA-01430: column being added already exists in table

 

SQL>

ORA-12985

If you try to drop a column from a table in a read only tablespace, you get an ORA-12985. If you really need to drop the column, you must put the tablespace back into read write mode first. You can see what I mean in the example below, which I tested in an Oracle 9 database:

 

SQL> create tablespace andrews_ts

  2  datafile '/tmp/andrew.dbf'

  3  size 1m

  4  /

 

Tablespace created.

 

SQL> grant dba to andrew identified by reid

  2  /

 

Grant succeeded.

 

SQL> alter user andrew default tablespace andrews_ts

  2  /

 

User altered.

 

SQL> conn andrew/reid

Connected.

SQL> create table tab1

  2  (col1 number, col2 number)

 3  /

 

Table created.

 

SQL> alter tablespace andrews_ts read only

  2  /

 

Tablespace altered.

 

SQL> alter table tab1 drop column col2

  2  /

alter table tab1 drop column col2

*

ERROR at line 1:

ORA-12985: tablespace 'ANDREWS_TS' is read only,

cannot drop column

 

SQL> alter tablespace andrews_ts read write

  2  /

 

Tablespace altered.

 

SQL> alter table tab1 drop column col2

  2  /

 

Table altered.

 

SQL> drop tablespace andrews_ts

  2  including contents and datafiles

  3  /

 

Tablespace dropped.

 

SQL>

Moving a Table Deletes its Statistics

Statistics are important as they help the optimizer to work out the execution plan for a SQL statement. If you move a table, this deletes its statistics so you need to analyze it again afterwards. You can see this in the example below. First I created a table:

SQL> create table object_list

  2  as select * from dba_objects

  3  /

Table created.

SQL>

When you create a table it has no statistics so the num_rows column is null:

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT
----------------------------------------

NULL

                                                  

SQL>

When you calculate statistics, the num_rows column is updated:

SQL> analyze table object_list

  2  compute statistics

  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT

----------------------------------------
7932

SQL>

Moving the table deletes the statistics so num_rows is null afterwards:

SQL> alter table object_list move

  2  /

Table altered.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT

---------------------------------------- NULL                                       

SQL>

To reinstate the statistics, simply analyze the table again:

SQL> analyze table object_list

  2  compute statistics

  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT
----------------------------------------
7932

SQL>