jerry gitomer <jgitomer_at_verizon.net> wrote in message news:<kEjpe.4543$zT2.756_at_trndny04>...
> 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
Thx. In my Oracle DB, the second SQL is faster, but I am not sure
whether they are equivalent for results.
Received on Tue Jun 07 2005 - 20:32:40 CDT
