Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

Accepted Solutions
Highlighted

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

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.

2 REPLIES 2
Highlighted

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

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.

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

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-...

Don't have an account?
Coming from Hortonworks? Activate your account here