Проверка будет ли использоваться индекс, без создания

1) Разные индексы на одинаковых данных. Влияние распределения данных.
2) Виртуальный индекс.

1)
clear;

drop table t1;
drop table t2;
drop table src;

create table t1(
id  number,
pok number,
val number
);

create table t2(
id  number,
pok number,
val number
);

create table src(
id  number,
pok number,
val number
);

insert into src
select rownum as id,
       round(dbms_random.value(1,100))    as pok,
       round(dbms_random.value(1,100000)) as val
from dual
connect by rownum <=100000;

commit;

insert into t1
select * from src order by pok;

insert into t2
select * from src order by dbms_random.value(0,1);

commit;

create index it1 on t1(pok);

create index it2 on t2(pok);

begin
 dbms_stats.gather_index_stats(user,indname => 'IT1');
 dbms_stats.gather_index_stats(user,indname => 'IT2');
end;
/

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

select *

from

user_indexes i

where i.INDEX_NAME in ('IT1','IT2')

INDEX_NAME AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR

IT1        2                       295

IT2        240                     24047

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

Разный план, на одинаковых таблицах и полностью одинаковых данных, толкьо по разному распределенных.

select sum(1),sum(val) from t1 where t1.pok=5    Index range scan

select sum(1),sum(val) from t2 where t2.pok=5    Table full scan

2) Удалчяем индекс, смотрим план, создаём вирутальный индекс, смотирм план.

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

clear;

set autotrace on explain; 

set serveroutput on;

drop index it1;

EXPLAIN PLAN FOR (select sum(1),sum(val) from t1 where t1.pok=5);

SELECT * FROM table(dbms_xplan.display);

create index it1 on t1(pok) nosegment;

alter session set "_use_nosegment_indexes" = true; 

EXPLAIN PLAN FOR (select sum(1),sum(val) from t1 where t1.pok=5);

SELECT * FROM table(dbms_xplan.display);

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

Вывод:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
Connected as dbadmin@NSSER_HARD

Cannot SET AUTOTRACE

Index dropped

Explained

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |    13   (8)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  1556 | 40456 |    13   (8)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."POK"=5)
Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected

Index created

Session altered

Explained

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3461075613
--------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    26 |     5   (0)| 00:00
|   1 |  SORT AGGREGATE              |      |     1 |    26 |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |  1556 | 40456 |     5   (0)| 00:00
|*  3 |    INDEX RANGE SCAN          | IT1  |   441 |       |     1   (0)| 00:00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."POK"=5)
Note
-----
   - dynamic sampling used for this statement (level=2)