Re: Which SQL query should I go for?

"Dimitri" <jose.cuervo_at_lycos.co.uk> a écrit dans le message de news:2452bd93.0408100049.46217571_at_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

Have a look at lead/lag and analytic functions:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions56a.htm#83619 http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions2a.htm#81409

-- 
Regards
Michel Cadot