Re: Which SQL query should I go for?

"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?

-- 
Regards
Michel Cadot