Re: Views based on function returning table

Using bulk collect is definitely useful, but doesn't answer the question of whether to use views based on functions returning collections. Most, if not any, code where you loop through a cursor could be sped up by using bulk collect. Basing a view on a function that returns a collection has advantages, as pointed out, but that in itself isn't reason to use it.

I find in coding that it's best to write code as simple as possible while meeting the requirements. It's always best to code for performance, so bulk collect yes. You may wish to add levels of security in the future, so don't give direct access to tables. Instead, access data through views, but the view only needs to be a basic view until there is a requirement that calls for a view based on a function that returns a collection. I beleive you're talking about pipelined table functions that pipe rows.

I've seen too many projects that become overly complex for no good reason other than hype. I might add that in those overly complex projects often there is poorly written SQL. Too much effort is poured into the flavor of the week and not enough into good SQL and performance planning. As the saying goes - if the shoe fits wear it. Translation - if the technique fits your needs use it, otherwise don't buy into shoes that don't fit.

My 2 cents,
Ray