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 Go to next message](/forum/theme/orafaq/images/down.png) |
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?
|
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:31:34 CDT 2024
|