I have some hierarchical data I'd like to look at as if it were a tree. The core of the query looks like following.
SELECT CONNECT_BY_ROOT output_unit_id AS START_UNIT_ID,
units.*,
FROM units
CONNECT BY PRIOR input_unit_id = output_unit_id
If I make it into a view, I can then do stuff like following.
select * from vw_unit_chain
where START_UNIT_ID = 123456
(It gives me all the units up the transformation chain which were used for given one)
But if I instead use the "start with"-statement, like
SELECT CONNECT_BY_ROOT output_unit_id AS START_UNIT_ID,
input_units.*,
FROM input_units
CONNECT BY PRIOR input_unit_id = output_unit_id
start with output_unit_id = 123456
the query takes only half as much time.
Is there anything I can do to get both the performance and the query being a view?
There isnt a way I know of implicitly. Depending on the level the control you have in your application, you could be perhaps use a context do it. For example, your view could be:
SELECT CONNECT_BY_ROOT output_unit_id AS START_UNIT_ID,
units.*,
FROM units
CONNECT BY PRIOR input_unit_id = output_unit_id
start with output_unit_id = sys_context('MY_CTX','UNIT_ID')
and your application sets the context variable before accessing the view.
Hope this helps.