Hi All,
I have a Big Select Reporting query which retrieves columnar values from two similar table Structures of Eg: Table A and Table B . ID column
select count(1) from Table A = ~ 145000000
select count(1) from Table B= ~ 4090000
It was originally Designed in such a way that there are no relational constraints between Table A and Table B.
We have this Big reporting query which runs Union All between these Two Tables Columns where from_date and To_date along with other parameters are given as inputs. And this produces a Excel report for Analysis . This query is running longer time.
To Start with I have used, /*+ parallel(auto) NO_INDEX */ hint since query needs to scan the entire tables.
Report query will be run daily, monthly, quarterly.
Are the hints fine enough or any other fine tuning suggestions would be of great help like materialised views etc..