b* tree index vs no full table scan
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: b* tree index vs no full table scan

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    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,
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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!!!!
    Don't blame me, I'm from Red Sox Nation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width