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