View question

[datamart]

Hi,
I currently have a summary table that is created in a very complex manner. This summary table consists of data for each and every user and his security access. The front end queries this table with filter conditions such as userid, and other components.
This table is running into a lot of issues related to space. Also, this table usually table about 10-15 mins to get populated.
We are planning some other methods to achieve results.

What we thought of was creating a VIEW using the sql used to build the summary table.

CREATE VIEW v_AGG_TABLE AS (
aggregate table sql
)

The front end uses this view to query data. The front end uses the same conditions as before.

SELECT COL1, COL2,..
FROM v_AGG_TABLE
WHERE USERID = ABC AND ...

My question is,
1. will v_agg_table first execute and then the condition for userid be applied
2. is there a better way by which i can obtain results.

thanks.

 

[Turkbear]

Hi,
If I understand what you are thinking of doing, it will be slower and less efficient than your current way..

A view is just, in essence, Sql code that is executed on demand so , each time you query it, it will need to 'recreate' the summary table..Your current method creates it only once and the query accesses this...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Beilstwh]

Why don't you paste your query here. I have found over the years, that there is almost always a way to tune and speed up a query.

Bill
Oracle DBA/Developer
New York State, USA