Oracle External tables – Overview

Overview on Oracle External tables

External tables can read flat files (that follow some rules) as though they were ordinary (although read-only) Oracle tables. Therefore, it is convenient to use external tables to load flat files into the DB. External can be used like SQL tables in querying purpose. So we can use it for insert and update of data into permanent tables. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn’t need to be staged and can be queried in parallel.

More Info : http://vinaytechs.blogspot.com/2009/12/oracle-external-tables-overview.html

Apex- Oracle’s Best Kept Secret

What is Oracle Apex?

Oracle Application Express (Oracle Apex, previously named Oracle HTML-DB) is a freeware software development environment based on the Oracle database. It allows a very fast development cycle to be achieved to create web based applications.

APEX is Oracle’s answer to wizard-based Web Development. It certainly is full of wizards! APEX contains metadata (lots of it) on everything in the tool, too. It produces dynamic HTML and it’s fast! APEX uses the PL/SQL module for Apache (mod_plsql). It doesn’t use any Java code. Anything you can do in SQL or PL/SQL can be done using APEX. APEX provides a nice Web-based team development environment for your organization.

Read More : http://vinaytechs.blogspot.com/2009/12/apex-oracles-best-kept-secret.html

Oracle External tables – Overview

Overview on Oracle External tables

External tables can read flat files (that follow some rules) as though they were ordinary (although read-only) Oracle tables. Therefore, it is convenient to use external tables to load flat files into the DB. External can be used like SQL tables in querying purpose. So we can use it for insert and update of data into permanent tables. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn’t need to be staged and can be queried in parallel.

Read More : http://vinaytechs.blogspot.com/2009/12/oracle-external-tables-overview.html

DB2 System Objects

This article gives overview on DB2 system objects and helps DB2 developers to know detail description about each system objects

Introduction

DB2 has a comprehensive infrastructure that enables it to provide data integrity, performance, and the ability to recover user data. DB2 controls and accesses system objects.

Read More : http://vinaytechs.blogspot.com/2009/12/db2-system-objects.html

how to tune the joins?

A SQL statement involving two or more tables requires Oracle join data from multiple sources in order to achieve the desired result. The Oracle Server has several built in methods for performing joins, and each method has its own performance and resource usage qualities. While an application developer or DBA doesn’t need to explicitly tell Oracle which join method to use or the order in which different tables should be joined, a solid understanding of how Oracle processes joins allows us to design and build systems for optimal performance. In this presentation we’ll examine the different methods Oracle uses for joining data from multiple sources, and we’ll see how this understanding can be used to design better database schemas, tune SQL statements, and tune databases as a whole for better join performance. Since most SQL statements involve joins, tuning joins leads to improved overall system performance.

Read More : http://vinaytechs.blogspot.com/2009/12/how-to-tune-joins.html

Exception handling in Oracle(9i)

Oracle 9i: Exception Handling

This article covers Oracle Exception Handling and the different types in it with sample SQL scripts along with the screen shots. Topics covered in this are, Introduction to Exception Handling – Propagation of Errors Types of Exceptions – Named System Exceptions; Unnamed System Exceptions; User-Defined Exceptions, SQL error code, SQL error message.

Read More : http://vinaytechs.blogspot.com/2009/12/exception-handling-in-oracle9i.html

DB2 Tuning Tips

1) Take out any / all-Scalar functions coded on columns in predicates. For example, this is the most common:

SELECT EMPNO, LASTNAME

FROM EMPLOYEE

WHERE YEAR(HIREDATE) = 2005

Should be coded as:

SELECT EMPNO, LASTNAME

FROM EMPLOYEE

WHERE HIREDATE BETWEEN ‘2005-01-01’ and ‘2005-12-31’