create table t1 as select * from all_objects;--47222

create table t2 as select * from all_objects;--47223

ALTER table t1 add constraint idx_t1_pk primary key (object_id);

ALTER table t2 add constraint idx_t2_pk primary key (object_id);

create index idx_t1_type on t1(object_type);

select * from t1,T2 where T1.object_type='INDEX' and t1.object_id=t2.object_id;

SQL> select * from t1,T2 where T1.object_type='INDEX' and t1.object_id=t2.object_id;

1700 rows selected.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 1256071923

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  1700 |   425K|   231   (2)| 00:00:03 |
|*  1 |  HASH JOIN                   |             |  1700 |   425K|   231   (2)| 00:00:03 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1          |  1700 |   212K|    80   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T1_TYPE |  1700 |       |     6   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | T2          | 49217 |  6152K|   150   (2)| 00:00:02 |
--------------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - access("T1"."OBJECT_TYPE"='INDEX')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        881  consistent gets
          0  physical reads
          0  redo size
     147164  bytes sent via SQL*Net to client
       1735  bytes received via SQL*Net from client
        115  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1700  rows processed