 <p>Your WHERE clause contains only joins. There are no filters. This means your query needs to consider all the rows in at least one table. From this it follows that your query should execute a FULL TABLE SCAN of at least one of your tables, not an indexed read. A full table scan is the most efficient way of getting all the rows in a table. </p> <p>So don't fix the syntax of your INDEX hint, get rid of it.</p> <p>Next, figure out which table should drive your query. This is business logic. Probably your requirement is something like </p> <blockquote> <p>"Summarise BL_DETAILS and BL_FREIGHT for every row in BL_CONTAINERS."</p> </blockquote> <p>In which case you might think you need a full table scan of BL_CONTAINERS. But if BL_FREIGHT has more rows than BL_CONTAINERS and every BLF_REF_NO matches a BL_REF_NO (i.e. there is a foreign key on BL_FREIGHT.BLF_REF_NO referencing BL_CONTAINERS.BL_REF_NO) it would probably be better to drive from BL_FREIGHT.</p> <p>Note that this is true if you are only interested in BL_CONTAINERS which have matching BL_FREIGHT rows. But if you want to include containers which have not been used (i.e. they have no matching no BL_FREIGHT records) you need to use outer joins and drive off the BL_CONTAINERS table.</p> <p>These considerations get more complicated when you throw BL_DETAILS into the mix. Your report seems to be based around the BL_DETAILS categories (as Jeffrey observes it is hard for us to understand your query without aliases or describes). So perhaps BL_DETAILS is the right candidate for driving table.</p> <p>As you can see, tuning requires insight into the business logic and the details of the data model. You have that local knowledge, we do not.</p> <p>There are tools which can help you. Oracle has EXPLAIN PLAN which will show you how the database will execute the query. The query optimizer gets better with each release so it matters which version of the database you're using. Here is <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm" rel="nofollow">the documentation for 10g</a>.</p> <p>The important thing to note is that you need to give the database accurate statistics, in order for it to come up with a good plan. <a href="http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm" rel="nofollow">Find out more.</a> </p>