Help with a connect by prior query

This query only uses a single full table scan, or can use index range scans if there are indexes.

select distinct order_idfrom greg_teststart with order_id = :order_idconnect by nocycle pallet_id = prior pallet_id or order_id = prior order_id;

If you're on 11gR2, this will run a little faster than the above connect by query, although the syntax is weirder IMO.

with orders(order_id, pallet_id) as(    select order_id, pallet_id    from greg_test    where order_id = :order_id    union all    select greg_test.order_id, greg_test.pallet_id    from greg_test    inner join orders        on greg_test.pallet_id = orders.pallet_id            or greg_test.pallet_id = orders.pallet_id) cycle order_id, pallet_id set is_cycle to 'Y' default 'N'select distinct order_id from orders;

If you have large amounts of data you'll want to thoroughly test whichever solution you use. Hierarchical queries often have serious performance problems.

-- Z lists all order pairs that share a pallet, and also pairs each order with itselfWITH pairs AS (    -- all pairs of orders on the same pallet    SELECT DISTINCT a.order_id a, b.order_id b FROM greg_test a, greg_test b     WHERE a.pallet_id = b.pallet_id AND a.order_id != b.order_id  UNION ALL     -- pair all orders with themselves    SELECT DISTINCT order_id a, order_id b FROM greg_test)-- Now connect all the pairsSELECT DISTINCT a FROM pairs CONNECT BY NOCYCLE PRIOR a = b START WITH a = :order_id

Probably there is a more efficient solution.