1 2 3 Previous Next 34 Replies Latest reply on Jul 15, 2018 11:58 AM by William Robertson

    data in a table stored in memory for faster access to them

    francy77

      Hi all,

      I want to store in memory data that are in a table; Try to explain better:

       

      the result set of this query:

       

      select field1,

             filed2,

             filed3  

      from table t

       

      I should want to store the result in a varray or associative array, in order to have the result set in memory;

       

      and after given a couple (field1,field2) getting the value of field field3;

       

      Which one is the better way to accomplish that?

       

       

      thanks really much.

        • 1. Re: data in a table stored in memory for faster access to them
          John Thorton

          francy77 wrote:

           

          Hi all,

          I want to store in memory data that are in a table; Try to explain better:

           

          the result set of this query:

           

          select field1,

          filed2,

          filed3

          from table t

           

          I should want to store the result in a varray or associative array, in order to have the result set in memory;

           

          and after given a couple (field1,field2) getting the value of field field3;

           

          Which one is the better way to accomplish that?

           

           

          thanks really much.

          Above is classic example of Worst Practice.

           

          Do  NOT do in PL/SQL that which can be done  in plain SQL!

           

          When you start with the wrong question, no matter how good an answer you get, it won't matter very much.

           

          The underlying premise is flawed & incorrect.

           

          What happens when other session changes the content of the table & your array now contain old & incorrect values?

          1 person found this helpful
          • 2. Re: data in a table stored in memory for faster access to them
            Stew Ashton

            Hello,

             

            You are speaking of three different things at the same time:

            1. A requirement for faster access to data (but faster than what?)
            2. A general solution: "store" in memory.
            3. A specific solution: copy the data to a PL/SQL array.

             

            Please don't confuse these three things.

             

            Now, i'm not sure why data in a table is not "fast enough" for you. If there is a lot of data, with the right indexes you can access specific rows very fast.

             

            Also, there are many ways to have data "in memory".

            • If you are accessing small amounts of data it will always be in memory inside a data block.
            • You can also use different "result caches" to keep some result sets in memory for the next SELECT.
            • You could also use the "In Memory" option of the Extended Edition, if your database version is recent enough.

             

            I would consider all these options before doing your specific "solution," which as John says doesn't work if you don't take changes into account.

             

            Best regards,

            Stew Ashton

            1 person found this helpful
            • 3. Re: data in a table stored in memory for faster access to them
              Stew Ashton

              Perhaps a supported and working method would be the PL/SQL result cache. I'll create a demo function.

              • The first time it executes, it will query the table T.
              • The second time, it will not query the table but return the cached result. We can see that because the number of executions of the query remains 1.
              • Afterwards, I change the underlying data. The function will then automatically query the table again (the execution count goes up) and it will return the new and correct data.

              SQL> create table t(

                2    field1 number,

                3    field2 number,

                4    field3 number

                5  );

               

              SQL> insert into t values(1,2,3);

               

              SQL> create or replace function f(p_field1 in number, p_field2 in number)

                2    return number result_cache relies_on(t) is

                3    l_field3 number;

                4  begin

                5    select field3 into l_field3 from t

                6    where field2 = p_field2 and field1 = p_field1;

                7    return l_field3;

                8  end;

                9  /

               

              SQL> alter system flush shared_pool;

               

              SQL> select f(1,2) from dual;

               

                  F(1,2)

              ----------

                       3

               

              SQL> select executions from v$sql

                2  where sql_text like 'SELECT FIELD3 FROM T%';

               

              EXECUTIONS

              ----------

                       1

               

              SQL> select f(1,2) from dual;

               

                  F(1,2)

              ----------

                       3

               

              SQL> select executions from v$sql

                2  where sql_text like 'SELECT FIELD3 FROM T%';

               

              EXECUTIONS

              ----------

                       1 -->> THE NUMBER OF EXECUTIONS DID NOT GO UP

               

              SQL> update t set field3 = 4;

               

              SQL> select f(1,2) from dual;

               

                  F(1,2)

              ----------

                       4

               

              SQL> select executions from v$sql

                2  where sql_text like 'SELECT FIELD3 FROM T%';

               

              EXECUTIONS

              ----------

                       2 -->> NOW THE NUMBER OF EXECUTIONS WENT UP

               

              I think this solution corresponds most closely to what you want - although I am not convinced you need to do it.

               

              Best regards,

              Stew

              1 person found this helpful
              • 4. Re: data in a table stored in memory for faster access to them
                John Thorton

                Stew Ashton wrote:

                 

                Perhaps a supported and working method would be the PL/SQL result cache. I'll create a demo function.

                • The first time it executes, it will query the table T.
                • The second time, it will not query the table but return the cached result. We can see that because the number of executions of the query remains 1.
                • Afterwards, I change the underlying data. The function will then automatically query the table again (the execution count goes up) and it will return the new and correct data.

                SQL> create table t(

                2 field1 number,

                3 field2 number,

                4 field3 number

                5 );

                 

                SQL> insert into t values(1,2,3);

                 

                SQL> create or replace function f(p_field1 in number, p_field2 in number)

                2 return number result_cache relies_on(t) is

                3 l_field3 number;

                4 begin

                5 select field3 into l_field3 from t

                6 where field2 = p_field2 and field1 = p_field1;

                7 return l_field3;

                8 end;

                9 /

                 

                SQL> alter system flush shared_pool;

                 

                SQL> select f(1,2) from dual;

                 

                F(1,2)

                ----------

                3

                 

                SQL> select executions from v$sql

                2 where sql_text like 'SELECT FIELD3 FROM T%';

                 

                EXECUTIONS

                ----------

                1

                 

                SQL> select f(1,2) from dual;

                 

                F(1,2)

                ----------

                3

                 

                SQL> select executions from v$sql

                2 where sql_text like 'SELECT FIELD3 FROM T%';

                 

                EXECUTIONS

                ----------

                1 -->> THE NUMBER OF EXECUTIONS DID NOT GO UP

                 

                SQL> update t set field3 = 4;

                 

                SQL> select f(1,2) from dual;

                 

                F(1,2)

                ----------

                4

                 

                SQL> select executions from v$sql

                2 where sql_text like 'SELECT FIELD3 FROM T%';

                 

                EXECUTIONS

                ----------

                2 -->> NOW THE NUMBER OF EXECUTIONS WENT UP

                 

                I think this solution corresponds most closely to what you want - although I am not convinced you need to do it.

                 

                Best regards,

                Stew

                yes, but is it faster than only using plain SQL?

                • 5. Re: data in a table stored in memory for faster access to them
                  Stew Ashton

                  Did you read the part where I said "I am not convinced you need to do it" ?

                   

                  I submit that with a correct solution we can better discuss whether the idea is worthwhile.

                   

                  There is no guarantee it will be faster, but it can often be.

                   

                  Best regards,

                  Stew

                  • 6. Re: data in a table stored in memory for faster access to them
                    rp0428

                    I submit that with a correct solution we can better discuss whether the idea is worthwhile.

                    But John submits, and I agree, that there can NOT be a 'correct solution' without knowing the 'correct requirement'.

                    • 7. Re: data in a table stored in memory for faster access to them
                      Stew Ashton

                      rp0428 wrote:

                       

                      I submit that with a correct solution we can better discuss whether the idea is worthwhile.

                      But John submits, and I agree, that there can NOT be a 'correct solution' without knowing the 'correct requirement'.

                      That is a gratuitous remark. As I stated clearly myself, the OP mentioned a "requirement" and a "solution". We all have agreed from the beginning about the "requirement", so there is no need to place me in one camp and John and yourself in another.

                       

                      As far as the "solution" is concerned, there is a correct way to implement it.

                       

                      When I said "correct" I meant a correct way to implement the solution that was mentioned. I explicitly doubted that it was appropriate to whatever the requirement really is. In order to misinterpret me as you did, you had to ignore most of what I have written so far.

                       

                      Regards,

                      Stew

                      • 8. Re: data in a table stored in memory for faster access to them
                        francy77

                        I'm sorry i was unclear;

                        I should want to store the result of a query in a collection; in particular a associative array;

                        I declare a type as i

                        TYPE assoc_array IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);

                        var assoc_array;

                        I want to store the result of that query in var; without cycling to the result set and inserting item after Item;

                        I want to populate the collection at one time;

                        Besides I want that (field1,field2) will be the key of this associative array.

                         

                        thanks really much

                        • 9. Re: data in a table stored in memory for faster access to them
                          rp0428

                          No one is placing anyone in camps.

                          the OP mentioned a "requirement" and a "solution". We all have agreed from the beginning about the "requirement",

                          Talk about being gratuitous!

                           

                          We have NOT all agreed on any such thing. OP so far has still NOT provided the actual requirements - all they talk about is the solution that they want to use.

                           

                          OP hasn't provided ANY info about the actual problem to be solved - only saying 'this is the solution I want to use please help me use it'.

                           

                          I, for one, certainly don't consider that a requirement.

                          • 10. Re: data in a table stored in memory for faster access to them
                            rp0428

                            I should want to store the result of a query in a collection; in particular a associative array;

                            And we 'should want' that you actually tell us WHAT PROBLEM are you trying to solve and why you think using collections is the best way  to solve it.

                             

                            Besides I want that (field1,field2) will be the key of this associative array.

                            Then you won't be using Oracle because that functionality does NOT exist.

                             

                            Please read the Oracle docs about:

                             

                            1. the functionality that DOES exist

                            2. what that functionality does

                            3. how to use it

                            4. working examples that illustrate its use

                            https://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS005

                            1 person found this helpful
                            • 11. Re: data in a table stored in memory for faster access to them
                              Billy~Verreynne

                              francy77 wrote:

                               

                              I'm sorry i was unclear;

                              I should want to store the result of a query in a collection; in particular a associative array;

                              I declare a type as i

                              TYPE assoc_array IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);

                              var assoc_array;

                              I want to store the result of that query in var; without cycling to the result set and inserting item after Item;

                              I want to populate the collection at one time;

                              Besides I want that (field1,field2) will be the key of this associative array.

                               

                              thanks really much

                               

                              Still does not clarify why you need/want to do this. It is not a scalable approach.

                               

                              The associative array is stored in very expensive private server memory. The more you cache in this array, the larger the cost in server memory. If 10 app/user sessions use your code, each one of them will have an associative array in server memory. A 100 sessions? A 100 associative arrays in server memory. Many likely being identical copies of one another.

                               

                              Another session commits a transaction and changes database data, and your 100 associative arrays for a 100 sessions now contain stale data - and base their session processing on it. So much for data integrity, and robust and sound business processing.

                               

                              The database already caches "hot" data in memory - shared server memory. Meaning this memory cache, and its contents, are available to all sessions.

                               

                              Why do you want to reinvent the database's buffer cache using associative arrays? Why not use the database, and SQL language, for what it is designed to do - crunch data volumes in a very effective, efficient, and scalable way?

                               

                              And no - you cannot perform a  single bulk cursor fetch directly into an associative array.

                              1 person found this helpful
                              • 12. Re: data in a table stored in memory for faster access to them
                                Stew Ashton

                                rp0428 wrote:

                                 

                                No one is placing anyone in camps.

                                the OP mentioned a "requirement" and a "solution". We all have agreed from the beginning about the "requirement",

                                Talk about being gratuitous!

                                 

                                We have NOT all agreed on any such thing. OP so far has still NOT provided the actual requirements - all they talk about is the solution that they want to use.

                                 

                                OP hasn't provided ANY info about the actual problem to be solved - only saying 'this is the solution I want to use please help me use it'.

                                 

                                I, for one, certainly don't consider that a requirement.

                                Again, you misinterpret me strangely. I meant we agreed that the "requirement" was not adequately stated! Good grief.

                                 

                                Regards,

                                Stew

                                • 13. Re: data in a table stored in memory for faster access to them
                                  Stew Ashton

                                  francy77 wrote:

                                   

                                  I'm sorry i was unclear;

                                  I should want to store the result of a query in a collection; in particular a associative array;

                                  ...

                                  As others have explained brilliantly:

                                   

                                  You can't do that.

                                   

                                  You shouldn't want to do that. If you could, it would be a horrible idea.

                                   

                                  Why do you still think this is an idea worth pursuing?

                                   

                                  What is wrong with just using SQL against a table?

                                   

                                  Regards,

                                  Stew

                                   

                                  P.S. I mentioned the function result cache earlier: it cannot be used to return a collection, so it won't do what you say you want.

                                  1 person found this helpful
                                  • 14. Re: data in a table stored in memory for faster access to them
                                    francy77

                                    There are no more than one session at time (so there is not possible to have at same time 100 varray that hold all the same data);

                                    I want (or better i have to because my boss wants) to use an associative array;

                                    I think that having the data in an associative array is faster because every time i need to retrieve a value i do not have to access to disk (i have them im memory RAM),

                                    is that true?

                                    1 2 3 Previous Next