Home » RDBMS Server » Performance Tuning » Count(*) performance
Count(*) performance [message #65851] Wed, 12 January 2005 23:16 Go to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hello Experts,

I have this query which is taking 3 hrs. to execute.
I just wanted to how we can improve performance of count(*) as this will not use an Index and will go for a Full Table scan. Is there anyway that we can improve the performance of count(*) queries as I have lot of queries which are taking long time.

select /*+INDEX(SL ICUS24F4) first_rows(100)*/
count(*)
from TSOP_LOG SL
where ((SL.ORG_ID_C is null and SL.ORG_NME_SEQ_C is null) or exists
(select 'x'
from TACCOUNT ACCT,
TORGANIZATION ORG,
TORGANIZATION ACCT_ORG,
TCOMP_REP R
where ORG.ORG_ID_C = SL.ORG_ID_C and
ORG.ORG_NME_SEQ_C = SL.ORG_NME_SEQ_C and
ORG.JURIS_ID_C <> '70001' and
ACCT_ORG.ORG_ID_C = ORG.ORG_ID_C and
ACCT_ORG.JURIS_ID_C <> '70001' and
ACCT_ORG.ORG_ID_C = ACCT.ORG_ID_C and
ACCT_ORG.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C and
ACCT.ACCT_TYP_C = 'COMP' and
ACCT.ACCT_STAT_C NOT IN ('POTENL', 'ACTWSB') AND
(ACCT.SVC_CENTER_ID_C not in ('555555555', '666666666') or
ACCT.SVC_CENTER_ID_C is null) and
R.ORG_ID_C = ACCT_ORG.ORG_ID_C and
R.ORG_NME_SEQ_C = ACCT_ORG.ORG_NME_SEQ_C and
R.SVC_C in
('000005', '000006', '000020', '000021', '000046', '000047',
'000048', '000049', '000053', '000054', '000056', '000057',
'000148', '000154', '000160', '000161', '000223', '000227',
'000228', '000229', '000356', '000560', '008201', '008202',
'008203', '008204') and
R.COMP_REP_ID_C NOT IN (851756, 1312489, 1330506))) and
SL.JURIS_ID_C <> '70001' and SL.DOM_JURIS_ID_C <> '70001' and
(exists
(select 'x'
from TCOMP_REP R, TACCOUNT ACCT, TORGANIZATION ORG
where R.COMP_REP_ID_C = SL.COMP_REP_ID_C and
R.SVC_C in
('000005', '000006', '000020', '000021', '000046', '000047',
'000048', '000049', '000053', '000054', '000056', '000057',
'000148', '000154', '000160', '000161', '000223', '000227',
'000228', '000229', '000356', '000560', '008201', '008202',
'008203', '008204') and
R.COMP_REP_ID_C NOT IN (851756, 1312489, 1330506) and
ACCT.ORG_ID_C = R.ORG_ID_C and
ACCT.ORG_NME_SEQ_C = R.ORG_NME_SEQ_C and
ORG.ORG_ID_C = ACCT.ORG_ID_C and
ORG.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C and
ACCT.ACCT_TYP_C = 'COMP' and
ACCT.ACCT_STAT_C NOT IN ('POTENL', 'ACTWSB') AND
(ACCT.SVC_CENTER_ID_C not in ('555555555', '666666666') or
ACCT.SVC_CENTER_ID_C is null) and ORG.JURIS_ID_C <> '70001') or
SL.COMP_REP_ID_C IS null);

Any help is appreciated.

Milind.
Re: Count(*) performance [message #65852 is a reply to message #65851] Wed, 12 January 2005 23:22 Go to previous messageGo to next message
Padders
Messages: 79
Registered: January 2004
Member
How do you envisage an index being used in this case? Me I would be worrying about the monstrous WHERE EXISTS subquery.

BTW hint syntax is FIRST_ROWS_100, not FIRST_ROWS (100).
Re: Count(*) performance [message #65853 is a reply to message #65852] Thu, 13 January 2005 06:57 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And then again, you want to count all records, so why use a first_rows hint? That would be counterproductive imho.

Just to be sure (have seen this too often): You don't do a count(*) to just check whether there are any records that match your criteria; you really _need_ the count?

hth
Previous Topic: experience on solid state disks
Next Topic: How much used% we need to keep for this tablespace?
Goto Forum:
  


Current Time: Tue Sep 22 07:37:59 CDT 2020