tag:blogger.com,1999:blog-518481768015386858.post5134952625920238361..comments2023-08-06T04:28:52.092-04:00Comments on Karen Morton: You don't really need that index (or that SQL)Karenhttp://www.blogger.com/profile/03309823327597536648noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-518481768015386858.post-4817277232405684632009-06-23T14:06:00.552-04:002009-06-23T14:06:00.552-04:00Check out the Robotman.
(I used to hang out with ...Check out <a href="http://the-robotman.com/" rel="nofollow">the Robotman</a>.<br /><br />(I used to hang out with Fred when we were both a lot younger and worked at the same company).<br /><br />word: nomoutJoel Garryhttps://www.blogger.com/profile/13325061229393838224noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-78697246984561365982009-06-19T09:37:31.073-04:002009-06-19T09:37:31.073-04:00Hi Narendra,
You are right, EXTRACT_END_DTS canno...Hi Narendra,<br /><br />You are right, EXTRACT_END_DTS cannot be left out. I meant that you need two of those lines, I just wrote one of them.<br /><br />Regards,<br />Rob.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-58164084136630457232009-06-19T04:23:32.465-04:002009-06-19T04:23:32.465-04:00Karen/Rob,
Interesting.
But I am not sure how is
...Karen/Rob,<br /><br />Interesting.<br />But I am not sure how is<br /><i>a.AUDIT_REC_UPDT_DTS > (select EXTRACT_START_DTS from tb_transaction where trans_sys_id = (select max(trans_sys_id) from tb_transaction))<br />and a.AUDIT_REC_UPDT_DTS < (select EXTRACT_END_DTS from tb_transaction where trans_sys_id = (select max(trans_sys_id) from tb_transaction))</i><br />equivalent to<br /><i>a.audit_rec_updt_dts > ( select max(extract_start_dts) keep (dense_rank last order by id) from tb_transaction )</i><br /><br />Why can EXTRACT_END_DTS be left out?Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-6132529727578767492009-06-18T18:51:38.192-04:002009-06-18T18:51:38.192-04:00Karen -
Long time fan, first time blog commenter....Karen -<br /><br />Long time fan, first time blog commenter... :)<br /><br />I like Rob's comment, and I'll have to "keep" that construct<br />in mind. There are always multiple ways of doing SQL, and my first<br />thought went here:<br /><br />with my_factor as<br />(select extract_start_dts,<br /> extract_end_dts<br /> from tb_transaction<br /> where trans_sys_id = (select max(trans_sys_id) from tb_transaction)<br />)<br />--rest of query using my_factor here--<br />/<br /><br />The important idea, of course, is to keep the amount of work to a minimum<br />by reducing your two operations to one. I realize that the difference is<br />trivial, but it's good to keep in mind for general purposes.<br /><br />Back to the original set of four separate queries for a moment. I can <br />imagine all sorts of havoc being created depending upon the circumstances. <br />With four separate queries, does the<br />process behind the original code guarantee that no changes whatsoever can<br />occur to the underlying tb_transaction and tr_policy_register tables during<br />the period when these four queries are executed? Without<br />that guarantee, you can never be absolutely certain that the work is<br />correct at a certain point in time.<br /><br />On a similar note, the use of rownum without an order by should raise all sorts<br />of eyebrows. As Tom Kyte points out repeatedly, Oracle will not guarantee the<br />order of results delivered by a query. Even without changes to the underlying<br />tables over the course of a few seconds or minutes, it's probably possible to<br />have the 2627 rows of one "sub-query" overlap with the 2627 rows of another.<br />Changes to the table/index statistics after one "sub-query" but before the<br />next could probably do that, I'd bet.<br /><br />That's all. Good work. I find it hard to believe that this approach of breaking<br />up larger sets into slightly more manageable one is common, but I see all sorts<br />of bad habits in my day-to-day work. Perhaps it does occur!<br /><br />Tom GainesTom Gaineshttps://www.blogger.com/profile/01211171087473458831noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-89368505664709550992009-06-18T16:23:16.819-04:002009-06-18T16:23:16.819-04:00Good one Rob, thanks!Good one Rob, thanks!Karenhttps://www.blogger.com/profile/03309823327597536648noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-82125235027328074022009-06-18T16:21:15.990-04:002009-06-18T16:21:15.990-04:00Hi Karen,
Nice post about the unfortunately not-s...Hi Karen,<br /><br />Nice post about the unfortunately not-so-rare urge to split a long running query into several parts.<br /><br />Another very small improvement can be made by rewriting the two "subquery predicates" to this form:<br /><br />and a.audit_rec_updt_dts > ( select max(extract_start_dts) keep (dense_rank last order by id) from tb_transaction )<br /><br />It won't save you very much time, but your plan will likely look even more simple.<br /><br />Regards,<br />Rob.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.com