SQL>
SQL> create table t
2 as
3 select * from all_objects;
Table created.
SQL>
SQL> create index t_idx1 on t(object_id);
Index created.
SQL>
SQL> create index t_idx2 on t(owner,object_type);
Index created.
SQL>
SQL> analyze table t
2 compute statistics
3 for table
4 for all indexes
5 for all indexed columns;
Table analyzed.
SQL> set autotrace traceonly explain
SQL> select object_id, owner, object_type
2 from t
3 where object_id between 100 and 2000
4 and owner = 'SYS'
5 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 938 | 66598 | 25 |
|* 1 | TABLE ACCESS FULL| T | 938 | 66598 | 25 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000 AND "OWNER"='SYS')
Note
-----
- cpu costing is off (consider enabling it)
SQL>
SQL> set autotrace traceonly
SQL> select object_id, owner, object_type
2 from t
3 where object_id between 100 and 2000
4 and owner = 'SYS'
5 /
1250 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 938 | 66598 | 25 |
|* 1 | TABLE ACCESS FULL| T | 938 | 66598 | 25 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000 AND "OWNER"='SYS')
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
246 consistent gets
0 physical reads
0 redo size
25173 bytes sent via SQL*Net to client
1293 bytes received via SQL*Net from client
85 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1250 rows processed
SQL>
SQL> select /*+ index( t t_idx1 ) */ object_id, owner, object_type
2 from t
3 where object_id between 100 and 2000
4 and owner = 'SYS'
5 /
1250 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4055207394
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 938 | 66598 | 32 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 938 | 66598 | 32 |
|* 2 | INDEX RANGE SCAN | T_IDX1 | 1810 | | 5 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=2000)
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
195 consistent gets
0 physical reads
0 redo size
25173 bytes sent via SQL*Net to client
1293 bytes received via SQL*Net from client
85 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1250 rows processed
SQL>
SQL> select /*+ index( t t_idx2 ) */ object_id, owner, object_type
2 from t
3 where object_id between 100 and 2000
4 and owner = 'SYS'
5 /
1250 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3371054274
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 938 | 66598 | 522 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 938 | 66598 | 522 |
|* 2 | INDEX RANGE SCAN | T_IDX2 | 6520 | | 23 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000)
2 - access("OWNER"='SYS')
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
670 consistent gets
0 physical reads
0 redo size
22392 bytes sent via SQL*Net to client
1293 bytes received via SQL*Net from client
85 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1250 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.