Please enable JavaScript.

Coggle requires JavaScript to display documents.

MyNewLearning (Pl/Sql (Variables (Collections (Assicative array (AA)…

- - - - %ROWTYPE
        
        syntax:
        rec_name Table|Cursor%ROWTYPE;
        mycomment: one record can be saved in this variable
        
        example:
        declare
        my_rc bsscpf%ROWTYPE;
        begin
        select * into my_rc from bsscpf fetch 1 rows only;
        end;
      - User defined
        
        syntax:
        type my_t is record (field_name filed_data_type, filed 2...);
        rc my_t;
        
        example:
        declare
        type my_t is record ( bn varchar2(10), un unique_number bsscpf.unique_number%type); -- unique_number%type, must follow with %type and varchar2( initialization number) is needed.
        my_r my_t;
        begin
        select bschednam, unique_number into my_r from bsscpf fetch 1 rows only;
        end;
    - - Assicative array (AA)
        
        syntax:
        type my_aar is table of datatype index by binary_integer;
        v_aar my_aar;
        
        index by binary_integer
        
        index by varchar2(20): TO_DO
        
        bulk collect
        
        variable type associative array of one column of a table:
        type my_t is table of bsscpf.bschednam%type index by binary_integer;
        Then for the bulk collect we can do:
        select bschednam into my_v from bsscpf fetch first n rows only;
        
        variable type with multiple columns of a table:
        For this use Two Dimensional Associative Array
        
        use limit to control the PGA.
        Important Point: In the Loop of bulk collect with limit, exit when the collection count is 0 rather than using notfound:
        E.g:
        FETCH cur BULK COLLECT INTO many_rows LIMIT 100;
        EXIT WHEN many_rows.COUNT () = 0;
        --.... rest of code ommited...
        Another Point: Limit as 100 is good enough in most cases. If we process millions records then it can go up like 10000..
        Good article
        https://livesql.oracle.com/apex/livesql/file/tutorial_IEHP37S6LTWIIDQIR436SJ59L.html
        
        Two Dimensional Associative Array(2DAA)
        
        syntax:
        type my_t is table of record_type index by binary_integer;
        
        example of associative array of all columns of a table.
        
        associative array of two column of a table:
        The idea is declare a record of user defined type with the 2 columns of the table. Then declare associative array 2-D with table of this record type. Example:
        declare
        type m_t is record (
        a bsscpf.bschednam%type,
        b bsscpf.unqiue_number%type );
        type m2d_aar is table of m_t index by binary_integer;
        m_v m2d_aar;
        
        The most efficient way to pass collections to and from the database server is to set up data values in associative arrays, then use those associative arrays with bulk constructs (the FORALL statement or BULK COLLECT clause).
        https://docs.oracle.com/cd/B14117_01/appdev.101/b10807/05_colls.htm
        
        Code tried successfully to send the 2DAA in SP parameter.
        
        20201227: AssociativeArray, was known as index-table prior of Oracle 9i. This is basically one dimensional data. Generally two column tables. First column is index seconds column is homogeneous values. The minimal use case I can think of this AA is for having lookup type of information. Which can be sent from Java as well, using jdbc#setPlsqlIndexTable. But beyond that it has no major advantage like join with tables not possible with AA. There NT wins.
      - Nested Tables (NT)
        
        similar to associated arrays; except
        (A) no "index by".
        (B) not initialized by default so, necessary to initialize
        (C) and necessary to extend
        (D) two NT can be compared, but not AA
        (E) mulitset operators possible on NT not on AA
        (F) delete of elements possible from NT
        (G) NT can be stored in db columns but AA cannot.
        
        Multiset Operations:
        Multiset Union
        Multiset Intersect
      - VARRAY
- - - - Manual PGA Setting
        
        The parameters having largest impact on the PGA size
        
        SORT_AREA_SIZE (SAS)
        
        SORT_AREA_RETAINED_SIZE (SARS)
        
        HASH_AREA_SIZE (HAS)
        
        SAS: The amount of RAM used for sort, before it starts swapping to temporary tablespace.
        
        SARS: The amount of sorted data retained in RAM
        
        HRS: The hashjoins sets are stored in memory. The smallest hash set is stored and other hash set is stored in temporary tablespace.
        
        Find the current use of PGA (+UGA)
        
        A table without index queried with order by will sort
      - Automatic PGA setting
        
        PGA_AGGREGATE_TARGET this value sets the soft limit of the PGA RAM for Oracle instance.
        
        The automatic PGA memory management was designed for multiuser "fair". If we have only one session like night batch, in those cases we can take control and change the SAS|HAS. As reining in with these limits will cause I/O on temporary tablespace