Re: Which SQL query should I go for?

"Turkbear" <john.g_at_dot.spamfree.com> a écrit dans le message de news:5i6ih0961fh1btsn6r2ulflj2pcjpeah8i_at_4ax.com...
> "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
> >
> >"Turkbear" <john.g_at_dot.spamfree.com> a écrit dans le message de
> >news:qq2ih0h374c2f5snbf0hmj1p611c71qob5_at_4ax.com...
> >> "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> >>
> >> >
> >> >"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
> >>
> >> Maybe I'm missing something ( not a rare event) but would not this work:
> >>
> >> SELECT t.strategy_dim_key, t.POSITION_ID, t.account_hier_dim_key,
> >> t.POSITION_ID ...
> >> FROM POSITION t
> >> WHERE
> >> (t.cob_date = to_date('20040423','yyyymmdd') OR t.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
> >>
> >> With an index on cob_date it should be fairly efficient...
> >>
> >>
> >
> >What t_1 stand for?
>
> Sorry, bad editing..should be t. like the others..
>
> AND t.strategy_dim_key = t.strategy_dim_key
> AND t.account_hier_dim_key = t.account_hier_dim_key
>
>

And what is the meaning of these ones?
strategy_dim_key is not null and account_hier_dim_key is not null

Btw, the OP question is to get *in the same row* the values for one day and its previous one. That is what lag function is made for. Have a look at the first link i sent.

-- 
Regards
Michel Cadot