Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Fine tuning Big Select Reporting Query

vijzMar 30 2018 — edited Apr 1 2018

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..

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 28 2018
Added on Mar 30 2018
11 comments
2,703 views