Dynamic Row Adding and Inserting using JSON

Objective: To achieve, Dynamically adding or removing parent rows and include or exclude ‘n’ number child rows for the particular parent in the form.

  

Apex Steps:

1. Create HTML table structure using HTML tags in header and footer section.       

2. First button Add New Parent will be appear. Using this button can add new parent Row row with text fields, buttons.

/*Add Parent Button ID - addrisk*/

$("#addrisk").click(function(){

$("#removeallrows").show();

$("#SUBMIT_BTN").show();$("#customFields").last().append('<tr><th><label for="Risk" id="labid">Parent</label></th><td><input type="text" class="code" id="customFieldValue" name="customFieldValue[]" value="" placeholder="Input Value" size="20"/> &nbsp;&nbsp;<button id = "adderror"  href="javascript:void(0);" class="t-Button t-Button--hot js-ignoreChange custom_addbtn">Add Child</button> &nbsp;<button href="javascript:void(0);" id = "remrisk" class="t-Button t-Button--hot js-ignoreChange custom_rembtn">Remove Parent</button></td></tr>');

return false;

}); 

3. After adding parent row, It contain buttons Remove All Risks, Add Child, Remove Parent.

/* Remove Parent Button ID - remrisk */

$("#customFields").on('click','#remrisk',function(){

var rowCount = $('#customFields td').length;

$(this).parent().prev().remove();

$(this).parent().remove();

    if (rowCount == 1)

        {

         $("#removeallrows").hide();

         $("#SUBMIT_BTN").hide();

        }

return false;

});

4. Button Remove all Risks can able to remove all the risks which is added earlier. After removing all risks button Add new Parent only will be there.

5. Using button Add Child can able to add new child row with text fields, buttons by appending into the parent.

/* Add Child button ID- adderror*/ 

$("#customFields").on('click','#adderror',function(){

    $(this).parent().append('<tr valign="top"><td><input type="text" class="code" id="customFieldValue" name="customFieldValue[]"value="" placeholder="Input Value" size="20" /> &nbsp;<button href="javascript:void(0);" id = "remove_error" class="t-Button t-Button--hot js-ignoreChange custom_rembtn padding: 10px 50px;">Remove child</button></td><tr>');return false;

    });

6. Button Remove Child can able to remove current child row. 

/* Remove child button ID - remove_error */

$("#customFields").on('click','#remove_error',function(){

$(this).parent().parent().remove();

});

/* Remove all rows button ID - removeallrows */

$("#removeallrows").click(function(){        

$("#customFields tr").remove();    

    $("#removeallrows").hide();

    $("#SUBMIT_BTN").hide();

return false;    

});

8. Save the data when submit by get data from the form/table and put it into

an array and set into APEX ITEM.

var ar = [];

$("#customFields tr .nr").each(function(){

alert('trrr');

var v = $(this).val();

ar.push(v);

console.log(ar);

var el = $("#labid").text();

console.log(el);

});

var myJSON = JSON.stringify(ar);

$s('P0_TAB_VAL', myJSON);

9. Then convert into JSON string from APEX ITEM and insert into Oracle table.

declare

   v_cnt number;

   v_error varchar2(4000);

   begin

   

   APEX_JSON.parse(:P0_TAB_VAL);

   

   for i in 1..APEX_JSON.get_count('.')

   LOOP

   myjson1 := APEX_JSON.GET_VARCHAR2(p_path=> '[%0]', p0 => I);

   insert into test_error values (myjson1);

   end loop;

   exception when others then

   v_error := sqlerrm;

   insert into test_error values ('v_error '||v_error);

   end;

POPULATE FORM/HTML TABLE FROM ORACLE TABLE USING JSON:

Objective: Dynamically populate HTML table from APEX report/oracle data using JSON

Steps:

1. Get ID from APEX report when clicking column by setting value to page item.

2. Button Add Error can able to add one more error in to the Error.

3. Form JSON string from oracle data and put it into a page item.

DECLARE

  l_cursor SYS_REFCURSOR;

  v_error varchar2(4000);

BEGIN

  

  OPEN l_cursor FOR

    SELECT risk AS "risk",

           error AS "error"

          

    FROM   risks

    WHERE  risk = :P8_RISK;

  APEX_JSON.initialize_clob_output;

  APEX_JSON.write(l_cursor);

  apex_util.set_session_state('P0_JSON_DATA',APEX_JSON.get_clob_output);

  APEX_JSON.free_output;

  

exception when others then

v_error := sqlerrm;

insert into test_error values ('Error in JSON conversion '||v_error);

end;

4. Populate HTML table using JSON string from page item using JavaScript.

/* getting row count of html table */

var loop_cnt = $v('P8_ROW_COUNT');

/* removing existing html table */

$("#customFields tr").remove();

$("#adderror").remove();

/* Check if data count */

if (loop_cnt == 0)

    {

     $("#removeallrows").hide();

     $("#SUBMIT_BTN").hide();

     $("#addrisk").hide();

    }

else

    {

/* Fetching the JSON data */        

for (k = 0; k < loop_cnt; k++)

{

var jsonArray = JSON.parse(P0_JSON_DATA.value);

   

    if (k == 0)

        {

/* Added html first row */

$("#customFields").append('<button id = "adderror"  style="float: left;" href="javascript:void(0);" class="t-Button t-Button--hot js-ignoreChange custom_addbtn">Add Error</button>&nbsp;&nbsp;&nbsp;');

$("#customFields").append('<tr valign="top" style = "vertical-align: middle";><th id="rs" rowspan="0"><label for="Risk" id="labid">'+jsonArray[k].risk+'</label></th></tr>');

$("#customFields").append('<tr valign="top" style = "vertical-align: middle"><td ><input type="text" class="code" id="customFieldValue1" name="customFieldValue[]"value="'+jsonArray[k].error+'" placeholder="Input Value" size="20"/>&nbsp;&nbsp;&nbsp;<button href="javascript:void(0);" id = "remove_error" class="t-Button t-Button--hot js-ignoreChange custom_rembtn">Remove Error</button></td></tr>')

$("#customFields").append('<tr valign="top" style = "vertical-align: middle"><td ><input type="text" class="code" id="customFieldValue1" name="customFieldValue[]"value="'+jsonArray[k].error+'" placeholder="Input Value" size="20"/>&nbsp;&nbsp;&nbsp;<button href="javascript:void(0);" id = "remove_error" class="t-Button t-Button--hot js-ignoreChange custom_rembtn">Remove Error</button></td></tr>')

$("#customFields").append('<tr valign="top" style = "vertical-align: middle"><td ><input type="text" class="code" id="customFieldValue1" name="customFieldValue[]"value="'+jsonArray[k].error+'" placeholder="Input Value" size="20"/>&nbsp;&nbsp;&nbsp;<button href="javascript:void(0);" id = "remove_error" class="t-Button t-Button--hot js-ignoreChange custom_rembtn">Remove Error</button></td></tr>')

        }

    else

        {

            /* Remaining rows will be added here */

            $("#customFields").append('<tr valign="top" ><td><input type="text" class="code" id="customFieldValue1" name="customFieldValue[]"value="'+jsonArray[k].error+'" placeholder="Input Value" size="20"/> &nbsp;&nbsp;<button href="javascript:void(0);" id = "remove_error" class="t-Button t-Button--hot js-ignoreChange custom_rembtn">Remove Error</button></td></tr>');

        }

  } 

        

  }

    

  

MERGE,NESTED LOOPS,MERGE SORT

Merge Statement :-

ØThe MERGE statement reduces table scans and can perform the operation in parallel if required.

ØIt helps us to conditionally insert or update data depending on the condition.

ØIt is also known as an "upsert". 

Syntax :

MERGE INTO target_table 

USING source_table 

ON search_condition

     WHEN MATCHED THEN

        UPDATE SET col1 = value1, col2 = value2,...

        WHERE <update_condition>

        [DELETE WHERE <delete_condition>]

    WHEN NOT MATCHED THEN

        INSERT (col1,col2,...)

        values(value1,value2,...)

        WHERE <insert_condition>; 

Syntax in Detail :

ØSpecify the target table (target_table) which you want to update or insert into in INTO clause.

ØSpecify the source of data (source_table) to be updated or inserted in the USING clause. (It might me a table or a select query)

ØSpecify the search condition upon which the merge operation either updates or inserts in the ON clause.(Might be your join conditions)

Øor each row in the target table, Oracle evaluates the search condition.

ØIf the result is true, then it updates the row with the corresponding data from the source table or Query.

ØIn case the result is false for any rows, then it inserts the corresponding row from the source table into the target table.

Advantage :

ØThe MERGE statement becomes convenient when you want to combine multiple INSERT, UPDATE, and DELETE statements in a single operation.

ØTo execute the MERGE statement, you must have the INSERT and UPDATE object privileges on the source tables.

Example :

MERGE INTO employees e

    USING hr_records h    --(It can be a select Query also)

    ON (e.id = h.emp_id)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address);

Nested Loops:-

ØThe "Nested" term means that something is performed iteratively.

ØIt is like For each A do B.

ØFor a Nested Sub-Query, for each row in the outer query, execute the sub-query.

ØIt is fine when we perform few loops, but is generally not scalable to large volumes.

ØThe much faster way to handle large data volumes is to use  a Sort-Merge join.

When optimizer uses nested loops?

ØOptimizer uses nested loop when we are joining tables containing small number of rows with an efficient driving condition.

ØIt is important to have an index on column of inner join table as this table is called every time for a new value from outer table.

Optimizer may not use nested loop in case:

1. No of rows of both the table is quite high.

2. Inner query always results in same set of records.

3. The access path of inner table is independent of data coming from outer table.

Merge Sort :

ØIn a SORT-MERGE join, Oracle sorts the first row source by its join columns.

ØAnd sorts the second row source by its join columns and then merges the sorted row sources together.

ØAs matches are found, they are put into the result set.

ØSORT-MERGE joins can be effective when lack of data selectivity or useful indexes render a NESTED LOOPS join or when both of the row sources are quite large.

ØHowever, SORT-MERGE joins can be used only for equijoins (WHERE D.deptno = E.deptno, as opposed to WHERE D.deptno >= E.deptno).

ØSORT-MERGE joins require temporary segments for sorting.

ØThis can lead to extra memory utilization and/or extra disk I/O in the temporary tablespace.

          

         select /*+ ordered */ ename, dept.deptno from emp, dept where 

        dept.deptno=emp.deptno;

  

ØBelow table illustrates the method of executing the query shown next when a SORT-MERGE join is performed.

                                                                

DYNAMIC SAMPLING

Introduction 

Oracle Database introduced Dynamic Sampling to allow the optimizer to gather

additional information at parse time.

If database statistics were missing, stale or insufficient to produce a good execution

plan.

Dynamic sampling was controlled by the OPTIMIZER_DYNAMIC_SAMPLING

initialization parameter.

They can be initialized at instance or session level, or for individual queries using the

DYNAMIC_SAMPLING hint.

with available values ranging between "0" (off) to "10" (aggressive sampling) with

default value of "2".

Dynamic Sampling in 12C 

Dynamic sampling has been renamed to Dynamic Statistics in Oracle Database 12c.

Much of the functionality is the same, but a new sample level of 11 has been added.

The new setting allows the optimizer to decide if dynamic statistics should be sampled

and if so, what sample level to use.

Why Use Dynamic Sampling

Dynamic sampling enables the server to improve performance by :

Estimating single-table predicate selectivities where available statistics are missing or 

may lead to bad estimations.

Estimating statistics for tables and indexes with missing statistics.

Estimating statistics for tables and indexes with out of date statistics.

At compile-time Oracle determines if dynamic sampling would improve query

performance.

If so it issues recursive statements to estimate the necessary statistics.

Dynamic sampling benefits:

Dynamic sampling can be beneficial in the following ways

The sample time is small compared to the overall query execution time.

T he current database statistics alone would not create an optimal plan, so dynamic

sampling results in a better performing query.

The query may be executed multiple times.

In addition to the OPTIMIZER_DYNAMIC_SAMPLING system parameter the dynamic 

sampling level can be set.

We use DYNAMIC_SAMPLING optimizer hint for specific queries like the following.

SELECT /*+ dynamic_sampling(emp 10) */

       empno, ename, job, sal

FROM   emp

WHERE  deptno = 30;

The results of dynamic sampling are repeatable provided no rows are inserted,

updated or deleted from the sampled table.

When to Sample

The following situations will trigger automatic sampling to gather dynamic statistics

Missing Statistics  

Dynamic statistics are sampled if there are missing database statistics.

They may be missing because they are newly created objects, or had their statistics

locked before any statistics were gathered.

Although the dynamic statistics should help the optimizer, these statistics are 

considered low quality compared to conventional database statistics.

Stale Statistics

Statistics are considered stale when 10% or more of the rows in the table have

changed since the statistics were last gathered.

Stale statistic can affect cardinality estimates.

Because of changes to the number of rows in the table and inaccuracies in column

statistics, such as number of distinct values, high and low column values.

Insufficient Statistics

Existing database statistics may not be sufficient to generate an optimal execution

plan.

Dynamic statistics can make up for the absence of extended statistics for column

groups and expressions, as well as missing histograms that would identify data skew.

Even when all the necessary statistics are present it may not be possible to correctly

estimate cardinalities.

For some complex predicates, operations or joins, so dynamic sampling may still be

necessary.

Parallel Execution

Parallel execution is typically used to speed up long running processes.

For a long running process, the time associated with sampling dynamic statistics is

compared to the query execution time.

So it may be worth spending a little more time to make sure the execution plan is

optimal.

SQL Plan Directives

The presence of one or more usable SQL plan directives will trigger the sampling of

dynamic statistics.

SQL plan directives are created when the optimizer identifies misestimates.

The cardinality of operations or degree of parallelism (DOP) from previous executions

of the statement, or other statements using similar query expressions.

Controlling Dynamic Statistics

Dynamic statistics can be controlled by the

OPTIMIZER_DYNAMIC_SAMPLING initialization parameter and the

DYNAMIC_SAMPLING hint.

The following examples show how to control dynamic statistics at system, session and 

statement level.

   --System level. Don't do this!

   ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING=11;

   --Session level.

   ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=11;

   -- Statement level

   SELECT /*+ dynamic_sampling(emp 11) */

   empno, ename, job, sal FROM emp WHERE  deptno = 30;

     

You should not need to change the default value of "2".   

If the optimizer identifies misestimates, it can create SQL plan directives to force

dynamic sampling.

The presence of SQL plan directives influence the way DBMS_STATS gathers

statistics, which potentially fixes the root cause of the problems.

Reuse of Dynamic Statistics

Dynamic statistics can be stored in the SGA as OPT_ESTIMATE hints in the V$SQL_REOPTIMIZATION_HINTS view.