SQL & PL/SQL :: Deducing Rows

drop table test;
create table test
(
my_env varchar2(8) ,
id int
)
/
alter table test add constraint pk_test primary key (my_env, id);
insert into test values ('A', 1);
insert into test values ('A', 2);

[Code]...

These rows are valued :

drop table valued_test;
create table valued_test
(
my_env varchar2(8) ,
id int ,
val int
)
/

[Code].....

Now the rows of the table test can move from one situation to another. To model the movement from one situation to another, there is another table test_history

drop table test_history;
create table test_history
(
my_env varchar2(8) ,
id int ,
my_env_new varchar2(8) ,
id_new int
)
/

[Code]...

So the row (A,1) moved to (A1,1). What I want to do is to replicate the valued rows with(A,1) to (A1,1).

if we issue select * from <some_query> we will get this :

MY_ENV ID VAL
-------- ---------- ----------
A 1 100
A 3 200
B 1 100
A1 1 100

We can issue this query to get it :

select my_env, id, val
from valued_test
union
select my_env_new my_env, id_new, val
from valued_test, test_history

[Code]...

MY_ENV ID VAL
-------- ---------- ----------
A 1 100
A 3 200
A1 1 100
B 1 100

It works fine, but when we have lot of data in test and valued_test, these query becomes very slow. I think it is because of the union. So here are my questions :

1) could we remove the union
2) What columns in test, valued_test, test_history should we index to make the result be returned faster.