#1. Never use ORDER BY on large portion of data from pipelined table function...
Oracle known nothing about indexes that can be used to sort this data. You should use ORDER BY inside the function and pipe already sorted rows.
#2. Join with local table makes faster selects from remote table (over a dblink) in pipelined table functions.
My configuration:
- database L - local Oracle 10g
- database R - remote Oracle 11g
- in database L exists dbLink to database R
A. Test with subquery on remote table
SQL: select count(*) from (select * from dtab@L)
Execution: fast
B. Test with subquery placed in pipelined table function "pip_fun"
SQL: select count(*) from TABLE( pip_fun )
-- where pipelined table function returns rows: select * from dtab@L
Execution: slow
C. B + join with local table
SQL: select count(*) from TABLE( pip_fun )
-- where pipelined table function returns rows: select * from
dual, dtab@L
Execution: fast
Conclusion: pipelined functions are optimized to read data row by row, but "join" force Oracle to do fast copy the entire remote table to local database?
#3. Using pipelined table function over a dblink
626100