SQL> explain plan for
2 select a.startdate,b.subsid from tab_1 a,tab_2 b where
3 a.servid='025001003681' and a.status!='C' and a.mid=b.mid;
Explained.
Elapsed: 00:00:00.03
SQL> select * from table(dbms_xplan.display)
SQL> /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 369 | 23985 | 980 | | |
| 1 | NESTED LOOPS | | 369 | 23985 | 980 | | |
| 2 | PARTITION HASH ALL | | | | | 1 | 4 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| tab_1 | 369 | 14022 | 242 | 1 | 4 |
|* 4 | INDEX RANGE SCAN | IDX_tab_1_SERVID | 492 | | 10 | 1 | 4 |
| 5 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| tab_2 | 1 | 27 | 2 | KEY | KEY |
|* 7 | INDEX UNIQUE SCAN | PK_tab_2_MID | 1 | | 1 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."STATUS"<>'C')
4 - access("A"."SERVID"='025001003681')
7 - access("A"."MID"="B"."MID")
Note: cpu costing is off
22 rows selected.
Elapsed: 00:00:00.56