My favourite hints - I
Hinting a query should only be done as last resort. Unless the question is just to execute a query as fast as possible. Hinting in applications code may impact upon the usage of features specific to future Oracle versions.
Now that you've been warned, here it goes.
Join hints
/*+ use_nl(a b) */ - this forces a nested loop join, which is the most obvious fashion of joining two tables. It is actually a good choice, provided that the lookup table has an usable index for the join, and the driving dataset is small compared with the lookup data. A clear winner in most OLTP applications. a and b being the aliases of the tables to be joined.
/*+ use_hash(a b) */ - this one forces a hash join. An hash join is obtained computing an hash value on the join columns of both tables, and selection the records that match. The good news is that indexes aren't normally used when doing hash joins. It is a lousy performer on small sets of large tables, but yelds great times joining huge sets on huge tables. If you're on datawarehouse that's the join for you.
/*+ ordered */ - this forces Oracle to perform the join according to the order the tables are in the FROM clause.
Index hints
/*+ index(a) */ or /*+ index(a b) */ - in the first format, forces Oracle to use an index to access the table with 'a' alias - Oracle will hopefully choose the best index for the execution based upon the query's WHERE clause; in the second format, Oracle will use the 'b' index to access 'a' table.
/*+ index_ffs(a) */ or /*+ index_ffs(a b) */- a great choice for COUNT, provided all the information needed is on the index. This hint forces a full index scan on an index selected by Oracle in the first format, and on an index supplied by the user in the later. It doesn't actually read the table.
More on hints later.
posted by Pedro Oliveira @ 6:59 AM
0 Comments:
Post a Comment
<< Home