• John Smallberries (cs)

    <font size="2">they should just ban all code.
    obviously, it's evil.</font>

  • limelight (cs)

      This strikes me as code that was intentionally written to be difficult to understand, whether for fun/amusement or to show off the authors 1337ness.

  • Dave (unregistered)

    Don't ban all the code... just ban features that make our jobs easier :)

  • Mike R (cs)

    Hey, wasn't this discussed in a a previous post?

  • John Smallberries (cs) in reply to Dave

    <font size="2">

    Anonymous:
    Don't ban all the code... just ban features that make our jobs easier :)


    Doesn't code make your job easier?
    Try writing software without it.</font>
  • Mike R (cs) in reply to John Smallberries
    John Smallberries:
    <font size="2">
    Anonymous:
    Don't ban all the code... just ban features that make our jobs easier :)

    Doesn't code make your job easier?
    Try writing software without it.</font>


    Thats it, make it a requirement that every piece of software a programmer writes be in machine code with no use of 3rd party libaries. (Some OS calls are permitted...)

  • Alex Papadimoulis (cs) in reply to Mike R

    Mike R:
    Hey, wasn't this discussed in a a previous post?

    That discussion prompted me to post this one :-). It kinda ... sorta makes sense why they used ACOS(-1) ... I'll break down a predicate:

    NVL(BCTRY_ID, TO_CHAR(ACOS(-1))) = DECODE(MOD(FLOOR(VLOOPER /4), 2), 0, VCTRY_ID, TO_CHAR(ACOS(-1)))

    If IsNull(BCTRY_ID) Then
      LeftOperand = PI
    Else
      LeftOperand = BCTRY_ID

    If Floor(VLOOPER/2) Mod 2 = 0 Then
      RightOperand = 0
    Else
      RightOperand = PI

    What still gets me is WTF is with the Floor/Mod nonsense. Any ideas?

  • Dave (unregistered) in reply to John Smallberries

    For some people, giving them the job of writing software makes my job much harder :(

  • Mike R (cs) in reply to Alex Papadimoulis
    Alex Papadimoulis:

    Mike R:
    Hey, wasn't this discussed in a a previous post?

    That discussion prompted me to post this one :-). It kinda ... sorta makes sense why they used ACOS(-1) ... I'll break down a predicate:

    NVL(BCTRY_ID, TO_CHAR(ACOS(-1))) = DECODE(MOD(FLOOR(VLOOPER /4), 2), 0, VCTRY_ID, TO_CHAR(ACOS(-1)))

    If IsNull(BCTRY_ID) Then
      LeftOperand = PI
    Else
      LeftOperand = BCTRY_ID

    If Floor(VLOOPER/2) Mod 2 = 0 Then
      RightOperand = 0
    Else
      RightOperand = PI

    What still gets me is WTF is with the Floor/Mod nonsense. Any ideas?



    I guessed. I was surprised to see this actually used in practice rather than an acedemic exercise at code obfuscation.
    My guess the floor/mod stuff is the same as saying "Every fourth iteration". I'm PL/SQL ignorant (I've never had the pleasure of using Oracle), but what does NVL do, and why use PI?


  • Disgruntled DBA (unregistered) in reply to Dave

    That has a name around here.  "Doing Negative Work".

  • Disgruntled DBA (unregistered)

    NVL (value1, value2) is like isnull in SQL Server.

    if value1 is null then
      return value2
    else
      return value1
    end

    This procedure needs an isbull () function.

  • Mike R (cs) in reply to Mike R
    Mike R:

    My guess the floor/mod stuff is the same as saying "Every fourth iteration".


    Or rather creates a pattern like TTFFTTFF [:$]
  • Maurits (cs)

    I've never used ACOS, but I have used ASIN in T-SQL:
    CREATE FUNCTION DistanceBetween
    -- given the latitude and longitude of two points on the surface of the earth
    -- this returns the distance in miles between them as the crow flies
    -- The first point is (@lata, @longa)
    -- The second point is (@latb, @longb)
    (
        @lata float,
        @longa float,
        @latb float,
        @longb float
    )
    RETURNS float
    AS
    BEGIN

    RETURN
    (    (
            6367 *
            0.6214
        ) *
        (
            2 *
            Asin(
                Sqrt(
                    (
                        (
                            1 -
                            Cos(
                                2 *
                                (
                                    (
                                        (
                                            0.017453293 *
                                            @lata
                                        ) -
                                        (
                                            0.017453293 *
                                            @latb
                                        )
                                    ) /
                                    2
                                )
                            )
                        ) /
                        2 +
                        Cos(
                            0.017453293 *
                            @latb
                        ) *
                        Cos(
                            0.017453293 *
                            @lata
                        ) *
                        (
                            1 -
                            Cos(
                                2 *
                                (
                                    (
                                        (
                                            0.017453293 *
                                            @longa
                                        ) -
                                        (
                                            0.017453293 *
                                            @longb
                                        )
                                    ) /
                                    2
                                )
                            )
                        ) /
                        2
                    )
                )
            )
        )
    )
    END





  • Otto (cs) in reply to Alex Papadimoulis

    Alex Papadimoulis:
    What still gets me is WTF is with the Floor/Mod nonsense. Any ideas?

    That's what we used to call "smart-assery".

    vlooper is a loop variable going from 0 to 7.

    MOD(FLOOR(VLOOPER /4), 2), therefore, will produce 0,0,0,0,1,1,1,1, in that order.

    MOD(FLOOR(VLOOPER /2), 2) will produce 0,0,1,1,0,0,1,1.

    And MOD(FLOOR(VLOOPER), 2) will produce 0,1,0,1,0,1,0,1.

    I can't comment further, because I don't know VB and thus don't have any idea what DECODE() will do.

     

  • Matt B (cs)

    Can someone summarize this for those of us unfamiliar with PL/SQL?

  • Otto (cs) in reply to Otto

    Why did I type VB? I meant, I don't know what DECODE does in SQL. Sorry. :)

     

  • Otto (cs) in reply to Matt B

    Matt B:
    Can someone summarize this for those of us unfamiliar with PL/SQL?

    It's essentially going through and for three columns in a database (which appear to be flags), trying all combinations of those three columns until it finds one that exists. To do this, he uses some rather obfuscated math tricks.

    The fact that this could have been done with a single select and not specifying the values in those three columns seems to have escaped the original programmer.

     

  • Anonymouse Cowherd (unregistered) in reply to Maurits

    Maurits:
    I've never used ACOS, but I have used ASIN in T-SQL:
    CREATE FUNCTION DistanceBetween
    -- given the latitude and longitude of two points on the surface of the earth
    -- this returns the distance in miles between them as the crow flies
    -- The first point is (@lata, @longa)
    -- The second point is (@latb, @longb)
    (
        @lata float,
        @longa float,
        @latb float,
        @longb float
    )
    RETURNS float
    AS
    BEGIN

    RETURN
    (    (
            6367 *
            0.6214
        ) *
        (
            2 *
            Asin(
                Sqrt(
                    (
                        (
                            1 -
                            Cos(
                                2 *
                                (
                                    (
                                        (
                                            0.017453293 *
                                            @lata
                                        ) -
               ...Massive Snippage

     

    How is that even remotely readable?

    And your radius of the earth is wrong, it's 6378 km

  • Otto (cs) in reply to Anonymouse Cowherd
    Anonymous:
    How is that even remotely readable?

    And your radius of the earth is wrong, it's 6378 km

    See, just one more reason not to have constants in your code. What if the radius of the earth changes in the future? Clearly he needs a table to hold the radius of various planets, so that he can pull this information from them.

  • ammoQ (cs) in reply to Matt B
    Matt B:
    Can someone summarize this for those of us unfamiliar with PL/SQL?


    you have 3 fields in the database (bctry_id, bbusu_id, bextsys_id) and three search  parameters (vctry_id, vbusu_id, vextsys_id) for these fields and want to find the best match.
    (There is a bug in the procedure,  vctry_id is compared twice and vbusu_id never)

    One or more of the database fields can be NULL, meaning that the rule is more general.
    So first it checks for a match on all search parameters, then for a rule with two matches and one NULL. The decode(mod(floor(.... magic is for deciding if you look for a match at the corresponding field or for NULL. ACOS(-1) is a arbitrary replacement for NULL, so you can compare it (NULL=NULL doesn't work). Interestingly, the procedure does not determine the best rule, only if it exists, so

    select count(*) into vcnt from b2r_business_fule_usage
      where bbusrl_nm = vbus_rule
         and (bctry_id = vctry_id or bctry_id is null)
         and (bbusu_id = vbusu_id or bbusu_id is null)
         and (bextsys_id = vextsys_id or bextsys_id is null);

    would probably do the job just as well (the count result might differ since this statement checks all 8 cases at once).

  • Daniel T (unregistered) in reply to Otto

    DECODE is like a switch-case: the first argument is the value to check, after that each pair of values consists of a value to compare, and a value to return if they match, and the last argument is the default value to return.

    I think I figured out how this thing works (sort of)...

    NVL(BEXTSYS_ID, ACOS(-1)) = DECODE(MOD(VLOOPER, 2), 0, VEXTSYS_ID, ACOS(-1));
    The NVL statement returns BEXTSYS_ID if it's there, or pi if it's not.  The DECODE is used here like a ?: operator - it returns VEXTSYS_ID if the loop is at a certain point (every other iteration in this case), or pi otherwise).

    So it is alternating checking to see if BEXTSYS_ID is VEXTSYS_ID or null (i.e. pi) for each iteration.  Since there are 3 comparisons like this, it is doing this for 3 fields, with different loop iteration checkers (MOD 2, MOD 4...).

    This guy definitely had too much time on his hands, that's for sure.

    --Daniel T

  • Oliver Klozoff (cs)

    Frighteningly enough, I actually understand what this code is doing.  And the comments are accurate, if not quite detailed enough.

    Here is a slightly tweaked code to make it easier on T-SQL minds:

    <FONT face="Courier New" size=2>FUNCTION BUS_RULE_FL
      (VCTRY_ID IN varchar2, VBUSU_ID IN varchar2,
       VEXTSYS_ID IN number, VBUS_RULE IN varchar2)
    RETURNS NUMBER AS
    -- return TRUE (1) or FALSE (0) whether the supplied business rule
    -- is selected for the supplied values of CTRY, LNG, EXTSYS
      VLOOPER NUMBER := 0;
      VCNT NUMBER := 0;
    BEGIN
      -- this loop will try all combinations of matching ctry/busu/extsys columns to either
      -- supplied values or null until a match is found, or all combinations exhausted
      WHILE VLOOPER < 8 AND VCNT = 0 LOOP
        SELECT COUNT(*)
          INTO VCNT
          FROM B2R_BUSINESS_FULE_USAGE
         WHERE BBUSRL_NM = VBUS_RULE
           AND isnull(BCTRY_ID, TO_CHAR(ACOS(-1)))
            = (floor(vlooper / 4) % 2 == 0) ? vctry_id : to_char(PI)
    --        vlooper = 0, 1, 2, 3   
                = DECODE(MOD(FLOOR(VLOOPER /4), 2), 0, VCTRY_ID, TO_CHAR(ACOS(-1)))
           AND isnull(BBUSU_ID, TO_CHAR(ACOS(-1)))
                = DECODE(MOD(FLOOR(VLOOPER /2), 2), 0, VCTRY_ID, TO_CHAR(ACOS(-1)))
    --        vlooper = 0, 1,       4, 5
           AND isnull(BEXTSYS_ID, ACOS(-1))
                = DECODE(MOD(VLOOPER, 2), 0, VEXTSYS_ID, ACOS(-1));
        --    vlooper = 0,    2,    4,   6

       VLOOPER := VLOOPER + 1;
      END LOOP;</FONT>


    WTF #1: ACOS(-1).

    Basically, the purpose of ACOS(-1) was to pick a value (pi) that could never be a valid country/business unit/external system ID, and replace NULLs with that value.  The reason for this is obvious to experienced SQL users: under standard SQL semantics, (NULL=NULL) is false, but (ACOS(-1)=ACOS(-1)) is true.

    Personally, I'd have just used -1 or 'invalid' or something like that, but hey.

    WTF #2: the MOD(VLOOPER) crap

    This is a bit more involved.  The mapping goes like this:
    country ID: iterations 0, 1, 2, 3
    business unit ID: iterations 0, 1, 4, 5
    external system ID: iterations 0, 2, 4, 6

    There are eight iterations, 0 through 7.

    If a particular iteration is in the above list for an item, that item is checked exactly; otherwise, a passed NULL matches anything.  This probably won't make much sense without a detailed example to go with it.

    Iteration 0, the first attempt, will try matching all three inputs to their respective columns on the table.
    If there is an exact match, this code is very efficient and returns the number of exact rules (which I expect would be 1) immediately.

    On the next iteration, country ID and business unit ID are matched exactly against the parameters, but, if the function is passed NULL for external system ID, that will still match a non-NULL value in that column (because 1 is not in external system ID's list above). This means that
    BUS_RULE_FL('country1', 'unit1', NULL, 'ruleX') will return the number of rules defined for country1 on unit1, regardless of the external system ID they are set for.

    Iteration 2: Country ID and external system ID are the only two with 2 in them. This is very similar to iteration 1, except that
    BUS_RULE_FL('country1', NULL, 'extsys1', 'ruleX')  will return the number of rules defined for country1 on extsys1, regardless of the business unit they are set for.

    Iteration 3: Only Country ID matches this one. This provides the functionality that
    BUS_RULE_FL('country1', NULL, NULL, 'ruleX') will return the number of rules for country1 in any business unit and any external system.

    Iteration 4: Provides BUS_RULE_FL(NULL, 'unit1', 'extsys1', 'ruleX') to count the number of countries for which unit1 and extsys1 apply to ruleX.

    Iteration 5: Provides BUS_RULE_FL(NULL, 'unit1', NULL, 'ruleX'). I think you understand what it counts.

    Iteration 6: Provides BUS_RULE_FL(NULL, NULL, 'extsys1', 'ruleX').

    Iteration 7: Provides BUS_RULE_FL(NULL, NULL, NULL, 'ruleX'), a catch-all that matches any country, any business unit, and any external system for ruleX.

    Phew! catches breath.

    I must say, this is actually a rather clever piece of code. It is deftly handling the REAL wtf, which is the fact that NULL is being used to mean two different things; one meaning is "match an actual NULL in the corresponding column", while the other is "match anything".  It reminds me of a screwup where we sent out about 5000 devices that were accidentally printed with the wrong serial numbers -- serial numbers that overlapped existing IDs in our system.  The fix for that was a huge WTF, too.

  • Oliver Klozoff (cs) in reply to Oliver Klozoff

    and WHAT THE FSCKING HELL IS WRONG WITH THIS FORUM SOFTWARE?

    ED: I fixed your post. You were going and doing all fancy stuff like marking it up with [code] blocks. It doesnt like that.

  • GalacticCmdr (cs) in reply to Maurits

    When you wrote this code were you?

    A) Drunk
    B) On Crack
    C) Both A and B
    D) Buggering the nearest LISP book

    Maurits:
    I've never used ACOS, but I have used ASIN in T-SQL:
    CREATE FUNCTION DistanceBetween
    -- given the latitude and longitude of two points on the surface of the earth
    -- this returns the distance in miles between them as the crow flies
    -- The first point is (@lata, @longa)
    -- The second point is (@latb, @longb)
    (
        @lata float,
        @longa float,
        @latb float,
        @longb float
    )
    RETURNS float
    AS
    BEGIN

    RETURN
    (    (
            6367 *
            0.6214
        ) *
        (
            2 *

    ... snip Pabst drinking/Crack-smoking code ...

       )
        )
    )
    END
  • Daniel T (unregistered) in reply to ammoQ

    You beat me, ammoQ...

  • rhett (unregistered) in reply to Anonymouse Cowherd
    Anonymous:

    How is that even remotely readable?

    And your radius of the earth is wrong, it's 6378 km

    The earth is an ellipsoid, not a sphere.  Therefore the radius varies depending on your latitude.  To compensate for this, you need to use Vincenty's Inverse Geodesic formula, rather than the Great Circle formula quoted.

    So you are both wrong, by up to about 1%.

  • ammoQ (cs) in reply to Daniel T
    Anonymous:
    You beat me, ammoQ...


    *g* better luck next time (maybe I have an unfair advantage because here in Austria it's already 22:23, so I'm not interrupted by real work)
  • travisowens (cs) in reply to rhett

    I could point out the concept that many people feel it's bad to do complex math within your database server as your coding language of choice will always perform it faster.  This is even more important when you look at the past where the database server is far more overloaded than your app server, but now that I/O has improved somewhat, this isn't as much of an issue as it use to be.

  • ammoQ (cs) in reply to travisowens
    travisowens:

    I could point out the concept that many people feel it's bad to do complex math within your database server as your coding language of choice will always perform it faster.  This is even more important when you look at the past where the database server is far more overloaded than your app server, but now that I/O has improved somewhat, this isn't as much of an issue as it use to be.



    I see no reason why Java or C# or C++ on an app server would calculate ACOS faster (or slower) than the database (unless the Oracle guys wrote the ACOS alorithm in PL/SQL themself)
  • AtomicTesting (cs) in reply to rhett
    Anonymous:
    Anonymous:

    How is that even remotely readable?

    And your radius of the earth is wrong, it's 6378 km

    The earth is an ellipsoid, not a sphere.  Therefore the radius varies depending on your latitude.  To compensate for this, you need to use Vincenty's Inverse Geodesic formula, rather than the Great Circle formula quoted.

    So you are both wrong, by up to about 1%.

    You're all wrong.  The radius varies even more than that because of differences in altitude.  The top of Mt. Everest (8,850 m above local sea level) vs. the bottom of the Mariana trench  (10,911 m below local sea level) is significantly different points on the outside of the "oblate spheroid" that the earth really is.  Now, granted, I'm referring to the fact that earth itself refers to the masses of land/soil and not inclusive of the ocean specifically.  Had you intended it to refer to the planet, it would have been capitalized "Earth."

    (data taken from Wikipedia http://en.wikipedia.org/wiki/Earth)

  • Volmarias (cs) in reply to ammoQ

    Oh dear.

    I tried to read this code. I really did. But halfway through my overflow register keeps getting ticked and I fail.

  • Maurits (cs) in reply to Volmarias

    Actually I stole / inherited that code.  But I have run it, and it works well enough for my purposes.

    The worst thing about it IMHO is that it multiplies something by two, and then immediately divides it by two again.

  • Maurits (cs) in reply to Maurits

    After some spur-of-the-moment research the 6367 number is defended by Wikipedia:
    http://en.wikipedia.org/wiki/Great_circle_distance

    "The shape of the Earth more closely resembles a flattened spheroid with extreme values for the radius of curvature of 6336 km at the equator and 6399 km at the poles. Using a sphere with a radius of 6367 km results in an error of up to about 0.5%."

  • CornedBee (cs) in reply to Volmarias

    I completely failed to understand this code. I didn't even really try.

    Where in Vienna, ammoQ?

  • qu1j0t3 (cs) in reply to travisowens
    travisowens:
    many people feel it's bad to do complex math within your database server as your coding language of choice will always perform it faster.  ... now that I/O has improved somewhat, this isn't as much of an issue as it use to be.

    I think that is a little beside the point (in fine dwtf tradition). One of the most valuable take-home messages of The Elements of Programming Style is "say what you mean". This is db drudgework, not geometry... I would have left a copy of TEPS in my co-worker's Christmas stocking if I'd discovered this in his oeuvre. And congratulations to Maurits for outdoing a wtf in its own thread.
  • JR (unregistered) in reply to CornedBee

    Ah...  yea, right.

    Having seen some nasty PL/SQL over the years, I'm not surprised that someone tried junk like that.  Now the arc cosine stuff is just stupid.

    Now I have to ask on the great circle stuff.  Why would this be in the database?  I mean why would someone have the database do your application's math.

  • Tim Howland (unregistered) in reply to JR

    so I can select all the stores within a 5 mile radius of a given zip code in a store locator system...

    The alternative is to get all of the stores in the state, and in the surrounding states, and then sorting them in the application.

  • christoofar (unregistered) in reply to JR

    I don't see why you shouldn't do a lot of math in the database, particularly in the case of aggregation and compiling statistics, where it shines.

    However, this WTF does not support that reason.

  • Concerned Bystander (unregistered) in reply to travisowens

    Travis, I've had some misgivings about this for a while but have only brought myself to write about it today. Your avatar, when not studied carefully, appears to be a picture of you with an oversized green dildo in your mouth. I'm not sure if it's deliberate but I think your avatar is meant to say "I like giant cocktails" but appears to say "I like giant cock"

  • Concerneder Bystander (unregistered) in reply to Concerned Bystander

    CB: Perhaps you are just projecting.

  • dreifus (cs) in reply to Oliver Klozoff
    Oliver Klozoff:
    Frighteningly enough, I actually understand what this code is doing.  And the comments are accurate, if not quite detailed enough.
    ...


    Y
  • dreifus (cs) in reply to dreifus
    dreifus:
    Oliver Klozoff:
    Frighteningly enough, I actually understand what this code is doing.  And the comments are accurate, if not quite detailed enough.
    ...


    Y


    That's meant to be "thumb up" emoticon.
    Excellent analysis, sir.
  • Foon (cs)

    Dear god.

    It is looking for rows in the datbase where VCTRY_ID is matched by BCTRY_ID, BBUSU_ID, BEXTSYS_ID and counting them. The acos(-1) is used to turn nulls into something that will work with a string equals. This code loops through the permutations of a three-bit number. 000, 001, 010, 011, 100 etc to handle the possible combinations of null/non-null values. But a simple nvl(the column, acos(1)) on each of the three columns will achieve the same thing.

    Of course, the basic conceptual problem is that he is treating NULL as some particular value, when it is actually meant to mean the absence of a value

  • Foon (cs) in reply to Foon

    While I am at it: say a table has 3 key columns: A, B, C. You want to index the table such that a search on any combination of A, B and C can use an index. How many indexes do you need?

    3 indexes will do: ABC, BC, CA

    What's the general rule?

  • Arachnid (unregistered) in reply to Foon
    Foon:

    While I am at it: say a table has 3 key columns: A, B, C. You want to index the table such that a search on any combination of A, B and C can use an index. How many indexes do you need?

    3 indexes will do: ABC, BC, CA

    What's the general rule?



    What? What if you want to search on A and B? Or just A?

    Why not just have indexes A, B and C?
  • Foon (cs) in reply to Maurits

    <FONT face="Courier New">-- given the latitude and longitude of two points on the surface of the earth
    -- this returns the distance in miles between them as the crow flies
    -- The first point is (@lata, @longa)
    -- The second point is (@latb, @longb)</FONT>

    To do this, convert the two points into unit vectors on the unit sphere, take their dot-product, take the acos of that, and multiply it by the radius of the earth. This uses the fact that the dot product of two unit vectors is equal to the cosine of the angle between them. I'll pretend that the trig functions use degrees.

    return radus_of_earth * 2pi/360 * acos (
       sin(lata) * sin(latb) /* y coordinate */
      + cos(lata)*cos(lona) * cos(latb)*cos(lonb) /* z coordinate */
      + cos(lata)*sin(lona) * cos(latb)*sin(lonb) /* x coordinate */
    

    );

  • Foon (cs) in reply to Arachnid

    We use the fact that (in oracle sql, anyway), indexes are sorted in the order that the columns in the index appear. In index serch simply involves doing a binary chop to find the start and end of the range you are interested int. So if you want to find all "a=3", then the index "a, b, c" can be used. In fact, "a, b, c" can be used to directly look up a, ab, and abc. If you want to find "a=3 AND c=2", a separate A and B index will not do the job on its own - you'd have to do a merge. And a "A, B, C" index is also no good. But any index with A and B in the first and second columns (in either order) is fine.

    With our simple example, the indexed used for the various searches are

    WHERE A = 1 AND B = 1 AND C = 1 : use index "A, B, C"

    WHERE A = 1 AND B = 1 : use index "A, B, C"

    WHERE A = 1 AND C = 1 : use index "C, A"

    WHERE A = 1 : use index "A, B, C"

    WHERE B = 1 AND C = 1 : use index "B, C"

    WHERE B = 1 : use index "B, C"

    WHERE C = 1 : use index "C, A"

    There is a straightforwqard rule for how many indexes you need for N columns, but I'm not sure about the algorithm to generate them.

  • emurphy (cs) in reply to Arachnid
    Anonymous:
    Foon:

    While I am at it: say a table has 3 key columns: A, B, C. You want to index the table such that a search on any combination of A, B and C can use an index. How many indexes do you need?

    3 indexes will do: ABC, BC, CA

    What's the general rule?



    What? What if you want to search on A and B? Or just A?

    Why not just have indexes A, B and C?


    You're thinking of an OLAP cube, right?  But never mind that, let's look at Foon's question as a math riddle.

    For N keys, you first need to cover (all keys but #1) through (all keys but #N), so that's N indexes.  To avoid duplication of subsets, order these as
    (#2 through #N)
    (#3 through #N + #1)
    (#4 through #N + #1 through #2)
    ...
    (#N-1 through #N + #1 through #N-3)
    (#N + #1 through #N-2)
    (#1 through #N-1)

    Then append #1 to the first one, to cover the (all keys) case.

    For 4 keys, this gives us BCDA, CDA, DAB, ABC.  We also need to cover AC and BD.

    Hypothesis:  For N keys, max (K=1 to N) C(N,K) indexes is sufficient, where C(N,K) is the standard "number of unordered subsets of K elements out of an N-element set" combination function.

    Exercise:  Prove or disprove this hypothesis.

  • Arachnid (unregistered) in reply to Foon
    Foon:

    We use the fact that (in oracle sql, anyway), indexes are sorted in the order that the columns in the index appear. In index serch simply involves doing a binary chop to find the start and end of the range you are interested int. So if you want to find all "a=3", then the index "a, b, c" can be used. In fact, "a, b, c" can be used to directly look up a, ab, and abc. If you want to find "a=3 AND c=2", a separate A and B index will not do the job on its own - you'd have to do a merge. And a "A, B, C" index is also no good. But any index with A and B in the first and second columns (in either order) is fine.


    Seperate A, B and C indexes will still work - the DB simply needs to do a union on the results from each index. Regardless, your original question was what was required to use "an index", which that fills just fine, even if the DB only uses an index for one column.
  • technites (cs)
    Alex Papadimoulis:

    FROM B2R_BUSINESS_FULE_USAGE

    I pity the business fule!

Leave a comment on “Relational Trigonometry”

Log In or post as a guest

Replying to comment #:

« Return to Article