• Jaloopa (unregistered)

    And they're only returning the frist result from the query...

  • Sole Purpose Of Visit (unregistered)

    I think the phrase "Cartesian Join" gives it away. (Twice.) Occasionally useful things, but ... not very often.

  • Ollie Jones (unregistered)

    The ancient curse of the comma join lurks under the surface of the data lake waiting to trap the unwary.

  • (nodebb) in reply to Ollie Jones

    Oh yes...

    select frob, bar baz from meh, duh, gah where frob.thin = bar.gummy and bar.other = gah.shite and gargle = 'froob'

    "oh, this doesn't return any results, let's do a left join!" .. frob.thin *= bar.gummy ..

    <years later> Query fails because some unaware poor soul did an "ALTER gah add frob varchar(255)" and presto: ambiguous column!

    /there's a reason why some people regularly drink too much.

    Addendum 2021-10-21 08:04: TIL that *= also is a "Multiplication Assignment"

    (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/multiply-equals-transact-sql?view=sql-server-ver15)

    T-SQL:

    declare @frob int = 5, @honk int = 10 select @frob *= 2 select @frob -- 10

    select @honk *= @honk select @honk -- 100

    honk

  • (nodebb)

    The aliases - are they original or the author's addition?

  • (author) in reply to Mr. TA

    That was the submitter's addition, and I just went with it.

  • That guy (unregistered)

    I'm reading this while working on this morning's coffee so it's nice the have the real WTF clearly labeled inline.

  • Zygo (unregistered)

    Me: "19 percent CPU? That's not so ba--" My brain: "Peta. The SI prefix for 10^15 is a P. We're off by 13 orders of magnitude." Me: "--aaaaaaaAAAAAAAA..."

  • Zygo (unregistered) in reply to Zygo

    My coffee: "17. We're off by 17 orders of magnitude."

  • ZZartin (unregistered)

    While the execution plan used by the database shouldn't be dependent upon how we write the query

    Sure I'd love it if the database would just magically know what data I wanted without me having to write a query....

  • (nodebb) in reply to ZZartin

    Sure I'd love it if the database would just magically know what data I wanted without me having to write a query

    It's not so much about not writing a query. It's about writing a query that clearly communicates your intentions to the optimizer.

    SELECT * FROM Employees WHERE LastName LIKE 'x%'

    ... is very different from ...

    SELECT * FROM Employees WHERE SUBSTRING(LastName, 1, 1) = 'x'

    The first communicates to the optimizer that it can seek directly to the 'x' section of an index for the values (if that turns out to provide better performance). The second tells it to run a function on every LastName and then filter the results. The second gives the optimizer far less room to consider alternate plans.

  • I know whatcha mean (unregistered)

    This morning I was reading my salad greens container and it said it was washed³. I was like, so as you progressed along each leaf, you washed each other leaf, and as you progressed along those you also washed each other leaf? So.... just like this crazy query.

  • Sole Purpose Of Visit (unregistered)

    I'm not even sure that there's an excuse based upon "oh, it must be auto-generated." I mean, what is so hard about autogenerating

    SELECT what, the, f
FROM  T_91CDDC57
WHERE f IN (SELECT ' By data furtling)

    ... with optional ORDER BY and TOP and what not? I mean, seriously. I'll admit that my Mickey Mouse implementation above is terrible, but the worst you do (without suitable indexing and ordering within the database, is O(N) for the number of rows in the table. Even the stupidest automatic SQL generator can manage this.

  • (nodebb)

    Those table names might not be autogenerated. When I started working a little bit before year 2000, one car manufacturer I was working at, was using Oracle 7, but all database objects had a naming convention:

    • strictly 8 characters
    • first two characters for the type of object: TA for tables, VI for Views, ...
    • second two character were the "functional area", of such table. This was a master list somewhere that was mantained and blessed by the architects/leads/project owners/don't remember who
    • last 4 character a progressive counter (I believe it was per area if not global) that you had to ask to someone to give it to you when you wanted a new table.

    (example - simplified, off memory, just to give the idea) TAPP0023 was of course the table from the PP (= Piattaforma Progettuale = set of tools used to contain the bill of materials for the various car models) listing the car models TAPP0096 was the table for the list of parts available across the models TAPP0104 was the relation between the two above (because the request for the two tables had to be done in two different documents sent for approval, so the number would not end being contiguous).

    and of course you would be joining a lot between TAPP096 and TASP0040 (the table from the "Siti Produttivi" = production plants) listing where such parts would be produceable.

    Simple, isn't it?

  • (nodebb) in reply to molleafauss

    Worse... Old mainframe shop. Log books (paper type) for everything... Need to write a job... ot to the book, look for next blanks space, write title and sign. The number on that line was the name of you jcl job. They employed (I mean abused) two interns to just write sequences for different things into log books as they filled regularly...

  • Loren Pechtel (unregistered) in reply to iKnowItsLame

    |select frob, bar baz from meh, duh, gah where frob.thin = bar.gummy and bar.other = |gah.shite and gargle = 'froob' | |"oh, this doesn't return any results, let's do a left join!" .. frob.thin *= bar.gummy .. |<years later> Query fails because some unaware poor soul did an "ALTER gah add frob |varchar(255)" and presto: ambiguous column! | |/there's a reason why some people regularly drink too much.

    While I agree that in multi-table queries you should identify where each field comes from for future safety that's not what's happening here. This abomination specifies the same table to be queried three times. I'm surprised at the result, though--I would have thought the worst case here was just doing three times the work.

  • Rob (unregistered)

    I'm surprised nobody noticed that this query is probably returning an incorrect result. While the intent is probably to get the minimum moddate where the rdate (whatever that is) is larger than sysdate-1095 (what kind of random number is that?), that where clause is not applied to the table that produces the minimum moddate. The end result therefore is the minimum moddate of any record in this table.

  • (nodebb)

    Pretty sure this is how Paula would do it -- it's so Brilliant!

  • Daniel Godson (unregistered)

    Did anyone else notice that the 'From' clause embeds the phrase 'What the f'?

    It was the first thing I saw, not being a dba!

  • PokestarFan (unregistered) in reply to Ollie Jones

    Honestly who thought that a comma should mean full join? Please join when I ask for a join!

  • Erk (unregistered)

    select Min(the.moddate) "ModifiedDate" From T_91CDDC57 revenge , T_91CDDC57 [is] , T_91CDDC57 mineth where mineth.rdate > sysdate-1095;

    Right?

  • Erk (unregistered)

    select Min(the.moddate) "ModifiedDate" From T_91CDDC57 revenge , T_91CDDC57 [is] , T_91CDDC57 mineth where mineth.rdate > sysdate-1095;

    Right?

  • BOFH (unregistered)

    I've seen Oracle crap itself with bad query plan. We have a table with 20m or so rows. Usually query plan uses index and has to work with less than 100 rows. On some star alignment Oracle decides to go out of its way and somehow make a plan that works on 24P rows. Fun times.

Leave a comment on “Performance Tuning for Exabyte Queries”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article