Re: compare 2 SQL performance

Hi,

  Could u post the schema of both the tables so that we can help u out.   It is very difficult to understand your requirement without the   complete details...

In article <8qrp1i$m5$1_at_nnrp1.deja.com>,   wy_at_fudan.edu wrote:
> 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.
>

--
Regards
Rajagopal Venkataramany


Sent via Deja.com http://www.deja.com/
Before you buy.