Re: view performance

Hi,

I checked indexes, and found two indexes on two tables that were not in the one database. I added those but did not see any difference.

I analyzed both databases and did see a difference in the explain plan generated in the bad database. I'm seeing a substantial difference at the top of the plan in the Rows column.

Good database

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

SELECT STATEMENT		1  	 	1642342  	 	      
	             	 
  SORT AGGREGATE		1  	 	 	 	      
	             	 
    VIEW	CTAC_SRCH	6 M	 	1642342  	 	      
	             	 
      SORT UNIQUE		6 M	1G	1642342  	 	      
	             	 
        HASH JOIN OUTER		6 M	1G	173983  	 	      
	             	 
          NESTED LOOPS OUTER		2 M	489 M	132157  	 
	      	             	 
            HASH JOIN		2 M	481 M	132157  	 	      
	             	 
              INDEX FAST FULL SCAN	CTAC_NAME_IDX	5 K	52 K	3  
	 	      	             	 
              

Bad database

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

SELECT STATEMENT		1  	 	8447290  	 	      
	             	 
  SORT AGGREGATE		1  	 	 	 	      
	             	 
    VIEW	CTAC_SRCH	25 M	 	8447290  	 	      
	             	 
      SORT UNIQUE		25 M	6G	8447290  	 	      
	             	 
        HASH JOIN OUTER		25 M	6G	443796  	 	      
	             	 
          HASH JOIN OUTER		8 M	1G	176237  	 	      
	             	 
            NESTED LOOPS OUTER		3 M	778 M	102116  	 
	      	             	 
              HASH JOIN		3 M	765 M	102116  	 	      
	             	 
                INDEX FAST FULL SCAN	CTAC_NAME_IDX	2 K	21 K	2  
	 	      	             	 


The schemas are the same.  Why would I get such drastic differences in 
the explain plan?                

Thanks for any help.

-- 
Jim Poe ( jpoe_at_fulcrumit.com )