> All fields in the where clause contain indices and constrains for

validation of business logic.

Please do not top-post.

Right.

Next, post the "query plan", which should be available in the
tool you're using and try re-writing the query to proper
JOIN syntax.

eg:

from
cd$$__abb__master_8 M
join Livery L on M.Card_Type = L.CardType
join cd$$__abb__detail_8 D ...

Then also post the results.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> > select
> > 'U'||D.Batch_No || '-' || D.Sub_Batch_No as Batch_No, d.Batch_No
> as FBatch_No,
> > d.Sub_Batch_No as FSub_Batch_No, L.Display_Name,
> L.C_Centre,d.reasoncode,
> > count(d.sub_batch_no) as carrier_count
> > from
> > cd$$__abb__master_8 M,Livery L, cd$$__abb__detail_8 D
> > where
> > (M.Card_Type = L.CardType) and (M.Batch_No = D.Batch_No) and
> d.carrier_required = 'T'
> > and d.pullout_code = 0 and d.runtype in

('NEW/REISSUE','REPLACEMENT')

> and
> > ((m.Batch_No = 7 and d.Sub_Batch_No = 1) or (m.Batch_No = 7 and
> d.Sub_Batch_No = 2)
> > or (m.Batch_No = 8 and d.Sub_Batch_No = 1))
> > group by
> > D.Batch_No,D.Sub_Batch_No,L.Display_Name,d.reasoncode,l.C_Centre
> > order by
> > d.Batch_No, d.Sub_Batch_No , L.Display_Name