Application Developer must know SQL

Changing the password of  user
--------------------------------------
  Navigate to path  C:\oraclexe\app\oracle\product\11.2.0\server\bin

1.  login to the  sqlplus as sysdba

2. alter user system identified by <new_pass>

 Changing the password of the sysdba account
-----------------------------------------------
1. login as sqlplus   /nolog
2. con /as sysdba
3.alter user sys indetified by sysdba123

Unlocking the user acccount
==================

ALTER USER system ACCOUNT UNLOCK

Creating sequence  on the column
============================

http://sathyam-soa.blogspot.in/2012/07/adf-db-sequence-using-db-trigger.html

create sequence vendor_sequence
starts with 1
incremented by 1
nonmaxvalue;

-- Define the DB sequence at ADF level is the best choice
http://sathyam-soa.blogspot.in/2012/07/adf-db-sequence-using-db-trigger.html

http://waslleysouza.com.br/en/2014/09/using-database-sequence-adf/

 Creating the trigger on the table
==========================

create trigger  vendor_trigger
before insert on vendor
for each row begin
select vendor_sequence.nextval   into :new.vendorid from dual;


: SELECT SQL_ID,ELAPSED_TIME,(ELAPSED_TIME/1000000) as TimeInSec,buffer_gets, sql_text FROM SQLREP_SQLSET_STATEMENTS WHERE SQLSET_NAME='R13_PRC_VIN_SQL_REPORT'
AND SQL_ID IN ('31amya9qm5q4u',
'993hvbpudj04u',
'dw9p9wwt59ac4',
'59yhd8nwm3f17',
'37mvdw8bhfzw6',
'81srdc545ram5',
'4258878w8hjhv',
'2jnmh4qqf8s68',
'20yqp8z2m793v',
'97b93wxyqfsah',
'gnwj2r6jyd9rn',
'7gan7r6341udh',
'd171tkna91dw6')
order by TimeInSec;


 How many times function get executes
===============================




 begin
  
      dbms_application_info.set_client_info(0);
     end;
   
      select
   dbms_utility.get_cpu_time-:cpu cpu_hsecs,
    userenv('client_info')
    from dual;

 Code in function :
 dbms_application_info.set_client_info
       (userenv('client_info')+1 );

analytic_function([ arguments ]) OVER (analytic_clause)

The analytic_clause breaks down into the following optional elements.
[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]


  GRANT ALTER,DEBUG,DELETE,FLASHBACK,INDEX,INSERT,ON COMMIT REFRESH,QUERY REWRITE,REFERENCES,SELECT,UPDATE ON yogesh_tax TO fusion_runtime;

REST Vs SOAP

'Unless you have a definitive reason to use SOAP use REST'


REST :  REST is sweat spot when you are exposing the public API overt internet to handle the CRUD operation on the data.REST focuses on assessing the named resources through the  single consistent  Interface.


  SOAP:   SOAP brings it's own protocol and focuses on the exposing the pieces of application logic (not the data).SOAP focuses on accessing the named operation, which implements some business logic through different interfaces.
Though SOAP is commonly referred to as “web services” this is a misnomer. SOAP has very little if anything to do with the Web. REST provides true “Web services” based on URIs and HTTP.
 -Language, Platform ,protocol indepedent

Why REST ?
  1. REST  ( lightweight, ) has better performance and scalability .
  2.REST supports multiple data format XML,JSON etc.
  3.REST reads can be cached, while SOAP based read can not be cached.

Why SOAP ?

1. WS_SECURITY
  While SOAP supports SSL (just like REST) it also supports WS-Security which adds some enterprise security features. Supports identity through intermediaries, not just point to point (SSL). It also provides a standard implementation of data integrity and data privacy. Calling it “Enterprise” isn’t to say it’s more secure, it simply supports some security tools that typical internet services have no need for, in fact they are really only needed in a few “enterprise” scenarios.

2. WS_AUTOMIC_TRANSACTION

Rest doesn’t have a standard messaging system and expects clients to deal with communication failures by retrying. SOAP has successful/retry logic built in and provides end-to-end reliability even through SOAP intermediaries.
3. WS_MESSAGE_RELIBILITY 

4.WS_CO_ORDINATIOND


What is Integration ?

  Integration is process by which information is passed between tow or more distinct software entities.

THE CHOICE OF INTEGRATION MEDIA IS CATEGORICALLY IS DETERMINED WITH HELP OF FOLLOWING QUESTIONNAIRES.

1. Is transfer of information is synchronous or asynchronous ?
2. Is transfer of information acknowledged ?
3. Is transfer of information transactional ?
4. Does transfer of information requires message-level or transport-level  encryption ?
5.Does transfer of information occurs in batches composed of multiple message or one message at time ?
6. Does transfer of information occurs between system build using same technology or different technology ?
7. Does transfer of information use the technology-specific/transport protocol specific ?.


Java to Java  Integration
--------------------------
1. JMS  
1. JMS is interinsically designed for Asynchronous communication between between to java aplication.
   Features:
          1. publish subscribe and point to point messaging model.
          2. Message Delivery  Acknowledgement
          3.Message Level Encryption
          4.Distributed Transaction (JTA)

Java to Non-Java Integration :
------------------------------
  1.Web service are intrinsically designed to facilitate the integration of heterogeneous systems.
      1. Using web service is truly technology  independent .
     Which one embrace :
             1. SOAP
             2. Self Describing mesaage format Such as XML
         
    SOAP VS REST.   
 
https://dzone.com/articles/put-vs-post
https://knpuniversity.com/screencast/rest/put-versus-post

  Deciding between Put and POST

=======================

1. if the end point is idempotent .

2. URI must be addressed to resource being updated.

Patch :
  Update the resource without sending  all  the attributes in the request.

 

Java Memory Structure

JVM memory areas / components
    - Heap area
      Objects and array stored
      Created when JVM started  define fix size or vary between min and max size  –Xms -Xmx
       Yong Generation
        Eden Memory
        Survivor Memory
        Most of the newly created objects are located in the Eden memory space.
            When Eden space is filled with objects, Minor GC is performed and all the survivor objects are moved to one of the survivor spaces.
           
       Old Generation
         after many rounds of minor GC object is moved to the Old generation space.
       
    --  Perm Gem  :
      Permanent Generation or “Perm Gen” contains the application metadata required by the JVM to describe the classes and methods used in the application. Note that Perm Gen is not part of Java Heap memory.
    - Method area and runtime constant pool
      field, method data, code, constructor ,
      created on JVM started its part of Heap Memory
       The method area may be of a fixed size or may be expanded as required by the computation and may be contracted if a larger method area becomes unnecessary
      
    - JVM stack
      Each of the JVM threads has a private stack created at the same time as that of the thread. The stack stores frames. A frame is used to store data and partial results and to perform dynamic linking, return values for methods, and dispatch exceptions.
     
       If the computation in a thread requires a larger Java Virtual Machine stack than is permitted, the Java Virtual Machine throws a StackOverflowError.
      
       f Java Virtual Machine stacks can be dynamically expanded, and expansion is attempted but insufficient memory can be made available to effect the expansion, or if insufficient memory can be made available to create the initial Java Virtual Machine stack for a new thread, the Java Virtual Machine throws an OutOfMemoryError.
      
    - Native method stacks
        Native method stacks is called C stacks; it support native methods (methods written in a language other than the Java programming language), typically allocated per each thread when each thread is created. Java Virtual Machine implementations that cannot load native methods and that do not themselves rely on conventional stacks need not supply native method stacks.

The size of native method stacks can be either fixed or dynamic.
    - PC registers
   
--    Memory Pool
      To store immutable object
     
     
--  Runtime Constant Pool

Runtime constant pool is per-class runtime representation of constant pool in a class. It contains class runtime constants and static methods. Runtime constant pool is the part of method area.



VM Switch     VM Switch Description
-Xms     For setting the initial heap size when JVM starts
-Xmx     For setting the maximum heap size.
-Xmn     For setting the size of the Young Generation, rest of the space goes for Old Generation.
-XX:PermGen     For setting the initial size of the Permanent Generation memory
-XX:MaxPermGen     For setting the maximum size of Perm Gen
-XX:SurvivorRatio     For providing ratio of Eden space and Survivor Space, for example if Young Generation size is 10m and VM switch is -XX:SurvivorRatio=2 then 5m will be reserved for Eden Space and 2.5m each for both the Survivor spaces. The default value is 8.
-XX:NewRatio     For providing ratio of old/new generation sizes. The default value is 2.


Resource Link :
http://howtodoinjava.com/core-java/garbage-collection/jvm-memory-model-structure-and-components/
 http://docs.oracle.com/javase/specs/jvms/se7/html/jvms-2.html  
  http://www.journaldev.com/2856/java-jvm-memory-model-and-garbage-collection-monitoring-tuning

Count(1) Vs Count(*) Vs Count(experession) in Oracle.

1. Number of Records:

   The count(1)  and count(*)  returns same number of records, as its return the number rows in the table
   including the NULL records.
 
    While Count(Expression)  return the number of null records for which expression evalutes.
2. Performance Factor.
    There is no significance difference  in performance onwards release R8 oracle. 
  
  I will say don't invest too much time on this topics.

Source  doc Link
http://www.oracledba.co.uk/tips/count_speed.htm
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1156159920245

 

IN Vs EXIST and NOT IN VS NOT EXIST in oracle

1. IN condition Vs EXIST   condition
==========================

1.Use IN with list of sub-query (Where servey_date  in ('20015','2016'))
2. EXIST  looking for at least one row to return return true
3.If inner query has less records then;then use the IN
4. If inner query has more records then; the outer query then use EXIST.
 (Thumb rule to use the IN and EXIST)

IN query processed as
===============

Select * from T1 where x in ( select y from T2 )
is typically processed as:

select *
  from t1, ( select distinct y from t2 ) t2
 where t1.x = t2.y;

Exist Processed as folowing
===================

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:


   for x in ( select * from t1 )
   loop
      if ( exists ( select null from t2 where y = x.x )
      then 
         OUTPUT THE RECORD
      end if
   end loop

 - It always result into FULL_TABLE scan on the T1.

  -If your goal is the FIRST row exists might totally blow away IN this is 

the exception to thumb rule.

 Link to source page:  IN_VS_EXIST_ASK_TOM

NOT IN can be just as efficient as NOT EXISTS -- many orders of magnitude BETTER 

even -- "anti-join" can be used (if the subquery is known to not return nulls) 

Anti-Join fails when NULL;Therefore, a NOT IN operation would fail

 if the result set being probed returns a NULL. In such a case, 

the results of a NOT IN query is 0 rows  while a NOT EXISTS query

 would still show the rows present in the one table but not in the other table.