6 Replies Latest reply: Feb 23, 2013 2:23 AM by Billy~Verreynne RSS

    WITH clause Vs PL/SQL tables

    972228
      Hi,

      I am creating a stored proc for and my sqls will be returning recrods to the tune of 14 million to 9 million records.
      I have option of using WITH clause or PL/SQL tables.
      I dont have to use any indexes as logic is not looking for any specific indexed records.

      Will like to know which to use in which scenario? What are the internals of using these like memory management, possibilities of disk swaps etc.

      Thanks,
      Avinash.
        • 1. Re: WITH clause Vs PL/SQL tables
          sb92075
          969225 wrote:
          Hi,

          I am creating a stored proc for and my sqls will be returning recrods to the tune of 14 million to 9 million records.
          I have option of using WITH clause or PL/SQL tables.
          I dont have to use any indexes as logic is not looking for any specific indexed records.

          Will like to know which to use in which scenario? What are the internals of using these like memory management, possibilities of disk swaps etc.

          Thanks,
          Avinash.
          PL/SQL will never be faster than plain SQL.
          • 2. Re: WITH clause Vs PL/SQL tables
            972228
            Agreed.

            But isn't it that even WITH clause will be creating internal tables similar to pl/sql tables?

            Or any thumb rule like:
            If query is going to return huge amount of rows then better go for pl/sql tables rather than WITH clause?
            • 3. Re: WITH clause Vs PL/SQL tables
              Solomon Yakobson
              969225 wrote:
              But isn't it that even WITH clause will be creating internal tables similar to pl/sql tables?
              No, it does not. I believe you are referring to materializing WITH clause. First of all not avery WITH clause will be materialized by optimizer. Optimizer can decide that merging WITH clause results in a better plan. And when it is, it results in a temp table comparing to always in-memory nested table.

              SY.
              • 4. Re: WITH clause Vs PL/SQL tables
                6363
                969225 wrote:

                Or any thumb rule like:
                If query is going to return huge amount of rows then better go for pl/sql tables rather than WITH clause?
                Aside from the fact that rules of thumb are mostly useless, what exactly do you intend to do that you are comparing a PL/SQL array (not a table) to a factored sub query (WITH)? The two are in no way comparable. You are not thinking of putting the result in a PL/SQL array are you? If so that would be a really bad way of doing it and has no equivalent for using a factored sub query. Or are you using PL/SQL array vs. WITH during the process? In which case WITH would be preferred.

                The one rule of thumb I can think of that is not useless is that if you are using PL/SQL arrays you are probably doing it wrong and certainly doing it less efficiently.
                • 5. Re: WITH clause Vs PL/SQL tables
                  rp0428
                  >
                  I am creating a stored proc for and my sqls will be returning recrods to the tune of 14 million to 9 million records.
                  >
                  Why are you creating a proc? What does the proc do? Why do you need it?

                  'returning records' - returning to who? Are these being returned to a client application of some sort? What is the client going to do with millions of rows?
                  >
                  I have option of using WITH clause or PL/SQL tables.
                  >
                  Why are those your options? Who gave them to you? Why can't you use a REF CURSOR to return the data to a client? That is normally how it would be done. Or why not use a PIPELINED function?
                  >
                  Will like to know which to use in which scenario? What are the internals of using these like memory management, possibilities of disk swaps etc.
                  >
                  Why don't you quit focusing on what you think the solution should be and tell us what the problem is that you are trying to solve.
                  • 6. Re: WITH clause Vs PL/SQL tables
                    Billy~Verreynne
                    969225 wrote:
                    Agreed.

                    But isn't it that even WITH clause will be creating internal tables similar to pl/sql tables?

                    Or any thumb rule like:
                    If query is going to return huge amount of rows then better go for pl/sql tables rather than WITH clause?
                    A query returning millions of rows is extremely unusual. Why would you want to take millions of rows from the database and send it to a client. What for? To satisfy which requirement? And assuming this does in fact need to be done, it will be slow - as sending millions of rows via packets with a max size of 1500 bytes, using a heavy protocol like TCP is slow.

                    A query processing millions of rows and sending a couple of resulting rows to a client, is common (typical BI query). As is a query processing millions of rows and storing the results of that in another table (typical data warehouse query).

                    A query's response time is determined by the workload it has. The major contributor to that workload is I/O. I/O is slow. That means it is expensive. I/O is addressed by things like indexes (getting to data of interest via a short I/O path). Doing "faster" I/O such as multi-block reads for hitting large chunks of data as oppose to single-block reads. Or splitting that I/O into subunits and doing these in parallel. Etc.

                    This has nothing to do with so-called PL/SQL "tables" - a misunderstood feature that is incorrectly named as there are not such thing as SQL like tables in PL/SQL.

                    This however has everything to do with
                    a) proper data modelling
                    b) well designed physical data model
                    c) optimal query design

                    There is no magical silver bullet or rule-of-thumb that automagically decreases the workload and increases performance. Nor is performance something that can be addressed after the data model has been implemented with conjuring tricks (like PL/SQL "tables").

                    Performance is part and parcel of the design - as that determines the performance boundaries and scalability of processes using that data model.

                    Oracle itself is perfectly able to provide stellar performance - if the data model, and processes using it, allows it to.