Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query that uses around 6 or 7 tables and uses pivot to return a formatted string from this entire structure. Actually, today this query is dynamically generated via C#/Entity Framework and it takes at least one minute to process and return 10 rows.

We were looking for alternatives so we can improve the performance of this query, and we found the virtual columns. We could not find any benchmark comparing virtual columns and view so we can argue with our client which option we'll choose.

If we opt for virtual columns, will it run automatically the function linked to it? Or it needs to be queried to have its value "filled"?

share|improve this question
    
Maybe use a trigger to fill a new column with the desired data? –  Mihai Mar 14 at 13:42
    
I'm not sure I understand your questions. Virtual columns are, well, virtual. They're not stored in the data blocks. So there's nothing to "fill". –  Mat Mar 14 at 14:00
    
Let me explain better. I've read that these virtual columns can be indexed by Oracle, so my question is, when the value of these columns are evaluated and indexed? If Oracle evaluates it automatically for indexing, great. If Oracle evaluates the function linked to this virtual column row by row when a query is selecting its value, the performance may be worse than using views. Is it clear now? –  Juliano Nunes Silva Oliveira Mar 14 at 14:10
    
Yes, you can index virtual columns. They are not automagically indexed. –  ypercube Mar 14 at 14:15
    
Then I can assume that using a View will be way faster, right? –  Juliano Nunes Silva Oliveira Mar 14 at 15:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.