Stages of SQL Processing

 

Stages of SQL Processing

The general stages of SQL processing are parsing, optimization, row source generation, and execution. Depending on the statement, the database may omit some of these steps.

• SQL Parsing
  The first stage of SQL processing is SQL parsing. This stage involves separating the pieces of a SQL statement into a data structure that can be processed by other routines.

• During the parse call, the database performs the following checks:

  • Syntax check

  • Semantic check

  • Shared pool check
    The shared pool is one of the SGA components. SGA has some components shared by all the sessions. The shared pool has some sub-memories, too. Library cache, data dictionary cache, result cache, and some other caches.

• SQL Optimization
  Query optimization is the process of choosing the most efficient means of executing a SQL statement.

• The database optimizes queries based on statistics collected about the actual data being accessed. The optimizer uses the number of rows, the size of the data set, and other factors to generate possible execution plans, assigning a numeric cost to each plan. The database uses the plan with the lowest cost.

  The database must perform a hard parse at least once for every unique DML statement and performs optimization during this parse

• SQL Row Source Generation
  The row source generator is software that receives the optimal execution plan from the optimizer and produces an iterative plan, called the query plan, that is usable by the rest of the database.
• SQL Execution
  During execution, the SQL engine executes each row source in the tree produced by the row source generator. This is the only mandatory step in DML processing.
• During execution, if the data is not in memory, then the database reads the data from disk into memory. The database also takes out any locks and latches necessary to ensure data integrity and logs any changes made during the SQL execution. The final stage of processing a SQL statement is closing the cursor.
• 
  
• 

  
  
  
• SQL (oracle.com)
  
  

  
  
  
  

DBMS_SESSION : Managing Sessions From a Connection Pool in Oracle Databases

 Client Identifier (SET_IDENTIFIER and CLEAR_IDENTIFIER)

In many client-server applications it was common to allocate a different database user for each real user of the system. This made it simple to identify and audit the users at the database level. If applications used a single database user and managed security internally, this made identifying the real users of the system difficult. The issue was complicated further by multi-tier architectures that used connection pooling.

To counter this, Oracle 9iR1 introduced the SET_IDENTIFIER and CLEAR_IDENTIFIER procedures to allow the real user to be associated with a session, regardless of what database user was being used for the connection. 

CONN test/test

EXEC sys.DBMS_SESSION.set_identifier('ashish007');

COLUMN username FORMAT A20
COLUMN client_identifier FORMAT A20

SELECT USER , SYS_CONTEXT('userenv', 'client_identifier') AS client_identifier FROM dual;

USER             CLIENT_IDENTIFIER
-------------------- --------------------
TEST                 ashish007

SQL>SELECT username, client_identifier FROM v$session WHERE username = 'TRGT';

Nested table in oracle

 The syntax for creating a nested table 

CREATE TYPE a_simple_udt_t IS TABLE OF NUMBER(10);

SQL> CREATE TABLE a_table (
  2    col1 number,
  3    col2 a_simple_udt_t )
  4  NESTED TABLE col2 STORE AS a_table_col2;
The NESTED TABLE clause specifies the name of the table used to store the data in the nested column.

Collections in Oracle PL/SQL

 Collections in Oracle PL/SQL

• Index-By Tables (Associative Arrays)
• Nested Table Collections
• Varrays Collections
• Assignments and Equality Tests;

DECLARE

   CURSOR emp_cur
   IS
      SELECT emp_Id, first_name, last_name
        FROM employees
       WHERE department_id = department_id_in;

   TYPE employee_type IS TABLE OF  emp_cur%RowTYPE;
    -- index by works both ways

   l_emp_tt   employee_type;
BEGIN
   OPEN employees_cur;

   LOOP
      FETCH emp_cur
      BULK COLLECT INTO l_emp_tt  LIMIT 100;

      EXIT WHEN l_emp_tt.COUNT = 0;
   END LOOP;

--Print 
For idx in 1.. l_emp_tt.COUNT
Loop
  dbms_output.put_line(l_emp_tt(idx).emp_name );
End Loop;

END;
/
-----------------------------------------------------------
----------------------------------------------------------
--Using For Loop
---------------------------------------------------------------
If  not an index by Binary_integer And Using For Loop
--------------------------------------------------------------
Declare 
v_name varchar2(500);
 i pls_integer :=0;
 Cursor cr_cust is select * from customers where cust_gender='F' 
  fetch first 100 rows only;
 
 Type cust_Type is table of cr_cust%RowType ; -- no index by

 Cust_tt Cust_type := cust_type();  ---Then intialize 

 Begin
     
   For cx in cr_cust  
   Loop
    Cust_tt.Extend;
    Cust_tt(cust_tt.count).cust_city := cx.cust_city ;
   End Loop; 
   
  For idx in 1.. cust_tt.count
  Loop
    v_name:= cust_tt(idx).cust_city;
     dbms_output.put_line (v_name);
  End Loop;
  
 End;

----------------------------------------------------------------
Traversing the broken Nested table collection with .Next Keyword
----------------------------------------------------------
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN

  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
   
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;

UTL_CALL_STACK : Get Detailed Information About the Currently Running Subprogram

 Oracle database 12c introduced the UTL_CALL_STACK package to allow programmatic access to the call stack and error stack, giving much greater flexibility for debugging and error handling of PL/SQL code. This is only a replacement for the existing functionality if you need the extra level of control. The existing functionality in the DBMS_UTILITY package is still available and has not been deprecated.

• Call Stack     prior to it we use DBMS_UTILITY.format_call_stack
• Error Stack   prior to it we use DBMS_UTILITY.format_error_stack
• Backtrace     prior to it we use DBMS_UTILITY.format_error_backtrace

The UTL_CALL_STACK package contains APIs to display the contents of the call stack in a more readable form.

refer https://oracle-base.com/articles/12c/utl-call-stack-12cr1

• DYNAMIC_DEPTH : The number of subprograms on the call stack, starting at the current position in the call stack, to the initial call.
• LEXICAL_DEPTH : Lexical depth of the subprogram within the current call.
• UNIT_LINE : Line number in the subprogram of the current call.
• SUBPROGRAM : Subprogram name associated with the current call.
• CONCATENATE_SUBPROGRAM : Returns the UNIT.SUBPROGRAM.LOCAL_SUBPROGRAM form of the subprogram name.
• OWNER : The owner of the subprogram associated with the current call.

Grant Roles to PL/SQL Programs Only

 

Prior to Oracle 12c, when a PL/SQL program accessed a table, you needed to grant access to the user to both the PL/SQL program and the table that it accessed.

If you didn’t, you would get some kind of access error.

Now, in Oracle 12c, you can grant access to the user to just the PL/SQL program, and not the table it reads from.

This makes it much more secure. It means that you can set up your database so users do not have access to the underlying tables.

Invisible Index Usage

 Invisible indexes make it possible to test the effect of removing an index on query performance, without making a destructive change that must be undone should the index turn out to be required. Dropping and re-adding an index can be expensive for a large table, whereas making it invisible and visible are fast, in-place operations.

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;

ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

If you want to use invisible indexes in your optimisation, you can turn this on at the session level:
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

Partitioning: Partition Decisions

Partitioning is a tool. 

It is a tool to make administration easier (divide and conquer)

It is a tool to make availability higher (one of your partitions could take a hit and the 

others stay up and running while you repair one.   )

It is a tool that on a transactional system with lots of indexed reads you need to employ carefully to ensure you do not NEGATIVELY impact performance.

It is a tool on a data-warehouse with lots of "big operations" (full scans for example) you can use to significantly reduce run times (assuming you've DESIGNED to exploit that of course).

Your transactional system with this keyed read may well now benefit from concurrent inserts/updates and deletes -- given that you have N index segments (n root blocks to contend over, n branch blocks to contend over -- not just ONE) and N table segments (N freelists at least, increased concurrency).  

You have to be careful in many cases that partitioning doesn't KILL YOU performance wise.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:14188311731917

Oracle supports a wide array of partitioning methods:

• Range Partitioning - the data is distributed based on a range of values.
• List Partitioning The data distribution is defined by a discrete list of values. One or multiple columns can be used as partition key.
• Auto-List Partitioning - extends the capabilities of the list method by automatically defining new partitions for any new partition key values.
• Hash Partitioning - an internal hash algorithm is applied to the partitioning key to determine the partition.
• Composite Partitioning - combinations of two data distribution methods are used. First, the table is partitioned by data distribution method one and then each partition is further subdivided into subpartitions using the second data distribution method.
• Multi-Column Range Partitioning - an option for when the partitioning key is composed of several columns and subsequent columns define a higher level of granularity than the preceding ones.
• Interval Partitioning - extends the capabilities of the range method by automatically defining equi-partitioned ranges for any future partitions using an interval definition as part of the table metadata.
• Reference Partitioning Partitions - a table by leveraging an existing parent-child relationship. The primary key relationship is used to inherit the partitioning strategy of the parent table to its child table.
• Virtual Column Based Partitioning - allows the partitioning key to be an expression, using one or more existing columns of a table, and storing the expression as metadata only.
• Interval Reference Partitioning - an extension to reference partitioning that allows the use of interval partitioned tables as parent tables for reference partitioning.

A hybrid partitioned table is a feature in Oracle that allows partitions to reside in both internal and external sources. Internal partitions are in database data files, while external partitions are in external files and sources.

CTE or the WITH Query

 A Common Table Expression (CTE) is the result set of a query which exists temporarily and for use only within the context of a larger query

So for the database to understand we define it using WITH clause before the starting of the CTE query statement. Here statement means SELECT, INSERT, UPDATE, DELETE.

In Oracle 12c and above we can use functions in with clause

WITH 
    FUNCTION get_number RETURN NUMBER IS
    BEGIN
        RETURN 12345;
    END;

SELECT employee_id, first_name, last_name, get_number()
FROM hr.employees ;

The most important point is that we can refer it in the scope of the execution of the WITH clause query. Once the execution is done of the query we cannot use the CTE in any other statements or query. So we can say that the scope of the CTE is still the execution of the statement.

A Recursive common table expression (RCTE) as the keyword recursive suggests references itself. It has a sub query that refers to its own name. It is mainly used when we are dealing with hierarchical data such as company hierarchy positions chart or table because the recursive CTE will continue executing until the entire hierarchy is returned in the result set.

Temporary Tables

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction.

The ON COMMIT DELETE ROWS clause indicates the data should be deleted at the end of the transaction.

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should persist beyond the end of the transaction. They will only be removed at the end of the session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)

) ON COMMIT PRESERVE ROWS;

Although the data in a GTT is written to the temporary tablespace, the associated undo is still written to the normal undo tablespace, which is itself protected by redo, so using a GTT does not reduce undo and the redo associated with protecting the undo tablespace.

-- Check undo used by transaction.
SELECT t.used_ublk,       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

Ref:- https://oracle-base.com/articles/misc/temporary-tables

Oracle 12c introduced the concept of Temporary Undo, allowing  the undo segments for global temporary tables to be stored in the temporary tablespace , thereby preventing the need to create redo.

This feature is available using the Temp_undo_Enabled parameter at session or system level.
Refer-- https://oracle-base.com/articles/12c/temporary-undo-12cr1

A new variation of temporary tables has been introduced in Oracle 18c. A private temporary table is a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup . It uses the prefix ora$ptt_ .

CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DROP DEFINITION;

New Features

PLSQL – ACCESSIBLE BY Clause

In 12c onwards, if you wanted to limit package A to only be accessible by package B.

PACKAGE package_A
ACCESSIBLE BY (package_B)
IS
PROCEDURE someProcedure;
END;

DBMS_JOB Jobs Converted to DBMS_SCHEDULER Jobs in Oracle Database 19c

The DBMS_JOB package has been deprecated since 12cR2. Oracle 19c takes the demise of the DBMS_JOB package a step further by converting any DBMS_JOB jobs to DBMS_SCHEDULER jobs.

Pluggable databases are new features in Oracle 12c.
Alter session set container =pdb1;

Fetch FIRST 10 Rows ONLY.

New Feature of 12c to fetch Top-n rows.

SELECT first_name, last_name, date_of_birth
FROM student
ORDER BY date_of_birth
FETCH FIRST 10 ROWS ONLY

Create an Auto Increment Column 

CREATE TABLE idtest (
new_id NUMBER GENERATED AS IDENTITY,
first_name VARCHAR2(100)
last_name VARCHAR2(100)
);
or new_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,

In Oracle 12c, you can:

• Use sequences as the default value for a column
• Use default values only when the specified value is NULL

Before Oracle 12c, this had to be done using INSERT triggers.

CREATE SEQUENCE seq_test;

CREATE TABLE default_test (
seq_val NUMBER DEFAULT seq_test.NEXTVAL PRIMARY KEY,  <--
null_val NUMBER DEFAULT ON NULL 99                    <--
normal_val NUMBER
);

In Oracle 12c, you can take advantage of an increased column size for some data types.

The VARCHAR2, NVARCHAR2 and RAW data types allow for a larger number of characters than previous versions.

Data Type	Oracle 11g Limit	Oracle 11g PL/SQL Limit	Limit in Oracle 12c
VARCHAR2	2K	4K	32K
NVARCHAR2	2K	4K	32K
RAW	2K	4K	32K

How can you use these new maximum sizes?

You need to change a setting within the init.ora file. The init.ora file is Oracle’s initialization file, which is read every time the database starts up.

The setting is called MAX_STRING_SIZE. There are two values for this:

• STANDARD – The original sizes.
• EXTENDED – The new larger sizes.

Immutable Tables in Oracle Database 19c and 21c 

 An immutable table is a tamper-proof, insert-only table with an associated table-level and row-level retention period

create immutable table it_t1 (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint it_t1_pk primary key (id)
)
no drop until 0 days idle
no delete until 365 days after insert;

SELECT row_retention,
       row_retention_locked, 
       table_inactivity_retention
FROM   user_immutable_tables 
WHERE  table_name = 'IT_T1';

Trigger Preceedes FOLLOWS

 The FOLLOWS clause, allowing you to indicate that a trigger fire after a specified trigger.

Ref. http://www.grassroots-oracle.com/2009/09/precedes-follows-follows.html

create or replace trigger package_trigger
after update of salary
on employees
for each row
begin
dbms_output.put_line('package_trigger');
end old_way;
/

create or replace trigger custom_stuff
after update of salary
on employees
for each row
follows package_trigger
begin
dbms_output.put_line('custom_stuff');
end old_way;
/

Function regexp_replace

Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm#SQLRF06302

Ref : https://oracle-base.com/articles/misc/regular-expressions-support-in-oracle

 
The following example examines the string, looking for two or more spaces. Oracle replaces each occurrence of two or more spaces with a single space.

SELECT
  REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA',
                 '( ){2,}', ' ') "REGEXP_REPLACE"
  FROM DUAL;

REGEXP_REPLACE
--------------------------------------
500 Oracle Parkway, Redwood Shores, CA

select regexp_replace('East Champaran', '[^A-Za-z]', '') from dual;

Results 

-----------------

EastChamparan