Oracle PL/SQL Quick Reference
PL/SQL Block Structure
DECLARE --Optional --Variables, Cursors, User-defined exceptions BEGIN --Mandatory --SQL statements --PL/SQL statements EXCEPTION --Optional --Actions to perform when errors occur END; --Mandatory Read more...
Oracle SQL Quick Reference
SELECT Query Statement
SELECT [DISTINCT] {*, column [alias],…}
FROM table
WHERE condition(s)]
ORDER BY {column, exp, alias} [ASC|DESC]]
NOTE:
Avoid using DISTINCT with large table as it first does sorting of all the rows and then eliminates duplicate rows, so need a full table scan and thus it’s very slow.
ORDER BY is the last clause to get executed and thus could see all the column aliases; You can sort by a columns that is not in SELECT list; Default sorting order is Ascending (ASC)
consideration when writing an SQL statement is that it returns a correct result. The second is that it be the most efficient for a given situation. You can use many different SQL statements to achieve the same result. It is often the case that only one statement will be the most efficient choice in a given situation.
Remember that processing SQL is a sequence of Parse (syntax check and object resolution), Execution (required reads and writes), and Fetch (row results retrieved, listed, sorted, and returned). SQL “tuning” consists, quite simply, of reducing one or more of them.
Note: generally Parse is the greatest time and resource hog. Parse overhead can be minimized by the use of Procedures, Functions, Packages, Views, etc.
Inadequate performance can have a significant cost impact on your business. A poor performing system and application can result in customer dissatisfaction, reduced productivity, and high costs. It is absolutely critical that the system’s performance is operating at its peak levels.
Following are some general tips that often increase SQL statement efficiency. Being general they may not apply to a particular scenario. Read more…
SQL*Plus is a command-line tool that provides access to the Oracle RDBMS.
SQL*Plus enables you to:
SQL*Plus is available on several platforms. In addition, it has a web-based user
interface, iSQL*Plus.
SQL*Plus is a client terminal software allowing users to interact with Oracle server to manipulate data and data structures. Users type in SQL statements in SQL*Plus that send statements to Oracle server. Oracle server then validates and executes the statements on its databases. The query results are returned to SQL*Plus and displayed to the user. Besides sending SQL statements to the server, SQL*Plus also saves them into a local buffer and allow users to view and change the statements. The following figure illustrates the process. Read more…
The Import and Export utilities work together; Export sends database definitions and actual data to an export file and Import can read the file to perform many different tasks. You can use Export and Import for many important database tasks, such as restoring a table, generating CREATE scripts, copying data among Oracle databases, migrating among Oracle versions, and moving tables from one schema to another.
Oracle’s export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database.
These utilities can be used to move data between different machines, databases or schema. However, as they use a proprietary binary file format, they can only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database.
Various parameters are available to control what objects are exported or imported. To get a list of available parameters, run the exp or imp utilities with the help=yes parameter i.e. “exp help=yes” or “imp help=yes“.
The export/import utilities are commonly used to perform the following tasks: Read more…