Star Schema: Indexing ProblemPeriod

Star Schema: Indexing Problem
Period dimension has a compound key, a hierarchy of day, week, month, quarter, year.
Problems with compound key:
It requires multiple metadata definitions
Fact table must carry all key components
The size of the index increases, performance decreases
Alternatives:
Concatenate the keys into a single key (solve the first two problems)
Use artificial (generated) key. Move the compound key into non-key attributes (The Best Approach)

Star Schema: Level Indicator Problem
The dimensional table design often includes a level of hierarchy indicator for every record (whether the record stores details or aggregates).
The best alternative to using the level indicator is the snowflake schema. In this schema, aggregate fact tables are created separately from detail fact tables. In addition to the main fact tables, the snowflake schema contains separate fact tables for each level of aggregation.

STARjoin and STARindex
STARjoin is a high-speed, single-pass, parallelizable multitable join, invented by Red Brick. Red Brick’s DBMS can join more than two tables in a single operation.
The core technology in STARjoin is an innovative approach to indexing.
Red Brick’s RDBMS supports the creation of specialized indexes, called STARindexes, to dramatically accelerate join performance.

STARindex: The Concept
STARindexes are created on one or more foreign key columns of a fact table.
Unlike traditional indexes that contain information to translate a column value to a list of rows with that value, a STARindex contains highly compressed information that relates the dimensions of a fact table to the rows that contain those dimensions

STARindex vs Traditional Index
Typical Multicolumn index references a single table whereas the STARindex can reference multiple tables.
With multicolumn indexes, if a query’s WHERE clause does not constrain on all the columns in the composite index, the index cannot be fully used unless the specified columns are a leading subset. On the other hand, STARindex can be fully utilized regardless of patterns of constraint processing.

STARindex Example
Assume there are 500 possible PRODUCTS, 200 MARKETS, 300 PERIODS, and one million FACTS in the data warehouse database. Further assume that a particular query selects 50 PRODUCTS, 20 MARKETS, 30 PERIODS that ultimately will select 1000 of the FACTS.
A traditional pairwise join strategy would generate 111,000 rows. A cartesian product would perform better in generating 50x20x30=30,000 intermediate rows plus 1000 FACTS rows = 31,000 rows.
A well-constrained STARjoin would generate only slightly more combinations than exist in the selected rows of the FACTS table, on average about 10 percent more, resulting 1100 rows.

Bitmapped Indexing
SYBASE IQ is an example of a product that uses a bitmapped index structure of the data stored in the SYBASE DBMS.
The Indexing Technology is developed by Expressway Technologies, which Sybase acquired in 1994.
SYBASE IQ is a stand-alone database that targeted as an “ideal” data mart solution that is optimized to handle multiuser ad hoc queries.

Data Cardinality
Bitmap indexes are used to optimized queries against low-cardinality data – the total number of potential values is relatively low. For example, gender cardinality is only 2 (male and female).
Bitmap indexes can become cumbersome and even unsuitable for high-cardinality data where the range of potential values is high. For example, values like “income” may have an almost infinite number of values.
SYBASE IQ uses a patented technique called Bit-Wise technology to build bitmap indexes for high-cardinality data.

Shortcomings of indexing
No updates
Lack of core RDBMS features
Less advantageous for planned queries
High memory usage

Hasil (Inggris) 3:

星型模式：索引问题
期维复合键，一个层次的日，周，月，季度，年。
复合关键问题：
它需要多个元数据定义
事实表必须携带所有关键部件
的指数规模的增大，性能下降
选择：
连接键一个关键（解决前两个问题）
使用人工（产生）的关键。将组合键移动到非键属性（最好的方法）中，星型模式：级别的指标问题，维度表设计中经常包含一个层次的指标来记录所有记录（无论是记录存储细节还是聚集）。在这个模式，汇总事实表是单独创建的，从详细的事实表。除了主要的事实表，雪花架构包含每一级聚集单独的事实表。

starjoin和starindex
starjoin是高速，单通，并行多表连接，用红砖砌的发明。Red的数据库管理系统可以在单一操作中加入多个表。在starjoin核心技术是索引的一种创新的方法。
红砖的RDBMS支持的专业化指标，创建称为starindexes，大大加速连接性能。

starindex：概念
starindexes是对一个或多个事实表的外键列创建
。不同于传统的指标包含的信息将列值的列表，值行，一starindex包含高度压缩的信息，涉及一个事实表的维度包含这些维度

starindex VS传统指数
典型多列索引一个表而starindex可以引用多个表中的行。
在多列索引，如果查询的WHERE子句并不限制在复合索引的所有列的索引，不能充分利用，除非指定的列是一个领先的子集。另一方面，可以充分利用starindex无论约束处理模式。

starindex例
认为可能有500个产品，200个市场，300个阶段，数据仓库数据库中的一百万个事实。进一步假设一个特定的查询选择50个产品，20个市场，30个阶段，最终将选择1000的事实。笛卡尔积会产生50x20x30 = 30000中间排加1000行= 31000行的事实表现更好。
一个很好的约束starjoin稍微组合会产生比在事实表中选定行的存在，平均约百分之10，而1100行。

位图索引
Sybase IQ是一个例子，一个产品，使用一个位图索引的数据结构存储在Sybase数据库的索引技术是
。通过高速公路技术发展，其中Sybase收购的1994。
Sybase IQ是一个独立的数据库，有针对性的为“理想”的数据中心解决方案，优化处理多用户的即席查询。

数据基数
位图索引来优化查询和低基数数据–潜在价值总量相对较低。例如，性别的基数是2（男性和女性）。
位图索引可以成为累赘甚至不适合高基数数据的可能值的范围是高。例如，值“收入”可能有价值几乎无限多。
Sybase IQ使用一种称为位技术在高基数数据建立位图索引的专利技术。

缺点没有更新索引

缺乏核心数据库的特点
不太有利于计划查询，高内存使用率