Oracle : Verify index access without creating a real index

Index creation is a resource consuming operation. Creating index on a very big table is waste of resource and time, if it finally appears to be not used by query.

There is a simple way to understand if an index can be useful before creating the real index.

[ SYS @ DDB ] create table test.t_obj as select object_id,object_name from dba_objects;

Table created.

[ SYS @ ddb ] exec dbms_stats.gather_table_stats('TEST','T_OBJ');

PL/SQL procedure successfully completed.

[ SYS @ ddb ] explain plan for select * from test.t_obj where object_id=100;

Explained.

[ SYS @ ddb ] select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------

Plan hash value: 172510092

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    29 |   105   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_OBJ |     1 |    29 |   105   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

13 rows selected.

[ SYS @ ddb ] create index test.t_idx on test.t_obj(object_id) nosegment;

Index created.

[ SYS @ ddb ] select owner,table_name,index_name from dba_indexes where table_name='T_OBJ' and owner='TEST';

no rows selected

[ SYS @ ddb ] explain plan for select * from test.t_obj where object_id=100;

Explained.

[ SYS @ ddb ] select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------

Plan hash value: 172510092

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    29 |   105   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_OBJ |     1 |    29 |   105   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

13 rows selected.

[ SYS @ ddb ] alter session set "_use_nosegment_indexes"=true;

Session altered.

[ SYS @ ddb ] explain plan for select * from test.t_obj where object_id=100;

Explained.

[ SYS @ ddb ] select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------

Plan hash value: 1850536683

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    29 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ |     1 |    29 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)

14 rows selected.

[ SYS @ ddb ]

But the execution plan above shows the index can be used if we create it. The nosgment index is not a real index.

Now as we undersrtood index can be used, we can create a real index using the below statement without "nosegment" key word.


[ SYS @ ddb ] alter session set "_use_nosegment_indexes"=false;

Session altered.

[ SYS @ ddb ] drop index test.t_idx;

Index dropped.

[ SYS @ ddb ] create index test.t_idx on test.t_obj(object_id);

Index created.

[ SYS @ ddb ] select owner,table_name,index_name from dba_indexes where table_name='T_OBJ' and owner='TEST';

OWNER                          TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------ ------------------------------
TEST                           T_OBJ                          T_IDX

[ SYS @ ddb ] explain plan for select * from test.t_obj where object_id=100;

Explained.

[ SYS @ ddb ] select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------

Plan hash value: 1850536683

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    29 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ |     1 |    29 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)

14 rows selected.


[ SYS @ ddb ]

Oracle : Archived log block size

Let us keep in mind that block size of archived log files are not same as database block size i.e. db_block_size. 

If we calculate the size of archived log as "v$archived_log.blocks * < value of db_block_size parameter>" that might not be correct. 

The block_size column of v$archived_log view shows the logical block size of archived log. 

We should calculate size of the archived log as below, if we are not checking the size of file using OS commands. 

select thread#,sequence#,name,blocks*block_size/1024 size_kb
from v$archived_log
order by sequence#
/

There are situations where we have to calculate archived log size using programs.The above query might be helpful on those situations.