One of our report query needs a performance improvement. While analyzing all the indexes and data model design the only option left to use database Hints (Parallel) as it improves the query performance by 50%. We did not want to add hint at the RPD physical table as it will change the Optimizer plan for all the reports. Is there a way to add the hint on a particular adhoc report so that we do not have to perform any regression testing for all other existing reports using the same fact table?
We tried using EVALUATE('/*+ PARALLEL (<Table Id>) */ %1',"Time"."Year") but the hint is getting added somewhere in between the 10 other columns and not at the front of the "Select" clause. If there is a way to put this column at the top it would server the purpose of hint. Please advise.
