Service oriented Architecture (SOA)

Hi


http://www.youtube.com/watch?v=0hyXOuvyq2Q&list=TLsaIHJaZiiPY

http://www.youtube.com/user/fourthmonthllc?feature=watch

Basic of SOA

Simple SOA - Mainframe( has data , business logic and UI , graphics )  and dummy terminal .But issue is scalability

Later came the PC where validation can be done by browser , like validating date this is client server .But updation on all the machine became difficult as the 1000 user updation in an office network will be difficult.

then the client/server gave way to 3 tier architecture  A browser a  application server and database server.

Now even will 3 tier architectue it becomes difficult to manage so came teh service oriented architecture

So the db , application service and SAP , oracle

first there were function

then there were object and clasees

issue was .net and java were not able to interact

then people started talking in terms of messages


xml messages.. so messages were standedise

then with it camse services

services are logical encapsulation of self contained
business functionality


services communicates in termsn of messages

-----------------------------
few of points that services should be able to do are

services should be to self define themselves

services should be maintained in repository where we can find them

we should be able to orchestrate services that is
we should be able to design our workflow and plac services in them

consider example of security service and order processing services

we shoud be able to first use security service and then order process
that is check if user is valid and them do order processing

or we shoudl be able to process the order first and then do security check

so the order of service can change

But consider the predominant n-tier development environment in use today -- Java EE:

presentation implemented with JSP/servlets
business logic implemented with POJOs or EJBs
data access logic implemented with JPA/Hibernat

first there were function

then there were object and clasees

issue was .net and java were not able to interact

then people started talking in terms of messages


xml messages.. so messages were standedise

then with it camse services

services are logical encapsulation of self contained
business functionality


services communicates in termsn of messages

-----------------------------
few of points that services should be able to do are

services should be to self define themselves

services should be maintained in repository where we can find them

we should be able to orchestrate services that is
we should be able to design our workflow and plac services in them

consider example of security service and order processing services

we shoud be able to first use security service and then order process
that is check if user is valid and them do order processing

or we shoudl be able to process the order first and then do security check

so the order of service can change

But consider the predominant n-tier development environment in use today -- Java EE:

presentation implemented with JSP/servlets
business logic implemented with POJOs or EJBs
data access logic implemented with JPA/Hibernat

first there were function

then there were object and clasees

issue was .net and java were not able to interact

then people started talking in terms of messages


xml messages.. so messages were standedise

then with it camse services

services are logical encapsulation of self contained
business functionality


services communicates in termsn of messages

-----------------------------
few of points that services should be able to do are

services should be to self define themselves

services should be maintained in repository where we can find them

we should be able to orchestrate services that is
we should be able to design our workflow and plac services in them

consider example of security service and order processing services

we shoud be able to first use security service and then order process
that is check if user is valid and them do order processing

or we shoudl be able to process the order first and then do security check

so the order of service can change

But consider the predominant n-tier development environment in use today -- Java EE:

presentation implemented with JSP/servlets
business logic implemented with POJOs or EJBs
data access logic implemented with JPA/Hibernat

Amazon web sevices alllows for

computer , storage and database services

http://www.youtube.com/watch?v=DERzYnthq1s

what is webservice

we are callign a function which is inside a software from other software
software which gives the call is called client and software which receives the request is called server



both these things can be writen in different languages client can be in .net and server function can be in java

in this the format of input and output messages is important

there is 2 ways a web service can be used either the severs provides WSDL file or there is a UDDI universal description discovery and integration on the internet where all service providers register their service and the whoever want can reach UDDI which will list all similar services with their WSDL file

there are RESTful web services using rest protocol and SOAP web services


RESTful web services are accessed by passing URI from the webserve

server would send teh representation fo that service to client this can be in XML

REST ful webservices uses HTTP. they use 4 main http methods

GET retrieve a resource
POST create a resource
PUT update a resource
DELETE deletes a resource

What are Amazon web services

Hi

What is a webservice

What is Hadoop & BIG data

Below is a good link

http://readwrite.com/2013/05/23/hadoop-what-it-is-and-how-it-works#awesm=~odc8IiyozQvfDZ

also can check out on you tube

apche HDFS , mapreduce , apache hbase

Below is article by Akash Mitra . I liked it

http://www.dwbiconcepts.com/data-warehousing/18-dwbi-basic-concepts/176-what-is-big-data.html

http://www.dwbiconcepts.com/data-warehousing/18-dwbi-basic-concepts/2-map-reduce.html

Vijay Thakorals Blog on Hadoop Name node and its details

http://vijayjt.blogspot.in/2013/02/hadoop-namenode-web-interface.html

Good article on Hadoop from Yahoo developers site

http://developer.yahoo.com/hadoop/tutorial/module1.html

Mongo db tutorial videos on youtube

http://www.youtube.com/watch?v=bVRqd8mnQ6c&list=PLw2e3dFxewkIS1YjkLcdCUI5BPBg_YMwD

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

GFS -- white paper

Mapreduce --

Big table -- these are google whitepaper

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

To read 1 terabyte of data from one machine which

has 4 hard drives so 4 i/o channels each channel

has 100M/B per second speed

240000mb per minute 24gb per minute s0 240 gb

every 10 minutes so 43 or so minutes to read 1

terabyte of data

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

Now hadoop allows you to spread that across 10

machines so to read 1 terabyte it takes 4.5

minutes

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

name node is the master of file system

and data node is the slave
--------------------------------------

name node has metadata for data like which block

the data is broken into and where the file

resides

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

multiple data nodes are having a copy of file so

single file is stored on multiple nodes

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

name node has a webserver which gives the

information like how many webnodes makes

theserver

---------------------------
what is the format of data in HDFS .like we

usually format our harddisk to ntfs

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

for hadoop -- we have ext3 , ext4 ,XFS


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

your command will depends on file system you use

like command prompt.

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

how does your racks work like core switches.

Each data node is kept in rack.

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

Few thing to note about Hadoop

Hadoop can be deployed on raid configured drives.Raid is just configuring hard drives it not files system .Hadoop can be also deployed on cassandra file system

Apache Hbase -- Big table
Apache Flume -- RDBMS connect to hadoop
OOzie -- used for scheduling
HUE -- graphical interface for hadoop -- all these are offering by cloudera

PIG and Hive i am not sure that they do .


Latency --  it is the measure of time delay

Throughput -- amount of work a computer can do in given time frame.

Hadoop is designed for through put and not latency.Its like a train and not a sports car.It can pull huge data and you can notice it going only when data is really big .

Datawarehousing Fundamentals

Hi Guys,

This month July 2013 i am working on optimization of report on datawarehouse. So there are several post on query optimizatoin in July .Different techniques i tried .

We can achieve some amount of optimization by Partitining , indexing and making sure that those indexes are used correctly . Reading explain plans will help.I have some articles on reading explain plans and Oracles basics in July 2013.Now after all this you will achieve some improvement.But still you want better then it comes to design on datawarehouse .What attributes are kept in your dimension.How is your fact table structured.

I found below article by Kimball Group very helpful.I think most datawarehousing guys have read his book (Ralph Kimball) .If not do read.I found below article on basic design helpful

http://www.kimballgroup.com/2009/05/29/the-10-essential-rules-of-dimensional-modeling/

Now this is standard point.but in some cases we see a deviation

 Ensure that every fact table has an associated date dimension table.

Consider you need to store 24 months data for a particular dimension.You have a product , A product group that can be dynamically created by user like suppose he want to group ( toothpaste, biscuits , soap, tea,Milk) into highly sellable items .He creates group Sellable A. So you will have.

Sorry 24 rows till July 2011 i meant there

Now this has been model as shown below

We can combine product and product group together into single dimension . Also we can add one more time dimension to take care of months .Now look at the fact.Its a wide table because of 24 months columns.Now you want to make it a long table if you add a time dimension

Rule #5: Resolve many-to-many relationships in fact tables.

Since a fact table stores the results of a business process event, there’s inherently a many-to-many (M:M) relationship between its foreign keys, such as multiple products being sold in multiple stores on multiple days. These foreign key fields should never be null. Sometimes dimensions can take on multiple values for a single measurement event, such as the multiple diagnoses associated with a health care encounter or multiple customers with a bank account. In these cases, it’s unreasonable to resolve the many-valued dimensions directly in the fact table, as this would violate the natural grain of the measurement event. Thus, we use a many-to-many, dual-keyed bridge table in conjunction with the fact table.


Rule #6: Resolve many-to-one relationships in dimension tables.
Hierarchical, fixed-depth many-to-one (M:1) relationships between attributes are typically denormalized or collapsed into a flattened dimension table. If you’ve spent most of your career designing entity-relationship models for transaction processing systems, you’ll need to resist your instinctive tendency to normalize or snowflake a M:1 relationship into smaller subdimensions; dimension denormalization is the name of the game in dimensional modeling.
It is relatively common to have multiple M:1 relationships represented in a single dimension table. One-to-one relationships, like a unique product description associated with a product code, are also handled in a dimension table. Occasionally many-to-one relationships are resolved in the fact table, such as the case when the detailed dimension table has millions of rows and its roll-up attributes are frequently changing. However, using the fact table to resolve M:1 relationships should be done sparingly.

Why to migrate to Teradata from Oracle 11g

Hi Guys

( July 2014) My knowlege on the matter has matured over the span on year. Here is link for article on Shared nothing (Teradata ) and Shared disk (Oracle RAC) architure difference article. You can read the below article, Its basic level on different types of architure for more matured reading check latest article.

Now so many people have migrated to teradata they say its great for terabytes of information.So i am plannign to write some basic points on teradata.

 If you want to compare Oracle and Teradata compare Oracle Exadata with Teradata. Do not compare Oracle RAC with teradata.

The basis difference between Oracle RAC and Teradata is architecture they use. There are two kinds of architecture Shared Nothing and Shared everything. Oracle has shared everthing and Teradata has shared nothing. So whatever the strength of their architecture that is their strength.

Netezza has share nothing architecture like teradata.

Share nothing is like MPP and Shared everything is a SMP. Your dual core processor is a SMP it has 2 processors inside one single intel chip and both processors share same RAM and same hard disk. So disadvantage of this system would be your hard disk speed. Though your processors can do a lot you are limited by hard disk access speed and RAM space. So even in case of parallel processing its not so fast. Remember even if you are only inserting data into database it will check for consitency that is to check that data has not changed so for inserting as well there is overhead for reading.SMP also has some overhead of making sure what other processors are doing.

Share nothing is a MPP. Each processor has its dedicated RAM and dedicated hard disk. It has some overhead with coordination among processors. Apart from that major drawback is data shipping or distribution of data across disk of each processor.

MPP works well for datawarehousing or OLTP the overhead of distributing data across disk is not worth it if you only retrieving single records. MPP is like a train. Good for Throughput. Its not a sports car where speed is fast for retrieval. But if you are retrieving huge data like train pulling lot of boggies you will see train is faster for that load.


But this does not mean Teradata is better that Oracle for datawarehousing. Its not that simple. Check ASK tom for what he says on this. (its not simple as to direct comparison)

SMP appears to use a single disk with help of RAID. Almost all servers use RAID configuration to store your data. Read for basics on RAID

One major difference in Shared Everything is its a SMP (Symmetric multiprocessor ) system. That is all processors use same main memory (RAM) . So its like your intel i5 on your laptop. Shares the same RAM with all its 4 processors inside.

But in a MPP system like teradata. Each processor has its own dedicated main memory (RAM)

Difference between Shared nothing and Shared Everything

http://cognossimplified.blogspot.in/2013/08/basics-about-servers.html

Oracle Exadata uses Infiband to connect between server and disks. It does not use a fibre channel.Fibre channel has a speed of upto 16 Gigabits per second but Infiband has (best one) 300Gigabits per seconds .Your hard disk has rate of 500 giba bits per seconds. So its almost like directly accessing from drive there is no network delay for data to come to server from hard disk. Here hard disk are not part of node(server--only RAM and processor) its share everything architecture

Oracle Exadata is closest to shared nothing . But is not our typical shared nothing with each cpu having its own memory (RAM) .

Very Few database are MPP .Teradata , HP Vertiga , Kognito , Netezza 

How hardware VT works---BIOS has VT enable for virtulization of processor

In computing, x86 virtualization is the facility that allows multiple operating systems to simultaneously share x86 processor resources in a safe and efficient manner, a facility generically known as hardware virtualization

In protected mode the Kernel runs at a higher privilege such as ring 0, and applications at a lower privilege such as ring 3. Similarly, a host OS must control the processor while the guest OSs are prevented from direct access to the hardware. One approach used in x86 software-based virtualization is called ring deprivileging, which involves running the guest OS at a ring higher than 0.[1]

How hardware VT works

default username password is root root

To say you need to migrate to Teradata from your usual Oracle RAC. Have you experienced any of the below( I have found anyone seeing a issue and then migrating)

1) Synching of Buffer cache between different RAC nodes causing bottlenecks ?
2)  High waits on accessing disk blocks- This can be due to any other reason too ?
3) Bottleneck caused by interconnect for disk access. ?

What is a Rules Engine

Hi Guys,

I came across this term during one of my regular discussion so thought of reading more

Two Layer Query - Report building technique

Hi Guys,

We all must have built many report.We first build reports then check how its performing.Now lets start report building by looking at Explain plans

The Article below is very good . It explains how we can overcome the lack of Aggregate Navigation feature in cognos. Aggregate navigator means when we have summary table and detailed table you want to Summary table on the fly based on the query.

It also explains two layered query approach for better Explain plans in oracle. Your can also check out my other articles on Oracle explain plans by typing " Cognossimplified Oracle Explain plans"



http://ibmcognosrmug.files.wordpress.com/2012/03/cognos_user_group_presentation.pdf

Oracle Database Concepts III

Hi Guys,

This is Part 3 of Oracle database concepts .For Part 1 and Part 2 use links below

http://cognossimplified.blogspot.in/2013/07/oracle-database-concepts.html

http://cognossimplified.blogspot.in/2013/07/oracle-database-concepts-ii.html

Partition in Oracle 

They are very important in datawarehouse.Especially for facts as most of the time we are playing with date ranges and partition can speed up the query 

Read this detailed article from oracle 

http://docs.oracle.com/cd/A87860_01/doc/server.817/a76956/partiti.htm

There are two type of partitioning 

• Single level 
• Composite 

There are 3 types for each

• Range
• Hash 
• List 

Hash is used when there is no obvious partitioning key available makes sure each partition has same amount of data 

Range to be used when you have a fact which keeps montly data so Jan 2013 , Feb2013 and so on each partition for each of the months.

what is partition pruning

Partition pruning dramatically reduces the amount of data retrieved from disk and shortens processing time, thus improving query performance and optimizing resource utilization. If you partition the index and table on different columns (with a global partitioned index), then partition pruning also eliminates index partitions even 

when the partitions of the underlying table cannot be eliminated.

Depending upon the actual SQL statement, Oracle Database may use static or dynamic pruning. Static pruning occurs at compile-time, with the information about the partitions accessed beforehand. Dynamic pruning occurs at run-time, meaning that the exact partitions to be accessed by a statement are not known beforehand. A sample scenario for static pruning is a SQL statement containing a WHERE condition with a constant literal on the partition key column. An example of dynamic pruning is the use of operators or functions in the WHERE condition.Partition pruning affects the statistics of the objects where pruning occurs and also affects the execution plan of a statement.

Below sql will give which tables in your schema are partitioned 

 select * from USER_PART_TABLES

Very good article on partition pruning 

http://www.orafaq.com/tuningguide/partition%20prune.html

create table part_trial (col1 int , col2 int )

 partition by range (col1)

 ( partition p1 values less than (1)

 )

alter table part_trial add

 partition p2 values less than (3)

While creating table you need to have a table with partition then only you can add one more partition to it. And note in the alter statement look we are not specifying the range keyword

insert into part_trial values (1,1);

insert into part_trial values (1,2);

insert into part_trial values (1,3);

insert into part_trial values (1,4);

insert into part_trial values (1,5);

insert into part_trial values (1,6);

 explain plan for

 select * from part_trial

 where col1 = 1

Notice that it accesses only the single partition which is required

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

| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT       |            |     6 |   156 |    14   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE|            |     6 |   156 |    14   (0)| 00:00:01 |     2 |     2 |

|*  2 |   TABLE ACCESS FULL    | PART_TRIAL |     6 |   156 |    14   (0)| 00:00:01 |     2 |     2 |

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

execute dbms_stats.gather_table_stats(user,'trial')

select partition_position, partition_name, num_rows  from user_tab_partitions where  table_name = 'TRIAL'

alter table TRIAL add partition

Below 7 points are taken from ASK tom question on hash partitioning


1) Hash partitions number is always in power of two, yes - 2, 4, 8, 16, 32, 64, ... and NO other numbers in between.
2) Else the data will be skewed across partitions with most of the data in the "middle" partitions and little data on the end partitions.
3)Also, a hash key should be fairly unique - lots and lots and lots of distinct values, else, you won't achieve even distribution of data
4) it is not record driven. 1,000,000 records might be 10mb, 100mb, 1tb - it is about volume.
5)What is the biggest segment you want to have?
What are you trying to accomplish with partitioning - what is your reason for using it - that will drive your partition sizes and your partitioning scheme.
6)understand WHY you are partitioning. then and only then APPLY it
7) when you APPLY it, make sure you understand the ramifications of doing so and consider each and ever index independent of every other index. eg: say this is an employee table and you hash partition into 16 partitions by empno.

Say you have an index on last_name. Say you just locally partition that index on last_name

when you query: select * from t where last_name=:x

you will do 16 index range scans - you will do 16 times the work!

you would have wanted to either

a) not partition that index
b) partition that index by range - A-K go into this partition, L-Z go into this one
c) hash partition it (if you always use equals)
 

Types of dimensions in datawarehouse

Before analysing explain plan indexes please have a look at the type of dimension implemented in your datawarehouse.

Below is a wikipedia link. I cant explain better than that

http://en.wikipedia.org/wiki/Slowly_changing_dimension

Type 1 -- No history information

Type 2 -- Has a column star date and End date

Type 3 - Adds one addition column last value , last to last value.Its number of columns will increase with each addition of history information

Difference between a index and rowid

• Rowid is a part of table it gives the direct address of the row.
• whereas indexes are separate from table they contain the block address of a row.
• Oracle needs to read the full block and discard the unnecessary rows .

What are hash joins 

In HASH JOIN method of oracle, HASH TABLE will be built on one of the tables and other will be joined depending on the values in the hash table.

So, what is it? it's just a bunch of key-value pairs. The data is stored as a linked list with head nodes that group the data by the use of something called HashCode to find things faster. Something like this:

a    ->     b     ->    c
Any         Bitter      Class
Array       Bold        Count

A hash table is a table where you can store stuff by the use of a key. It is like an array but stores things differently

what are bind variables(They are generally for OLTP not Datawarehouse)

To understand bind variables, consider an application that generates thousands of SELECT statements against a table; for example:

SELECT fname, lname, pcode FROM cust WHERE id = 674;
SELECT fname, lname, pcode FROM cust WHERE id = 234;
SELECT fname, lname, pcode FROM cust WHERE id = 332;

Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is know as a «hard parse» and for OLTP applications can actually take longer to carry out that the DML instruction itself.

When looking for a matching statement in the shared pool, only statements that exactly match the text of the statements are considered; so, if every SQL statement you submit is unique (in that the predicate changes each time, from id = 674 to id=234 and so on) then you'll never get a match, and every statement you submit will need to be hard parsed. Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas, which whilst it might not affect a single program running against a small set of data, can bring a multi-user system to it's knees if hundreds of copies of the program are trying to hard parse statements at the same time. The extra bonus with this problem is that contention caused by hard parsing is pretty much immune to measures such as increasing available memory, numbers of processors and so on, as hard parsing statements is one thing Oracle can't do concurrently with many other operations, and it's a problem that often only comes to light when trying to scale up a development system from a single user working on subset of records to many hundreds of users working on a full data set.

The way to get Oracle to reuse the execution plans for these statements is to use bind variables. Bind variables are «substituion» variables that are used in place of literals (such as 674, 234, 332) and that have the effect of sending exactly the same SQL to Oracle every time the query is executed. For example, in our application, we would just submit

SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;

and this time we would be able to reuse the execution plan every time, reducing the latch activity in the SGA, and therefore the total CPU activity, which has the effect of allowing our application to scale up to many users on a large dataset

Conclusion on Bind Variables

Lastly, it's worth bearing in mind that there are some instances where bind variables are probably not appropriate, usually where instead of your query being executed many times a second (as with OLTP systems) your query in fact actually takes several seconds, or minutes, or hours to execute - a situation you get in decision support and data warehousing. In this instance, the time taken to hard parse your query is only a small proportion of the total query execution time, and the benefit of avoiding a hard parse is probably outweighed by the reduction in important information you're making available to the query optimizer - by substituting the actual predicate with a bind variable, you're removing the ability for the optimiser to compare your value with the data distribution in the column, which might make it opt for a full table scan or an index when this isn't appropriate. Oracle 9i helps deal with this using a feature known as bind variable peeking, which allows Oracle to look at the value behind a bind variable to help choose the best execution plan.

Another potential drawback with bind variables and data warehousing queries is that the use of bind variables disallows the potential for star transformations, taking away this powerful option for efficiently joining fact and dimension tables in a star schema.

TableSpace

Oracle has tablespaces inside which tables , index , materialised views are created

I am not clear how data can be kept in different tablespaces for a partitions

Below are types of tablespaces 

Permanent tablespaces
Permanent tablespaces are used to store user data and user created objects like tables, indexes and materialized views. Sample create statements:

Temp tablespaces
Temp or temporary tablespaces are used to store data with short lifespan (transient data), for example: global temporarily tables or sort results.

Undo tablespaces
Undo tablespaces are used to store "before image" data that can be used to undo transactions. See ROLLBACK.This needs to be understood in more detail as in datawarehousing tools we have commit intervals which are related to this. You have set commit interval such that you are making maximum use of your undo tablespace.


Locking on Tables -- Have you ever noticed ORA-00054 -No wait on resource.

consider you are loading a table through ETL tool and you try to drop it you will get this error. You can also get this error while loading data when you try to drop a index. I will try to update more on locking later


How to create a dummy table

INSERT INTO
  T1
SELECT
  ROWNUM,
  DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
  DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
  TRUNC(SYSDATE)+TRUNC(ROWNUM/100),
  DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
  DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
  RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A'),
  DECODE(MOD(ROWNUM,5),0,NULL,
   RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A')),
  DECODE(MOD(ROWNUM,5),0,NULL,
   RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A'))
FROM
  DUAL
CONNECT BY

  LEVEL<=200000;


CREATE TABLE dummy (USERID NUMBER,
                  USERNAME VARCHAR2(20),
                    USERPWD VARCHAR2(10),
                    EMAILID VARCHAR2(20),
                    FIRSTNAME VARCHAR2(10),
                    LASTNAME VARCHAR2(10),
                    ISACTIVE NUMBER)
                   
                    select count(*) from dummy
                   
                    commit          
                                     
                   
                    INSERT INTO dummy
   SELECT srl,
             name,
             pwd,
             LOWER(SUBSTR(name, 1, 10)) || '@abc.com',
             SUBSTR(name, 1, 10),
            SUBSTR(name, 11, 20),
             1
        FROM (
     SELECT level srl,
            dbms_random.string('U', 20) name,
            dbms_random.string('A', 10) pwd
       FROM DUAL
    CONNECT BY LEVEL <= 10000000)

Disclaimer and Citations 

The content here is taken from various sources found by googling. I have given links wherever possible.For me i dont need in detail information so i have copy pasted the basic information for my use.Also taken are comments from blogs , forum. I have added lot of information according to my understanding of subjects. If anyone finds anything objectionable please leave a comment.

Query Rewrite feature in Oracle

Hi Guys,

Query rewrite is a powerful feature in Oracle which you can use to speed up your cognos queries .In short you can have a materialised view which contains summary information and you dont need to include this materialized view in Framework.

When cognos run the query in database.Oracle will replace the query with MV results if it finds them suitable .

Cognos Does not support Aggregate awareness and this has to be implemented from database. For details you can read my article on Aggregate Awareness Article  

Taken from Oracle site 

http://docs.oracle.com/cd/B28359_01/server.111/b28313/qrbasic.htm

When base tables contain large amount of data, it is expensive and time-consuming to compute the required aggregates or to compute joins between these tables. In such cases, queries can take minutes or even hours. Because materialized views contain already precomputed aggregates and joins, Oracle Database employs an extremely powerful process called query rewrite to quickly answer the query using materialized views.

One of the major benefits of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables. The transformation is transparent to the end user or application, requiring no intervention and no reference to the materialized view in the SQL statement. Because query rewrite is transparent, materialized views can be added or dropped just like indexes without invalidating the SQL in the application code.

A query undergoes several checks to determine whether it is a candidate for query rewrite. If the query fails any of the checks, then the query is applied to the detail tables rather than the materialized view. This can be costly in terms of response time and processing power.

The optimizer uses two different methods to recognize when to rewrite a query in terms of a materialized view. The first method is based on matching the SQL text of the query with the SQL text of the materialized view definition. If the first method fails, the optimizer uses the more general method in which it compares joins, selections, data columns, grouping columns, and aggregate functions between the query and materialized views.

Analytic functions in Oracle

Hi Guys,

Now any time you are going for custom sql(feature in cognos where you can write your sql).Do use analytical functions .Me and people from my team for some reaosn always thought they slow down the query and so no one encouraged it .

But since analytical functions are oracle function they are there for improving query performance .Oracle introduced them to be faster

Check this link for analytical function.This guy has writen it well

http://www.orafaq.com/node/55

Oracle Database Concepts II

Hi Guys,

This is part 2 of Oracle database basics.For part 1 refer below link 

Link for Oracle Database basics Part 1


So consider there is a big query .First time you run it .It takes 30 seconds second time it takes 7 seconds.Now what is the query time (7 or 30) and why this happens.

Answer - First time it reads from physical memory (actual db) ,Next time it reads from cache.This is very important to understand for sql tuning (physical reads and Consistent gets) .

What is database Instance.

A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database.

Every running Oracle database is associated with at least one Oracle database instance. Because an instance exists in memory and a database exists on disk, an instance can exist without a database and a database can exist without an instance.(SGA Exists in your RAM).So more the RAM for your server its faster

When an instance is started, Oracle Database allocates a memory area called the system global area (SGA) and starts one or more background processes. The SGA serves various purposes, including the following:

•Maintaining internal data structures that are accessed by many processes and threads concurrently

•Caching data blocks read from disk

•Buffering redo data before writing it to the online redo log files

•Storing SQL execution plans

The SGA is shared by the Oracle processes, which include server processes and background processes, running on a single computer. The way in which Oracle processes are associated with the SGA varies according to operating system.

A database instance includes background processes. Server processes, and the process memory allocated in these processes, also exist in the instance. The instance continues to function when server processes terminate.


The most important SGA components are the following:
 

1. Database Buffer Cache 
2. Redo Log Buffer
3. Shared Pool
4. Large Pool
5. Java Pool
6. Streams Pool
7. Fixed SGA

SGA (contains buffer cache)

The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance” (an instance is your database programs and RAM).

Main Areas of SGA

1) The buffer cache (db_cache_size)
2)The shared pool (shared_pool_size)
3)The redo log buffer (log_buffer)

Main thing to understand is 

The Buffer Cache (also called the database buffer cache) is where Oracle stores data blocks.  With a few exceptions, any data coming in or going out of the database will pass through the buffer cache.

When Oracle receives a request to retrieve data, it will first check the internal memory structures to see if the data is already in the buffer. This practice allows to server to avoid unnecessary I/O

The database buffer cache holds copies of the data blocks read from the data files. The term data block is used to describe a block containing table data, index data, clustered data, and so on. Basically it is a block that contains data

An Oracle block is different from a disk block.  An Oracle block is a logical construct -- a creation of Oracle

Some more point on Buffer cache(For those looking for more detail)

The total space in the Database Buffer Cache is sub-divided by Oracle into units of storage called “blocks”. Blocks are the smallest unit of storage in Oracle and you control the data file blocksize when you allocate your database files.

An Oracle block is different from a disk block.  An Oracle block is a logical construct -- a creation of Oracle, rather than the internal block size of the operating system. In other words, you provide Oracle with a big whiteboard, and Oracle takes pens and draws a bunch of boxes on the board that are all the same size. The whiteboard is the memory, and the boxes that Oracle creates are individual blocks in the memory

Consistent Gets

A Consistent Get is where oracle returns a block from the block buffer cache but has to take into account checking to make sure it is the block current at the time the query started.

Oracle fetches pretty much all of its data that way. All your data in your database, the stuff you create, the records about customers or orders or samples or accounts, Oracle will ensure that what you see is what was committed at the very point in time your query started. It is a key part to why Oracle as a multi-user relational database works so well.
Most of the time, of course, the data has not changed since your query started or been replaced by an uncommitted update. It is simply taken from the block buffer cache and shown to you.

A Consistent Get is a normal get of normal data. You will see extra gets if Oracle has to construct the original record form the rollback data. You will probably only see this rarely, unless you fake it up.

Consistent Gets – a normal reading of a block from the buffer cache. A check will be made if the data needs reconstructing from rollback info to give you a consistent view but most of the time it won’t.
DB Block Gets – Internal processing. Don’t worry about them unless you are interested in Oracle Internals and have a lot of time to spend on it.
Physical Reads – Where Oracle has to get a block from the IO subsystem

Nicely explained by below blog 
http://mwidlake.wordpress.com/2009/06/02/what-are-consistent-gets/

My full article on Consistent Gets 

http://cognossimplified.blogspot.in/2013/09/using-consistent-gets-to-tune-sql.html 

What is latching 

Along with keeping physical I/O to minimum we also need to keep consistent gets to minimum as consistent gets involve latching. A latch is a lock.
Locks are serialization devices.Consider 2 users were accessing a table ( from cache ) when first user is reading a latch is set so that it reads only current data and 2 person has to wait till this latch is free

Serialization devices inhibit scalability, the more you use them, the less concurrency you get.

Though common idea is to keep physical read to minimum we also want to keep consistent gets minimum due to various reason like it might create latches issue and high cpu utilizatoin and for that consistent get to happen . physical i/o must have occured some time.

Consider you had a bigger cache that the physical i/o will be reduced but does that solve the probelem .NO


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6643159615303

what is recursive calls 

Sometimes, to execute a SQL statement issued by a user, the Oracle Server must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, the Oracle Server makes recursive calls to allocate the space dynamically if dictionary managed tablespaces are being used. Recursive calls are also generated:

When data dictionary information is not available in the data dictionary cache and must be retrieved from disk

• In the firing of database triggers
• In the execution of DDL statements
• In the execution of SQL statements within stored procedures, functions, packages and anonymous PL/SQL blocks
• In the enforcement of referential integrity constraints

The value of this statistic will be zero if there have not been any write or update transactions committed or rolled back during the last sample period. If the bulk of the activity to the database is read only, the corresponding "per second" metric of the same name will be a better indicator of current performance.



Dynamic sampling --- allows CBO to estimate number of rows for tables that are not analysed .Which helps it to come up with better estimation plan.

The number of rows show in dynamic sampling are not actual rows but a estimate from dynamic sampling

What is Clustering factor 

Oracle has something called cluster tables ( data from two tables having common column) saved on same block and index on such cluster table is called clustered index.

Now Clustering factor is something different 


the clustering_factor column in the user_indexes view is a measure of how organized the data is compared to the indexed column, is there any way i can imporve clustering factor of a index. or how to improve it

This defines how ordered the rows are in the index.  If CLUSTERING_FACTOR approaches the
number of blocks in the table, the rows are ordered.  If it approaches the number of rows in the table, the rows are randomly ordered.  In such a case (clustering factor near the number of rows), it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.

Note that typically only 1 index per table will be heavily clustered (if any).  It would be extremely unlikely for 2 indexes to be very clustered.

If you want an index to be very clustered -- consider using index organized tables.  They force the rows into a specific physical location based on their index entry.

Otherwise, a rebuild of the table is the only way to get it clustered (but you really don't want to get into that habit for what will typically be of marginal overall improvement)

Easy way to create a dummy table with lot of Rows

create table t ( a int, b int, c char(20), d char(20), e int );

 insert into t select 1, 1, 1, 1, rownum from all_objects;

create unique index t_idx on t(a,b,c,d,e);



Disclaimer and Citations 

The content here is taken from various sources found by googling. I have given links wherever possible.For me i dont need in detail information so i have copy pasted the basic information for my use.Also taken are comments from blogs , forum. I have added lot of information according to my understanding of subjects. If anyone finds anything objectionable please leave a comment.

How to Enable Statistics IO in sql developer

Hi Guys,

This is a part of Explaination of Explain plan and Statistics IO.Meant as trouble shooting article.

Note -- The statistics shown in sql developer and Sql plus are different. Check below for sql plus statistics from autotrace. Those are the most important ones .








Its not possible to show consistent gets in sql developer using Autotrace. So you need to use Sql Plus . It gives you the Execution plan. The plan that was actually used by sql to run.  Below are commands

How to use SQL Plus

SET long 500 longchunksize 500

SET LINESIZE 1024

SET AUTOTRACE TRACEONLY;

c:\users\kapil\desktop>sql plus user/password@tnsname >outputfilename.txt@fileofsql.sql

Below is example of Statistics IO 

   Statistics
-----------------------------------------------------------
               3  user calls
               0  physical read total bytes
               0  physical write total bytes
               0  spare statistic 3
               0  commit cleanout failures: cannot pin
               0  TBS Extension: bytes extended
               0  total number of times SMON posted
               0  SMON posted for undo segment recovery
               0  SMON posted for dropping temp segment
               0  segment prealloc tasks

If you try below command in sql developer

 set statistics IO on --For sql plus only.  
 set Autotrace on --- For sql developer it will show at the end of the script the statistics and the explain plans

you will get below error 

The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.

Some more options for Autotrace

set autotrace on: Shows the execution plan as well as statistics of the statement.
set autotrace on explain: Displays the execution plan only.
set autotrace on statistics: Displays the statistics only.
set autotrace traceonly: Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.
set autotrace off: Disables all autotrace

Why this Error

Sqltrace is not installed by default with oracle installation.We need to login with user with admin right and then follow below steps.

Steps

1) Login - sys as sysdba  password leave blank just press enter
2) My path of file

Path of the file.
E:\app1\ADMIN\product\11.2.0\dbhome_1\sqlplus\admin

Just write @ before E and then ; at the end and press enter.See screenshot below

3) Close sql developer and open again

4) select * from dual

Run as script you will get this




Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

   Statistics
-----------------------------------------------------------
               3  user calls
               0  physical read total bytes
               0  physical write total bytes
               0  spare statistic 3
               0  commit cleanout failures: cannot pin
               0  TBS Extension: bytes extended
               0  total number of times SMON posted
               0  SMON posted for undo segment recovery
               0  SMON posted for dropping temp segment
               0  segment prealloc tasks

How to read Explain plans for Sql tuning

Hi Guys,

The main idea to improve speed of query is to get the result by going through as little physical reads ( from disk) as possible.

We do indexing , partition all things so that we can easily identify our data from millions of rows.Without having to go through each row.So the idea behind reading explain plan is identifying which part of sql is not using best way to read from db.There might be one table which is doing full scan(reading entire table) to reach at a particular row (say employee id) .Now how can we make it reach it faster , may be by not having to go through entire table and just reading one row.There are number of techniques out there.

For datawarehouse a Bitmap index may speed up {My Actual case study on DW} .OR indexes on a particular column which is forcing a full table read may speed up. So lets look what is a explain plan and What is Statistics IO.

A word of Caution for reader - Below is a article i have writen on reading Explain plans.If you have good experience ( I mean you can write and debug sql query very very easily ) then only you should read this advance stuff.Its involves a lot of detailed discussion.Not for freshers.I am not discouraging anyone but for less experienced people it wont make much sense.

Some Notes before we start

Two things we need to tune sql (Explain plan and Statistics IO) ......Since Statistics IO is not set by default.Below are steps to enable it

If you are having difficulties understanding terms discussed in this article.Please go through below link
http://cognossimplified.blogspot.in/2013/07/oracle-database-concepts.html)

Below are steps to enable Statistics IO if you dont have it enabled.(Usually DBA would have enabled this )
http://cognossimplified.blogspot.in/2013/07/how-to-enable-statistics-io-in-sql.html

Example of Explain plan and Statistics IO 

  create table t ( a int, b int, c char(20), d char(20), e int );
 
  Insert into t select 1, 1, 1, 1, rownum  from all_objects ;( it will create a dummy table with lot of records)

create index a_indx on t (a) compute statistics;

set autotrace on

select a from t where a =1

Plan hash value: 2016457929

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        | 74275 |   942K|    44   (3)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| A_INDX | 74275 |   942K|    44   (3)| 00:00:01 |
-------------------------------------------------------------------------------

  Statistics
-----------------------------------------------------------
              13  user calls
               0  physical read total bytes
               0  physical write total bytes
               0  spare statistic 3
               0  commit cleanout failures: cannot pin
               0  TBS Extension: bytes extended
               0  total number of times SMON posted
               0  SMON posted for undo segment recovery
               0  SMON posted for dropping temp segment
               0  segment prealloc tasks

Very Important link gives the various terms used in explain plan in detail.

http://www.akadia.com/services/ora_interpreting_explain_plan.html

What is Index fast full scan ---They are similar to full table scans that is they read the full index and while doing so oracle will fetch the next multiple blocks in anticipation that they will be required.It makes use of flag db_file_multiblock_read . Similar to full table scan.It is used when we dont even need to touch db to get our data .See in this case the the physical read is zero.we got our result just from index scan as the column requested in select statement is a index column .

In index fast full scan index is read as a table. Normally in a index with mutliple leaf nodes we go from one node to other but in fast full scan we read all nodes. Not necessary in order and it will use multiblock i/o that is it will read from multiple blocks

select count(distinct deptno) from t

and either of EMPNO or DEPTNO is defined as "not null" -- we may very well use the INDEX
via a FAST FULL INDEX SCAN over the table (the index being a "skinny version" of the
table in this case.

Now lets change the column to one which is not indexed let see the response

select b from t where b =1

Notice that it goes for full table scan

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 74275 |   942K|   171   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    | 74275 |   942K|   171   (1)| 00:00:03 |
--------------------------------------------------------------------------

Now lets go for condition on A .We have a index on A

select * from t where A >1

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    83 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     1 |    83 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | A_INDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Two links which you might like

http://www.dwbiconcepts.com/database/22-database-oracle/26-oracle-query-plan-a-10-minutes-guide.html

http://www.dwbiconcepts.com/database/22-database-oracle/30-query-execution-plan-part2.html

A Query to understand DW concept

Hi Guys,

In month of July 2013 i was asked to improve reporting performance in datawarehouse.The reporting on DW was very slow .Report on DW were taking about 18 min to run which was unacceptable.

So i started from Oracle basics.You will see a lot of article on oracle ,Explain plan and other in July. After basics its time to understand how a query structure will be.How the actual joins should be taking place.Below is cognos GosalesDW query which has slightly Snowflaked schema same as my office environment so its good for analysis.In other article i am planning to study the explain plan of this and will try some tricks to improve plan.


SELECT (COALESCE("D2"."memberUniqueName2", "D3"."memberUniqueName2")) "memberUniqueName2",
  MIN((COALESCE("D2"."rc", "D3"."rc"))) over (partition BY (COALESCE("D2"."Product_type_key", "D3"."Product_type_key"))) "Product_type",
  (COALESCE("D2"."memberUniqueName4", "D3"."memberUniqueName4")) "memberUniqueName4",
  MIN((COALESCE("D2"."rc10", "D3"."rc8"))) over (partition BY (COALESCE("D2"."Retailer_country_key", "D3"."Retailer_country_key")), (COALESCE("D2"."Retailer_key", "D3"."Retailer_key"))) "Retailer_name",
  "D2"."memberUniqueName6" "memberUniqueName6",
  "D2"."Order_method_type" "Order_method_type",
  "D2"."Quantity" "Quantity",
  "D3"."Sales_target" "Sales_target",
  (COALESCE("D2"."Product_type_key", "D3"."Product_type_key")) "Product_type_key",
  (COALESCE("D2"."Retailer_country_key", "D3"."Retailer_country_key")) "Retailer_country_key",
  (COALESCE("D2"."Retailer_key", "D3"."Retailer_key")) "Retailer_key"
FROM
  (SELECT "T0"."C0" "memberUniqueName2",
    "T0"."C1" "memberUniqueName4",
    "T0"."C2" "memberUniqueName6",
    "T0"."C3" "Retailer_country_key",
    "T0"."C4" "Retailer_key",
    "T0"."C5" "Product_type_key",
    MIN("T0"."C6") over (partition BY "T0"."C2") "Order_method_type",
    "T0"."C7" "Quantity",
    "T0"."C8" "rc",
    "T0"."C9" "rc10"
  FROM
    (SELECT "coguda00"."PRODUCT_LINE_CODE" "C0",
      "coguda10"."REGION_CODE" "C1",
      "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_KEY" "C2",
      "coguda10"."COUNTRY_KEY" "C3",
      "coguda11"."RETAILER_KEY" "C4",
      "coguda00"."PRODUCT_TYPE_KEY" "C5",
      MIN("SLS_ORDER_METHOD_DIM"."ORDER_METHOD_EN") "C6",
      SUM("SLS_SALES_FACT"."QUANTITY") "C7",
      MIN("coguda02"."PRODUCT_TYPE_EN") "C8",
      MIN("coguda11"."RETAILER_NAME") "C9"
    FROM "GOSALESDW"."SLS_PRODUCT_DIM" "coguda00",
      "GOSALESDW"."SLS_PRODUCT_LINE_LOOKUP" "coguda01",
      "GOSALESDW"."SLS_PRODUCT_TYPE_LOOKUP" "coguda02",
      "GOSALESDW"."SLS_PRODUCT_LOOKUP" "coguda03",
      "GOSALESDW"."SLS_PRODUCT_COLOR_LOOKUP" "coguda04",
      "GOSALESDW"."SLS_PRODUCT_SIZE_LOOKUP" "coguda05",
      "GOSALESDW"."SLS_PRODUCT_BRAND_LOOKUP" "coguda06",
      "GOSALESDW"."GO_REGION_DIM" "coguda10",
      "GOSALESDW"."SLS_RTL_DIM" "coguda11",
      "GOSALESDW"."SLS_ORDER_METHOD_DIM" "SLS_ORDER_METHOD_DIM",
      "GOSALESDW"."SLS_SALES_FACT" "SLS_SALES_FACT"
    WHERE "coguda00"."PRODUCT_KEY"         ="SLS_SALES_FACT"."PRODUCT_KEY"
    AND "SLS_SALES_FACT"."ORDER_METHOD_KEY"="SLS_ORDER_METHOD_DIM"."ORDER_METHOD_KEY"
    AND "coguda11"."RETAILER_SITE_KEY"     ="SLS_SALES_FACT"."RETAILER_SITE_KEY"
    AND "coguda10"."COUNTRY_CODE"          ="coguda11"."RTL_COUNTRY_CODE"
    AND "coguda00"."PRODUCT_LINE_CODE"     ="coguda01"."PRODUCT_LINE_CODE"
    AND "coguda00"."PRODUCT_NUMBER"        ="coguda03"."PRODUCT_NUMBER"
    AND "coguda00"."PRODUCT_SIZE_CODE"     ="coguda05"."PRODUCT_SIZE_CODE"
    AND "coguda00"."PRODUCT_TYPE_CODE"     ="coguda02"."PRODUCT_TYPE_CODE"
    AND "coguda00"."PRODUCT_COLOR_CODE"    ="coguda04"."PRODUCT_COLOR_CODE"
    AND "coguda06"."PRODUCT_BRAND_CODE"    ="coguda00"."PRODUCT_BRAND_CODE"
    AND "coguda03"."PRODUCT_LANGUAGE"      =N'EN'
    GROUP BY "coguda00"."PRODUCT_LINE_CODE",
      "coguda10"."REGION_CODE",
      "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_KEY",
      "coguda00"."PRODUCT_TYPE_KEY",
      "coguda10"."COUNTRY_KEY",
      "coguda11"."RETAILER_KEY"
    ) "T0"
  ) "D2"
FULL OUTER JOIN
  (SELECT "T0"."C0" "memberUniqueName2",
    "T0"."C1" "memberUniqueName4",
    "T0"."C2" "Retailer_country_key",
    "T0"."C3" "Retailer_key",
    "T0"."C4" "Product_type_key",
    "T0"."C5" "Sales_target",
    "T0"."C6" "rc",
    "T0"."C7" "rc8"
  FROM
    (SELECT "Product"."Product_line_code" "C0",
      "Retailer_site"."Region_code" "C1",
      "Retailer_site"."Retailer_country_key" "C2",
      "Retailer_site"."Retailer_key" "C3",
      "Product"."Product_type_key" "C4",
      SUM("SLS_SALES_TARGET_FACT"."SALES_TARGET") "C5",
      MIN("Product"."Product_type") "C6",
      MIN("Retailer_site"."Retailer_name") "C7"
    FROM
      (SELECT "SLS_PRODUCT_DIM"."PRODUCT_LINE_CODE" "Product_line_code",
        "SLS_PRODUCT_DIM"."PRODUCT_TYPE_KEY" "Product_type_key",
        MIN("SLS_PRODUCT_TYPE_LOOKUP"."PRODUCT_TYPE_EN") "Product_type"
      FROM "GOSALESDW"."SLS_PRODUCT_DIM" "SLS_PRODUCT_DIM",
        "GOSALESDW"."SLS_PRODUCT_TYPE_LOOKUP" "SLS_PRODUCT_TYPE_LOOKUP"
      WHERE "SLS_PRODUCT_DIM"."PRODUCT_TYPE_CODE"="SLS_PRODUCT_TYPE_LOOKUP"."PRODUCT_TYPE_CODE"
      GROUP BY "SLS_PRODUCT_DIM"."PRODUCT_LINE_CODE",
        "SLS_PRODUCT_DIM"."PRODUCT_TYPE_KEY"
      ) "Product",
      (SELECT "Retailer_region_dimension"."REGION_CODE" "Region_code",
        "Retailer_region_dimension"."COUNTRY_KEY" "Retailer_country_key",
        "SLS_RETAILER_DIM"."RETAILER_KEY" "Retailer_key",
        MIN("SLS_RETAILER_DIM"."RETAILER_NAME") "Retailer_name"
      FROM "GOSALESDW"."GO_REGION_DIM" "Retailer_region_dimension",
        "GOSALESDW"."SLS_RTL_DIM" "SLS_RETAILER_DIM"
      WHERE "Retailer_region_dimension"."COUNTRY_CODE"="SLS_RETAILER_DIM"."RTL_COUNTRY_CODE"
      GROUP BY "Retailer_region_dimension"."REGION_CODE",
        "Retailer_region_dimension"."COUNTRY_KEY",
        "SLS_RETAILER_DIM"."RETAILER_KEY"
      ) "Retailer_site",
      "GOSALESDW"."SLS_SALES_TARG_FACT" "SLS_SALES_TARGET_FACT"
    WHERE "Product"."Product_type_key"           ="SLS_SALES_TARGET_FACT"."PRODUCT_TYPE_KEY"
    AND "SLS_SALES_TARGET_FACT"."RETAILER_KEY"   ="Retailer_site"."Retailer_key"
    AND "SLS_SALES_TARGET_FACT"."RTL_COUNTRY_KEY"="Retailer_site"."Retailer_country_key"
    GROUP BY "Product"."Product_line_code",
      "Retailer_site"."Region_code",
      "Product"."Product_type_key",
      "Retailer_site"."Retailer_country_key",
      "Retailer_site"."Retailer_key"
    ) "T0"
  ) "D3"
ON "D2"."memberUniqueName2"    ="D3"."memberUniqueName2"
AND "D2"."memberUniqueName4"   ="D3"."memberUniqueName4"
AND "D2"."Retailer_country_key"="D3"."Retailer_country_key"
AND "D2"."Retailer_key"        ="D3"."Retailer_key"
AND "D2"."Product_type_key"    ="D3"."Product_type_key"