4 Replies Latest reply: Nov 19, 2012 3:48 AM by Jonathan Lewis RSS

    inconsistant Count return by same query.

    974582
      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
          Please format your query
          • 2. Re: inconsistant Count return by same query.
            974582
            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
              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
                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