SQL Query Tuning

Non-Column Expressions 

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

Write the query as

SELECT id, name, salary 

FROM employee 

WHERE salary < 25000;

Instead of:

SELECT id, name, salary 

FROM employee 

WHERE salary + 10000 < 35000;

Usage of DECODE to avoid multiscan

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

To avoid the scanning of same rows or joining the same table repetitively. 

DECODE can also be made used in place of GROUP BY or ORDER BY clause. 

For Example: Write the query as

SELECT id FROM employee 

WHERE name LIKE 'S%' 

and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee 

WHERE name LIKE 'Ramesh%';

Tar a directory in Linux Server

Archive a File or Directory

=====================================

tar -czvf name-of-archive.tar.gz /path/to/directory-or-file

• -c: Create an archive.
• -z: Compress the archive with gzip.
• -v: Display progress in the terminal while creating the archive, also known as “verbose” mode. The v is always optional in these commands, but it’s helpful.
• -f: Allows you to specify the filename of the archive.

If no compression required (i.e., no gzip)

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

tar -cvf name-of-archive.tar.gz /path/to/directory-or-file

Multiple Directories at a time

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

tar -czvf archive.tar.gz /u01/app  /u01/appv2/tnslatest.txt

Exclude any files or directories

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

tar -czvf name-of-archive.tar.gz /u01/app --exclude=/u01/app/oracle/backups --exclude=/u01/app/oracle/Middleware/BI_bkp

tar -czvf name-of-archive.tar.gz /u01/app --exclude=*.txt

Extract the Archive

=================================

Just replace "-c" switch with "-x" switch

tar -xzvf name-of-archive.tar.gz /path/to/directory-or-file