compare 2 SQL performance

From: <wy_at_fudan.edu>
Date: Wed, 27 Sep 2000 03:20:22 GMT
Message-ID: <8qrp1i$m5$1@nnrp1.deja.com>

I have 2 table .
salecard,sub_salecard.they are linked by salecard_no

I want to update the salecard.status to '7' while all the sub_salecard.status is '7'.
the filter condition is billno.

I write 2 sql to realize the function.
(1)

update salecard set status='7'
where salecard_no in
(select salecard_no from sub_salecard

 where billno ='A212'
and not exists
(select null from sub_salecard a where a.status<>'7'

and a.salecard_no = sub_salecard.salecard_no))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

this part query can use the above query result or ...?

the explain:
1.12 UPDATE STATEMENT Optimizer =CHOOSE Cost=12   2.1 UPDATE SALECARD
    3.1 NESTED LOOPS

      4.1 VIEW
        5.1 SORT UNIQUE
          6.1 FILTER
            7.1 TABLE ACCESS BY INDEX ROWID SUB_SALECARD
              8.1 INDEX RANGE SCAN IDX_SUB_SALECARD_BILLNO NON-UNIQUE
            7.2 TABLE ACCESS BY INDEX ROWID SUB_SALECARD
              8.1 BITMAP CONVERSION TO ROWIDS
                9.1 BITMAP MINUS
                  10.1 BITMAP MINUS
                    11.1 BITMAP CONVERSION FROM ROWIDS
                      12.1 SORT ORDER BY
                        13.1 INDEX RANGE SCAN SYS_C004501 UNIQUE
                    11.2 BITMAP INDEX SINGLE VALUE IDX_BIT_SUB_STATUS
                  10.2 BITMAP INDEX SINGLE VALUE IDX_BIT_SUB_STATUS
      4.2 INDEX UNIQUE SCAN SYS_C004473 UNIQUE

(2)

update salecard set status='7'
where salecard_no in
(select salecard_no from sub_salecard

 where billno ='A212'
and not exists
(select null from sub_salecard a where a.status<>'7'
and a.salecard_no = sub_salecard.salecard_no and salecard_no in
(select salecard_no from sub_salecard

 where billno ='A212')))
^^^^^^^^^^^^^^^^^^^^^^^^^^^

I add this part to restricted the result. I don't know if it is the redundant?
the explain:
1.12 UPDATE STATEMENT Optimizer =CHOOSE Cost=12   2.1 UPDATE SALECARD
    3.1 NESTED LOOPS

      4.1 VIEW
        5.1 SORT UNIQUE
          6.1 FILTER
            7.1 TABLE ACCESS BY INDEX ROWID SUB_SALECARD
              8.1 INDEX RANGE SCAN IDX_SUB_SALECARD_BILLNO NON-UNIQUE
            7.2 NESTED LOOPS
              8.1 TABLE ACCESS BY INDEX ROWID SUB_SALECARD
                9.1 INDEX RANGE SCAN IDX_SUB_SALECARD_BILLNO NON-UNIQUE
              8.2 TABLE ACCESS BY INDEX ROWID SUB_SALECARD
                9.1 INDEX RANGE SCAN SYS_C004501 UNIQUE
      4.2 INDEX UNIQUE SCAN SYS_C004473 UNIQUE

I don't know which is better.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Sep 26 2000 - 22:20:22 CDT