Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view (oracle 9i)
Materialized view [message #344278] |
Fri, 29 August 2008 05:25  |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
I have a materialized view of 12000 records, ther are no duplicate rows in it. It is taking 1 mint to get the results from the materialized view. If I call a procedure to get the results using ref cursor it is taking 3 mints. Why is the refcursor taking lot of time.
There are no indexes on the materialized view.
Can you suggest me how can we improove the speed.
If the solution is to create index then what kind of index can be choose.
Regards,
Pointers.
|
|
|
|
Re: Materialized view [message #344336 is a reply to message #344278] |
Fri, 29 August 2008 07:36   |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
There is one Materialized View which is created on different Materialized Views.
I am querying directly on the Final Materialized View to get the final data.
(this is taking 1 mint)
The other method that I am using to get the data is using a procedure with refcursor(this is taking 3 mints)
PROCEDURE get_mv( status OUT tp_refcursor)
AS
BEGIN
OPEN rc_status FOR
SELECT
APP_NAME,
APP_ID,
APP_STATE_NAME,
APP_COST_CENTER ,
APP_CLICKSTREAM,
APP_URL,
GNO ,
GCO,
GSO ,
COCKPIT_PAUSE,
COCKPIT_SORT,
CORP,
SSO_ENABLED ,
PAUSE_REASON ,
APP_SSO_PAIR ,
APP_STATE_DT,
NETWORK_STATUS,
SERVER_STATUS,
REST_STATUS ,
YIELD_1HR,
YIELD_24HRS ,
YIELD_7DAYS,
AVAIL_1HR ,
AVAIL_24HRS ,
AVAIL_7DAYS,
MV_DATE ,
ALERT_DT,
ALERT_TYPE,
ALERT_ID ,
host_id,
host_name,
hoststatus,
id,
sla,
transaction_name,
location_name,
t_status,
response_time,
transaction_id,
date_Time
FROM mv;
END get_mv;
I have tried with bitmap index
create bitmap index ind_mv_app_tx_sr on mv_app_tx_combin_status_test(APP_NAME,APP_ID,APP_STATE_DT,ALERT_DT,ALERT_TYPE)
and queried
select * from mv where APP_NAME='hdguadd' and APP_ID=1234
It is still reducing the performance compared to the earlier.
Please tell me if any other things needed.
Regards,
Pointers.
|
|
|
Re: Materialized view [message #344340 is a reply to message #344278] |
Fri, 29 August 2008 07:53   |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
My question is, if I query directly on the materialized view it is taking 1 min. Bu if I use procedure with refcurosr, it is taking 3 mints(this procedure also query on the materialized view). How to reduce this time.
Regards,
Pointers.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 12:12:12 CST 2025
|