Efficient ways of writing SQL:

Better SQL writing practice:

How to write better SQL queries:


Here is some of my tips for Best  SQL Coding Practices for efficient data retrieval:
1.  select rowid
 from emp_data
 where  rowid = 'AAAENeAABAAAKz6AAA‘
The rowid of a row specifies the datafile and data block containing the row and the location of the row in that block.
Locating a row by specifying its rowid is the fastest way to retrieve a single row, because the exact location of the row in the database is specified.
To access a table by rowid, Oracle first obtains the rowids of the selected rows, either from the statement's WHERE clause or through an index scan of one or more of the table's indexes.
Oracle then locates each selected row in the table based on its rowid.


2. select *
 from emp_data
 where substr(emp_code,1,3) = '934'
 Vs

INDEX RANGE SCAN
 select *
 from emp_data
 where  emp_code  like '934%'


3.An index range scan is a common operation for accessing selective data.
It can be bounded (bounded on both sides) or unbounded (on one or both sides).
Data is returned in the ascending order of index columns.
If data must be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort


INDEX UNIQUE SCAN INDEX:

select *
 from emp_data
 where slno = 1

INDEX UNIQUE SCAN INDEX

An index range scan is a common operation for accessing selective data.
It can be bounded (bounded on both sides) or unbounded (on one or both sides).
Data is returned in the ascending order of index columns.
If data must be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort


FULL TABLE SCAN

select *
 from emp_data
 where slno between 1 and 800

This type of scan reads all rows from a table and filters out those that do not meet the selection criteria.
During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement's WHERE clause.
When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process.
The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT.
Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once

Index Scan through Hints:

select  /*+ index (emp_data,IDX_emp_data) */
    *
 from emp_data
 where  1=1
 and sc_name = 'AJANTA SOYA'
  -- and emp_code = '519216'
 and sc_group = 'B'

Index Scans through Date:

select *
 from emp_data
 where dt = sysdate

 FTS
select *
 from emp_data
 where trunc(dt) = trunc(sysdate )

-- Tuned
select *
 from emp_data
 where dt between trunc(sysdate-1) and (trunc(sysdate-1) + .999)


Index Scan through where condition re-org:

select *
 from emp_data
 where emp_code + 10 = '934847'
  Vs

 select *
 from emp_data
 where emp_code  = to_char(934847 - 10)


Having condition and performance:

select sc_group, count(*)
 from emp_data
 where 1=1
 group by sc_group
 having sc_group = 'Z '
 
 Vs

 select sc_group, count(*)
 from emp_data
 where sc_group = 'Z '
 group by sc_group


UNION & UNION ALL:

select *
 from emp_data
 where sc_group = 'F '
 UNION --ALL
 select *
 from emp_data
 where sc_group = 'T '

Data Type sensitive Filters:

select *
 from emp_data
 where slno = '100'

Vs

 -- Char will not use index  if Num
 select *
 from emp_data
 where emp_code = (519216)


Composite Indexes:
 select *
 from emp_data
 where 1=1
 --and emp_code = '519216'
 --and sc_name = 'SOYA '
 --and sc_group = 'B '

Concat Problem for performance:

select *
 from emp_data
 where emp_code|| sc_group = '934844F‘
 
  Vs

  select *
 from emp_data
 where emp_code = '934844'
 and  sc_group = 'F'


Try to check how does a count work:

 select count(*)
 from emp_data

 Vs

  select count(slno)
 from emp_data

More on dates:

select *
 from emp_data
 where to_char(dt,'dd Mon yyyy') = '27 Nov 2012'

  select *
 from emp_data
 where dt between to_date('27 Nov 2012') and to_date('27 Nov 2012') + .999


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