I have a query that's joining 4 tables. Very simple joins primary key to foreign key. Let's call them tables1 thru 4. The query is:
Select distinct
table1.column, table2.column, table3.column
from table1, table2, table3, table4
where
table1.pkey = table2.pkey and
table2.pkey = table3.pkey and
table3.pkey = table4.pkey and
table4.column like 'somevalue'
The query takes 3 minutes to run, but changing the query to:
Select distinct
table1.column, table2.column, table3.column
from table1, table2, table3, table4
where
table1.pkey = table2.pkey and
table2.pkey = table3.pkey and
table3.pkey = table4.pkey and
table4.pkey = table4.pkey and
table4.column like 'somevalue'
causes the query to run in 3 seconds, can someone please explain why?
Thanks in advance
Last edited by Bruce Trimpop; 08-10-2004 at 04:25 PM.
why are you not joining ALL like columns??
1 to 2 but not 1 to 4??
PHP Code:
SELECT DISTINCT
table1.COLUMN, table2.COLUMN, table3.COLUMN
FROM table1, table2, table3, table4
WHERE table1.pkey = table2.pkey
AND table1.pkey = table3.pkey
AND table1.pkey = table4.pkey
AND table2.pkey = table3.pkey
AND table2.pkey = table4.pkey
AND table3.pkey = table4.pkey
AND table4.COLUMN LIKE 'somevalue'
Note the self join is commented out, but uncommented the query performs significantly faster.
SELECT DISTINCT
pcsv.provinv_id AS ProvIn_ID,
ppm.PGMNME AS ProgNme,
ppm.PROVPGM_ID AS Prog_ID,
pcsv.svcdte AS SvcMnth
FROM
provinv pi,
provpgm ppm,
provconssvc pcsv,
optList
WHERE
pcsv.provinv_id = pi.provinv_ID
AND pi.PROVPGM_ID = ppm.PROVPGM_ID
AND pi.InvMethod_OL = OptList.OptList_ID
-- AND OptList.OptList_ID = OptList.OptList_ID
AND UPPER(OptList.DSCR) LIKE 'ATTENDANCE FILE'
the UPPER clause in the where statement causes the full
table scans. Somehow adding the commented line allows the CBO to
do something different evidentally.
BTW - why do you need the LIKE clause?
Essentially that is an equals statement.
select distinct
pcsv.provinv_id AS provin_id,
ppm.pgmnme AS prognme,
ppm.provpgm_id AS prog_id,
pcsv.svcdte AS svcmnth
from
provpgm ppm,
provconssvc pcsv,
(select pi.provinv_id, pi.provpgm_id
from provinv pi, optlist
where pi.invmethod_ol = optlist.optlist_id
and upper(optlist.dscr) = 'ATTENDANCE FILE') pi
where
pcsv.provinv_id = pi.provinv_id and
ppm.provpgm_id = pi.provpgm_id;
No idea why they are using LIKE. I agree that unless multiple values are being passed in based on user selections the LIKE should be an =. I'm not really involved in the application and how the queries are being created. I was asked about this sort of in a casual water fountain conversion.
BTW, cookie your rewrite still performs much like the original and adding the oplistid=oplistid to the derived table where clause made the query significantly faster?!
Writing the same column (indexed) on both sides in the WHERE clause was a trick to tell the optimizer to use index that I used 14 years ago when RBO was the only choice in Oracle 6/7.
How ever, with CBO, it should atleaset find out an optimal plan with less cost based on the available statistics.
What is the Oracle release you are using?
I guess the problem seems to be in PROVINV table. In both self-join and w/o self join, the plans show full table scan.
Did you analyze the table OPTLIST in between 2 explain plans.
Because I see the difference in values in cardinality for the OPTLIST table. One shows 3495 and other 2.
Does the index IDX_OPTLIST_UPPER_DSCR include OptList_ID column also?
Do one thing.
Create a concatenated index on provinv
on (invmethod_ol, provpgm_id, provinv_ID).
And add a HINT ORDERED
Code:
SELECT /*+ ORDERED */
DISTINCT
pcsv.provinv_id AS ProvIn_ID,
ppm.PGMNME AS ProgNme,
ppm.PROVPGM_ID AS Prog_ID,
pcsv.svcdte AS SvcMnth
FROM
optList,
provinv pi,
provpgm ppm,
provconssvc pcsv
WHERE
pcsv.provinv_id = pi.provinv_ID
AND pi.PROVPGM_ID = ppm.PROVPGM_ID
AND pi.InvMethod_OL = OptList.OptList_ID
-- AND OptList.OptList_ID = OptList.OptList_ID
AND UPPER(OptList.DSCR) LIKE 'ATTENDANCE FILE';
Tamil
Last edited by tamilselvan; 08-11-2004 at 03:52 PM.