Re: Are the two SQLs equivalent?

On 7 Jun 2005 07:07:53 -0700, li.js_at_neusoft.com (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)
> )

1. Don't use SYS as a table alias...
2. Prefix the columns consistently...e.g., what tables contain the columns BNK_CD and STN_CD? From the second query, obviously more than one...
3. Use more parentheses ... e.g, do you REALLY know what the parser is going to do when it sees this:
   > AND ( KYO.DEL_DATE < SYS.CNTR_DATE OR KYO.DEL_DATE IS NULL)
   ???
4. Post some DDL for your tables so we know what is going on WRT data types, indexes, constraints, etc. etc.

Two things that fly at me in my face:
(a) first query:
> WHERE
> RTRIM(START_DATE||BNK_CD||STN_CD) IN (...)
No way the optimizer will use an index on any of these columns here because of RTRIM()...

(b) second query:
> having BT.START_DATE = MAX(START_DATE)
Where is your GROUP BY?

MAX(START_DATE) obviously needs to be evaluated only once, so I don't see any sense in putting it in a HAVING clause.

--
Bob Hairgrove
NoSpamPlease_at_Home.com