Can that data be
dynamically derived from the large, static tables on demand? Of course. But if the users frequently
request it, and the data is largely unchanging, then it makes sense to periodically store that data in
the format in which the users will ask for it.
For example, some applications store historical data and current data in the same table. Each
row may have a timestamp column, so the current row in a set is the one with the most recent
timestamp. Every time a user queries the table for a current row, the user will need to perform a
subquery, such as the following:
where timestamp_col =
(select max(timestamp_col)
from table
where emp_no=196811)
If two such tables are joined, there will be two subqueries. In a small database, this may not
present a performance problem, but as the number of tables and rows increase, performance
problems will follow. Partitioning the historical data away from the current data or storing the
historical data in a separate table will involve more work for the DBAs and developers but should
improve the long-term performance of the application.
User-centered table design, rather than theory-centered table design, will yield a system that
better meets the users??™ requirements; this is not to say that you should not design the database
244 Oracle Database 11g DBA Handbook
using 3NF and 4NF methodologies: it??™s a good starting point for revealing business requirements
and a prerequisite for the physical database design.
Pages:
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407