4 Replies Latest reply: May 4, 2012 1:36 AM by 934440 RSS

    Comparing PLSQL table value with Actual DB values

    934440
      Hi,
      I have a set of values in my PLSQL table and i need to loop through each record to ensure its existence in the database. Looping through each record and checking the values in DB consumes more time for execution. Is there any other alternatives to accomplish this task which executes faster??

      Thanks in Advance
        • 1. Re: Comparing PLSQL table value with Actual DB values
          Billy~Verreynne
          There is no such concept as "+PL/SQL tables+" - especially in a RDBMS environment where the term table has a very specific meaning.

          The correct term is array/collection or associative array. And these are the terms also used in C/C++, Pascal, Visual Basic, Java and other programming languages.

          As for the approach of chucking data into a PL/SQL array and looping through it, hitting the SQL engine (sometimes repeatedly) per loop iteration - of course that will be slow.

          It is known as slow-by-slow processing.

          Why is your data inside PL/SQL arrays? How did it get there?

          The best place for data in a database environment like Oracle, is inside database tables. And not some primitive array structure that resides in expensive private process memory on the server.

          The fastest method to crunch data in Oracle, is using the very powerful and scalable SQL language only. Not PL/SQL.

          These are the basic answers to your issue (which you failed to define technically and provide the Oracle version for).

          Also, what has your question to do with Oracle RAC? This seems to be a basic conceptual issue of how to use SQL, and how to use PL/SQL, in optimal fashion. Which means you should be raising your issue in the {forum:id=75} forum and not here.
          • 2. Re: Comparing PLSQL table value with Actual DB values
            934440
            My PLSQL Arrays are populated by fetching values from a uploaded file through the application. that and all are getting loaded in seconds even the uploaded files have got 5000 and above lines, i am again looping through the loaded PLSQL arrays and validating each value against DB value. Is there any alternatives to do this comparison against DB instead of looping through each and every record.
            • 3. Re: Comparing PLSQL table value with Actual DB values
              Billy~Verreynne
              user5350058 wrote:
              My PLSQL Arrays are populated by fetching values from a uploaded file through the application. that and all are getting loaded in seconds even the uploaded files have got 5000 and above lines, i am again looping through the loaded PLSQL arrays and validating each value against DB value. Is there any alternatives to do this comparison against DB instead of looping through each and every record.
              Use a GTT (Global Temporary Table) for the uploaded data. Scales better, supports proper SQL data structures and indexing, has no impact on expensive PGA memory, and can be use in native SQL to populate the target table. And SQL is by far superior in this regard than PL/SQL.

              A GTT is like an object class definition. It is defined once and up front. The 1st DML statements against it in a session, creates the local GTT for that session. Kind of like a constructor instantiating an instance of that object class. At the end of the session, the GTT for that session is destroy.

              PL/SQL is invariable row-by-row processing (a fact that bulk processing does not change - it simple introduces a bigger pipe for row-by-row data transfer). The SQL language is superior. It is the most powerful and flexible language for processing database data.

              Which is why it is the first choice for performance - PL/SQL will not give you the same performance than what native SQL can. PL/SQL should only be considered when the processing complexity exceeds the ability of the SQL language. Which is 99.99% of the time, not the case.