Jinshan Li wrote:
> 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.
It is impossible to know based on what you posted. To know that we
would need DDL for all tables and constraints.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Tue Jun 07 2005 - 21:34:24 CDT
