时间戳

首页 > 解决方案 > 在 SQL 视图中使用查找值的最佳方法？

sql - 在 SQL 视图中使用查找值的最佳方法？

问题描述

我需要从表中查找日期，然后使用该值过滤另一个表。这两个表没有任何关系。

这是虚拟示例设置

CREATE TABLE config (
    idp INT NOT NULL,
    eff_date DATE NOT NULL , 
    last_transaction DATE NOT NULL, 
    a VARCHAR(50) NULL
);

INSERT INTO config (idp, eff_date, last_transaction, a)
  VALUES
(1, DATE '2021-05-03', DATE '2021-05-02', 'abc')
;

CREATE TABLE transactions (
    mth_eff_date DATE NOT NULL , 
    amt INT NOT NULL 
);
CREATE INDEX transaction_date ON transactions (mth_eff_date);

INSERT INTO transactions VALUES (DATE '2021-05-02', 825);
INSERT INTO transactions VALUES (DATE '2021-05-02', 650);
INSERT INTO transactions VALUES (DATE '2021-05-02', 825);
INSERT INTO transactions VALUES (DATE '2021-05-03', 500);
INSERT INTO transactions VALUES (DATE '2021-05-03', 650);
INSERT INTO transactions VALUES (DATE '2021-05-04', 235);
INSERT INTO transactions VALUES (DATE '2021-05-06', 853);

这是我的解决方案，它在大型数据集上的性能非常差。在测试时，我检查了运行时间，如果我对(SELECT last_transaction from lt)部件进行硬编码并且它运行得非常快。

WITH lt AS 
  (SELECT last_transaction
    FROM config
  ),
  transactions_filt AS 
  (SELECT * 
    FROM transactions
    WHERE mth_eff_date <= (SELECT last_transaction FROM lt)
  )
  SELECT * FROM transactions_filt
;

（以上内容可作为小提琴使用。）

做这个的最好方式是什么？

标签： sqldatabaseoracle

解决方案

当查询性能不佳时，第一个响应是尝试索引相关列，在这种情况下config.last_transaction。

CREATE INDEX config_last_transaction ON config (last_transaction);

使用小样本集，性能上的差异不会很明显，但在执行计划中是可见的。没有索引：

ID 手术 姓名 行 字节 成本 时间
0 选择声明 1 22 3 00:00:01
1 按索引 ROWID 访问表 交易 1 22 0 00:00:01
* 2 索引范围扫描 交易日期 1 0 00:00:01
3 表访问权限已满 配置 1 9 3 00:00:01

有一个索引：

ID 手术 姓名 行 字节 成本 时间
0 选择声明 1 22 1 00:00:01
1 按索引 ROWID 访问表 交易 1 22 0 00:00:01
* 2 索引范围扫描 交易日期 1 0 00:00:01
3 索引全扫描 CONFIG_LAST_TRANSACTION 1 9 1 00:00:01

请注意，查询从使用成本为“3”的表扫描变为使用成本“1”的索引扫描。

另一种方法（不排除创建索引）是尝试编写查询的替代方法，例如连接：

SELECT t.mth_eff_date, t.amt
  FROM transactions t
    INNER JOIN config c ON t.mth_eff_date <= c.last_transaction
;

推荐阅读