I have something like this:
SELECT
cansa1.NAME,
mod(cansa1.PRODUCT_ID, 1000000) prodIdHash
FROM CANSA_TABLE cansa1
INNER JOIN CUSER_TABLE cuser1 ON cansa1.PRODUCT_ID = cuser1.PRODUCT_ID
AND mod(cansa1.PRODUCT_ID, 1000000) = cuser1.PRODUCT_HASH
This query is working, but I want replace the second occurrence (in the inner join) of the mod() function, to avoid execute it two times. I tried replace it by the alias in the select clause but not works. Any idea of that I can use to make this query don't repeat the mod() function?
Sorry by my english
Don't worry about executing it twice, the SQL engine will optimize the query and will decide whether the function value is cached or it executes twice and can end up re-writing the query so that what is executed has a different structure than the written query because it has determined that it would be more efficient.
If you really want to try to rewrite it then:
SELECT c.NAME,
c.prodIdHash
FROM (
SELECT name,
mod(PRODUCT_ID, 1000000) As prodIdHash
FROM CANSA_TABLE
) c
INNER JOIN CUSER_TABLE u
ON ( c.PRODUCT_ID = u.PRODUCT_ID
AND c.prodIdHash = u.PRODUCT_HASH )
However, the SQL engine may rewrite the query and push the function to the outer scope so you may need a seemingly irrelevant filter condition to materialize the inner query and force the calculation not to be rewritten:
SELECT c.NAME,
c.prodIdHash
FROM (
SELECT name,
mod(PRODUCT_ID, 1000000) As prodIdHash
FROM CANSA_TABLE
WHERE ROWNUM > 0
) c
INNER JOIN CUSER_TABLE u
ON ( c.PRODUCT_ID = u.PRODUCT_ID
AND c.prodIdHash = u.PRODUCT_HASH )
However, this really seems like a case of premature optimisation. You should check if there is actually a problem first before you try and apply an optimisation that probably is not needed.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments