Re: star_transformation_enabled

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in news:b1pd2a$8n2$1 $830fa79f_at_news.demon.co.uk:

> Which version of Oracle ?

8.1.7

> What value for the COMPATIBLE parameter ?
8.1.7

> What were the two execution plans ?

star_transformation_enabled=false

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out
	PStart	PStop
SELECT STATEMENT Hint=CHOOSE		1  	 	845
  SORT UNIQUE		1  	199  	845
    NESTED LOOPS OUTER		1  	199  	843
      NESTED LOOPS		1  	103  	5
        NESTED LOOPS		1  	90  	4
          NESTED LOOPS		1  	72  	3
            TABLE ACCESS BY INDEX ROWID	TASK_MODIFICATION_GROUP	1  
	61    2
              INDEX UNIQUE SCAN	XPKTASK_MODIFICATION_GROUP	1  	 	1
            TABLE ACCESS BY INDEX ROWID	PFUSER	1 K	16 K	1
              INDEX UNIQUE SCAN	XPKPFUSER	1 K
          TABLE ACCESS BY INDEX ROWID	EVENT	218  	3 K	1
            INDEX UNIQUE SCAN	XPKEVENT	218
        TABLE ACCESS BY INDEX ROWID	TASK_CATEGORY	6  	78  	1
          INDEX UNIQUE SCAN	XPKTASK_CATEGORY	6 
      VIEW		27 K	2 M
        SORT GROUP BY		27 K	461 K	838             
          HASH JOIN		145 K	2 M	302             
            TABLE ACCESS FULL	TASK_MODIFICATION_REQUEST	104 K	1022 K
	36
            TABLE ACCESS FULL	SCHEDULE_MODIFICATION_REQUEST	145 K	995 K
	51

Star_transformation_enabled = true

The only real difference is in the processing of the view. It goes from a hash join to a nested loops

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

SELECT STATEMENT Hint=CHOOSE		1  	 	29
  SORT UNIQUE		1  	207  	29
    NESTED LOOPS OUTER		1  	207  	27
      NESTED LOOPS		1  	103  	5
        NESTED LOOPS		1  	90  	4
          NESTED LOOPS		1  	72  	3
            TABLE ACCESS BY INDEX ROWID	TASK_MODIFICATION_GROUP	1  
	61  	2
              INDEX UNIQUE SCAN	XPKTASK_MODIFICATION_GROUP	1  	 	1 
            TABLE ACCESS BY INDEX ROWID	PFUSER	1 K	16 K	1 
              INDEX UNIQUE SCAN	XPKPFUSER	1 K
          TABLE ACCESS BY INDEX ROWID	EVENT	218  	3 K	1
            INDEX UNIQUE SCAN	XPKEVENT	218
        TABLE ACCESS BY INDEX ROWID	TASK_CATEGORY	6  	78  	1
          INDEX UNIQUE SCAN	XPKTASK_CATEGORY	6
      VIEW		1  	104
        SORT GROUP BY		1  	17  	22
          NESTED LOOPS		21  	357  	22
            TABLE ACCESS BY INDEX ROWID	TASK_MODIFICATION_REQUEST	4  
	40  	2
              INDEX RANGE SCAN	XIF1627TASK_MODIFICATION_REQUE	4  	 
	1
            TABLE ACCESS BY INDEX ROWID	SCHEDULE_MODIFICATION_REQUEST
	145 K	995 K	5
              INDEX RANGE SCAN	XPKSCHEDULE_MODIFICATION_REQUE	145 K	 
	2