|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 8i / 9i
OS Details :
Sun Solaris9
Listed
in this article are a few tips, techniques and methods on SQL coding.
(1)
Reporting top 10 rows from a table:
Method 1 : Using
inline views
--
In the above method, the sort is
done before the restriction is enforced on the final output.
SELECT qty,title_id FROM (SELECT qty,title_id
FROM sales ORDER BY qty)
WHERE rownum < 11;
Method 2 : Using the newly introduced RANK() function within an inline
view starting oracle 8i
SELECT qty, title from (select
qty,title_id,rank() over(order by qty desc) rk FROM SALES)
WHERE rk < 11;
Method
3 : For Cost based optimization : (can be used from oracle8 and above
but only if CBO is enabled for the DB)
SELECT qty,title_id FROM (SELECT /*+
INDEX_DESC (SALES SALES_QTY_IDX) */ qty,title_id FROM sales) WHERE
rownum < 11;
(2)
A simple Tip on Working with
columns of DATE datatype :
Many times when a WHERE clause needs to restrict on a date column, we use the trunc function to
remove the time factor associated with the column. However that method makes oracle to skip the
usage of the index.
For example :
select * from abc where trunc(col1) = trunc(sysdate);
As of oracle8i, function based indexes can be built on the column and with query-rewrite enabled,
oracle will use the index. This is not possible with oracle8 and there is a slight overhead with query
rewrites in oracle. A simple solution to that will be to move the trunc function to the other side
of the query.
The query can be re-written as :
select * from abc where col1 between trunc(sysdate) and trunc(sysdate) + 1 and
col1 <> trunc(sysdate) + 1;
The above query will allow oracle to use an index on col1, if it exists.
(3) Splitting data in to ranges :
Oracle, starting
8i has provided us with an in-built function called NTILE that allows
us to split data in to ranges. An example on using NTILE function:
Select min(rowid),max(rowid),bucket
from (select rowid,ntile(10) over(order by rowid) as bucket from abc)
group by bucket
Please be aware
that using this functionality on splitting the entire table data in to
ranges will result in FULL table scans on the table.
(4) FULL
Outer joins :
Starting 9i, it
is possible to FULL OUTER join between 2 tables. In versions prior to
9i, trying to do a full outer join through the use of (+) will cause
ORA-01468 error and the functionality of FULL OUTER JOIN was achieved by doing a UNION
of a left outer join and a right outer join which meant that an
additional set of IO will have to be done.
Example:
select * from ( select * from
table1
where table1.col1
= 'ABC' and
table1.col2
= '200503' )
a
full outer
join
( select * from
table2
where
table2.col3
= 'XYZ' )
b
on (a.colx
=
b.colyand
a.colA
=
b.colB)
(5) Handling datatype and
precision mismatches :
Oracle provides
us with the CAST function to overcome datatype and precision
mismatches. This will particularly helpful when doing CTAS statements
with SELECTs being done against views.
Example:
create
table abc
as select
distinct cast(tablespace_name as varchar2(50)) as col1,owner from
dba_tables
(6) Avoiding Multiple
sub-queries :
Oracle9i,
provides us the WITH clause, which can be used effectively to
eliminate the additional IO needed to query a table as part of a
sub-query. An example on the usage of WITH clause:
Query without the
WITH clause:
SELECT
department_name, SUM(salary) dept_total FROM employees, departments
WHERE employees.department_id=departments.department_id
GROUP BY department_name HAVING SUM(salary) >
(SELECT sum(salary) * 1/3 FROM employees, departments WHERE
employees.department_id=departments.department_id)
ORDER BY dept_total;
Query using the
WITH clause:
WITH
summary AS (SELECT department_name, SUM(salary) dept_total
FROM employees, departments WHERE
employees.department_id = departments.department_id GROUP BY
department_name)
SELECT department_name, dept_total FROM summary WHERE dept_total >
(SELECT SUM(dept_total) * 1/3 FROM summary)
ORDER BY dept_total DESC;
(7) Converting
Columns to rows :
Often times there will be a need
where columns need to be converted in to rows. For example:
col1 col2
------------
1
2
3
4
5
6
Needs to be converted to:
colX
-----
1
2
3
4
5
6
SQL Statement (convert 2 column
output):
Method 1: Using
cartesian join with UNION ALL
-- Group by is being
used to eliminate duplicates
select
decode(r,1,object_id,object_name) from abc,(select 1 r from dual
union all select 2 r from dual) group by
decode(r,1,to_char(object_id),object_name);
Method 2: Using cartesian join
with CONNECT BY
-- Group by is being
used to eliminate duplicates
select
decode(col1,1,object_id,object_name) from abc,(select level col1
from dual connect by level <= 2) group by
decode(col1,1,to_char(object_id),object_name); |
