What is SQL?

What is SQL?

SQL is pronounced “Ess-Queue-Ell” or “Sequel,” depending on the source and location of your training. The meaning of the acronym SQL is Structured Query Language. SQL is the language used to access data from a relational database, such as Oracle Database. SQL was originally devised as a computer language with the development of the IBM relational database called System R. The idea of SQL originates from the relational data model. SQL is used to access subsets of data, in the form of rows or tuples from a larger data set. A data set is represented by a related group of entities, in a data model of a set of tables, in an Oracle Database schema.

What is DUAL Table?

Every DML statement creates an implicit cursor. A cursor is an area in memory allocated for the results of an SQL statement. SELECT statements require a source table for the implicit cursor to operate on. The DUAL table is required as a dummy table, to allow the SELECT statement to retrieve data not stored in a table, view, or other database object. The DUAL table is a repository for an expression result applied to a single value. The DUAL table acts as a temporary repository for the result of an expression. The result of the expression is selected from the DUAL table, as in the following examples:

SELECT * FROM DUAL;

SELECT SYSDATE FROM DUAL;

SELECT USER FROM DUAL;

SELECT 'This is a string' FROM DUAL;

What is NULL value?

There are several things to remember about NULL:

Ø  NULL represents nothing.

Ø  Not even a space character is NULL.

Ø  NULL is not the same as zero.

Ø  NULL values are not always created in indexes.

Ø  Most functions return NULL when passed a NULL value.

Ø  Test for NULL using IS [NOT] NULL.

Ø  An expression containing a NULL returns NULL.

Ø  Use the NVL(value, replace) function to account for NULL values in expressions.

Ø  NULL values sort as the highest value.

• What Is the Parser?
• 
  
  The meaning of the word parsing is that of syntactical and perhaps grammatical or semantic analysis of a sentence. In programming terms, parsing is a syntax check on a line of program code and between related lines. SQL statements are lines of program code, so the parser is effectively a compiler of SQL statements; much like a C compiler is a compiler or syntax analyzer of C programming language commands.
  
  
  Parses can be split into two categories:
  
  
  Hard parse: No match is found in the shared pool for a SQL statement never before submitted to the database engine.
  
  
  Soft parse: A match is found for a SQL statement, perhaps submitted to the database engine by a different session.
  
  
  Note: Hard parses are more expensive in system resources than soft parses, but reparsing is best avoided altogether if possible.
  
  

Why is a COMMIT statement faster than a ROLLBACK statement?

What occurs in the database during the processing of these statements?

Ø  Before COMMIT or ROLLBACK

§  Redo logs are written.

§   The database is changed.

§   Rollback is written.

Ø  On COMMIT

§  Rollback is deleted.

Ø  On ROLLBACK

§  Rollback is recorded in the redo logs.

§  Rollback is applied to the database.

§  Rollback is deleted.

How to tune a JOIN?

There are several factors to consider:

• Use equality first.
• Use range operators where equality does not apply.
• Avoid use of negatives in the form of != or NOT.
• Avoid LIKE pattern matching.
• Try to retrieve specific rows, and in small numbers.
• Filter from large tables first to reduce the number of rows joined. Retrieve tables in order from the most highly filtered table downward, preferably the largest table, which has the most filtering applied.
• Use indexes wherever possible, except for very small tables.
• Let the optimizer do its job.
• Materialized views and query rewrite. Materialized views are somewhat out of the scope of OLTP databases, but perhaps not so in the future.
• 
  
  
  
  Ref: Oracle® Performance Tuning for 10gR2 (Second Edition)
  By Gavin Powell
   

To Get User Environment Value From ORACLE Database Using USERENV Parameter.

Select SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data,
SYS_CONTEXT('USERENV','CURRENT_SQL') current_sql,
SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') client_identifier,
SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') global_context_memory
From Dual;

For more information, please Click Here.

Sequence, Nextval & Currval

Sequence:

A sequence is a database object that generates unique numbers, mostly used for primary key values. One can select the NEXTVAL and CURRVAL from a sequence. Selecting the NEXTVAL will automatically increment the sequence.

Another Definition,

A sequence is a highly scalable, non-blocking, generator that generates unique numbers.

Nextval:

If NEXTVAL is invoked on a sequence, it makes sure that a unique number is generated.

Currval:

CURRVAL can only be used if a session has already called NEXTVAL on a trigger or by sql, CURRVAL will then return the same number that was generated with NEXTVAL.

Create a simple sequence:

CREATE SEQUENCE empseq;

 Sequence created.

Selecting from the sequence:

SQL> select empseq.nextval from dual;

   NEXTVAL

----------

         1

SQL> select empseq.nextval from dual;

   NEXTVAL

----------

         2

Note that nextval and currval returns the same value for each row of a select:

SQL> select empseq.nextval, empseq.currval from dual;

   NEXTVAL    CURRVAL  

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

3                                            3         

In PL/SQL, up to Oracle 10g, nextval and currval have to be selected from dual:

declare

  n number;

begin

  select sequence_test.nextval into n from dual;

  dbms_output.put_line(n);

end

/

Apparently, in Oracle 11g, one is not required anymore to select from dual:

declare

  n number;

begin

  n := sequence_test.nextval;

  dbms_output.put_line(n);

end

/

Index, Bitmap Index, B-Tree Index

What is Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

What is Bitmap Index?

A bitmap index is an indexing method that can provide both performance benefits and storage savings. Bitmap indexes are particularly useful for data warehousing environments because data is usually updated less frequently and ad hoc queries are more common.

Bit-mapped indexes are best for “low-cardinality” data (such as a column to indicate a person’s gender, which contains only two possible values: MALE and FEMALE).

Characteristic of Bitmap Indexes:

01.  For columns with very few unique values (low cardinality)

02.  Tables that have no or little insert/update are good candidates (static data in warehouse)

03.  Stream of bits: each bit relates to a column value in a single row of table.

What is B-Tree Index?

B-tree indexes are usually created on columns containing the most unique values. It is an ordered set of entries in which each entry a search key value and a pointer to a specific row with that value. When a server finds a search key value matching a constraint, the pointer is used to fetch the row.

Difference between B-Tree & Bitmap Index

http://www.oracle.com/technology/pub/articles/sharma_indexes.html

Table Function in Oracle

Table Function:

Table functions are functions that produce a collection of rows (either a nested table or a varray) that can be queried like a physical database table or assigned to a PL/SQL collection variable. You can use a table function like the name of a database table, in the FROM clause of a query, or like a column name in the SELECT list of a query.

A table function can take a collection of rows as input. An input collection parameter can be either a collection type (such as a VARRAY or a PL/SQL table) or a REF CURSOR.

What Are Table Functions?

Table functions return a collection type instance representing rows in a table. They can be queried like a table by calling the function in the FROM clause of a query, enclosed by the TABLE keyword. They can be assigned to a PL/SQL collection variable by calling the function in the SELECT list of a query.

For Example:

The collection type BookSet_t is defined as:

CREATE TYPE Book_t AS OBJECT

( name VARCHAR2(100),

  author VARCHAR2(30),

  abstract VARCHAR2(1000));

CREATE TYPE BookSet_t AS TABLE OF Book_t;

The CLOBs are stored in a table Catalogs:

CREATE TABLE Catalogs

( name VARCHAR2(30),

  cat CLOB);

Function GetBooks is defined as follows:

CREATE FUNCTION GetBooks(a CLOB) RETURN BookSet_t;

The query below returns all the catalogs and their corresponding book listings.

SELECT c.name, Book.name, Book.author, Book.abstract

  FROM Catalogs c, TABLE(GetBooks(c.cat)) Book;

See another Link for Table Function: Link-1 or Link-2 

Pipelined Function:

A pipelined function is one that returns a result set as a collection, but does so iteratively.

Example:

CREATE TYPE pet_t IS OBJECT (

   NAME                          VARCHAR2 (60),

   breed                         VARCHAR2 (100),

   dob                           DATE);

/

CREATE TYPE pet_nt IS TABLE OF pet_t;

/

CREATE OR REPLACE FUNCTION pet_family (dad_in IN pet_t, mom_in IN pet_t)

    RETURN pet_nt

    PIPELINED

IS

    l_count PLS_INTEGER;

    retval   pet_nt := pet_nt ();

BEGIN

    PIPE ROW (dad_in);

    PIPE ROW (mom_in);

    IF mom_in.breed = 'RABBIT'

    THEN

       l_count := 12;

    ELSIF mom_in.breed = 'DOG'

    THEN

       l_count := 4;

    ELSIF mom_in.breed = 'KANGAROO'

    THEN

       l_count := 1;

    END IF;

    FOR indx IN 1 .. l_count

    LOOP

       PIPE ROW (pet_t ('BABY' || indx, mom_in.breed, SYSDATE));

    END LOOP;

    RETURN;

END;

SELECT *

  FROM TABLE (pet_family (

                    pet_t ('Bob', 'KANGAROO', SYSDATE),

                    pet_t ('Sally', 'KANGAROO', SYSDATE)

                 )

              );

OutPut:

NAME     BREED    DOB

Bob      KANGAROO    3/3/2010 11:24:41 AM

Sally    KANGAROO    3/3/2010 11:24:41 AM

BABY1    KANGAROO    3/3/2010 11:24:41 AM