Re: star_transformation_enabled

Interesting.

Testing 8.1.7.4, switching this parameter doesn't affect any other (hidden) parameters (you might like to check in your version).

It looks from the path and statistics, though, as if it has enabled some other feature of Oracle - such as the ability to pull in constraints, generate extra predicates, and push down predicates into the view.

You might try a full explain plan (using utlxpls.sql from $ORACLE_HOME/rdbms/admin) to see if the VIEW line is qualified by something like (PUSHED PREDICATE).

Alternatively, you could run the query with event 10060 set and a kkoipt_table created so that you can collect the filter and access predicates used by the query.

create table kkoipt_table (
 n1 number,
 v1 varchar2(255)
);

The results are virtually incomprehensible, list, then clear out the results after each version of the query. You may spot a
difference.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


Chuck wrote in message ...


>"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