Mega Code Archive
Show parameter optimizer_index
<![CDATA[
SQL> create table clustered ( x int, data char(255) );
Table created.
SQL>
SQL> insert /*+ append */ into clustered (x, data)
2 select rownum, dbms_random.random from all_objects;
12652 rows created.
SQL>
SQL> alter table clustered
2 add constraint clustered_pk primary key (x);
Table altered.
SQL>
SQL> analyze table clustered compute statistics;
Table analyzed.
SQL>
SQL> create table non_clustered ( x int, data char(255) );
Table created.
SQL>
SQL> insert /*+ append */ into non_clustered (x, data)
2 select x, data from clustered ORDER BY data;
12652 rows created.
SQL>
SQL> alter table non_clustered
2 add constraint non_clustered_pk primary key (x);
Table altered.
SQL>
SQL> analyze table non_clustered compute statistics;
Table analyzed.
SQL>
SQL> select index_name, clustering_factor
2 from user_indexes
3 where index_name like '%CLUSTERED_PK';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
CLUSTERED_PK 469
NON_CLUSTERED_PK 12626
SQL>
SQL> show parameter optimizer_index
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
SQL> set autotrace traceonly explain
SQL> select * from clustered where x between 50 and 2750;
Execution Plan
----------------------------------------------------------
Plan hash value: 1763666373
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 2702 | 683K| 108 (0)
| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| CLUSTERED | 2702 | 683K| 108 (0)
| 00:00:02 |
|* 2 | INDEX RANGE SCAN | CLUSTERED_PK | 2702 | | 7 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X">=50 AND "X"<=2750)
SQL> select * from non_clustered where x between 50 and 2750;
Execution Plan
----------------------------------------------------------
Plan hash value: 681052411
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 2702 | 683K| 130 (1)| 00:00:0
2 |
|* 1 | TABLE ACCESS FULL| NON_CLUSTERED | 2702 | 683K| 130 (1)| 00:00:0
2 |
--------------------------------------------------------------------------------
---
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"<=2750 AND "X">=50)
SQL> set autotrace off
SQL>
SQL> drop table clustered;
Table dropped.
SQL> drop table non_clustered;
Table dropped.
SQL>
]]>