Speedup Oracle query

Question

I am trying to figure out a solution to speed a query that I already have. Indexing few columns helps but it's not great. In present to achieve all results I need to run the query few times with multiple variations in the where condition and this takes few hours to complete everything.

Basic example of what I'm trying to achieve:

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

Am I able to hit result in col1 from where and for col2 from where and some other fields but not adding them in the where condition ? I know there is a possibility but I don't know how to search for an example.

Thank you very much

---

An example of what I have to do now

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;

Answer 1

You can vastly simplify your procedure by using SQL's inherent ability to join tables, plus conditional aggregation.

That makes your procedure something like:

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;
/

N.B. a couple of points:

1. you were assigning strings to dates in your procedure - don't do that. When you do so, you force the code to rely on the NLS_DATE_FORMAT parameter, and that may not be the same wherever the code is run. Instead, tell Oracle the format your date-as-a-string is in (either by to_date() or the DATE literal). That makes your code far more robust.
2. When you do a comparison on dates, it includes the time part. Therefore, if your date columns contains times other than midnight, doing <= 29/02/2020 won't find 10 o'clock on the 29th Feb 2020. That may not be a problem for you, depending on your data and requirements, but it's something to consider when you write your SQL.

Answer 2

You can combine the queries into one (at least for the example you show), like this (you can add the condition for the third query):

select count(*), sum(case when deleted=0 then 1 else 0 end) count_deleted
            into l_total, 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 this may probably be still faster, as it removes the outer loop, but you need to adapt your code (not shown):

select c.id, c.org_nr,count(*) count_all, sum(case when i.deleted=0 then 1 else 0 end) cout_deleted
                from invoices i join customers c using (i.customer_id=c.id and i.orgnr=c.org_nr)
                where c.is_active=1
                and i.some_date between l_start_date and l_end_date
                group by c.id,c.org_nr;