Leon wrote:
> Why? There will be no such field as "record number", the only
> place where it can exist is the field which references another
> table. I can quite share your feeling about wrongness of
> physical-oriented things in abstract tables, but don't
> plain old indices deal with physical record numbers? We could
> do the same - hide the value stored in such field and only
> offer the user ability to use it in queries without knowing
> the value.
Leon,
In my understanding, pointer based approaches like you
are recommending have been implemented in several prototype
objected oriented databases. They have been shown to be
orders of magnitude slower than set oriented techniques,thus
many OO databases are implemented as wrappers over
relational systems!
In general, the best way to handle stuff like this for reports
is to cashe small tables which are joined (like product lookups)
in memory to make the queries run much faster. To do this,
your design has to be smart, by seperating those tuples which
are "active" products from those "inactive" products so that
the database can cashe the active records and not the inactive
records. Perhaps something like:
1. CREATE VIEW PRODUCT AS ( SELECT * FROM PRODUCT_ACTIVE_CASHED
UNION ALL SELECT * FROM PRODUCT_INACTIVE);
2. SELECT ORDER_NO, PRODUCT_NAME FROM ORDER_LINE, PRODUCT WHERE
PRODUCT.PRODUCT = ORDER_LINE.PRODUCT and ORDER_LINE.ORDER = 120;
Would be a general like solution, where orders with active
products are brought up quickly since the join is done
in memory, but orders with inactive products take much
longer, since the query on the active table is a cashe
miss, leaving a disk access on the inactive table.
Perhaps there are several other nicer ways do to this, from my
understanding a HASH based cashe could allow frequently accesed
tuples to be cahsed in memory? ... anyway, I'm no expert.
A more traditional method (which I use all the time), is to
have canned reports that are pre-generated using common
conditions. These are then saved on a web server and
updated daily. It is a bit less accurate, but often for 99%
of the purposes, day old information is just fine....
Hope this helps!
;) Clark