Odd query optimization

[Bruce Trimpop]

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

[slimdave]

That's interesting ... can you show us the explain plans for those queries?

[Cookies]

why are you not joining ALL like columns??
1 to 2 but not 1 to 4??

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' 

[Bruce Trimpop]

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'

[raghud]

Can you take out distinct clause from query on both cases and let us know the results ??

[Cookies]

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.

[Cookies]

does this do anything different?

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; 

[Bruce Trimpop]

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?!

Very curious

[tamilselvan]

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

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

[Bruce Trimpop]

Hi Tamil,

We're using 9i Enterprise, I'll let you know what you're suggestion does.

Thanks