Re: CBO Increase the Cost of Test tables.

I'm using Oracle 9.2.0.7 in an Unix environment.

I was testing this in our old development environment on some queries that had mutiple joins. I tried increadsing the index num of rows with the set_index_stats. Reading explain plans are still new to me, but from my understanding the furthest line in is the starting point based on the smallest set of data then works its way back out?

Example:

In this case I increased the RTI_IDS_PK row_num to 10,000,000 and block count to 500,000....
I tried doing similiar things on multiple tables, but nothing changed.

SELECT STATEMENT Cost = 8
  SORT ORDER BY
    FILTER

      NESTED LOOPS OUTER
        NESTED LOOPS
          NESTED LOOPS
            VIEW  VW_NSO_1
              SORT UNIQUE
                TABLE ACCESS BY INDEX ROWID RTI_IDS
                  NESTED LOOPS
                    TABLE ACCESS BY INDEX ROWID RTI_IDS
                      INDEX RANGE SCAN RTI_IDS_PK
                    INDEX RANGE SCAN RTI_IDS_2_IX
            TABLE ACCESS BY INDEX ROWID RTI_BUS_ACCOUNTS
              INDEX RANGE SCAN RTI_BACCT_3_IX
          INDEX UNIQUE SCAN RTI_BENT_PK
        TABLE ACCESS BY INDEX ROWID RTI_IDS
          INDEX RANGE SCAN RTI_IDS_PK

What I really need is to find some documentation on how to properly do this. The only thing I found while searching was someone briefly mentioning set_table_stats. My experience in this area is lacking mainly b/c I'm brand a brand new dba, so if you could just point me in the right direction I'm sure I can figure out the rest.

Thanks
Shawn Received on Tue Aug 22 2006 - 17:53:46 CDT