Here is the problem:
We represent a hierarchy of related record with a two column DB table called dataRecordRef. The columns are recordId and parentRecordId. A child record can have more than one parent record, so in this hierarchical representation there can be more than one path to the root for any given record.
We need a fast way given a set of record id's, what all it's descendant records are...namely a list of all the given records paths to the bottom of the tree. We have coded a solution as a single query that works, but we are unsatisfied with its performance.
We are fine if the solution is a single query like we coded, or if its a stored procedure.
We will pay more for more speedup over our current solution. In other words if you are 2x faster you can get more than if you are [url removed, login to view] faster. We would really like to see 10x performance as that is the performance we get with a MySQL stored procedure and we feel like we should be able to at least equal that on Oracle.
We include the following files:
* the dataRecordRef table as a CSV file you can import for testing
* the example query that we created that works but is too slow
* the input record ids as a csv that you will take as inputs to compute their ancestors against the provided dataRecordRef table
We will pay $100 for each doubling of performance up to 10x, which would be $1,000 award.