I have oracle 10g installed on windows server 2003.
I have 22,000,000 records in single table and this is a transactional table,
increasing of records in same table approx. 50,000 per month.
My question is that whenever I run query on it always my query too slow. Is there any method by which I can improve the performance of the query, like partitioning the table or else?
select a.prd_code
, a.br_code||'-'||br_title
, a.size_code||'-'||size_title
,size_in_gms
, a.var_code||'-'||var_title
, a.form_code||'-'||form_title
, a.pack_code||'-'||pack_title
, a.pack_type_code||'-'||pack_type_title
, start_date
, end_date
, a.price
from prices a
, brand br
, (select distinct prd_code
, br_code
, size_code
, var_code
, form_code
,packing_code
, pack_type_code
from cphistory
where prd_code = '01'
and flag = 'Y'
and project_yy = '2009' and '01' and '10') cp
, (select prd_code
, br_code
, size_code
, size_in_gms
from sizes
where prd_code = '01'
and end_date = '31-dec-2050'
and flag = 'Y') sz
, (select prd_code
, br_code
, var_code
, var_title
from varient) vt
, (select prd_code
, br_code
, form_code
, form_title
from form) fm
, (select prd_code
, pack_title
from package) pc
, (select prd_code
, pack_type_title
from pakck_type) pt
where a.prd_code = br.prd_code
and a.br_code = br_br_code
and a.prd_code = sz.prd_code
and a.br_code = sz.br_code
and a.size_code = sz.size_code
and a.prd_code = vt.prd_code
and a.br_code = vt.br_code
and a.var_code = vt.var_code
and a.prd_code = fm.prd_code
and a.br_code = fm.br_code
and a.form_code = fm.form_code
and a.prd_code = pc.prd_code
and a.br_code = pc.br_code
and a.pack_code = pc.pack_code
and a.prd_code = pt.prd_code
and a.pack_type_code = pt.pack_type_code
and end_date = '2009'
and prd_code = '01'
order by a.prd_code
, a.br_code
, a.size_code
, a.var_code
, a.pack_code
, a.form_code
tables used in this query are:
prices : has more than 2.1M rows
cphistory : has more than 2.2M rows
sizes : has more than 5000 rows
brand : has more than 1200 rows
varient : has more than 1800 rows
package : has more than 200 rows
pack_type : has more than 150 rows
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT
a.prd_code,
a.br_code || '-' || br_title,
a.size_code || '-' || size_title,
sz.size_in_gms,
a.var_code || '-' || vt.var_title,
a.form_code || '-' || fm.form_title,
a.pack_code || '-' || pc.pack_title,
a.pack_type_code || '-' || pt.pack_type_title,
start_date,
end_date,
a.price
FROM
prices a,
brand br,
(SELECT
DISTINCT cphistory.prd_code,
cphistory.br_code,
cphistory.size_code,
cphistory.var_code,
cphistory.form_code,
cphistory.packing_code,
cphistory.pack_type_code
FROM
cphistory
WHERE
cphistory.prd_code = '01'
AND cphistory.flag = 'Y'
AND cphistory.project_yy = '2009'
AND '01'
AND '10') cp,
(SELECT
sizes.prd_code,
sizes.br_code,
sizes.size_code,
sizes.size_in_gms
FROM
sizes
WHERE
sizes.prd_code = '01'
AND sizes.end_date = '31-dec-2050'
AND sizes.flag = 'Y') sz,
(SELECT
varient.prd_code,
varient.br_code,
varient.var_code,
varient.var_title
FROM
varient) vt,
(SELECT
form.prd_code,
form.br_code,
form.form_code,
form.form_title
FROM
form) fm,
(SELECT
package.prd_code,
package.pack_title
FROM
package) pc,
(SELECT
pakck_type.prd_code,
pakck_type.pack_type_title
FROM
pakck_type
WHERE
(
pakck_type.prd_code = '01'
)) pt
WHERE
a.prd_code = br.prd_code
AND a.br_code = br_br_code
AND a.prd_code = sz.prd_code
AND a.br_code = sz.br_code
AND a.size_code = sz.size_code
AND a.prd_code = vt.prd_code
AND a.br_code = vt.br_code
AND a.var_code = vt.var_code
AND a.prd_code = fm.prd_code
AND a.br_code = fm.br_code
AND a.form_code = fm.form_code
AND a.prd_code = pc.prd_code
AND a.br_code = pc.br_code
AND a.pack_code = pc.pack_code
AND a.prd_code = pt.prd_code
AND a.pack_type_code = pt.pack_type_code
AND end_date = '2009'
AND 1 = 1
ORDER BY
a.prd_code,
a.br_code,
a.size_code,
a.var_code,
a.pack_code,
a.form_code