Thank you !! On Mon, 11 Jul, 2022, 8:16 pm Jeremy Evans, <[email protected]> wrote:
> On Mon, Jul 11, 2022 at 7:25 AM ashwin k <[email protected]> wrote: > >> It would be appreciated If you could correct me if Im incorrect.When >> offset >> is zero getting columns is not required; >> > > Correct, because offset of 0 is the same as no offset at all. > > when offset is greater than zero why columns are required ? >> > > Emulating offset on old versions of Oracle requires a query where the > underlying columns must be specified. Oracle didn't start natively > supporting offsets until Oracle 12. So if you want to avoid this query, > update to Oracle 12 (or use a different database). > > Thanks, > Jeremy > > >> >> On Mon, 11 Jul, 2022, 7:44 pm Jeremy Evans, <[email protected]> >> wrote: >> >>> On Mon, Jul 11, 2022 at 1:54 AM ashwin k <[email protected]> wrote: >>> >>>> The extra query is getting triggered only when offset is greater than >>>> zero. >>>> >>>> I didn't notice this query when offset is zero. >>>> >>> >>> When offset is zero, the query to get the columns is not necessary, so >>> Sequel doesn't issue it. >>> >>> Thanks, >>> Jeremy >>> >>> >>>> On Sun, 10 Jul, 2022, 10:39 pm Jeremy Evans, <[email protected]> >>>> wrote: >>>> >>>>> On Sun, Jul 10, 2022 at 1:51 AM ashwin k <[email protected]> wrote: >>>>> >>>>>> Not sure if first query can be ignored? >>>>>> >>>>> >>>>> Not sure what you mean by "can be ignored". It's necessary to get the >>>>> columns, and you need to know the columns to create the second query. >>>>> >>>>> Thanks, >>>>> Jeremy >>>>> >>>>> >>>>>> >>>>>> On Sat, 9 Jul, 2022, 11:52 am Jeremy Evans, <[email protected]> >>>>>> wrote: >>>>>> >>>>>>> On Fri, Jul 8, 2022 at 9:41 PM ashwin k <[email protected]> wrote: >>>>>>> >>>>>>>> Limit fires an extra query when the offset is more than 1. I >>>>>>>> created a temp table and executed the query >>>>>>>> DB[:temp].limit(3,3).all >>>>>>>> >>>>>>>> *First it executed this query* >>>>>>>> START SELECT * FROM (SELECT * FROM "TEMP") "T1" WHERE (ROWNUM <= 1) >>>>>>>> - END ( 0.0060s) SELECT * FROM (SELECT * FROM "TEMP") "T1" WHERE >>>>>>>> (ROWNUM <= 1) >>>>>>>> >>>>>>>> *and then this* >>>>>>>> >>>>>>>> - START SELECT "ID", "NAME" FROM (SELECT "T1".*, ROWNUM >>>>>>>> "X_SEQUEL_ROW_NUMBER_X" FROM (SELECT * FROM "TEMP") "T1") "T1" WHERE >>>>>>>> (("X_SEQUEL_ROW_NUMBER_X" > 3) AND ("X_SEQUEL_ROW_NUMBER_X" <= (3 + >>>>>>>> 3))) >>>>>>>> >>>>>>>> - END ( 0.0033s) SELECT "ID", "NAME" FROM (SELECT "T1".*, ROWNUM >>>>>>>> "X_SEQUEL_ROW_NUMBER_X" FROM (SELECT * FROM "TEMP") "T1") "T1" WHERE >>>>>>>> (("X_SEQUEL_ROW_NUMBER_X" > 3) AND ("X_SEQUEL_ROW_NUMBER_X" <= (3 + >>>>>>>> 3))) => >>>>>>>> [{:id=>0.4e1, :name=>"d"}, {:id=>0.5e1, :name=>"e"}] >>>>>>>> >>>>>>> >>>>>>> Not sure what database you are using, but I'm guessing an old >>>>>>> version of Oracle? In order to run the query with a limit and offset >>>>>>> on an >>>>>>> old version of Oracle, the limit and offset need to be emulated using >>>>>>> ROWNUM, and that requires getting the columns. The first query is run >>>>>>> to >>>>>>> get the columns, and the second query to get the data (you can see the >>>>>>> second query uses the columns returned by the first query). >>>>>>> >>>>>>> Thanks, >>>>>>> Jeremy >>>>>>> >>>>>>> -- >>>>>>> You received this message because you are subscribed to the Google >>>>>>> Groups "sequel-talk" group. >>>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>>> send an email to [email protected]. >>>>>>> To view this discussion on the web visit >>>>>>> https://groups.google.com/d/msgid/sequel-talk/CADGZSScb43%2B3r9M3-coN1G60B%3DuRJV_QuaRfY1XYBSf2SjKLAg%40mail.gmail.com >>>>>>> <https://groups.google.com/d/msgid/sequel-talk/CADGZSScb43%2B3r9M3-coN1G60B%3DuRJV_QuaRfY1XYBSf2SjKLAg%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>>>>> . >>>>>>> >>>>>> -- >>>>>> You received this message because you are subscribed to the Google >>>>>> Groups "sequel-talk" group. >>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>> send an email to [email protected]. >>>>>> To view this discussion on the web visit >>>>>> https://groups.google.com/d/msgid/sequel-talk/CAPd3BLZ%2BChuU6%2BBbL4FsxBnafjqd13Z4_CBpGbAep0cUSTe6Uw%40mail.gmail.com >>>>>> <https://groups.google.com/d/msgid/sequel-talk/CAPd3BLZ%2BChuU6%2BBbL4FsxBnafjqd13Z4_CBpGbAep0cUSTe6Uw%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>>>> . >>>>>> >>>>> -- >>>>> You received this message because you are subscribed to the Google >>>>> Groups "sequel-talk" group. >>>>> To unsubscribe from this group and stop receiving emails from it, send >>>>> an email to [email protected]. >>>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/sequel-talk/CADGZSSca%2BbZiwyU8UaYNg_FwmT%3DscUuVDj-Ju40vG%2B848kRvkQ%40mail.gmail.com >>>>> <https://groups.google.com/d/msgid/sequel-talk/CADGZSSca%2BbZiwyU8UaYNg_FwmT%3DscUuVDj-Ju40vG%2B848kRvkQ%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "sequel-talk" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/sequel-talk/CAPd3BLaesLrqOPCruK_M6SNx127pN6CZpGJ21PYh8xr5j3-yTw%40mail.gmail.com >>>> <https://groups.google.com/d/msgid/sequel-talk/CAPd3BLaesLrqOPCruK_M6SNx127pN6CZpGJ21PYh8xr5j3-yTw%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>> . >>>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "sequel-talk" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/sequel-talk/CADGZSSf4LovejckXk%2BOw__OWMpnw4MHbs6xR5tx1iNXPHRLfhg%40mail.gmail.com >>> <https://groups.google.com/d/msgid/sequel-talk/CADGZSSf4LovejckXk%2BOw__OWMpnw4MHbs6xR5tx1iNXPHRLfhg%40mail.gmail.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "sequel-talk" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sequel-talk/CAPd3BLYz6GMqqhZHMZUeAHc%2B%3DJ0M024pMks_1g_HeBORjbwSYA%40mail.gmail.com >> <https://groups.google.com/d/msgid/sequel-talk/CAPd3BLYz6GMqqhZHMZUeAHc%2B%3DJ0M024pMks_1g_HeBORjbwSYA%40mail.gmail.com?utm_medium=email&utm_source=footer> >> . >> > -- > You received this message because you are subscribed to the Google Groups > "sequel-talk" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sequel-talk/CADGZSSesUpWCMtEP5PfWtbS4pPC_wJpwYEj1p%3DrewwAUx8emog%40mail.gmail.com > <https://groups.google.com/d/msgid/sequel-talk/CADGZSSesUpWCMtEP5PfWtbS4pPC_wJpwYEj1p%3DrewwAUx8emog%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/CAPd3BLbJJH-_gSvL6FE4Z-GqHs6XTSHFZe7p5mkkizm1XVL2PQ%40mail.gmail.com.
