Which SQL query should I go for?

From: Dimitri <jose.cuervo_at_lycos.co.uk>
Date: 10 Aug 2004 01:49:53 -0700
Message-ID: <2452bd93.0408100049.46217571@posting.google.com>

Hello everyone,

I was wondering if anyone could help me with a decision I have to make.
I need to write a query inside an Oracle stored procedure that draws its data from just one very large table called POSITION containing hundrends of thousands of records.
The table contains records for various dates. The query needs to return columns from one date (T), together with columns from a previous date (T-1) in each record. I have come up with the following two alternatives but I can't decide which one is best in terms of efficiency and maintainability:

CHOICE 1: SELECT t.strategy_dim_key, t.POSITION_ID, t.account_hier_dim_key, t_1.POSITION_ID ... (possibly more fields from date T or date T-1) FROM POSITION t, POSITION t_1
WHERE t.cob_date = to_date('20040423','yyyymmdd') AND t_1.cob_date = to_date('20040422','yyyymmdd')

AND t.strategy_dim_key = t_1.strategy_dim_key
AND t.account_hier_dim_key = t_1.account_hier_dim_key
______________________________________________________________

CHOICE 2:          SELECT T.strategy_dim_key, T.position_id, T.account_hier_dim_key, T_1.position_id FROM
(

SELECT n.strategy_dim_key,n.position_id,n.account_hier_dim_key,n.adjustment_dim_key FROM POSITION n WHERE n.cob_date = to_date('20040423','yyyymmdd') ) T,
(

SELECT n.strategy_dim_key,n.position_id,n.account_hier_dim_key,n.adjustment_dim_key FROM POSITION n WHERE n.cob_date = to_date('20040422','yyyymmdd') ) T_1
WHERE T.Strategy_dim_key = T_1.Strategy_dim_key AND T.account_hier_dim_key = T_1.account_hier_dim_key

---

columns strategy_dim_key and account_hier_dim_key form a composite key.

My worry is that choice 1, could result into a cartesian query depending on how the optimiser is configured but I'm not a database administrator so I don't know much about how or in what order the optimiser chooses to run statements.
Choice 2 looks a bit long-winded to me.
Any help would be appreciated.

Dimitri Received on Tue Aug 10 2004 - 03:49:53 CDT