Re: Cost based optimizer with snapshots

Actually an Oracle snapshot is composed of other Oracle objects, such as tables, indexes and stored procedures. A snapshot called "CRACKLE_POP" will have a view with same name and a table called "SNAP$_CRACKLE_POP". Be sure to analyze this table, and fell free to add indexes onto it.

Michael Ellison -- El Gitano

swapn187_at_my-dejanews.com wrote in message <6ur038$dpa$1_at_nnrp1.dejanews.com>...
>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 Wed Sep 30 1998 - 09:21:26 CDT