Clever Geek Handbook

How to simplify this Sql query

Table - The request has 2 columns (functionId, depFunctionId)

I want all values ​​to be either in functionid or depfunctionid

I am using this:

select distinct depfunctionid from Query
union 
select distinct functionid from Query

How to do it better?

+1


source to share


4 answers


I think the best you get.



+6


source


This is as good as it sounds ...



+3


source


Lose the DISTINCT clauses, as your UNION (vs UNION ALL) will take care of removing duplicates.

An alternative - but perhaps less clear and probably with the same execution plan - would be to have a FULL JOIN across 2 columns.

SELECT 
    COALESCE(Query1.FunctionId, Query2.DepFunctionId) as FunctionId
FROM Query as Query1
FULL OUTER JOIN Query as Query2 ON
    Query1.FunctionId = Query2.DepFunctionId
+2


source


I'm pretty sure you can lose the distinction. When you use UNION instead of UNION ALL, duplicate results are removed.

It all depends on how heavy your inline query is. The key to better performance would be to execute only once, but that's not possible given the data it returns.

If you do it like this:

select depfunctionid , functionid from Query
group by depfunctionid , functionid

It is very likely that you will get duplicate results for depfunctionid or functionid.

I may be wrong, but it seems to me that you are trying to get a dependency tree. If so, I will personally try to use the materialized path.

If the materialized path is stored in the self-referencing table name, I would retrieve the tree using something like

select asrt2.function_id
from   a_self_referencig_table asrt1,
       a_self_referencig_table asrt2
where  asrt1.function_name = 'blah function'
and    asrt2.materialized_path like (asrt1.materialized_path || '%')
order  by asrt2.materialized_path, asrt2.some_child_node_ordering_column

This will restore the entire tree to the correct order. It's worth building a materialized path based on function_id and parent_function_id (or in your case, functionid and depfunctionid), but a trigger can take care of that pretty easily.

0


source






More articles:


All Articles