Posted on

Performance measures in Pl/Sql

Cases should be avoided –1. DO NOT use FUNCTIONS (,TO_NUMBER ,Decode,NVL, TO_CHAR) unnecessarily when a simple join would work
2. DO NOT use ‘SELECT *’ explicitly. Make sure that you select only required columns from the tables.
3. DO NOT use HAVING clause where a WHERE clause would do.
4. DO NOT use ORDER BY clause in a query unless necessarily by logic.
5. DO NOT use UNION and DISTINCT together in a single query
6. DO NOT use CURSOR LOOPS unnecessarily when a simple/single DML/SELECT can serve the purpose
7. DO NOT use DUAL table unnecessarily in programs and queries.

Cases should be followed –
1. Use UNION ALL as far as possible instead of UNION.
2. Use single/direct DML’s/SELECT’s instead of LOOP’s.
3. Use BULK COLLECT & FORALL for looping when the loop is going to get executed multiple time (take > 100 iterations as a base).
4. Use MATERIALIZED VIEWS or GLOBAL TEMPORARY TABLE for queries fetching data across dblinks
5. Use EXISTS in place of IN in the queries.
6. Care should be taken to use the LIMIT clause while using BULK operations in order to limit memory utilization. LIMIT should be set between 500-1000 and reduced or decreased as per performance behavior of the program

References – http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#i48876
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/design.htm#i29012

pimp it

Posted on

Query to get a particular row in Sql Server 2005

In this article we will look into how to write a sql query to get a particular row in sql.

Lets write the code to find 2nd row in a table named mcc_measures.

So first we declare a temporary table 

declare @temp table (id int)

No in this temp table we insertrow numbers of the table

insert into @temp(id)

SELECT 
ROW_NUMBER() over(ORDER BY Measure_Id DESC)
FROM 
mcc_measures

Now from the temp table we select the row where rowid=2 as required

SELECT
*
FROM
@temp
WHERE
id = 2

kick it on DotNetKicks.com

Shout it

pimp it

Posted on

Trading Community Architecture (TCA)

Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers,.TCA is the global repository for all name and address information, including those of customers and employees. This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications. These applications, as well as TCA itself, provide user interfaces, batch data entry functionality, and other features for you to view, create, and update Registry information (Source Overview Oracle® Trading Community Architecture User Guide).

The parties in TCA could be one of following four types:

Organization e.g. ABC corporation
Person e.g. vinay kumar
Group e.g. Silicon group
Relationship e.g. vinay kumar at ABC corporation.

Main components of Oracle TCA

Contacts
Locations
Party Layer
Sites
Relationships
Account Layer
Customer Accounts

Main Tables in TCA

HZ_PARTIES
HZ_RELATIONSHIPS
HZ_RELATIONSHIP_TYPES
HZ_ORG_CONTACTS
HZ_ORG_CONTACT_ROLES
HZ_CONTACT_POINTS
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_ORGANIZATION_PROFILES

A Party is any entity with which have potentially do business. It could be an organization, an individual or a relationship .

A Location is a point in geographical space described by a street address.

An Account represents is the financial realtionship of company with party .

A Party Site links a party with a location, indicating that party’s usage of the location.

An Account Site is a party site that is used by a customer account, for example, for billing or shipping purposes. (Account Sites are Organization specific in Multi-Org terms, just as Customer Sites were.)

A Contact is a person in the context of an organization, modelled as a relationship between an organization and a person or between two people.A Contact Point -is point of contact the party, for example, a phone number, e-mail address.