This discussion is archived
4 Replies Latest reply: Nov 19, 2012 1:48 AM by Jonathan Lewis RSS

inconsistant Count return by same query.

974582 Newbie
Currently Being Moderated
I am Having below query which is having total 664 records and for WHERE Clause (accountno ='13987135') it is having 3 records but when i am taking count it is returning 3 at first time and again returning 4 every time from then onwords.

SELECT Count(*) cnt FROM(SELECT rownum rnum,
secno,
positionname,
tradingsymbol,
cusipcum,
businessclientname,
businessclientid,
ProductCode,
planname,
planid,
accountype,
sectype,
sysid,
accountno
FROM
(SELECT rownum rnum,
secno,
positionname,
tradingsymbol,
cusipcum,
businessclientname,
businessclientid,
ProductCode,
planname,
planid,
accountype,
sectype,
sysid,
accountno
FROM
(SELECT rownum rnum,
secno,
positionname,
tradingsymbol,
cusipcum,
businessclientname,
businessclientid,
ProductCode,
planname,
planid,
accountype,
sectype,
sysid,
accountno
FROM
(SELECT secno,
positionname,
tradingsymbol,
cusipcum,
businessclientname,
businessclientid,
ProductCode,
planname,
planid,
accountype,
sectype,
sysid,
accountno
FROM
( SELECT DISTINCT sec.sec_no secno,
sec.SEC_NM positionname,
sec.SEC_SYMB_CD tradingsymbol,
sec.cusip_no cusipcum,
orgdet.org_nm businessclientname,
orgdet.org_id businessclientid,
plntyp.plan_typ_cd ProductCode,
plntyp.plan_typ_dsc planname,
accthis.plan_id planid,
invacct.ACCT_TYP_DSC accountype,
sec.sec_typ_dsc sectype,
invacc.sys_id sysid,
accthis.inv_acct_no accountno
FROM chbm_test.chbm_securities sec,
chbm_test.chbm_inv_acct_positions acctpos,
chbm_test.chbm_plan_type plntyp,
chbm_test.CHBM_INVESTMENT_ACCOUNT invacc,
chbm_test.chbm_plan plan,
chbm_test.CHBM_INVESTMENT_ACCOUNT invacct,
chbm_test.chbm_party_role_on_contract PROC,
chbm_test.chbm_organization org,
chbm_test.CHBM_INV_ACCT_HISTORY accthis,
(SELECT c.plan_id,
b.org_nm,
b.org_id
FROM chbm_test.chbm_party_role_on_contract a,
chbm_test.chbm_organization b,
chbm_test.chbm_plan c
WHERE a.org_id=b.org_id
AND a.cont_id =c.plan_id
AND a.role_cd ='OWNER'
) orgdet
WHERE sec.sec_no = acctpos.sec_no
AND accthis.inv_acct_no = acctpos.inv_acct_no
AND accthis.plan_id = orgdet.plan_id
AND plan.plan_typ_cd =plntyp.plan_typ_cd
AND plan.plan_id = accthis.plan_id
AND invacc.inv_acct_no = acctpos.inv_acct_no
AND invacct.inv_acct_no = accthis.inv_acct_no
AND org.org_id =proc.prty_id
AND plan.plan_id =proc.cont_id
AND proc.cont_id =orgdet.plan_id
AND TRUNC(plan.eff_dt) >= '31-DEC-10'
AND TRUNC(plan.eff_dt) <= '08-NOV-12'
)
ORDER BY positionname ASC
)
)
WHERE rnum BETWEEN 1 AND 200
)
ORDER BY accountno ASC) WHERE accountno ='13987135'

Edited by: 971579 on Nov 18, 2012 11:12 PM
  • 1. Re: inconsistant Count return by same query.
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Please format your query
  • 2. Re: inconsistant Count return by same query.
    974582 Newbie
    Currently Being Moderated
    Below is the formeted query..

    SELECT COUNT(*) cnt
    FROM
    (SELECT rownum rnum,
    secno,
    positionname,
    tradingsymbol,
    cusipcum,
    businessclientname,
    businessclientid,
    ProductCode,
    planname,
    planid,
    accountype,
    sectype,
    sysid,
    accountno
    FROM
    (SELECT rownum rnum,
    secno,
    positionname,
    tradingsymbol,
    cusipcum,
    businessclientname,
    businessclientid,
    ProductCode,
    planname,
    planid,
    accountype,
    sectype,
    sysid,
    accountno
    FROM
    (SELECT rownum rnum,
    secno,
    positionname,
    tradingsymbol,
    cusipcum,
    businessclientname,
    businessclientid,
    ProductCode,
    planname,
    planid,
    accountype,
    sectype,
    sysid,
    accountno
    FROM
    (SELECT secno,
    positionname,
    tradingsymbol,
    cusipcum,
    businessclientname,
    businessclientid,
    ProductCode,
    planname,
    planid,
    accountype,
    sectype,
    sysid,
    accountno
    FROM
    ( SELECT DISTINCT sec.sec_no secno,
    sec.SEC_NM positionname,
    sec.SEC_SYMB_CD tradingsymbol,
    sec.cusip_no cusipcum,
    orgdet.org_nm businessclientname,
    orgdet.org_id businessclientid,
    plntyp.plan_typ_cd ProductCode,
    plntyp.plan_typ_dsc planname,
    accthis.plan_id planid,
    invacct.ACCT_TYP_DSC accountype,
    sec.sec_typ_dsc sectype,
    invacc.sys_id sysid,
    accthis.inv_acct_no accountno
    FROM chbm_test.chbm_securities sec,
    chbm_test.chbm_inv_acct_positions acctpos,
    chbm_test.chbm_plan_type plntyp,
    chbm_test.CHBM_INVESTMENT_ACCOUNT invacc,
    chbm_test.chbm_plan plan,
    chbm_test.CHBM_INVESTMENT_ACCOUNT invacct,
    chbm_test.chbm_party_role_on_contract PROC,
    chbm_test.chbm_organization org,
    chbm_test.CHBM_INV_ACCT_HISTORY accthis,
    (SELECT c.plan_id,
    b.org_nm,
    b.org_id
    FROM chbm_test.chbm_party_role_on_contract a,
    chbm_test.chbm_organization b,
    chbm_test.chbm_plan c
    WHERE a.org_id=b.org_id
    AND a.cont_id =c.plan_id
    AND a.role_cd ='OWNER'
    ) orgdet
    WHERE sec.sec_no = acctpos.sec_no
    AND accthis.inv_acct_no = acctpos.inv_acct_no
    AND accthis.plan_id = orgdet.plan_id
    AND plan.plan_typ_cd =plntyp.plan_typ_cd
    AND plan.plan_id = accthis.plan_id
    AND invacc.inv_acct_no = acctpos.inv_acct_no
    AND invacct.inv_acct_no = accthis.inv_acct_no
    AND org.org_id =proc.prty_id
    AND plan.plan_id =proc.cont_id
    AND proc.cont_id =orgdet.plan_id
    AND TRUNC(plan.eff_dt) >= '31-DEC-10'
    AND TRUNC(plan.eff_dt) <= '08-NOV-12'
    )
    ORDER BY positionname ASC
    )
    )
    WHERE rnum BETWEEN 1 AND 200
    )
    ORDER BY accountno ASC
    )
    WHERE accountno ='13987135'
  • 3. Re: inconsistant Count return by same query.
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Use code it will be better , your query is long and needs to be review
  • 4. Re: inconsistant Count return by same query.
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    971579 wrote:
    Below is the formeted query..
    SELECT COUNT(*) cnt
    FROM
    (SELECT rownum rnum,
    secno,
    positionname,
    tradingsymbol,
    cusipcum,
    businessclientname,
    businessclientid,
    ProductCode,
    planname,
    planid,
    accountype,
    sectype,
    sysid,
    accountno
    FROM
    (SELECT rownum rnum,
    secno,
    positionname,
    tradingsymbol,
    cusipcum,
    businessclientname,
    businessclientid,
    ProductCode,
    planname,
    planid,
    accountype,
    sectype,
    sysid,
    accountno
    FROM
    (SELECT rownum rnum,
    secno,
    positionname,
    tradingsymbol,
    cusipcum,
    businessclientname,
    businessclientid,
    ProductCode,
    planname,
    planid,
    accountype,
    sectype,
    sysid,
    accountno
    FROM
    (SELECT secno,
    positionname,
    tradingsymbol,
    cusipcum,
    businessclientname,
    businessclientid,
    ProductCode,
    planname,
    planid,
    accountype,
    sectype,
    sysid,
    accountno
    FROM
    ( SELECT DISTINCT sec.sec_no secno,
    sec.SEC_NM positionname,
    sec.SEC_SYMB_CD tradingsymbol,
    sec.cusip_no cusipcum,
    orgdet.org_nm businessclientname,
    orgdet.org_id businessclientid,
    plntyp.plan_typ_cd ProductCode,
    plntyp.plan_typ_dsc planname,
    accthis.plan_id planid,
    invacct.ACCT_TYP_DSC accountype,
    sec.sec_typ_dsc sectype,
    invacc.sys_id sysid,
    accthis.inv_acct_no accountno
    FROM chbm_test.chbm_securities sec,
    chbm_test.chbm_inv_acct_positions acctpos,
    chbm_test.chbm_plan_type plntyp,
    chbm_test.CHBM_INVESTMENT_ACCOUNT invacc,
    chbm_test.chbm_plan plan,
    chbm_test.CHBM_INVESTMENT_ACCOUNT invacct,
    chbm_test.chbm_party_role_on_contract PROC,
    chbm_test.chbm_organization org,
    chbm_test.CHBM_INV_ACCT_HISTORY accthis,
    (SELECT c.plan_id,
    b.org_nm,
    b.org_id
    FROM chbm_test.chbm_party_role_on_contract a,
    chbm_test.chbm_organization b,
    chbm_test.chbm_plan c
    WHERE a.org_id=b.org_id
    AND a.cont_id =c.plan_id
    AND a.role_cd ='OWNER'
    ) orgdet
    WHERE sec.sec_no        = acctpos.sec_no
    AND accthis.inv_acct_no = acctpos.inv_acct_no
    AND accthis.plan_id     = orgdet.plan_id
    AND plan.plan_typ_cd    =plntyp.plan_typ_cd
    AND plan.plan_id        = accthis.plan_id
    AND invacc.inv_acct_no  = acctpos.inv_acct_no
    AND invacct.inv_acct_no = accthis.inv_acct_no
    AND org.org_id          =proc.prty_id
    AND plan.plan_id        =proc.cont_id
    AND proc.cont_id        =orgdet.plan_id
    AND TRUNC(plan.eff_dt) >= '31-DEC-10'
    AND TRUNC(plan.eff_dt) <= '08-NOV-12'
    )
    ORDER BY positionname ASC
    )
    )
    WHERE rnum BETWEEN 1 AND 200
    )
    ORDER BY accountno ASC
    )
    WHERE accountno ='13987135'
    You haven't stated the Oracle version number, and that might be significant - 11g, for example, allows for "cardinaliity feedback" to change plans between executions.
    Your query has a "rownum between 1 and 200" predicate after an ORDER BY on position name. Since the order is not deterministic with respect to accountno, you may find that it is legal for rows for accountno 13987135 to change position within positionname as the execution plan changes. If you're running 11g then the optimizer may have decided to change execution plans between the first and second executions.

    Repeat your tests, and check the actual execution plans that appeared. For simplicitly, I suggest you enable sql_trace and check the resulting trace file, alternatively call dbms_xplan.display_cursor() to check the plan after each execution.


    Regards
    Jonathan Lewis

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points