Cardinality wrong in Explain Plan

From: srivenu <srivenu_at_hotmail.com>
Date: 14 Apr 2003 05:30:45 -0700
Message-ID: <1a68177.0304140430.5476c836@posting.google.com>

We are running Oracle 8.1.7.0 on Sun Solaris 8. When i run the following SQL statement, i get wrong value for cardinality in the Execution plan.
Can someone tell me the reason.

analyze table deposit_voucher_header compute statistics;

set autot on

SELECT DEPOSIT_VOUCHER_ID
FROM DEPOSIT_VOUCHER_HEADER
WHERE BANK_ACCOUNT_NO = 'xxxxx'
AND BANK_ID = 'yyy'
AND LOCATION_ID = 'zzz'
AND VOUCHER_STATUS_ID = 2
/

DEPOSIT_VOUCHER_ID

---

              7729
              ----  
              ----

970 rows selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=4 Bytes=96)    1 0 TABLE ACCESS (FULL) OF 'DEPOSIT_VOUCHER_HEADER' (Cost=18 C

          ard=4 Bytes=96)

Statistics

---

          0  recursive calls
          4  db block gets
        179  consistent gets
          0  physical reads
          0  redo size
      10765  bytes sent via SQL*Net to client
       4733  bytes received via SQL*Net from client
         66  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        970  rows processed

Why is cardinality reported as 4 in the plan ?

Then i issue this statement
analyze table deposit_voucher_header
compute statistics
for columns bank_account_no, bank_id,location_id,voucher_status_id /

SELECT DEPOSIT_VOUCHER_ID
FROM DEPOSIT_VOUCHER_HEADER
WHERE BANK_ACCOUNT_NO = 'xxxxx'
AND BANK_ID = 'yyy'
AND LOCATION_ID = 'zzz'
AND VOUCHER_STATUS_ID = 2
/

DEPOSIT_VOUCHER_ID

---

              7729
              ----  
              ----

970 rows selected.

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=127 Bytes=30
          48)

   1    0   TABLE ACCESS (FULL) OF 'DEPOSIT_VOUCHER_HEADER' (Cost=18 C
          ard=127 Bytes=3048)

Statistics

---

          0  recursive calls
          4  db block gets
        179  consistent gets
          0  physical reads
          0  redo size
      10765  bytes sent via SQL*Net to client
       4733  bytes received via SQL*Net from client
         66  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        970  rows processed

Now cardinality shows as 127. why?
I think cardinality is the number of rows that the optimizer thinks will be returned in a step.
How do i get the optimizer to use the correct values. Thanks in advance for any help.

regards
Srivenu Received on Mon Apr 14 2003 - 07:30:45 CDT