Tuning is null query in oracle

When there is some simple query which is using just is null criteria on big table, oracle cost based optimizer making table access full, so query running slow, here is demonstration how to tune similar queries:

create table my_test_table as select * from dba_objects;

insert into my_test_table  select * from dba_objects;
commit;

UPDATE my_test_table set object_name=null where rownum<=100;
commit;

create index my_test_table_i1 on my_test_table(object_name);

begin
  dbms_stats.gather_table_stats(ownname => 'QOBESA',
                                tabname => 'MY_TEST_TABLE',
                                cascade => true);
end;


select * from my_test_table where object_name is null;


------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |  100 | 12500 |  108 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | MY_TEST_TABLE |  100 | 12500 |  108 | 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("OBJECT_NAME" IS NULL)

So we have table with more than 100 000 rows and in 100 rows column object_name is null, which is indexed column, but optimizer doesn't using it and making full table scan, if your table is huge and it contains many gigabytes of data it will be big problem. Many believes that there is no way to tune this type of statement, because oracle does not store NULL values in indexes, but here is little trick to use index on not null columns

create index my_test_table_i2 on my_test_table(object_name, -1);


begin
  dbms_stats.gather_table_stats(ownname => 'QOBESA',
                                tabname => 'MY_TEST_TABLE',
                                cascade => true);
end;


select * from my_test_table where object_name is null;


---------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |  100 | 12900 |   78 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | MY_TEST_TABLE    |  100 | 12900 |   78 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | MY_TEST_TABLE_I2 |  100 |       |    3 | 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("OBJECT_NAME" IS NULL)

So solution is to add some constant to the end of the index to be sure null values are stored.

How to Check and Modify Default Attributes of Tables and Indexes Partitions

Sometimes there is situation when you need to modify default attributes for tables, tables partitions, index or index partitions in Oracle database. When you modify default attributes, the new attributes affect only future partitions, or subpartitions, that are created. The default values can still be specifically overridden when creating a new partition or subpartition. Here I'll show you how to do these things:

Check Default atributes for partitioned Table :

SQL> SELECT DEF_TABLESPACE_NAME FROM DBA_PART_TABLES WHERE TABLE_NAME='TABLE_NAME';

Check Default atributes for partitioned Index :

SQL> SELECT DEF_TABLESPACE_NAME FROM DBA_PART_INDEXES WHERE INDEX_NAME='INDEX_NAME';

Modify Default Attributes for a Table :

SQL> ALTER TABLE OWNER.TABLE_NAME MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;

Modify Default Attributes for a Index :

SQL> ALTER INDEX OWNER.INDEX_NAME MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;

Modify Default Attributes for a Table partitions :

SQL> ALTER TABLE OWNER.TABLE_NAME MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME;

Modify Default Attributes for a Index partitions :

SQL> ALTER INDEX OWNER.INDEX_NAME MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME;

Oracle Text index size

Oracle Text index is a very powerful feature for searching user specified text in big text columns for example in clob type columns. As a Database Administrator (DBA) you need to monitor it's sizes and growth, but it's segments are not shown in dba_segments view, because every text index internally consists several tables with names prefixed 'DR$', some of these tables have their own indexes and some of them are Index Organized Tables (IOT). Therefore when you need to find actual size of oracle text index you have to consider all 'DR$' tables and their indexes.
Here is script which calculates all text indexes sizes in Oracle Database:

------ partitioned text index size
(select table_name, x.index_name, sum(MB) MB
  from (select substr(table_name, 4, instr(table_name, '#', -1) - 4) index_name,
               sum(bytes) / 1024 / 1024 MB
          from dba_tables t, dba_segments s
         where t.table_name = s.segment_name
           and t.table_name like 'DR$%$%'
         group by substr(table_name, 4, instr(table_name, '#', -1) - 4)
        union
        select substr(table_name, 4, instr(table_name, '#', -1) - 4) index_name,
               sum(bytes) / 1024 / 1024 MB
          from dba_indexes i, dba_segments s
         where i.index_name = s.segment_name
           and i.table_name like 'DR$%$%'
         group by substr(table_name, 4, instr(table_name, '#', -1) - 4)) x,
       dba_indexes ind
 where x.index_name = ind.index_name
 group by table_Name, x.index_name
)
union 
--- not partitioned text index size
(select table_name, x.index_name, sum(MB) MB
  from (select substr(table_name, 4, instr(table_name, '$', -1) - 4) index_name,
               sum(bytes) / 1024 / 1024 MB
          from dba_tables t, dba_segments s
         where t.table_name = s.segment_name
           and t.table_name like 'DR$%$%'
         group by substr(table_name, 4, instr(table_name, '$', -1) - 4)
        union
        select substr(table_name, 4, instr(table_name, '$', -1) - 4) index_name,
               sum(bytes) / 1024 / 1024 MB
          from dba_indexes i, dba_segments s
         where i.index_name = s.segment_name
           and i.table_name like 'DR$%$%'
         group by substr(table_name, 4, instr(table_name, '$', -1) - 4)) x,
       dba_indexes ind
 where x.index_name = ind.index_name
 group by table_Name, x.index_name
 )
 order by table_name,  index_name;

ORA-08104: this index object ##### is being online built or rebuilt

Hello,
If you have very busy database and you decide to run an online index rebuild, but unfortunately your session terminated abnormally or it becomes necessary to kill this session.  after that you are attempting to run the index rebuild again and is throwing the error : ORA-08104: this index object ##### is being online built or rebuilt
cause : A session failure during an online index rebuild can leave the data dictionary in a state reflecting a Rebuild is ongoing when in fact it is not.
solution :
Run dbms_repair.online_index_clean for fix this issue
declare
var1 BOOLEAN;
begin
var1 := dbms_repair.online_index_clean(#####);
end;
/

now you can run again online rebuild of index.

ORA-01502: index or partition of such index is in unusable state

Hello,
here I'll show you how to find and rebuild unusable indexes partition in oracle:

SQL> select distinct status from dba_ind_partitions;
STATUS
——–
USABLE
UNUSABLE
SQL>select count(1) from dba_ind_partitions where status='UNUSABLE';
  COUNT(1)
———-
         2

we have two unusable indexes, get code for rebuild this indexes

SQL>select  ' alter index '||index_owner||'.'||index_name||' rebuild partition '||
partition_name||';' from dba_ind_partitions where status='UNUSABLE';

and execute that code

SQL>alter index IKA.TEST_TBL_idx rebuild partition P_2014_02;
Index altered.
alter index IKA.TEST_TBL_idx rebuild partition P_2014_03;
Index altered.

check that we have all indexes usable state

SQL>select distinct status from dba_ind_partitions;
STATUS
——–
USABLE

that's all.

ORA-29857: domain indexes and/or secondary objects exist in the tablespace

While dropping a tablespace it fails with error ORA-29857 like below.

SQL> drop tablespace example including contents and datafiles cascade constraints;
drop tablespace example including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace

The problem arises because there was an attempt to drop a tablespace which contains secondary objects and/or domain indexes.

Solution:

Drop the domain indexes on the tablespace. Also, find the domain indexes which created secondary objects in this tablespace and drop them. Then try dropping the tablespace.

SQL> select index_name from dba_indexes where index_type = 'DOMAIN' 
and tablespace_name = 'EXAMPLE';
no rows selected

which means it does not have any domain index. Next you should look for secondary objects.

select distinct owner from dba_tables where tablespace_name='EXAMPLE';
OWNER
------------------------------
HR
OE
PM
SH
IX

If you think you don't need these users you can try to delete users

SQL> drop user HR cascade;
User dropped.

SQL> drop user OE cascade;
User dropped.

SQL> drop user sh cascade;
User dropped.

SQL> drop user oe cascade;
User dropped.

SQL> drop user ix cascade;
User dropped.

after that we can delete tablespace

SQL> drop tablespace example including contents and datafiles;