I'll put the disclaimer right up front: IANADBA! Okay, now that we've got that covered let's dive into database directives and hints.
Query directives, (sometimes called 'hints'), are special comments usually placed at or near the beginning of your SQL. These comments are actually "suggestions" to the database's internal query optimizer that, if used properly, make your SQL run faster.
The "Right" Way:
All report developers, (as a normal step in their standard yet rigorous report
development process), send all of the query sections of all of their reports, once
reasonably stable, over to their internal IM team for refinement and optimization. The
local IM team, (rapidly and cheerfully springing into action), then handles 'the technical
bits' like re-ordering the tables, considering alternative index optimization schemes,
and the adding in of these db directives. Right?
The "Seat of the Pants" Method:
A second approach might be to take a bunch of night courses at the local community
college and/or [shudder] RTFM associated with your database of choice.
We're going to try a third approach here and 'just go for it'. Ready? Start off by pulling up Designer and creating a query section that joins half a dozen Oracle tables together. (Directive syntax is, of course, database specific. The example shown below will be Oracle specific.) Get out your stopwatch and time the query. Hopefully you've constructed something running in the 5 to 15 minute range. If not, fiddle about by adding a few computed fields in your select statement in order to crank the execution time up to something that might be worth optimizing.
[By the way, if you're throwing together an example from scratch instead of sacrificing an existing report, please be sure to add some items from each table to the request or limit lines. Many enterprise level DBs are pretty smart - so smart in fact that even if the table is sitting on your nice gui screen it will not be used in the query unless explicitly needed. This has been known to leave some analysts scratching their heads wondering why their result set didn't meet their expectations. When in doubt, review the query log to see what SQL was *actually* sent to the database.]
Okay, let's try adding in our directive. Right click on the 'Request' line and 'Add a Computed Item'. Give the new item any name you like, but define it like this, (but without the quotes): "/*+rule*/1". Got it? Now drag this computed item to the front of the line ... this MUST be the FIRST item on your request line.
Now reset your stopwatch and re-execute the query. [Remember to 'hide' the new column in your result set since it won't be visibly meaningful to your users.]
Faster, isn't it?
If you're looking for an explanation of why your report now runs faster, I suggest you go talk to your friendly neighborhood dba. For many reports, this easy-to-use technique will result in faster reports.
It was briefly mentioned above, but another trick you can try, (if you have a good understanding of your local data structures and/or a lot of time on your hands for trial and error), is to use the 'Topic Priorities' tab of the Data Model Options command available from the Query section in Designer to re-order your tables. But we won't go into that here.
Enjoy!
RonL.