Revision 8317
Added by Aaron Marcuse-Kubitza over 11 years ago
| inputs/FIA/occurrence_all/import | ||
|---|---|---|
| 7 | 7 |
DROP VIEW IF EXISTS occurrence_all; |
| 8 | 8 |
CREATE OR REPLACE VIEW occurrence_all AS |
| 9 | 9 |
SELECT * |
| 10 |
FROM "REF_RESEARCH_STATION" |
|
| 11 |
NATURAL JOIN "REF_UNIT" |
|
| 12 |
NATURAL JOIN "COUNTY" |
|
| 13 |
NATURAL JOIN "PLOT" |
|
| 14 |
NATURAL JOIN "COND" |
|
| 15 |
NATURAL LEFT JOIN "REF_HABTYP_DESCRIPTION" |
|
| 16 |
NATURAL JOIN "SUBPLOT" |
|
| 17 |
NATURAL JOIN "TREE" |
|
| 18 |
NATURAL LEFT JOIN "REF_SPECIES" |
|
| 19 |
NATURAL LEFT JOIN "REF_PLANT_DICTIONARY" |
|
| 10 |
/* directional joins ensure that the PostgreSQL query planner always joins |
|
| 11 |
starting with the TREE table */ |
|
| 12 |
FROM ("REF_RESEARCH_STATION"
|
|
| 13 |
NATURAL RIGHT JOIN ("REF_UNIT"
|
|
| 14 |
NATURAL RIGHT JOIN ("COUNTY"
|
|
| 15 |
NATURAL RIGHT JOIN ("PLOT"
|
|
| 16 |
NATURAL RIGHT JOIN ("COND"
|
|
| 17 |
NATURAL LEFT JOIN "REF_HABTYP_DESCRIPTION" |
|
| 18 |
NATURAL RIGHT JOIN ("SUBPLOT"
|
|
| 19 |
NATURAL RIGHT JOIN ("TREE"
|
|
| 20 |
NATURAL LEFT JOIN "REF_SPECIES" |
|
| 21 |
NATURAL LEFT JOIN "REF_PLANT_DICTIONARY" |
|
| 22 |
))))))) |
|
| 20 | 23 |
EOF |
| 21 | 24 |
|
| 22 | 25 |
remake_VegBIEN_mappings |
Also available in: Unified diff
inputs/FIA/occurrence_all/import: Use directional joins (LEFT/RIGHT JOIN) instead of inner joins to ensure that the PostgreSQL query planner always joins starting with the TREE table. Note that the directional joins are now needed for a different reason than when they were initially added, which had been to avoid slow sorts. The sorts (at least for LIMIT-only queries) went away when small tables such as COUNTY and REF_UNIT were added to the joins.