Re: Which SQL query should I go for?

"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 Received on Tue Aug 10 2004 - 13:57:08 CDT