时间戳

首页 > 解决方案 > 加速 Oracle 查询

sql - 加速 Oracle 查询

问题描述

我正在尝试找出一种解决方案来加快我已经拥有的查询。索引几列会有所帮助，但这不是很好。目前，为了获得所有结果，我需要在 where 条件中有多种变化的情况下运行几次查询，这需要几个小时才能完成所有事情。

我正在努力实现的基本示例：

select
    col1
   ,col2
   ,...
from table
where <few conditions>

我是否能够从 where 和 col2 的 where 和其他一些字段中找到 col1 的结果，但不能在 where 条件中添加它们？我知道有可能，但我不知道如何搜索示例。

非常感谢

我现在必须做的一个例子

procedure trigger_reporting
is
    type id_tt is table of customers.id%type index by binary_integer;
    type org_tt is table of customers.org_nr%type index by binary_integer;

    lt_id   id_tt;
    lt_org  org_tt;

    l_total pls_integer;
    l_res1  pls_integer;
    l_res2  pls_integer;
    ...etc

    --Here I just give an example
    l_start_date    date := '01.02.2020';
    l_end_date  date := '29.02.2020';
begin
    select id, org_nr
    into lt_id, lt_org
    from customers
    where is_active = 1;

    if lt_id.count > 0 then
        for i in lt_id.first..lt_id.last loop
            select count(*)
            into l_total
            from invoices
            where customer_id = lt_id(i)
            and orgnr = lt_org(i)
            and some_date between l_start_date and l_end_date;

            select count(*)
            into l_res1
            from invoices
            where customer_id = lt_id(i)
            and orgnr = lt_org(i)
            and some_date between l_start_date and l_end_date
            and deleted = 0;

            select count(*)
            into l_res2
            from invoices
            where customer_id = lt_id(i)
            and orgnr = lt_org(i)
            and some_date between l_start_date and l_end_date
            and status = 'Something';

            ...etc
        end loop;
    end if;
end;

标签： sqloracleoracle11g

解决方案

通过使用 SQL 连接表的固有能力以及条件聚合，您可以极大地简化您的过程。

这使您的程序类似于：

procedure trigger_reporting
is
    type id_tt is table of customers.id%type index by binary_integer;
    type org_tt is table of customers.org_nr%type index by binary_integer;

    lt_id   id_tt;
    lt_org  org_tt;

    l_total pls_integer;
    l_res1  pls_integer;
    l_res2  pls_integer;

    --here i just give an example
    l_start_date date := to_date('01.02.2020', 'dd.mm.yyyy'); -- always explicitly convert strings into dates.
    l_end_date   date := to_date('29.02.2020', 'dd.mm.yyyy');
begin

    select count(*) total,
           count(case when i.deleted = 0 then 1 end) res1,
           count(case when i.status = 'Something' then 1 end) res2
    into   l_total,
           l_res1,
           l_res2
    from   customer c
           inner join invoices i on c.id = i.customer_id
                                    and c.org_nr = i.orgnr
    where  c.is_active = 1
    and    i.some_date between l_start_date and l_end_date; -- did you really mean this? Does your some_date column contain dates set to midnight?
end;
/

注意几点：

  1. 您在程序中将字符串分配给日期 - 不要那样做。当您这样做时，您会强制代码依赖 NLS_DATE_FORMAT 参数，并且无论在何处运行代码，这可能都不相同。相反，告诉 Oracle 您的日期作为字符串的格式（按to_date()或DATE文字）。这使您的代码更加健壮。
  2. 当您对日期进行比较时，它包括时间部分。因此，如果您的日期列包含午夜以外的时间，<= 29/02/2020则不会找到 2020 年 2 月 29 日 10 点。这对您来说可能不是问题，具体取决于您的数据和要求，但是当您编写你的 SQL。

推荐阅读