Hello, my portlet query is very complex with lots of INNER and OUTER JOINS and its pulling a lot of data which takes up forever to run.

I would like to run my portlet on top of a materialized view to fasten the processing time.

Since I never did this before, I am looking for some guidance from senior members.

Here are some of the @SELECT statements. How would I access these thru a view and how complex is this process?

SELECT

@SELECT:DIM:USER_DEF:IMPLIED:PROJECT:Project_CODE:Project_CODE@

,@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:Project_ID_Browser:Project_ID_Browser@

,@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:Resource_Filter: Resource_Filter@

If you could provide an example, it would be great.

Sorry to say not being a techie I don't off hand know what a materialized view is.

Sounds as if you are after a way of simplifying the NSQL. Typically that would be done put the complex standard SQL inside an wrapper so the the main NSQL query becomes very simple.

There is an example in

Multi Table portlet

you can replace the

(select *

from odf_CA_zObject) z

inside the wrapper with anything that will return standard data types (not blobs) like queries from views and functions, but not stored procedures.

1. Create a materialized view which will get the data from the desired tables and fill it in the staging table

2. Create a job that will run at a specified time and fill the data in the materialized view

3. Create the portlet using the staging table (referenced in 1)

Check this as well -

How to report against historical Clarity data?

NJ

Thanks for your response to my query,  I did exactly what you mentioned. I have a follow up question though.

Portlet runs super fast if I reference ONLY the staging table without below given parameter.

When I try to add in below given parameter and one more join shown below, portlet takes forever to load. I join this code with my staging table so I can pull out TEAM_V2.REQUIREMENT_NAME

LEFT OUTER JOIN ODF_TEAM_V2  TEAM_V2 ON (TEAM_V2.PRPROJECTID = TAB.PROJECT_ID_BROWSER AND TEAM_V2.INVESTMENT_TYPE = TAB.ODF_OBJECT_CODE)

I have a 'where' clause in my NSQL which is filtering on basis of TEAM_V2.REQUIREMENT_NAME  and I really need this where clause.

.

Here's my where clause:

WHERE

TEAM_V2.REQUIREMENT_NAME IN

      (

SELECT DISTINCT S.FULL_NAME REQUIREMENT_NAME  FROM PRTEAM PR

INNER JOIN SRM_RESOURCES S ON S.ID = PR.PRRESOURCEID

INNER JOIN SRM_RESOURCES R ON PR.PRROLEID = R.ID

WHERE R.ID IN (5202003, 5202009, 5202012, 5202025, 5004026)

AND  S.FULL_NAME = @NVL@ ( @WHERE:PARAM:USER_DEF:STRING:RESOURCE_SELECT@ ,S.FULL_NAME)

      )

I cannot print out TEAM_V2.REQUIREMENT_NAME in SELECT clause because it starts giving multiple records / duplicate dimension error

I just need to filter on its basis.

Now since I created view and staging table, i cannot access TEAM_V2.REQUIREMENT_NAME anymore and I am trying to join this table with staging table which is causing portlet to die out.

You can also put an oracle optimizer hint and see if the performance is better

e.g., /*+ optimizer_features_enable('10.1.0') */

NJ

For - "portlet to die out" - can u get the AWR checked once the portlet is run ?

May be the dba would have a better solution for the query performance

NJ