1

i am sorry if i sound silly asking but i haven't been using sql hints long and i am going over some chapter review work for school. I am having trouble getting my head wrapped around them.

For instance, one question i did in oracle on a test database i had made was "Show the top 10% of the daily total number of auctions. My answer was(which worked):

SELECT DAYOFWEEK, DAILY_TOTAL
FROM (
    SELECT T.DAYOFWEEK,
      SUM(AF.TOTAL_NUM_OF_AUCTIONS) AS DAILY_TOTAL,
      CUME_DIST() OVER (ORDER BY SUM(AF.TOTAL_NUM_OF_AUCTIONS) ASC) AS Percentile
    FROM TIME_DIM T, AUCT_FACT AF
    WHERE AF.TIME_ID = T.TIME_ID
    GROUP BY T.DAYOFWEEK)
WHERE Percentile > .9
ORDER BY Percentile DESC;

The problem i have now is, it says, for me to try and achieve this output with a different query, which i asked my teacher and they said that they mean to use hints, i looked over notes i have on them and it really doesn't explain thoroughly enough how to optimise this query with hints, or to do it in a simpler manner.

Any help would really be appreciated

=) thanks guys!

Tom H
  • 46,766
  • 14
  • 87
  • 128
Miwa
  • 11
  • 1
  • Does this query work at all? The inline view has both an anlaytic function and a "group by." I don't think you could can have both. – redcayuga May 23 '11 at 19:11
  • 2
    What you say your teacher said is wrong. Hints are never supposed to change the logical results of a query (if they did, it would be a bug). – Jeffrey Kemp May 24 '11 at 05:28

1 Answers1

1

Hints are options you include in your query to direct the cost base optimizer which indexes to use. It looks like daily total is something you can implement a summary index on.

suhprano
  • 1,723
  • 1
  • 16
  • 22
  • Thank you! i appreciate your input. – Miwa May 24 '11 at 01:07
  • So are hints just added IN the SQL, they don't change the sql itself but accompany it to better define the question? – Miwa May 24 '11 at 01:08
  • 1
    Hints do not define the question better, they instruct the database *how* to get to the answer. Queries with hints are like driving a car while ignoring the GPS navigation. You would only want to do that when you *know* you can do it better than your GPS. – Martin Schapendonk May 24 '11 at 08:41