Re: Which SQL query should I go for?

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 11 Aug 2004 18:23:46 +0200
Message-ID: <411a4819$0$26993$626a14ce@news.free.fr>

"Dimitri" <jose.cuervo_at_lycos.co.uk> a écrit dans le message de news:2452bd93.0408110625.66accfdd_at_posting.google.com... > "Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:<41191c1d$0$23886$626a14ce_at_news.free.fr>...
> > "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.

>
>
> Michel understood the issue well. I want to have in the same row, data
> for one date and its previous one. T and T-1 are just the aliases of
> two instances of the same table called "POSITION".
> It look like the function Michel is suggesting would do the job, but
> the problem is that the link you gave me Michel requires registration
> and the page to set up a new account says I already have an account
> although I've never registered there in my life. If the page is not
> too big could you email it to me please? I would appreciate it
> greatly.
>
> Thanks
> Dimitri

It's the LAG function page from SQL Reference. I'll send it to you.

-- 
Regards
Michel Cadot

Received on Wed Aug 11 2004 - 11:23:46 CDT