How to increase performance in sql query?

Question

I'm using oracle sql. I have a query:

query1

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

It actually works. Now I have to select n-level parent of t1.object1.

Its my query 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 works too, if I write t1.object_id value manually. The problem is, if I write some numbers instead of query3, it would work around 100 times faster. IMO it happens because query executes each time for each object.

Now I need to make one big query with good performance. How can I do it?

First of all I need to increase performance of query2. I want query3 not to execute many times. I can bulk collect it into some variable, but I want to use as many pl/sql as possible. So I think about with with - as syntax.

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 --; 

Output for this query is exeption. How can I do it without exception and as fast as possible?

And how to select parent types in query1? Do I have to write an hierarchical query in where clause?

Answer 1

You don't want to do a nested query if you are looking for speed. Try putting each data set that exists in the nested queries into a temp table and joining them. Then, you only pull the columns you need and the smaller dataset, and run it one time. You are right, the nested query will run over and over, so if you have a lot of data it is inefficient.

select col1, col2 into #query3
from table1

then,

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

I decided to make 2 queries in my function. First is query1, I have not changed it. Then I realised that I have a loop for my output array from query1, so I wrote

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;

I think, I had problems in my query3. I wrote new query and it works good. Now I have pretty good performance. Thank you!