Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
3,212 views