I have a bit complex oracle view which i use to generate live report. Since all the rows of deriving table is pulled, there is little scope of improvement either by indexing or partitioning.
I wanted to know if I can anyway use pipelined table function to improve the performance of my report.
This came to my mind because I am not sure how exactly the SQL works. Does the queries waits untill all rows from the tables are fetched or it works like a pipelined table function in a way as soon a row is fetched from underlying table it is processed.
A SQL query returns rows as soon as they are available. Depending on the optimizer query plan this may be "immediately" or there may be a significant delay while records are gathered and sorted, etc.
Using a pipelined function cannot improve this performance, because the pipelined function has to first get some data back from a query and then start piping it out. It will not get the data from the query any faster than you will.