What is Pivoting insert in Oracle?

Pivoting insert:  In your Datawarehouse we come across situations where non-relational data has to be stored in a relational format .Here we can use the pivoting insert statement.  When we have data as empid,weekid,sales_mon,sales_tues,sales_wed,...etc.  We can use in a more relational format in a table as empid,week,sales columns.  So pivoting is an operation in which one has to build a transformation such that each record from any input stream, such as a non-relational database table, must be converted into multiple records for a more relational database format.

EX: We have source table as sales_source

SQL> create table  sales_source (empno number(5), weekid number(2),sales_m number(8,2),sales_tu number(8,2),sales_w number(8,2),sales_th number(8,2), sales_f number(8,2));

Table created.

In your data warehouse, you would want to store the records in a more typical relational form in a fact table sales_info

SQL> create table sales_info (empid number(6), week number(2),SALES NUMBER(8,2));

Table created.

SQL> insert into sales_source values(176,6,2000,3000,1000,5000,6000);

1 row created.

SQL> insert all into sales_info values(employid,weekid,sales_mon)

    into sales_info values(employid,weekid,sales_tu)

    into sales_info values(employid,weekid,sales_wed)

    into sales_info values(employid,weekid,sales_th)

    into sales_info values(employid,weekid,sales_f)

    select empno employid,weekid weekid,sales_m sales_mon,

    sales_tu sales_tu,sales_w sales_wed,sales_th sales_th,sales_f sales_f from  sales_source;

5 rows created.

SQL> select * from sales_info;

     EMPID       WEEK      SALES

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

       176          6       2000

       176          6       3000

       176          6       1000

       176          6       5000

       176          6       6000

Informatic Workflow

You can monitor the workflow status for a job in the Workflow Monitor. 

Workflow Monitor

After you create a workflow, you run the workflow in the Workflow Manager and monitor it in the Workflow Monitor. The Workflow Monitor is a tool that displays details about workflow runs in two views, Gantt chart view and Task view. You can monitor workflows in online and offline modes.

The Workflow Monitor consists of the following windows:

• Navigator window. Displays monitored repositories, servers, and repositories objects.
• Output window. Displays messages from the Power Center Server.
• Time window. Displays progress of workflow runs.
• Gantt Chart view. Displays details about workflow runs in chronological format.
• Task view. Displays details about workflow runs in a report format.

Workflow Manager

The Workflow Manager consists of three tools to help you develop a workflow:

• Task Developer. Create tasks you want to accomplish in the workflow in the Task Developer.
• Workflow Designer. Create a workflow by connecting tasks with links in the Workflow Designer. You can also create tasks in the Workflow Designer as you develop the workflow.
• Worklet Designer. Create a worklet in the Worklet Designer. A worklet is an object that groups a set of tasks. A worklet is similar to a workflow, but without scheduling information. You can nest multiple worklets inside a workflow.

Before you create a workflow, you must configure the following connection information:

• PowerCenter Server connection. Register the PowerCenter Server with the repository before you can start it or create a session to run against it.
• Database connections. Create connections to source and target systems.

• Other connections. If you want to use external loaders or FTP, you configure these connections in the Workflow Manager. 

Loading Data:

In the Workflow Manager, you define a set of instructions to execute tasks, such as sessions, emails, and shell commands. This set of instructions is called a workflow.

After you create a workflow in the Workflow Designer, the next step is to add tasks to the workflow. The Workflow Manager includes tasks, such as the Session task, the Command task, and the Email task so you can design your workflow. The Session task is based on a mapping you build in the Designer.

You then connect tasks with links to specify the order of execution for the tasks you created. Use conditional links and workflow variables to create branches in the workflow.

When the workflow start time arrives, the PowerCenter Server retrieves the metadata from the repository to execute the tasks in the workflow.

  

Workflow Manager Windows

The Workflow Manager displays the following windows to help you create and organize workflows:

• Navigator. Allows you to connect to and work in multiple repositories and folders.
• Workspace. Allows you to create, edit, and view tasks, workflows, and worklets.
• Output. Displays messages from the PowerCenter Server and the Repository Server. The Output window also displays messages when you save or validate tasks and workflows.
• Overview. An optional window that makes it easier to view workbooks containing large workflows. Outlines the visible area in the workspace and highlights selected objects in color. Choose View-Overview Window to display this window.

What are Global and Local Indexes?

LOCAL INDEXES

Local partitioned indexes are easier to manage than other types of partitioned indexes. They also offer greater availability and are common in DSS environments.

In the local prefixed index the partition key is specified on the left prefix. Local keyword tells oracle to create a separte index for each partition.Local prefixed indexes can be unique or non unique and is easier to manage

Ex:CREATE INDEX invoices_idx ON invoices (invoice_date)

LOCAL (PARTITION invoices_q1 TABLESPACE users,  

PARTITION invoices_q2 TABLESPACE users);

GLOBAL INDEXES

A global Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and this the global clause allows you to create a non-partitioned index.

Global indexes may perform uniqueness checks faster than local (partitioned) indexes.Also you cannot create global indexes for hash partitions or subpartitions.

Ex:

SQL> CREATE INDEX invoices_idx

ON COST_TABLE(invoice_date) 

GLOBAL PARTITION BY RANGE (invoice_date)

(PARTITION invoices01 VALUES LESS THAN 

(TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,

PARTITION invoices02 VALUES LESS THAN 

(TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users);

Similar to table partitions, it is possible to move them from one device to another. But unlike table partitions, movement of index partitions requires individual reconstruction of the index or each partition (only in the case of global index).

Ex:

SQL> alter index stud_ind rebuild partition p2

Index partitions cannot be dropped manually.They are dropped implicitly when the data they refer to is dropped from the partitioned table.

Get range of records from SQL query Oracle

SELECT *
  FROM (SELECT a.*,
               row_number() over (order by userid asc) rn
          FROM activeSessionsLog a)
 WHERE rn BETWEEN 8 AND 10

SQL> ed
Wrote file afiedt.buf

  1  select empno, ename, job
  2    from (select e.*,
  3                 row_number() over (order by empno) rn
  4            from emp e)
  5*  where rn between 1 and 3
SQL> /

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7499 ALLEN      SALESMAN
      7521 WARD       SALESMAN

SQL> ed
Wrote file afiedt.buf

  1  select empno, ename, job
  2    from (select e.*,
  3                 row_number() over (order by empno) rn
  4            from emp e)
  5*  where rn between 4 and 8
SQL> /

     EMPNO ENAME      JOB
---------- ---------- ---------
      7566 JONES      MANAGER
      7654 MARTIN     SALESMAN
      7698 BLAKE      MANAGER
      7782 CLARK      MANAGER
      7788 SCOTT      ANALYST

SQL> ed
Wrote file afiedt.buf

  1  select empno, ename, job
  2    from (select e.*,
  3                 row_number() over (order by empno) rn
  4            from emp e)
  5*  where rn between 9 and 11
SQL> /

     EMPNO ENAME      JOB
---------- ---------- ---------
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN
      7876 ADAMS      CLERK
 SELECT c.*
  FROM (SELECT b.*, rownum rn
          FROM (SELECT a.*
                  FROM activeSessionsLog a
                 ORDER BY userid asc) b
         WHERE rownum <= 10) c
 WHERE rn >= 8