Re: Query questions

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