Optimizer for Queries on snapshot

Oracle 7.3.4 is running on two servers (HP UX). One is a staging server and the other is a production server, both for a data warehouse implementation. The production server has read-only snapshots of the fact and dimension tables from the staging server (with fast refresh). A huge Brio query joins 10 dimension tables to a fact table having more than 8 million records, and has multiple sorts and group by's. When the query was run on the staging server, it would run out of Temp space configured at 6G, since it used Sort-Merges. I analyzed the reference tables with ' compute statistics' and re-ran the query to find that now hash-joins were used, needing only 85M of temp space. This was great.

Question: When the same query is run on the Production server, I am expecting that the cost based analyzer cannot be used as the objects being queried are snapshots (not tables) that cannot be analyzed. Can anyone give me suggestions/information on forcing the cost-based optimizer to work on snapshots ?

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Sep 29 1998 - 10:46:14 CDT