SQL Tips And Tricks Part 4
1) The difference between UNIQUE and PRIMARY KEY is that there can be only one primary key in the table. While you can have as many as UNIQUE columns as you want.
2) Make use of SQL Analytical function for reporting purposes instead of sticking to basic things.
Analytical functions: RANK, DENSE_RANK, FIRST, LAST, FIRST_VALUE, LAST_VALUE, LAG and LEAD
3) Wherever possible use equality instead of LIKE predicate as it slow down the query
Example, SELECT salary FROM employees WHERE emp_id LIKE 1;
4) Mixing datatypes can slow down your query considerably.
Example, DECLARE v_1 NUMBER := ‘4’; v_name employees.name%TYPE; BEGIN SELECT emp_name INTO v_name FROM employees WHERE emp_id = v_1; END;
5) Use NOT EXISTS instead of NOT IN and HAVING clause. It will make the query execution faster.
6) IN and OR operators cannot be used with OUTER JOIN column.
7) Be careful while using triggers. If triggers are used, an extensively complex structure will be created that will be difficult to maintain.
8) Combine the triggering events using INSERTING, UPDATING and DELETING if possible instead of writing trigger for each and every event.
9) Assign an initial value to the variables that are declared CONSTANT or NOT NULL.
10) Always Use WHEN_OTHERS exception last. If you are sure of an exception make use of that particular exception instead of WHEN_OTHERS.
11) You can also use ORDER BY 1,2 referencing first and second column respectively in the query rather than using column names.
Example, SELECT emp_name, dept_no, salary FROM employees WHERE emp_id =1 ORDER BY 1, 2;
12) Columns declared as LONG RAW cannot be queried like columns of other datatypes using SELECT statement.
13) Maximum size of CHAR and VARCHAR2 in PL/SQL is 32767 bytes
14) You can find out current schema using below query
SELECT SYS_CONTEXT ('userenv', 'current_schema')
FROM DUAL;
15) Oracle database version can be determined by below query
SELECT * FROM v$version;
16) You can generate random number in Oracle using below query
SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL; Here the random number between 0 and 100 is generated. If you want to increase the limit put that maximum number instead of 100.
17) Oracle 11g introduced a concept of VIRTUAL COLUMN. The values in these virtual columns are derived from other columns.
Syntax, column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL] CREATE TABLE employees ( Emp_id NUMBER, Emp_name VARCHAR2(10), salary NUMBER(9,2), dailysal AS ROUND((salary/30), 2) dailysal, yearlysal NUMBER GENERATED ALWAYS AS ROUND((salary*12), 2) VIRTUAL);
18) Oracle Database 12c has come with many interesting features. Few of them are as below:
- You can create sequence for specific session
- Columns can be made invisible
- Introduction of Identity keyword for sequential number generation
- Direct analytical functions
- Pluggable database facility. This means master child kind of relationship. All child databases are plugged into one master database.
19) There can be only one long raw column per table.
20) Comparing NULL with anything will always return NULL.
21) In PL/SQL, the SELECT statement must return only one row. If it is returning more than 1 row it throws TOO_MANY_ROWS exception. If it does not return any row it throws NO_DATA_FOUND exception.
22) The execution of block in PL/SQL terminates as soon as the exception is raised.
23) Tables dropped by using TRUNCATE command and cannot be retrieved back.
24) You can write cursor query directly into the FOR loop instead of writing it in DECLARE section.
25) “g” in 11g stands for grid computing while “c” in 12c stands for cloud computing. Simple tip but useful.
No comments:
Post a Comment