Foreign Generated Column

• Main Authors: Mang-rong Ho, 何孟蓉
• Other Authors: Jieh Hsuang
• Format: Others
• Language: en_US
• Published: 2001
• Online Access: http://ndltd.ncl.edu.tw/handle/56356772490927873118

碩士 === 國立臺灣大學 === 資訊工程學研究所 === 89 === A foreign generated column (FGC) is a derived column maintained by system through the user-defined or system-defined expression or sub-query in a normal table. It extends generated column (GC) to reference columns in other tables that makes it ever more helpful and powerful. In addition to all the advantages of GCs, FGCs reduce join operators. It is a simple data warehouse tool to keep data consistency compared with triggers and materialized views. FGCs cache important or frequently-used derived data and avoid repeating complicated computations. The SQL compiler saves CPU time at the query time because of pre-computing and storing values of FGCs defined by aggregate functions and join operators. An FGC must be defined by a scalar sub-query which outputs a single value for each row of an FGC. It is checked at run time. A typical scalar sub-query is a query defined the RI constraint or aggregate functions. Users can query the values of FGCs and GCs directly and the DBMS can rewrite user queries with the values of FGCs (GCs). Furthermore, indexing on FGCs or GCs is indirectly indexing on expressions. It takes advantage of indexes to save CPU time and disk I/O and the SQL compiler only retrieves required disk pages. This thesis focuses on the techniques of creating, maintaining FGCs and rewriting user queries. All implementations are based on IBM DB2 Relational DBMS.