This may be very basic, but I'm trying to get the exact number of records for a query in Oracle SQL.

Since the platform doesn't allow to get big files as an export, I need to divide the output in parts, but I want to know how many records I have in each year for example.

This is the query:

select 
    a.item1, c.item2, c.item3, d.date1, d.date2, 
    c.amount1, c.amount2, c.ID1, c.ID2
from
    Table1 a, Table2 b, Table3 c, Table4 d
where 
    a.ID1 = b.ID1
    and b.ID1 = c.ID1
    and c.ID1 = d.ID1
    and (d.ID4 = 'abc1'
         or d.ID4 = 'abc2'
         or d.ID4 = 'abc3')
    and trunc(d.date1) between to_date('20210101', 'YYYYMMDD') and to_date('20211231', 'YYYYMMDD')

The query runs fine in test mode, but in prod I get that my output is too big, that´s why I want to know how many records I get per year.

I'm expecting to see how many records per year I have with this specific query.

CodePudding user response：

select to_char(d.date1,'YYYY') , count(*) 
from Table1 a, Table2 b, Table3 c, Table4 d
where a.ID1 = b.ID1
and b.ID1 = c.ID1
and c.ID1 = d.ID1
and (d.ID4 = 'abc1'
or d.ID4 = 'abc2'
or d.ID4 = 'abc3')
group by to_char(d.date1,'YYYY')

CodePudding user response：

I want to know how many records I get per year.

So, why don't you do what you want?

select extract(year from d.date1) as year,
       count(*)
from table1 a join table2 b on a.id1 = b.id1
              join table3 c on c.id1 = b.id1
              join table4 d on d.id1 = c.id1
where d.id4 in ('abc1', 'abc2', 'abc3')
group by extract(year from d.date1)
order by extract(year from d.date1);

CodePudding user response：

Replace select list with count(*):

select count(*)
from Table1 a, Table2 b, Table3 c, Table4 d
where a.ID1 = b.ID1
and b.ID1 = c.ID1
and c.ID1 = d.ID1
and (d.ID4 = 'abc1'
or d.ID4 = 'abc2'
or d.ID4 = 'abc3')
and trunc(d.date1) between to_date('20210101','YYYYMMDD') and to_date('20211231','YYYYMMDD')

I recommend you:

• use modern join syntax
• access table in order of predicate usage for better performance
• eliminate or on index column to avoid table scan

select count(*)
from Table4 d
join Table3 c on c.ID1 = d.ID1
join Table2 b on and b.ID1 = c.ID1
join Table1 a on a.ID1 = b.ID1
where d.ID4 in ('abc1', 'abc2', 'abc3')
and trunc(d.date1) between to_date('20210101','YYYYMMDD') and to_date('20211231','YYYYMMDD')

and make the same change in the full query. You'll probably find it runs a lot faster.