I have a table with 680K rows of data. This table has a very low cardinality on 2 columns that are used in join statement on some reports. I'm running on 7.3.4 and compatibility in inti.ora is set to 220.127.116.11, therefore I cannot use bitmap index (ideal for low cardinality). Should I just leave the tables as is to execute full table scan or must I create a B* Tree index. Which would be more effecient.
I think you are correctly thinking by assuming that full table scans will be faster than Index lookups because of low cardinality. The bottom line is that a certain percentage threshold of retrieved rows is indeed faster in FTS than index -I believe the threshold is (ahmmmmm???...) 20%?
1. analyze the table(s) with compute statistics and see what the CBO chooses.
2. use the /*+ RULE */ hint and force a FTS then,
use the /*+ RULE */ hint and force an Index scan
see which performs better. In other words:
Don't blame me, I'm from Red Sox Nation.
Click Here to Expand Forum to Full Width