Oracle hint first_rows
Im running a select query by joining 2 tables. While trying to tune the query i found using first_rows(1) hint makes less reads from the joining tables than without it and hence improves performance. Below are the explain plan. Question is so using the first_rows hint really improves the performance and both returns same number of result sets..? (Just need an assurance from you guys). If so why not we always use this hint in order to have best response time..?
Plan without hint
SELECT STATEMENT ALL_ROWSCost: 98 Bytes: 340,392 Cardinality: 13,092
6 NESTED LOOPS Cost: 98 Bytes: 340,392 Cardinality: 13,092
3 VIEW CIT. Cost: 2 Bytes: 6 Cardinality: 1
2 CONNECT BY WITHOUT FILTERING
1 FAST DUAL Cost: 2 Cardinality: 1
5 PARTITION RANGE ITERATOR Cost: 96 Bytes: 261,840 Cardinality: 13,092 Partition #: 5
4 TABLE ACCESS FULL TABLE PDS.M_DM Cost: 96 Bytes: 261,840 Cardinality: 13,092 Partition #: 5
Plan with hint first_rows(1)
SELECT STATEMENT HINT: FIRST_ROWSCost: 2 Bytes: 26 Cardinality: 1
6 NESTED LOOPS Cost: 2 Bytes: 26 Cardinality: 1
3 VIEW CIT. Cost: 2 Bytes: 6 Cardinality: 1
2 CONNECT BY WITHOUT FILTERING
1 FAST DUAL Cost: 2 Cardinality: 1
5 PARTITION RANGE ITERATOR Cost: 0 Bytes: 20 Cardinality: 1 Partition #: 5
4 TABLE ACCESS FULL TABLE PDS.M_DM Cost: 0 Bytes: 20 Cardinality: 1 Partition #: 5
Also please let me know how to use code tags, since even if i post with indentations the code/explain-plan appears as un-indented
Edited by: michaelrozar17 on May 10, 2012 7:11 AM