Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view (oracle 9i)
Materialized view [message #344278] Fri, 29 August 2008 05:25 Go to next message
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 #344287 is a reply to message #344278] Fri, 29 August 2008 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The suggestion is that you post your code.

Regards
Michel
Re: Materialized view [message #344336 is a reply to message #344278] Fri, 29 August 2008 07:36 Go to previous messageGo to next message
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.
select * from mv
(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 Go to previous messageGo to next message
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.
Re: Materialized view [message #344407 is a reply to message #344340] Fri, 29 August 2008 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Now post the code that query the ref cursor.

Regards
Michel
Re: Materialized view [message #344760 is a reply to message #344278] Mon, 01 September 2008 03:55 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Sorry... I dont have that code.........I got only this much information.
Re: Materialized view [message #344766 is a reply to message #344760] Mon, 01 September 2008 04:01 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So we can't answer.

Regards
Michel
Previous Topic: Partition
Next Topic: Can we pass OUT parameter in a function ?
Goto Forum:
  


Current Time: Sat Feb 15 12:12:12 CST 2025