b* tree index vs no full table scan

[leonard905]

Hi ,

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 7.3.0.0, 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.

Thanks,

[gopi]

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%?

two things:

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:

Bench mark!!!!
Bench mark!!!!
Bench mark!!!!