WITH Clause Queries

SELECT E.ENAME,E.DEPTNO , DC.EMP_COUNT

FROM EMP  E ,

( SELECT DEPTNO,COUNT(*) AS EMP_COUNT FROM EMP GROUP BY DEPTNO) DC

WHERE E.DEPTNO = DC.DEPTNO

/

WITH EMP_COUNT AS (

SELECT DEPTNO,COUNT(*) AS EMP_COUNT FROM EMP GROUP BY DEPTNO

)

SELECT E.ENAME , EC.EMP_COUNT

FROM EMP E, EMP_COUNT EC

WHERE E.DEPTNO = EC.DEPTNO

COLLECTIONS AND RECORDS

A collection is an ordered group of elements, all of the same type

PL/SQL offers these collection types:

• Index by tables, also known as Associative arrays
• Nested Tables
• Varrays

Associative Arrays:

Available only in PL/SQL, not in SQL

Can be indexed by BINARY_INTEGER, PLS_INTEGER, VARCHAR2

Nested Tables :

Available both in PL/SQL and SQL

Nested table is single-dimensional, meaning that each row has a single column of data like a one-dimension array.

VArrays:

Available both in PL/SQL and SQL

Choosing between Associative Arrays, VArrays and Nested Tables :

Associative Arrays:

• Relatively small Look up tables
• Passing Collection from or to Database Server

Associative Arrays Use Case : 

Eg: 

DECLARE 

    TYPE INDIAN_STATE IS RECORD (

        STATE VARCHAR2(20),

        CAPITAL VARCHAR2(20)

        );

        

    TYPE V_STATE IS TABLE OF INDIAN_STATE 

    INDEX BY BINARY_INTEGER ; 

    

    T_STATE V_STATE; 

BEGIN 

    T_STATE(1).STATE := 'ANDHRA';

    T_STATE(1).CAPITAL := 'AMARAVATHI'; 

    

    T_STATE(2).STATE := 'TELANGANA';

    T_STATE(2).CAPITAL := 'HYDERABAD'; 

    

    FOR X IN  1 .. 2  

    LOOP 

    DBMS_OUTPUT.PUT_LINE ( 'CAPITAL OF '||  T_STATE(X).STATE || ' IS '  || T_STATE(X).CAPITAL  ); 

    END LOOP; 

END ;

/

VArrays :

• No. of elements is known in advance
• Elements are usually accessed sequentially

Nested Tables:

• Index values are consecutive
• There are no set number of index values
• You must delete or update some elements, but not all at same time

Use case of Nested Tables: 

DECLARE 

    TYPE EMPLOYEES IS TABLE OF EMP.ENAME%TYPE; 

    V_EMPLOYEES EMPLOYEES := EMPLOYEES(); 

    I NUMBER ; 

BEGIN 

  I := 0 ; 

   FOR C IN (SELECT ENAME FROM EMP ) 

    LOOP

        I := I+1; 

        V_EMPLOYEES.EXTEND;

        V_EMPLOYEES(I) :=  C.ENAME; 

    END LOOP; 

    

    DBMS_OUTPUT.PUT_LINE ('EMPLOYEE COUNT IS '|| V_EMPLOYEES.COUNT);

    

    FOR X IN 1 ..V_EMPLOYEES.count   

    LOOP 

        DBMS_OUTPUT.PUT_LINE (V_EMPLOYEES(X)); 

    END LOOP; 

END;

/ 

Collection Exceptions :

• COLLECTION_IS_NULL
• NO_DATA_FOUND
• VALUE_ERROR
• SUBSCRIPT_BEYOND_COUNT
• SUBSCRIPT_OUTSIDE_LIMIT

Collection Methods:

• COUNT
• LIMIT
• FIRST
• LAST
• EXTEND
• EXTEND(n)
• TRIM
• TRIM(n)
• DELETE
• DELETE(n)
• DELETE(m,n)
• PRIOR(n)
• NEXT(n)

Estimating the Size of an Index before creating it

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMPLOYEE');

DECLARE
  ALLOC_BYTES NUMBER;
  USED_BYTES  NUMBER;
BEGIN
  DBMS_SPACE.CREATE_INDEX_COST('CREATE INDEX IDX_EMPLOYEE_ENAME ON SCOTT.EMPLOYEE(ENAME)' , USED_BYTES ,ALLOC_BYTES );
  DBMS_OUTPUT.PUT_LINE(' USED MEMORY ' || USED_BYTES      /1024/1024 || ' MB ' );
  DBMS_OUTPUT.PUT_LINE(' ALLOCATED MEMORY ' || ALLOC_BYTES/1024/1024 || ' MB ' );
END ;
/

Indexed Virtual Column vs Virtual Index vs Invisible Index

Indexed Virtual Column

                It is an alternative to a function based index.
                Add a Virtual Column to the table, and create an index on it.

Eg :
CREATE TABLE EMP
(
EMPNO NUMBER,
ENAME VARCHAR(20),
SAL NUMBER(7,2),
COMM NUMBER(7,2),
TOTALSAL GENERATED ALWAYS AS (SAL +NVL(COMM,0))
);

CREATE INDEX IDX_EMP_TOTALSAL ON EMP(TOTALSAL);

Virtual Index

                You can instruct Oracle to create an index and that will never be used and won't have any extents allocated to it via NOSEGMENT clause

Eg : CREATE INDEX IDX_EMP ON EMP(EMPNO) NOSEGMENT;

                Even though the index is not physically instantiated,  you can instruct Oracle to determine if the index might be used by the optimizer via _USE_NOSEGMENT_INDEXES initialization parameter

Eg : ALTER SESSION SET  "_USE_NOSEGMENT_INDEXES" = true

Invisible Indexes

                Optimizer doesn't use the index when retrieving data for a query. However Insex structure is still maintained as the underlying table has records inserted, updated or deleted. This feature is used if you want to test the viability of an index without impacting existing application code

Eg : CREATE INDEX IDX ON EMP(EMPNO) INVISIBLE ;

Note: Virtual Columns & Invisible Indexes are only available in 11g and higher versions 

Procedure VS Functions

Datatypes in Oracle PL/SQL

A scalar data type can have subtypes. A subtype is a data type that is a subset of another data type, which is its base type. A subtype has the same valid operations as its base type. A data type and its subtypes comprise a data type family.

PL/SQL predefines many types and subtypes in the package STANDARD and lets you define your own subtypes.

The PL/SQL scalar data types are:

•          The SQL data types
•          BOOLEAN
•          PLS_INTEGER
•          BINARY_INTEGER
•          REF CURSOR
•          User-defined subtypes

BOOLEAN:
                We cannot pass a Boolean value to DBMS_OUTPUT.PUT or DBMS_OUTPUT.PUT_LINE functions. To print Boolean value, we need to use CASE or IF Statement.

PLS_INTEGER & BINARY_INTEGER:
                PLS_INTEGER requires less storage
                PLS_INTEGER uses hardware arithmetic, so they are faster than NUMBER operations, which uses Library Arithmetic
                SIMPLE _INTEGER is the sub type of PLS_INTEGER . Is it the PLS_INTEGER value with NOT NULL constraint
                BINARY_INTEGER is the subtype of NUMBER, which uses Library Arithmetic
                Predefined PLS_INTEGER Subtypes are

•          NATURAL
•          NATURALN
•          POSITIVE
•          POSITIVEN
•          SIMPLE_INTEGER

Exception Handling

Predefined Exceptions :

•          NO_DATA_FOUND
•          CASE_NOT_FOUND
•          COLLECTION_IS_NULL
•          INVALID_CURSOR
•          LOGIN_DENIED
•          DUP_VAL_ON_INDEX
•          ROWTYPE_MISMATCH
•          TOO_MANY_ROWS
•          TIMEOUT_ON_RESOURCE
•          ZERO_DIVIDE

User Defined Exceptions :

Declaration :

                DECLARE
                                EXCEPTION_NAME EXCEPTION;

Scope:

                We cannot declare an exception twice in the same block. However, declare the same exception in different blocks.

Associating a PL/SQL  exception with an error number : PRAGMA EXCEPTION_INIT

                DECLARE
                                EXCEPTION_NAME EXCEPTION;
                                PRAGMA_EXCEPTION (EXCEPTION_NAME,-60);
                BEGIN
                                /* Code that causes ORA-00060 error*/

                EXCEPTION
                                WHEN EXCEPTION_NAME           THEN
                                /*  Handle the error  */

                END  ;                /

Defining Our Own Error Messages : Procedure RAISE_APPLICATION_ERROR

                It lets you define the user defined ORA - error messages from stored programs. In that way we can report errors to our application and avoid returning unhandled exceptions.

                RAISE_APPLICATION_ERROR(error_number,message[,TRUE/FALSE]);
                                where error_number is a negative integer between -20000 and -20999, and message is a character string up to 2048 bytes long.

                RAISE_APPLICATION_ERROR is the part of DBMS_STANDARD package

Index Organized Tables

Typical tables are heap organized. It has a stable physical location. It means, when we insert a new record it will be added to the next row of the table .

Index organized tables builds Table & Index together.
These are good for

• Small tables
• Exact match (Look up )

Create table state_lookup_IOT
(
stateid number,
State varchar(20),
City varchar(20),
primary key (id)
)
ORGANIZATION INDEX ;

Inside Oracle Sorting

Oracle sorting is a very important component of Oracle tuning, yet sorting optimization is often overlooked.

An Oracle database will automatically perform sorting operations on row data under the following circumstances:

• When an index is created
• When using the ORDER BY clause in SQL
• When using the GROUP BY clause in SQL

There are several ways to sequence Oracle output rows:

• Oracle internal sort (in sort_area_size or TEMP tablespace)
• Retrieve the rows in pre-sorted order (using an index)

Using a third-party sort product - For shops that have to sequence millions of rows of output on an hourly basis, external sort products can be faster than using Oracle to sort the data.

ViTech

• Which statement is fast? Commit or Rollback
• RETURN statement is mandatory in a function?
• Difference b/w NVL and NVL2
• How to add a new parameter to a procedure without affecting existing calls
• Can we call a function which has DML in SELECT 
• Write a query to display how many days are there for his/her next birthday 
• For vs For All statements ?
• A select statement retrives 2 million records and took 10 minutes time. Now I executed delete and Commit. Again I executed select * form table, result is no rows selected. what will be the time taken for the second select satement?
• Emp table has 1 million records, Dept table has 4 rows. Now I wrote normal join query. How many hits will go ?
• What is the best practice to get Nth highest salary 

Query to get days for Next B'day :
SELECT HIREDATE,
, TRUNC(
case 
    when   
          TO_DATE (SUBSTR(HIREDATE,1,7)|| TO_CHAR(extract(year from sysdate))) -sysdate > 0 
      then    TO_DATE (SUBSTR(HIREDATE,1,7)|| TO_CHAR(extract(year from sysdate))) -sysdate  
    when
         TO_DATE (SUBSTR(HIREDATE,1,7)|| TO_CHAR(extract(year from sysdate))) -sysdate  < 0 
      then    
         TO_DATE (SUBSTR(HIREDATE,1,7)|| TO_CHAR(extract(year from sysdate)+1)) -sysdate 
  END ) AS   DAYSREMAINFOR_ANNIVERSARY
FROM EMP;

Parallel Execution of SQL Statements

Parallelism is the idea of breaking down a task so that, instead of one process doing all of the work in a query, many processes do part of the work at the same time.

SQL Operations that can be parallelized.

• Parallel Query
• Parallel DDL (Can't be used on the tables with Object or LOB type columns)
• Parallel DML
• SQL * Loader

Degree of Parallelism :

The number of parallel execution servers associated with a single operation is known as the Degree of parallelism.

Example:
With Select Statement

SELECT /*+ PARALLEL(employees 4) PARALLEL(departments 4) USE_HASH(employees)

ORDERED */

       MAX(salary), AVG(salary)

FROM employees, departments

WHERE employees.department_id = departments.department_id

GROUP BY employees.department_id;

With SQL Loader :
Available with Direct path data loads only 
$sqlldr control=emp.ctl parallel=true direct=true 

Note that hints have been used in the query to force the join order and join method, and to specify the degree of parallelism (DOP) of the tables employees and departments.

Generating Random Values in Oracle

It can be done by using the package DBMS_RANDOM.

Different functions available in DBMS_RANDOM package :

• RANDOM
• VALUE
• STRING

Examples:

1.  select SYS.DBMS_RANDOM.RANDOM FROM DUAL;
   It will return any random value
   
2. SELECT SYS.DBMS_RANDOM.VALUE FROM DUAL;
   It will return a numeric value between 0 and 1
   
3. SELECT SYS.DBMS_RANDOM.VALUE(1,1000) FROM DUAL;
   Returns any value between 1 and 1000.
   We need to use TRUNC function to remove the decimal is generated by this package
   SELECT TRUNC(SYS.DBMS_RANDOM.VALUE(1,1000)) FROM DUAL;
   
4. select SYS.DBMS_RANDOM.STRING('U',8) from DUAL;
   Returns the string with the 8 characters in uppercase
   
5. select SYS.DBMS_RANDOM.STRING('L',8) from DUAL;
   Returns the string with the 8 characters in lowercase
   A --> Alpha numeric
   X --> Alphanumeric with lower case

Table Partitioning in Oracle

Partitioning:

                A partitioned object has multiple pieces that can be managed either individually or collectively

Partitioning Key :

                Each row in a partitioned table is unambiguously assigned to  a partition. The partition key consists of one or more columns that determine the partition where each row is stored.

When to Partition a Table:

Here are some suggestions

• ·         Table greater than 2GB
• ·         Tables containing historical data, in which new data is added into new partition
• ·         When contents of table to be distributed across different storage devices

Benefits of Partitioning :

• ·         Performance
• ·         Availability
• ·         Manageability

Partitioning Strategies:

•      Range based
•       List based
•       Hash

Hash Partitioning :

Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Rows are mapped into partitions based on a hash value of the partitioning key

CREATE table SCOTT.STUDENT_HASH_PARTITION

(STDNO number(8),

  FIRSTNAME varchar2(10),

  MIDDLENAME varchar2(10),

  LASTNAME varchar2(10),

  MOBILENO varchar2(12),

  PHONENO varchar2(12)

)

partition by hash(STDNO)

partitions 4

STORE IN (STUDENT1,STUDENT2,STUDENT3,STUDENT4);

Benefits of Table Partitioning :

• Instead of having a 100 gig tablespace to backup, you have 100, 1 gig tablespaces. (each tablespace spends less time in backup mode, reduces the amount of potential extra redo, reduces the amount of manual recovery you need to do if the instance failes during backup).  same with restores.
• instead of having an index on a 100 gig table, you have say 100 indexes on 1 gig tables.  You can rebuild each one fast.... (and online with minimal overhead).  rebuilding a single index on a 100 gig table -- that would take maybe 100 times longer then each individual rebuild (true parallel query might reduce that).
• You can analyze each partition quickly, instead of running an analyze on a 100 gig table.
• You can reorg each partition independent of any other partition
• You can easily redistribute the load over many disks, you now have evenly distributed the data into 100 1 gig partitions, move them at will.  If you did this manually -- you would have a heck of a time moving the stuff around.

How can I delete the data in a single partition :
   SQL> ALTER TABLE <TABLENAME> DROP PARTITION <PARTITIONNAME>;

Tablespace Encryption

It introduced in 10g R2 .

It prevents the access to it from the Operating system. It allows encryption of the entire content of tablespace instead of column-by-column basis.

Wallet Creation:

Wallet must be created to hold the encryption key.

To setup the Encryption wallet Location, Configure the parameter in sqlnet.ora file

ENCRYPTION_WALLET_LOCATION=

  (SOURCE=(METHOD=FILE)(METHOD_DATA=

    (DIRECTORY=/u01/app/oracle/admin/DB11G/encryption_wallet/)))

Then the following command creates and open the wallet.

CONN sys/password@db11g AS SYSDBA

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "myPassword";

WALLET must be reopened after an instance restart and can be closed to prevent access to encrypted data

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";

ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

Tablespace Creation:

CREATE TABLESPACE encrypted_ts

DATAFILE '/u01/app/oracle/oradata/DB11G/encrypted_ts01.dbf' SIZE 128K

AUTOEXTEND ON NEXT 64K

ENCRYPTION USING 'AES256'

DEFAULT STORAGE(ENCRYPT);

ALTER USER test QUOTA UNLIMITED ON encrypted_ts;

SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME                ENC

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

SYSTEM                         NO

SYSAUX                         NO

UNDOTBS1                       NO

TEMP                           NO

USERS                          NO

ENCRYPTED_TS                   YES

6 rows selected.

Test Encryption:

CONN test/test@db11g

CREATE TABLE ets_test (

  id    NUMBER(10),

  data  VARCHAR2(50)

)

TABLESPACE encrypted_ts;

CREATE INDEX ets_test_idx ON ets_test(data) TABLESPACE encrypted_ts;

INSERT INTO ets_test (id, data) VALUES (1, 'This is a secret!');

COMMIT;

Database Startup :

SQL>CONN / AS SYSDBA

Connected.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

Total System Global Area  626327552 bytes

Fixed Size                  2255832 bytes

Variable Size             234882088 bytes

Database Buffers          381681664 bytes

Redo Buffers                7507968 bytes

Database mounted.

Database opened.

SQL> SELECT tablespace_name, encrypted, status FROM dba_tablespaces;

TABLESPACE_NAME                ENC STATUS

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

SYSTEM                         NO  ONLINE

SYSAUX                         NO  ONLINE

UNDOTBS1                       NO  ONLINE

TEMP                           NO  ONLINE

USERS                          NO  ONLINE

EXAMPLE                        NO  ONLINE

SOE                            NO  ONLINE

ENCRYPTED_TS                   YES ONLINE

8 rows selected.

SQL> SELECT * FROM test.ets_test;

select * from test.ets_test

                   *

ERROR at line 1:

ORA-28365: wallet is not open

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";

System altered.

sys@db11g> select * from test.ets_test;

        ID DATA

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

         1 This is a secret!

1 row selected.

SQL>

DML Error Logging while Inserting records

Create a log table  (Eg: Emp_Err_Log)

  INSERT INTO dw_empl
  SELECT empno, first_name, last_name, hire_date, sal, Deptno
  FROM emp
  WHERE hire_date > sysdate - 7
  LOG ERRORS INTO Emp_Err_Log('daily_load') REJECT LIMIT 25

If the records are exceeded 25 in Emp_Err_Log, the transaction will rollback.

Questions at techm

1. Difference between for loop cursor and Cursor declaration, Open Fecth and Closing the cursor
2. CASE vs DECODE
3. What are the 3 characteristics of a primary key (Unique+Notnull+?)
4. Deleting duplicate data without using NOT IN
5. Difference between IN, OUT and IN/OUT parameters 
6. How to return bulk data using function 
7. What is Pragma directive
8. Take two tables A and B which are having same structure. Can we write the query to list the records in the table A which are not available in B , using joins

2nd round

1. What is Local and Global Indexes
2. Index Organization 
3. Parellel execution 
4. What is a mutating error in triggers and how to resolve it 
5. What happens internally in architecture when a query fires 
6. what is forward declaration in oracle packages
7. How will you tune if a procedure is performance is poor
8. Analytical functions 
9. how to access an oracle table from a SQL server database 
10. If i have 2 million records in a source table and I want to load those records into a destination table using some business logic. How to get it done ?

Questions at Veri

1. List some Plsql datatypes 
2. How to insert and manipulate XML data 
3. Case vs Decode
4. Procedure vs function
5. exp vs expdp
6. How will you convey that a table needs normalization
7. listagg function

Design considerations for PL/SQL Code

• Create Standard Constants and Exceptions
  
• Write and call local sub-programs
  Local sub-programs must be declared at thet end of the declaration section after all local variables
  
• Control run-time privileges of a subprogram
  
• Perform autonomous transactions
  
• Use NOCOPY hint
  
• Use PARALLEL_ENABLE
  
• Use Deterministic clause
  
• Use Bulk Binding and the RETURNING clause with DML