16. Common table expressions

Quite a useful operator is WITH that let’s you name a subquery result for later use in your main query. Thus the queries are much easier to understand.

A second feature of it is that it enables you to force the database query planner to process parts of your query in the order you defined.

Each ADQL query will be translated in a sequence of steps the database will process in order to perform the whole query. This query plan may switch the order of steps which were defined in the scripts to enhance the performance. Sometimes a query planner gets a hickup and guesses wrong and an ADQL query that seems pretty simple may take much longer than expected. In this case, you can use common table expressions to force the query planner to perform parts of your query as a block. In complex queries, this might gain you some performance.

WITH ahip AS
(SELECT AVG(parallax) AS parav FROM arihip.main
WHERE DISTANCE (POINT  (56.75, 24.11666),
POINT (raj2000, dej2000)) < 10)
SELECT arihip.main.*, parav FROM arihip.main
JOIN ahip ON parallax < parav


Hendrik Heinl

Copyright Notice