Oracle星型查询转换解析

Oracle的星型查询转换（Star Query Transformation）是Oracle数据库优化器的一个重要特性，专门用于优化针对星型模式（Star Schema）数据模型的查询性能。星型模式通常应用于数据仓库中，它由一个大的事实表（Fact Table）和一组小的维度表（Dimension Tables）组成，这样的结构类似于一个星型。

一、星型查询转换原理

星型查询转换的核心思想是将原本的星型连接查询重写成更高效的形式。在原始查询中，事实表与维度表通过多个连接（JOINs）关联起来，这可能涉及大量的I/O操作和CPU计算，尤其是在维度表上应用过滤条件时。星型查询转换通过以下步骤优化查询：

1. 子查询转换：将针对维度表的过滤条件转化为对事实表的子查询，这样可以避免直接连接到维度表，减少I/O和CPU消耗。
2. 位图索引使用：在事实表的外键列上创建位图索引（Bitmap Indexes）。位图索引非常适合存储和检索少量的值，它们在处理大量行但少量唯一值的列时特别有效。
3. 隐式重写SQL语句：优化器在执行查询时会自动识别是否可以应用星型转换，并对SQL语句进行隐式的重写，以利用位图索引和子查询转换。

二、配置星型查询转换

为了启用星型查询转换，需要设置以下参数：

• STAR_TRANSFORMATION_ENABLED：此初始化参数控制是否允许优化器使用星型转换。默认情况下，这个参数可能是关闭的（FALSE），需要显式设置为TRUE。

假设我们有一个星型模式的数据模型，其中包含一个事实表sales和三个维度表customers、products、dates。事实表sales有外键指向这三个维度表。

• 原始查询

SELECT c.customer_name, p.product_name, d.date_name, s.amount
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
JOIN products p ON s.product_id = p.product_id
JOIN dates d ON s.date_id = d.date_id
WHERE c.customer_name = 'John Doe' AND p.product_name = 'Widget';

• 经过星型查询转换后的查询

SELECT c.customer_name, p.product_name, d.date_name, s.amount
FROM (
    SELECT *
    FROM sales
    WHERE sales.customer_id IN (SELECT customer_id FROM customers WHERE customer_name = 'John Doe')
    AND sales.product_id IN (SELECT product_id FROM products WHERE product_name = 'Widget')
) s
JOIN customers c ON s.customer_id = c.customer_id
JOIN products p ON s.product_id = p.product_id
JOIN dates d ON s.date_id = d.date_id;

实际上，优化器不会执行这样的显式重写，而是内部优化执行计划，使用位图索引和其他优化技术。

三、性能考虑

星型查询转换提高了查询效率，但同时也需要考虑位图索引的存储开销。在维度表上有大量唯一值的情况下，位图索引可能不会非常有效。因此，在设计数据模型和优化策略时，需要权衡各种因素，包括数据分布、查询模式和存储需求。

四、案例

使用一个典型的星型模式数据仓库结构，其中包含一个大型的事实表和几个小型的维度表。

1、数据模型

假设我们的数据模型如下：

• Fact Table: sales

  • sale_id: 销售ID
  • product_id: 产品ID
  • customer_id: 客户ID
  • date_id: 日期ID
  • quantity: 销售数量
  • amount: 销售金额

• Dimension Tables:

  • products
    • product_id: 产品ID
    • product_name: 产品名称
  • customers
    • customer_id: 客户ID
    • customer_name: 客户名称
  • dates
    • date_id: 日期ID
    • date: 日期

2、创建表和数据

首先，我们需要创建这些表和插入一些示例数据。由于这是一个示例，我们将简化数据量，但在实际生产环境中，事实表sales将包含大量记录，而维度表将相对较小。

CREATE TABLE sales (
  sale_id NUMBER,
  product_id NUMBER,
  customer_id NUMBER,
  date_id NUMBER,
  quantity NUMBER,
  amount NUMBER
);

CREATE TABLE products (
  product_id NUMBER,
  product_name VARCHAR2(100)
);

CREATE TABLE customers (
  customer_id NUMBER,
  customer_name VARCHAR2(100)
);

CREATE TABLE dates (
  date_id NUMBER,
  date DATE
);

接下来，我们插入一些测试数据。

-- Insert data into sales table
INSERT INTO sales VALUES (1, 1, 1, 1, 10, 100);
INSERT INTO sales VALUES (2, 2, 2, 2, 20, 200);
INSERT INTO sales VALUES (3, 1, 2, 1, 15, 150);

-- Insert data into products table
INSERT INTO products VALUES (1, 'Product A');
INSERT INTO products VALUES (2, 'Product B');

-- Insert data into customers table
INSERT INTO customers VALUES (1, 'Customer A');
INSERT INTO customers VALUES (2, 'Customer B');

-- Insert data into dates table
INSERT INTO dates VALUES (1, TO_DATE('2024-01-01', 'YYYY-MM-DD'));
INSERT INTO dates VALUES (2, TO_DATE('2024-01-02', 'YYYY-MM-DD'));

3、创建位图索引

为了使星型查询转换生效，我们还需要在事实表sales的外键列上创建位图索引。

CREATE BITMAP INDEX idx_sales_product ON sales (product_id);
CREATE BITMAP INDEX idx_sales_customer ON sales (customer_id);
CREATE BITMAP INDEX idx_sales_date ON sales (date_id);

4、查询优化前

现在，我们可以尝试一个查询，比如找出某个特定客户购买的特定产品的总销售金额。

SELECT SUM(s.amount)
FROM sales s
JOIN products p ON s.product_id = p.product_id
JOIN customers c ON s.customer_id = c.customer_id
WHERE c.customer_name = 'Customer A' AND p.product_name = 'Product A';

5、查询优化后

当我们运行上述查询时，如果启用了星型查询转换，Oracle优化器将自动重写执行计划，以利用位图索引来提高性能。这意味着，优化器将使用位图索引查找sales表中满足条件的行，而不是执行完整的连接。

6、检查执行计划

为了验证这一点，我们可以查看查询的执行计划，看看优化器是否确实使用了星型查询转换。

EXPLAIN PLAN FOR
SELECT SUM(s.amount)
FROM sales s
JOIN products p ON s.product_id = p.product_id
JOIN customers c ON s.customer_id = c.customer_id
WHERE c.customer_name = 'Customer A' AND p.product_name = 'Product A';

SELECT * FROM table(dbms_xplan.display);

执行计划将显示优化器是否使用了位图索引扫描和子查询，而不是常规的表连接。

综上所述，星型查询转换能够显著提高数据仓库中星型模式查询的性能，尤其是当事实表非常大而维度表相对较小的时候。通过创建位图索引和正确配置Oracle优化器，可以确保查询得到最佳的性能表现。