Python Basic questions

1. isinstance() function helps to check whether the object belong to specified Class.
    Eg: >>>isinstance(10,int)
                 True 
   
   
2. map() function

Data masking in Oracle - VPD

WIP

pipeline functions in oracle

Coming soon ...

Indexing

How to get the status of Indexes: 

SQL> select * from all_indexes where status = 'VALID';

SQL> SELECT INDEX_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED
     FROM   DBA_INDEXES

The status can be 

• VALID 
• INVALID
• N/A
• UNUSABLE

if the status is 'N/A', that is the partitioned index.  
We need to query the partition details in DBA_IND_PARTITIONS to get the 
status of particular partition.

SQL> SELECT PARTITION_NAME, STATUS, SEGMENT_CREATED
     FROM   DBA_IND_PARTITIONS;

UNUSABLE indexes will not hold any space, so below query return no records incase 

of Unusable indexes 

SQL> SELECT SEGMENT_NAME, BYTES
     FROM   USER_SEGMENTS
     WHERE  SEGMENT_NAME IN ('I_EMP_ENAME', 'EMP_EMAIL_UK');

DWH Concepts

 What is DWH? 

It is a relational database designed for analytical needs 

It functions on basis of OLAP 

It is a central location where consolidated data from multiple locations (databases) are stored 

It is also called as the process of transforming data into information 

OLAP: 

OLAP is a flexible way to make complicated analysis of multidimensional data

Opens up new views of looking at data 

Supports filtering/sorting of data 

Data can be refined 

Types of OLAP Cubes:

MOLAP - Stores data directly into multidimensional database. Excellent performance and can perform complex calculations. Only limited amount of data can be handled 

ROLAP - Performs dynamic multidimensional analysis of data stored in relational database rather than in multidimensional database. Greater amount of data can be processed. Requires more processing time/disk space. 

HOLAP - Hybrid OLAP, combination of MOLAP & ROLAP. It can drill-through from the cubes into underlying relational data

OLAP Operations:

ROLLUP: Performs aggregation on a data cube by either 

1. Climbing up a concept hierarchy for dimension 

2. Dimension reduction 

DRILL-DOWN: Reverse operation of ROLLUP 

1. Stepping down a concept hierarchy for a dimension 

2. Introducing new dimension 

SLICE: Provides a new sub-cube from one particular dimension in a given cube 

DICE:  Provides a new sub-cube from two or more dimension in a given cube

PIVOT: It is also known as rotation operation. It transposes X axis in order to provide an alternative presentation of data 

Dimensions:

• The tables that describe the dimensions involved are called dimensions.
• Dividing a data DWH project into dimensions provides structured information for analysis & reporting 
• End users queries on these dimension tables which contain descriptive information 

Facts:

• A fact is a measure that can be summed, averaged or manipulated 
• A Fact table contains two types of data. A dimension key and a measure
• Every dimension table is linked to a Fact table

Schemas:

• It gives logical description of entire database
• It gives about constraints, key values & how key values linked between tables 
• A database uses relational data model, while data warehouse uses Star, Snowflake and Fact Constellation schema 

Star Schema:

• Each dimension is represented in one dimension table 
• Every dimension table linked to a Fact tables 
• Fact table is at the center, which contains keys to every dimension table 

Snowflake Schema: 

• Dimension tables are normalized. Dimension table is split into multiple tables 

Galaxy Schema: 

• Also known as Constellation schema 
• Contains more than one Fact table 
• One dimension table can be shared by multiple Fact tables. Such dimensions are called as Conformed dimensions 

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)