Data warehouse Star_transformation_enable

[tony_n]

Hi

I have a data warehouse in ORACLE that uses star_transformation_enable=true in its init.ora file.

I have seen querys using a lot of Temp space and running out of temp space.

Has anyone come across the parameter
star_transformation_enable=temp_disable.

just wondered if anyone out there has used this parameter or has any info on it.

Thanks

Tony

[AJW_ID01]

Hi,

I had to use the temp_disable option in an oracle 8.1.6 warehouse.

There is a bug (not sure of number) but which generates internal memory errors when trying to generate system temporary tables to help in the transformation.

I believe there is also a bug in 8.1.7.0 - which causes sub-optimal plans to be generated - on offshoot of which is to cause the use of MASSES of temp space in the generation of system temporary tables.

I believe you can still use some benefits of the star_transformation by setting to 'temp_disable', BUT there is also the distinct possibility that in some scenarios - again - a sub-optimal path (not involving temp tables) may be chosen {but not always, or even necessarily frequently!}.

I understand the problems are fixed in 9i and patchset 8.1.7.3.

:-)

Hope that sheds a little light.

- Tony