Support Questions
Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

Is it possible in Phoenix to create a functional index on an UDF that uses as arguments columns from two different tables?

New Contributor

We need to execute geo-spatial queries where we use geo-spatial functions that operate on columns from two different tables (YELLOW_TAXI_TRIPS, TAXI_ZONES). The UDF works as a comparison to join the two tables, below is the query in which we want to improve performance applying a functional index.

SELECT ZONES.zone, count(1) AS hits
FROM YELLOW_TAXI_TRIPS AS TRIPS, TAXI_ZONES AS ZONES
WHERE STWithinFromText('POINT('||TRIPS.PICKUP_LONGITUDE||' '||TRIPS.PICKUP_LATITUDE||')', ZONES.geom)
GROUP BY ZONES.zone;
1 ACCEPTED SOLUTION

Super Collaborator

No, it's not possible. Index may have only one parent table. You need to keep in mind that indexes are updated when you update the parent table. Let's imagine that you have more than one parent table. Any update only one of them would mean that you have to make a lookup for all corresponding records in other parents to update index data. That would be too expensive for simple operation as upsert is.

View solution in original post

2 REPLIES 2

Super Collaborator

No, it's not possible. Index may have only one parent table. You need to keep in mind that indexes are updated when you update the parent table. Let's imagine that you have more than one parent table. Any update only one of them would mean that you have to make a lookup for all corresponding records in other parents to update index data. That would be too expensive for simple operation as upsert is.

New Contributor

Thanks for your reply!

The question was created twice without knowing it. Sorry!

https://community.hortonworks.com/questions/149484/is-it-possible-in-phoenix-to-create-a-functional-...