Re: Are the two SQLs equivalent?

Jinshan Li wrote:
> Are the two SQLs equivalent? And , you think which is better.
> 1.FROM table1
> WHERE
> RTRIM(START_DATE||BNK_CD||STN_CD) IN
> ( SELECT MAX(START_DATE)||BNK_CD||STN_CD
> FROM table1 KYO, table2 SYS
> WHERE START_DATE <= SYS.CNTR_DATE
> AND ( DEL_DATE < SYS.CNTR_DATE OR DEL_DATE IS NULL)
> GROUP BY BNK_CD,STN_CD
> )
>
>
> 2.FROM table1 BT
> WHERE
> EXISTS
> ( SELECT 'A'
> FROM table1 KYO, table12 SYS
> WHERE KYO.START_DATE <= SYS.CNTR_DATE
> AND ( KYO.DEL_DATE < SYS.CNTR_DATE OR KYO.DEL_DATE IS NULL)
> AND BT.BNK_CD = KYO.BNK_CD AND BT.STN_CD = KYO.STN_CD
> having BT.START_DATE = MAX(START_DATE)
> )

First, you should be aware of the fact that the alternative which is better in the current version may be worse in the next version. This is due to changes being made in both the code generators and the query optimizers from release to release.

I see that you are probably using Oracle which tells me that you should ANALYZE your database and then run EXPLAIN against each alternative. Doing so will tell you which is the better performer based on the release you are using and the current contents of your database.

HTH Jerry Received on Tue Jun 07 2005 - 11:08:48 CDT