Home » RDBMS Server » Performance Tuning » Materialized view not getting used (Oracle 10g)
Materialized view not getting used [message #332107] Mon, 07 July 2008 09:25 Go to next message
vkumar
Messages: 1
Registered: July 2008
Junior Member
Hi,

I have created materialized view as below-
CREATE MATERIALIZED VIEW mv_trk_ctlg
build IMMEDIATE
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT product_info_key,TERRITORY_KEY,partner_key,
COUNT (metadata_available) metadata_available,
COUNT (BA_CUSTOM_TONE) BA_CUSTOM_TONE,
COUNT (ba_streaming) ba_streaming,
COUNT (ba_download) ba_download,
COUNT (BA_FULL_ALBUM) BA_FULL_ALBUM,
COUNT (COPYRIGHT_CUSTOM_TONE) COPYRIGHT_CUSTOM_TONE,
COUNT (COPYRIGHT_STREAMING) COPYRIGHT_STREAMING,
COUNT (COPYRIGHT_DOWNLOAD) COPYRIGHT_DOWNLOAD,
COUNT (COPYRIGHT_FULL_ALBUM) COPYRIGHT_FULL_ALBUM,
COUNT (TR_DELVERBL_DOWNLD) TR_DELVERBL_DOWNLD,
COUNT (TR_DELVERBL_STRMNG) TR_DELVERBL_STRMNG
FROM (
SELECT product_info_key,TERRITORY_KEY,partner_key,
DECODE(metadata_available, 'Y', 'Y') metadata_available,
DECODE(BA_CUSTOM_TONE, 'Y ', 'Y ') BA_CUSTOM_TONE,
DECODE(ba_streaming, 'Y ', 'Y ') ba_streaming,
DECODE(ba_download, 'Y ', 'Y ') ba_download,
DECODE(BA_FULL_ALBUM, 'Y ', 'Y ') BA_FULL_ALBUM,
DECODE(COPYRIGHT_CUSTOM_TONE, 'Y ', 'Y ') COPYRIGHT_CUSTOM_TONE,
DECODE(COPYRIGHT_STREAMING, 'Y ', 'Y') COPYRIGHT_STREAMING,
DECODE(COPYRIGHT_DOWNLOAD, 'Y ', 'Y') COPYRIGHT_DOWNLOAD,
DECODE(COPYRIGHT_FULL_ALBUM, 'Y ', 'Y') COPYRIGHT_FULL_ALBUM,
DECODE(TR_DELVERBL_DOWNLD, 'Y ', 'Y') TR_DELVERBL_DOWNLD,
DECODE(TR_DELVERBL_STRMNG, 'Y ', 'Y') TR_DELVERBL_STRMNG
FROM TRACK_CATALOG)
GROUP BY product_info_key,TERRITORY_KEY,partner_key;

and have set the rquired parameters like-
optimizer_mode = choose, first_rows, or all_rows
job_queue_interval = 3600
job_queue_processes = 1
query_rewrite_enabled = true
query_rewrite_integrity = enforced


It is getting used only when the select query is similar to the one that is used in creating the MV. In all other cases base table is used. Any idea why MV is not used when only few qggregates are requested?
Re: Materialized view not getting used [message #332116 is a reply to message #332107] Mon, 07 July 2008 09:46 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Previous Topic: UNION causing slow performance
Next Topic: What do Oracle waits mean
Goto Forum:
  


Current Time: Wed Dec 07 22:42:36 CST 2016

Total time taken to generate the page: 0.05069 seconds