Programming Languages


















What is SQL?
SQL stands for structured query language. SQL is a standardized query language for requesting information from a database. The original version called SEQUEL (structured English query language) was designed by an IBM research center in 1974 and 1975. SQL was first introduced as a commercial database system in 1979 by Oracle Corporation.
SQL has been the favorite query language for database management systems running on minicomputer and mainframe however, SQL is being supported by PC database systems because it supports distributed databases (databases that are spread out over several computer systems). This enables several users on a local-area network to access the same database simultaneously.
Elapsed: 00:00:05.02
Tips
Tips
Deleting All Duplicates Except One
The method works for tables that have a logically unique index - composite or singular - that has not yet been placed on the table. Obviously you can't put the index or primary key constraint on the table if there are already duplicates in the table.
The idea is you use a correlated sub-query (see correlated sub-query elsewhere in this document) to find rows which have a matching unique key. The delete statement below deletes all rows that have a matching logical primary key except for the one row with the highest ROWID. ROWID is Oracle's unque row identifier for all rows in a table.
DELETE FROM SUBASSM_PART_SN O |
Whe n are indexes used?
This section addresses rule based optimization only. All bets are off if we are discussing indexes and cost based optimization.
When an index has been defined and the SQL in question can use it. So another relevant question is: when can't an index be used? It turns out there are several simple rules which govern when an index cannot be used. They are:
1) An index cannot be used in a function is wrapped around the field in the where clause, for example:
select * from employee |
If we had an index on this the field 'start_date' in the 'employee' table example above the index could NOT be used.
This select statement could be converted as follows to use the index:
select * from employee |
2) If you have a composite index or primary key (just another index). Then you must use the left most fields of the composite index/key in order to use the other components. If the following index definition exists:
Create index EXP_IDX1 on Sales_Order_Detail (Sales_Order_Number, Line_Item); |
The next select WILL use the index or at least partially - the left most component.
Select * from Sales_Order_Detail |
And the last, this select will use the entire index, i.e. both components.
Select * from Sales_Order_Detail |
Traversing Parent Child Relationships with Connect By
Traversing a parent child relationship is easy by using Oracle's connect by statement. If you had an EMPLOYEE table as defined below. A select statement identifying managers might look as follows:
select emp_name from EMPLOYEE |
The example above assumes only one employee named PAIGE SMITH. But more importantly shows all managers including managers managers and should terminate at the company president.
Explain Plan
Explain Plan is a utility provided by Oracle that gives you a look at how the SQL you issue is processed. It is useful for things like determining if an index is being used or not used, when a sort is being performed etc. The utility is used as follows:
You create a "plan_table" to contain the results of your query analysis. The script to create this table is provided by Oracle and is the file "UTLXPLAN.SQL" in your Oracle installations ADMIN directory
Run this script from an account that you have ability to create tables from and has visibility of the objects / tables you wish to hit in the SQL you want to analyze. Issue the "explain plan" command. This command syntax is as follows:
explain plan set statement_id='MY_STMT' for |
When the above command is issued Oracle performs the analysis of the specified SQL and places that analysis into the plan table - usually cleverly named "PLAN_TABLE".
The analysis is written to this table as a series or records with a key of "STATEMENT_ID". Yes, the same statement id you specified when you issue the command. This allows you to analyze multiple SQL statements simultaneously.
You then view the results of the analysis by running a query against the plan table. Use this SQL script as a template. The results are usually pretty self explanatory.
Calling a PL/SQL function from a SQL statement
Version 2.1 and higher of PL/SQL (2.1 of PL/SQL is provided with 7.1 and higher of the Oracle Server DB) provides the ability to call a stored function from a SQL statement.
Simply write the PL/SQL stored function as normal and call it.
This is an extremely powerful capability as it greatly extends and simplifies what can be done with one SQL statement. It should be pointed out that only selects are allowed in the pl/sql. You are not allowed to update the database and you must commit to this via a PL/SQL pragma in the definition of the function.
SQL Server won't install on my laptop or my home PC. Why not?
A laptop is a great configuration for people who support or perform development for SQL Server at multiple locations. Lack of a NIC is the most common installation problem for laptop and standalone PC users because the SQL Server installation program looks for the default SQL Server Named Pipe (SSNMPN60,\\.\pipe\SQL\query) during installation. You can't create this pipe if NT's networking services fail to start, and the network won't start unless you've installed a NIC.
Fortunately, the problem has a solution. Even if you don't have a NIC, you can fool NT into starting the network by installing the Loopback Adapter, from the Control Panel, Network applet, as Screen 1 shows. The Loopback Adapter lets network-aware applications access local resources as if the local resources were on a network. Better yet, the adapter is as easy to install as a regular NIC.
More powerful tracing
Most people will be familiar with the 'alter session set sql_trace = true' commands to enable a trace file for your session. A quick hunt through $ORACLE_HOME/rdbms/admin also reveals a PL/SQL equivalent (dbms_session.set_sql_trace). This is fine for anyone who cares about performance of their OWN code.
A great boon for DBA's and anyone trying to track SQL performance of OTHER people's code is the use of dbms_system.set_sql_trace_in_session, typically only executable by SYS.
All of the above is "well" documented in the Oracle manuals and directories. A more powerful (and totally undocumented) routine is also available
dbms_system.set_ev( sid,serial#,event,level,name) |
To initiate a trace for a connected session, simply issue:
dbms_system.set_ev( Sid,serial#,10046,8,'') |
For a more detailed trace, which show waits and the value of bind variables, you can use:
dbms_system.set_ev( Sid,serial#,10046,12,'') |
Avoiding memory problems with PL/SQL tables
An feature of PL/SQL tables is the ability to remove all records from the table using the TABLE_NAME.DELETE attribute. However, if you have had (say) 10,000 records in the table, then whilst the DELETE attribute will remove these entries, it does not free up the memory that was occupied by them.
The solution is to assign an empty table to reclaim the space.
declare |
Encryption in Oracle
As of Oracle 8.1.6 you can now perform thorough encryption using the built-in package DBMS_OBFUSCATION_TOOLKIT (and no, that wouldn't be my choice of name either!)
If you are not yet at 8.1.6, the following routine is very simple, gives very secure encryption and lets you choose your own key length. The nicest thing about this routine is that to reverse the encryption, you just call the SAME routine with the encrypted text.
function encipher(p_string varchar2, p_key varchar2) return varchar2 is |
Writing to the alert log
The following PL/SQL package can be used to add your own custom entries to the alert log.
dbms_system.ksdwrt(2,'A line of text'); |
Showing GIF's etc to the Web
To retrieve a GIF image from a database column and present it to a web page the following PL/SQL routine may be useful. We use DBMS_LOB to read the image content in 4k chunks and UTL_RAW.CAST_TO_VARCHAR2 to allow use within the standard HTP routines.
create or replace package image_get as |
Explicit vs. Implicit cursors
Pick any tuning manual, and you'll often see a section about coding with explicit cursors as opposed to implicit ones. The argument seems sound, typically going along the lines of:
"With an explicit cursor, you can open, fetch and close, whereas with an implicit cursor must issue open, fetch, second fetch, close - the second fetch being needed to check for a TOO_MANY_ROWS exception.
However, since 7.3, those cunning people at Oracle have worked around this problem and the second fetch is no longer required. Because PL/SQL is interpreted (watch out for the new native compile option in 9i), implicit cursors will actually run FASTER than explicit since typically you are using less code to achieve the same task.
Using DES Encryption
The new DBMS_OBFUSCATION_TOOLKIT allows sophisticated encryption but insists that the text to be encrypted be a multiple of 8 bytes. As suggested by an Ask Tom post, a simple wrapper allows any length text to be encrypted whilst still satisfying this requirement.
Function |
Business days
As we all know (hopefully), we should always deploy named program units (procedures/functions) within packages. However, not many developers are aware that its not just procedures and functions that can have a public (package spec) and private (package body) component. Cursors can also be defined in the same way as the following example shows:
CREATE PACKAGE my_pkg AS |
Checksum function in PL/SQL
This is a port of a C function that did a 32bit, 2's complement checksum.
Function |
SELECT * and more
To select all columns of a table:
select * from table |
However, to select all real columns, plus a pseudo-column like "user":
select table.*, user from table |
Bitwise operations
There is an undocumented 'bitand()' routine you can call, to do logical AND on two numbers. If you need the others, a little boolean math should suffice
CREATE OR replace FUNCTION bitor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER AS |
These functions are callable from SQL as well. Since this is all based on the PLS_INTEGER datatype, you are limited to numbers less than 2^31.
Faster SQL in PL/SQL
An upgrade to 9i gives benefits for PL/SQL called from SQL straight out of the box.
First we'll have a look at 8.1.7
SQL> set timing on
SQL> select count(*) from
2 ( select /*+ NO_MERGE */ func817(rownum) x from x$ksmmem
3 where rownum < 10000000 )
4 /
COUNT(*)
----------
9999999
Elapsed: 00:00:05.77
SQL> /
COUNT(*)
----------
9999999
Elapsed: 00:00:05.68
SQL> /
COUNT(*)
----------
9999999
Elapsed: 00:00:05.57
And then same experiment in 9i gives about a 10% improvement
SQL> set timing on
SQL> select count(*) from
2 ( select /*+ NO_MERGE */ func92(rownum) x from x$ksmmem
3 where rownum < 10000000 )
4 /
COUNT(*)
----------
9999999
Elapsed: 00:00:05.07
SQL> /
COUNT(*)
----------
9999999
Elapsed: 00:00:05.01
SQL> /
COUNT(*)
----------
9999999
|
|||