Thank you very much for your response! It leads to another couple of
questions:
I'm building a database containing key parameters for ~500,000 data
files. The design I found logical is
Two tables for each file:
1) Larger table with detailed key parameters
(10-15 columns, ~1000 rows), call it large_table
2) Small table with file summary
(~30 columns, 1 row), call it small_table
you want to create 1 million tables, all with one of
2 schemas?
I started out with a schema for each file, thinking I could utilize the
schema
structure in queries, but I don't see how. Schemas are useful for grouping
tables according to users/owners. Other than that, do they add anything
but a dot in the table name?
why not just 2 tables, each with the additional "file"
column ?
...
SELECT <large_table columns> FROM <regular expression>
WHERE <condition on large_table>
IF <condition on corresponding small_table>;
this would then be something like:
SELECT <large_table columns> FROM large_table
WHERE file ~ <regular expression>
AND <condition on large_table>
AND <subquery involving small_table>
The large_table would have ~500 million rows, each of which would have
to be checked for the first condition (pattern matching above). With
separate
tables there are "only" ~500,000 initial checks to do.
Also, I don't see how to construct that small_table-subquery. If it is
possible
I would love to know how! Can you (or anybody else) give an example?
Thank you,
Poul
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
