Skip to Main Content

SQL & PL/SQL

Pipelined Stored Procedure optimization

brilliantMay 8 2019 — edited May 10 2019

This has probably been beaten to death but my search yields many answers.

Wondering if someone can point me to a decent resource on accomplishing the below goals

1. Returning a large table from complex query (5k-2.5M) records from 11M records resultset of the complex query

2. Applying Row Level Security (preferably from stored procedure) for best performance and best security to a highly sensitive set of data

Questions,

1. Do I need a Table Type, Row Type, and Function or Package to return this efficiently?

2. I have resources online saying BULKCOLLECT (performance issue in shared environment), REF CURSOR, SYS REF CURSOR. Which way to go?

3. Should I run in PARALLEL hint or any others?

Any links or syntax help is appreciated

Thanks,

Comments
Post Details
Added on May 8 2019
24 comments
388 views