I have Oracle 7.3.4 running on two servers on separate HP-Unix systems, each
implemented as a data warehouse. One is a staging server and the other is a
production server. The production server has read-only snapshots (with fast
refresh) of the fact and dimension tables of the staging server. I have a
Brio query that joins 10 dimension tables with a Fact table (having more than
8 million records) and has multiple sorts and group by's. The Choose option
is set, but the cost-based optimizer was not used since I had not analyzed
the tables. This caused the Sort-Merge joins to be used, needing more than 6G
space in the Temp tablespace ! I analyzed the dimension tables: the
cost-based optimizer was used, with hash-joins needing only 85M temp space.
This was great !! Question: The same query needs to be run on the Production
server. I am expecting that it would not use the Cost-based optimizer, since
the objects used in the query are snapshots (not tables) that cannot be
analyzed. Can anyone give any suggestions/information about forcing the
cost-based optimizer to be used for queries against 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:58:33 CDT
