Dear Oracle, I HATE YOU! Love, BTK



  • I've been writing a query for a user for the last few days, and it has been the bane of my existence, because one of the tables it has to go after is 230 million rows tall.  And I have to join that table three times.  Development is slow because the queries take forever, even with filters because the fields being filtered on are not indexed.

    Anyways, here's a small snip of the code

    WHERE 1=1
    ----------------------------------
    -- joins
    ----------------------------------
    AND cal.calendar_date BETWEEN ass_part.effective_beg_date AND ass_part.effective_end_date
    <more join code>

    I was getting crazy results, saying "no, that part wasn't there at that time."  It became clear that this line of code (beginning with AND) was the issue.  Then it hit me!  Oracle's thinking that line is a comment!

    Oracle, you fail!



  • @belgariontheking said:

    ass_part
    giggles



  • If Oracle thought that line was a comment, you would get more results, not less. Wild guess: ass_part.effective_end_date is null, since there is no end date yet, and you failed to take that into consideration.



  • @ammoQ said:

    If Oracle thought that line was a comment, you would get more results, not less.
    Yep.  I was getting too many results.

    @ammoQ said:

    Wild guess: ass_part.effective_end_date is null, since there is no end date yet, and you failed to take that into consideration.
    Can't prove it, cuz it's export controlled data, but I changed the query to return cal.calendar_date, ass_part.effective_beg_date, and ass_part.effective_end date and it was returning rows where calendar_date was definitely NOT between those two.

    BTW, yes, we have a Calendar table.  Any time you want to know when something happened, you have to join to that table.  It's stupid.  The reason the table exists is for other programs that deal with financial data and have to know when the quarter end is, etc.  Not for us.



  • @belgariontheking said:

    Yep.  I was getting too many results.

    OK, so I misunderstood your first post. 

    Can't prove it, cuz it's export controlled data, but I changed the query to return cal.calendar_date, ass_part.effective_beg_date, and ass_part.effective_end date and it was returning rows where calendar_date was definitely NOT between those two.

    So it's time for another wild guess: there is also an OR in the where clause... not within paranthesis...

    BTW, which Oracle version? 



  • @ammoQ said:

    So it's time for another wild guess: there is also an OR in the where clause... not within paranthesis...
    Again, can't prove it, but no.  Nice try, I'm glad you're trying to help figure this out.  If I remove the

      ----------------------------------
      -- joins
      ----------------------------------
    The query works properly.

    @ammoQ said:

    BTW, which Oracle version? 

    select banner from v$version

    "BANNER"
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

    PL/SQL Release 10.2.0.3.0 - Production

    CORE    10.2.0.3.0    Production

    TNS for Solaris: Version 10.2.0.3.0 - Production

    NLSRTL Version 10.2.0.3.0 - Production



  • Hmm...

    looks like somehow

    WHERE 1=1
    ----------------------------------
    -- joins
    ----------------------------------
    AND cal.calendar_date BETWEEN ass_part.effective_beg_date AND ass_part.effective_end_date
    <more join code>

    becomes

    WHERE 1=1
    ----------------------------------
    -- joins
    ---------------------------------- AND cal.calendar_date BETWEEN ass_part.effective_beg_date AND ass_part.effective_end_date
    <more join code>

    within the database...

    If you run the query in TOAD or SQL-Developer, does this effect happen?

    If so, what's the result of

    SELECT * FROM dual
      WHERE 1 = 1
      -- blaa
      -- blubb
      -- bubu
       AND 1=2
       AND 2=2;



  • I agree with your analysis.

    @ammoQ said:

    If you run the query in TOAD or SQL-Developer, does this effect happen?
    Now that's odd.  It doesn't flub it up in TOAD.  SQL Developer (my normal development tool) was and is flubbing it up.

    @ammoQ said:

    If so, what's the result of..
    TOAD returns one null row.  SQL Developer returns zero rows.



  •  I think TOAD always shows the null row even when there are actually no results. As long as you don't see the X, everything is ok.


Log in to reply