How to improve performance in sql query?

Question

I am using oracle sql. I have a request:

Query1

    select t1.object_id object1, t2.object_id ... --etc--
from objects t1, objects t2, object_types t3 ... --etc--
where ... --many conditions--

It really works. Now I need to select the n-level parent for t1.object1.

Its my request for this:

Query2:

 select object_id
          from objects 
              where object_type_id in
            ( --query3-- ) 
and rownum = 1
            connect by prior parent_id = object_id
          start with object_id= -- t1.object_id value --;

It also works if I write the t1.object_id value manually. The problem is that if I write a few numbers instead of query3, it will work about 100 times faster. IMO this is because the request is executed every time for each object.

Now I need to make one big request with good performance. How can i do this?

query2. , query3 . bulk collect , pl/sql. .

with types as (--query3--)
select object_id
              from objects 
                  where object_type_id in
                (types) 
    and rownum = 1
                connect by prior parent_id = object_id
              start with object_id= -- t1.object_id value --; 

- exeption. ?

query1? where where?

Answer 1

, . , , temp . . , , , , .

select col1, col2 into #query3
from table1

select object_id
          from objects o 
          join #query3 q on o.field=q.field
              where object_type_id = valuefromquery3
and rownum = 1
            connect by prior parent_id = object_id
          start with object_id= -- t1.object_id value --;

Answer 2

2 . 1, . , query1,

select object_id bulk collect into some variable
          from objects 
              where object_type_id in
            ( --new query3-- ) 
and rownum = 1
            connect by prior parent_id = object_id
          start with object_id= variable;

, 3. , . . !