Different query COST depending on data

[rshivagami]

I have 2 different DBs but with the same data strucutures and indexes, but different set of data (one is empty and the other has some test data in it) - my explain plan for the same query between the 2 Databases is different and so is the Cost of the plan. Why is it so ?

This DB has some data :

 
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	6  	 	      	             	 
  NESTED LOOPS		1  	57  	6  	 	      	             	 
    NESTED LOOPS		1  	51  	5  	 	      	             	 
      NESTED LOOPS		1  	43  	4  	 	      	             	 
        TABLE ACCESS FULL	CLG_CLASS_USER_MAP	1  	13  	3  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CLG_USER	1  	30  	1  	 	      	             	 
          INDEX UNIQUE SCAN	PK_USER	1  	 	 	 	      	             	 
      INDEX RANGE SCAN	PK_GRADE_USER_MAP	882  	6 K	1  	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	CLG_GRADE	1  	6  	1  	 	      	             	 
      INDEX UNIQUE SCAN	PK_GRADE	1

This DB is empty :

 
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	2  	 	      	             	 
  NESTED LOOPS		1  	113  	2  	 	      	             	 
    NESTED LOOPS		1  	107  	1  	 	      	             	 
      MERGE JOIN CARTESIAN		1  	78  	1  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CLG_CLASS_USER_MAP	1  	52  	1  	 	      	             	 
          INDEX SKIP SCAN	PK_CLASS_TEACHER_MAP	1  	 	 	 	      	             	 
        BUFFER SORT		1  	26  	 	 	      	             	 
          INDEX FULL SCAN	PK_GRADE_USER_MAP	1  	26  	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	CLG_USER	1  	29  	 	 	      	             	 
        INDEX UNIQUE SCAN	PK_USER	1  	 	 	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	CLG_GRADE	13  	78  	1  	 	      	             	 
      INDEX UNIQUE SCAN	PK_GRADE	1

Thanks.

[DaPi]

Are you joking?

Oh well . . . the CBO takes account of the statistics on the tables (from running ANALYZE) to see how much "work" has to be done => COST. Big tables cost more!

For example a Cartesian Join can v.efficient for tiny (empty) tables but not for tables with 100'000 rows. Full Table Scan ditto.

[rshivagami]

Thanks. So one cannot rightly tune a query during development other than follow the 'good practices' unless there is simulated production kind of data....

[pando]

or you can bring your production stats to development to simulate the execution plan IMHO however that is not realistic

[rshivagami]

Well the system is not into Production yet...Thanks for the help.

[DaPi]

It is possible to import the statistics from (say) production to development. But if the tables are small, you may get acceptable performance no matter which plan is chosen.

The simplest and cheapest in person-time is to create development as a clone of production. If your db is v.big then you may have to expend a lot of person-time in creating a sufficiently big sub-set of the data (e.g. selecting 200'000 rows from a 10'000'000 row table, over and over - while keeping the db self-consistant!).