主页 / dba / 问题 / 11366
Accepted
Bob Jansen
Asked: 2012-01-24 01:20:12 +0800 CST

有效地找到不同的值

  • 772

我有许多带有主键（月、年、数字）的表，并且不同的基数有所不同。对于元组（月、年），历史不会追溯到很久以前，从长远来看，这可能不会超过 50。对于每个（月、年）元组，唯一数字不超过 200 万个。我想知道哪些月份和年份的组合可用。我使用此查询执行此操作：

select month, year from table group by month, year

这会返回正确的结果，但似乎效率不高。获得此结果的有效方法是什么（利用唯一索引）？

调优顾问建议为这个查询添加一个月年索引，但这似乎很浪费，因为已经有一个更大的索引可用。

oracle

2 个回答

  1. Best Answer

    您可以使用以下技术的变体 - 强制重复“最小/最大”范围扫描：

    假设

    1. 您可以生成所有可能的年/月组合的列表
    2. number不为空（它不能像在 PK 中那样，但我提到它是因为如果允许空值，有一种解决方法）

    试验台：

    create table foo(month, year, num, primary key(month, year, num)) as
with m as ( select extract(month from d) as month, extract(year from d) as year
            from (select add_months(sysdate,1-level) as d from dual connect by level<50) )
select month, year, num
from m cross join 
     (select level as num from dual connect by level<100000 order by dbms_random.random());

    正常查询：

    select distinct month, year from foo;
--gets=11656

    最小/最大技术：

    with m as ( select extract(month from d) as month, extract(year from d) as year
            from (select add_months(sysdate,1-level) as d from dual connect by level<50) )
select month, year, decode(( select min(num)
                             from foo
                             where month=m.month and year=m.year )
                           ,null, 'N', 'Y') as has_data_yn
from m;
--gets=294

    回应评论的一些解释：

    在每种情况下（测试台和最小/最大查询），子查询因式分解子句只生成一个（年、月）元组列表：

    with m as ( select extract(month from d) as month, extract(year from d) as year
            from (select add_months(sysdate,1-level) as d from dual connect by level<50) )
select * from m;
/*
MONTH                  YEAR                   
---------------------- ---------------------- 
1                      2012                   
12                     2011                   
11                     2011                   
10                     2011           
...
...
*/

    然后，该技术在select子句中使用子查询来检查（月，年）是否存在任何行——这个子查询必须最多只能产生 1 行：

    select min(num)
from foo
where month=m.month and year=m.year;

    这非常快，因为它利用了 PK 的有序性质 - 但是它需要每个月执行一次 - 如果每个月有数百万行是有意义的，但如果有足够少的行适合少数块。

    • 5

  2. 这是一个使用与 Jack Douglas (+1) 相同的技术的解决方案。它使用他的测试平台产生相同数量的一致获取，但是否更容易理解将在旁观者的眼中。

    SELECT extract(month from d) m, extract(year from d) y 
FROM (SELECT add_months(sysdate,1-level) d FROM dual CONNECT BY level < 50)
WHERE EXISTS (
 SELECT 1 FROM foo WHERE month=extract(month from d) AND year=extract(year from d)
);

    此选项使用 select from dual 来驱动查询，而 select from foo 仅用于决定保留哪些日期。

    同样的查询也可以这样写：

    SELECT * FROM (
   SELECT extract(month from add_months(sysdate,1-level)) m 
        , extract(year from add_months(sysdate,1-level)) y 
      FROM dual CONNECT BY level < 50)
WHERE EXISTS (
   SELECT 1 FROM foo WHERE month=m AND year=y
);
    • 3

相关问题