Table partitioning considerations

Here is an excerpt on how to plan for table partitions in Oracle.

"The Partition Decision

Now that you've seen the basics of partitioning, here comes the tough part: How do you decide on the type and columns used for partitioning? Your choice of partitioning has to address key partitioning objectives for manageability and performance. How you partition will be based on the priorities you assign to your objectives. I break down the objectives into the following categories: 

• Performance
• Ease of administration
• Data purge
• Data archiving
• Data movement
• Data lifecycle management
• Efficiency of backup"

The full article here at:

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56partition-090450.html

Unix - Get a list of words that match a pattern

Gets you a list of lines that match the above pattern.
grep -oh "th" *
To get a list of the words from the directory which matches a pattern.
grep -oh "\w*th\w*" *


Source: http://stackoverflow.com/questions/1546711/can-grep-show-only-words-that-match-search-pattern

Running Total SQL

Below is an example of how a running total can be derived in SQL:
--------------------------------------------------------
--  DDL for Table TABLE1
--------------------------------------------------------
DROP TABLE TABLE1;
CREATE TABLE "S2BDETL"."TABLE1"
  (
    "C1" VARCHAR2(20 BYTE),
    "C2" NUMBER,
    "C3" DATE
  ) ;
INSERT
INTO "S2BDETL"."TABLE1"
  (
    C1,
    C2,
    C3
  )
  VALUES
  (
    'A',
    '1',
    TO_DATE('2015-06-26 16:14:02', 'YYYY-MM-DD HH24:MI:SS')
  );
INSERT
INTO "S2BDETL"."TABLE1"
  (
    C1,
    C2,
    C3
  )
  VALUES
  (
    'A',
    '2',
    TO_DATE('2015-06-29 16:14:22', 'YYYY-MM-DD HH24:MI:SS')
  );
INSERT
INTO "S2BDETL"."TABLE1"
  (
    C1,
    C2,
    C3
  )
  VALUES
  (
    'A',
    '2',
    TO_DATE('2015-05-29 16:14:22', 'YYYY-MM-DD HH24:MI:SS')
  );
INSERT
INTO "S2BDETL"."TABLE1"
  (
    C1,
    C2,
    C3
  )
  VALUES
  (
    'A',
    '2',
    TO_DATE('2015-04-29 16:14:22', 'YYYY-MM-DD HH24:MI:SS')
  );
INSERT
INTO "S2BDETL"."TABLE1"
  (
    C1,
    C2,
    C3
  )
  VALUES
  (
    'A',
    '2',
    TO_DATE('2015-03-29 16:14:22', 'YYYY-MM-DD HH24:MI:SS')
  );
INSERT
INTO "S2BDETL"."TABLE1"
  (
    C1,
    C2,
    C3
  )
  VALUES
  (
    'A',
    '2',
    TO_DATE('2015-02-29 16:14:22', 'YYYY-MM-DD HH24:MI:SS')
  );
INSERT
INTO "S2BDETL"."TABLE1"
  (
    C1,
    C2,
    C3
  )
  VALUES
  (
    'A',
    '2',
    TO_DATE('2015-01-29 16:14:22', 'YYYY-MM-DD HH24:MI:SS')
  );
INSERT
INTO "S2BDETL"."TABLE1"
  (
    C1,
    C2,
    C3
  )
  VALUES
  (
    'A',
    '2',
    TO_DATE('2014-12-29 16:14:22', 'YYYY-MM-DD HH24:MI:SS')
  );

Without analytical functions:

SELECT SUM(TOTAL) running_Total,
  B.C3,
  A.c1
FROM
  (SELECT SUM(C2) TOTAL,C1,C3 FROM TABLE1 GROUP BY C1,C3
  ) A,
  (SELECT DISTINCT C3 FROM TABLE1
  ) B
WHERE A.C3 <= B.C3
GROUP BY B.C3,
  a.c1;

Using analytical functions:

SELECT c1,
  c3,
  SUM(c2) OVER (PARTITION BY c1 ORDER BY c3 RANGE UNBOUNDED PRECEDING) running_total
FROM table1;

Message: Failed to read from file for informatica Joiner

When you use a sorted joiner, be sure to assign enough data cache size. A joiner with sorted input doesn't act as a normal join, concerning data cache size. A normal joiner writes on disk if u do not assign enough cache size.

I found the above solution in the below thread link. I did see this was for Informatica 7.xx, and may not apply any more with the auto cache calculation feature now a standard since Version 8.

When I encountered this error, I also had my detail table partitioned. I was not able to solve the problem as above, so I had removed the partitioning as it was not offering me a big difference. I think this might have been the cause of the issue, as I realised one partition had started to write records to target while the second partition had not. I think maybe there was some cache file naming issue in my case, and not the cache size as in the case above.

[source: http://datawarehouse.ittoolbox.com/groups/technical-functional/informatica-l/informatica-router-output-rows-problem-2251811]

Creating a DBLINK on oracle with explicit TNS information

If you have tried to create an Oracle dblink, and you get the following error message;

*********************************************************************

Link  : "DBLINK_NAME"

Error : ORA-12154: TNS:could not resolve the connect identifier specified

*********************************************************************

then it means that the tns entry to the database you connecting to, does not exist. You could ask the dba to add the entry for you, otherwise you can use the following statement.

 CREATE DATABASE LINK dblink_name
CONNECT TO schema_name IDENTIFIED BY password
USING '(DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=)
      (PORT=)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=)
    )
  )'

Informatica 9 Architecture

Create table with Parallel command

The following statement will allow one to create a table using oracle parallelization.

create table big_emp parallel (degree 4)
  as select * from emp;