SQL & PL/SQL

Hi all,

I want to fetch records from mview m_view1 joining the columns in table1 and table2.

select mv1.col1,mv2.col2.., from mview1 mv1
where mv1.col1 in (
SELECT t1.col1
FROM table1 t1
WHERE t1.col2 = SUBSTR(user,3)
)
UNION ALL
(
SELECT t2.col1
FROM table2 t2
WHERE t2.col2 = SUBSTR(user,3)
)
UNION ALL
(
SELECT t1.col1
FROM table1 t1, table2 t2
WHERE t2.col2 = SUBSTR(user,3)
AND t2.col1 = t1.col2
);

Below is the explain plan I got :

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 616 | 2690 (1)| 00:00:33 |
| 1 | NESTED LOOPS | | 1 | 616 | 2690 (1)| 00:00:33 |
| 2 | VIEW | VW_NSO_1 | 2657 | 34541 | 30 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 2657 | 61164 | 30 (90)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
|* 5 | INDEX RANGE SCAN | table1_indx1 | 176 | 1760 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | table2_prim1 | 14 | 196 | 2 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 2467 | 59208 | 25 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | table2_prim1 | 14 | 196 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | table1_indx2 | 176 | 1760 | 2 (0)| 00:00:01 |
|* 10 | MAT_VIEW ACCESS BY INDEX ROWID| mview1 | 1 | 603 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | indx1 | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

table1_indx2 - index on col1 & col2 of table1.
table1_indx1 - index on col1 & col2 & col3 of table1.
Im new to oracle tuning. Could you please help me in tuning this.

Thanks in advance.