Re: Beat this....
From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Thu, 28 Mar 2013 09:06:13 -0500
Message-ID: <CAJvnOJa7Vi4hNmSyWysn_7x-sJsvJCucwE9DVPc5Md=-3aF7gQ_at_mail.gmail.com>
Well, I cant beat the actual cost, but I once ran into this query (names changed) which was used to create a table containing a series of dates in order and outputs. Note that it joins to all_objects. Cost was about 15000.:
(SELECT
TO_DATE ('2009-06-10' /*+:1*/, 'YYYY-MM-DD') - 1 + ROWNUM AS OUT_DAY FROM all_objects
WHERE TO_DATE ('2009-05-26'/*+:2*/, 'YYYY-MM-DD') - 1 + ROWNUM <= TO_DATE
('2009-06-08' /*+:3*/, 'YYYY-MM-DD')), NAME_CHANGED NC) NC2 I was able to rewrite with about 5 minutes thought to join to dual using a connect by to reduce the cost to basically nothing. I suspect that it could have been reduced even further, but I thought this indicated pretty classic ignorance of the nature of oracle views.
Date: Thu, 28 Mar 2013 09:06:13 -0500
Message-ID: <CAJvnOJa7Vi4hNmSyWysn_7x-sJsvJCucwE9DVPc5Md=-3aF7gQ_at_mail.gmail.com>
Well, I cant beat the actual cost, but I once ran into this query (names changed) which was used to create a table containing a series of dates in order and outputs. Note that it joins to all_objects. Cost was about 15000.:
(SELECT
TO_DATE ('2009-06-10' /*+:1*/, 'YYYY-MM-DD') - 1 + ROWNUM AS OUT_DAY FROM all_objects
WHERE TO_DATE ('2009-05-26'/*+:2*/, 'YYYY-MM-DD') - 1 + ROWNUM <= TO_DATE
('2009-06-08' /*+:3*/, 'YYYY-MM-DD')), NAME_CHANGED NC) NC2 I was able to rewrite with about 5 minutes thought to join to dual using a connect by to reduce the cost to basically nothing. I suspect that it could have been reduced even further, but I thought this indicated pretty classic ignorance of the nature of oracle views.
>
-- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 28 2013 - 15:06:13 CET