Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Which SQL query should I go for?

Re: Which SQL query should I go for?

From: Turkbear <john.g_at_dot.spamfree.com>
Date: Tue, 10 Aug 2004 12:56:16 -0500
Message-ID: <qq2ih0h374c2f5snbf0hmj1p611c71qob5@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... Received on Tue Aug 10 2004 - 12:56:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US