Skip to content

Instantly share code, notes, and snippets.

@sibnerian

sibnerian/better_query.sql

Last active August 29, 2015 14:20
Show Gist options
  • Save sibnerian/1f2eda316bbb0a913a1d to your computer and use it in GitHub Desktop.
Save sibnerian/1f2eda316bbb0a913a1d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
better_query.sql
CREATE INDEX stars2_idx ON businesses(stars2);
SELECT /*+ FIRST_ROWS(1) */
(SELECT /*+ ORDERED INDEX Businesses(stars2_idx) */ count(*) FROM Businesses WHERE stars2 >= 1 AND stars2 < 1.5) AS bucket1,
(SELECT /*+ ORDERED INDEX Businesses(stars2_idx) */ count(*) FROM Businesses WHERE stars2 >= 1.5 AND stars2 < 2) AS bucket2,
(SELECT /*+ ORDERED INDEX Businesses(stars2_idx) */ count(*) FROM Businesses WHERE stars2 >= 2 AND stars2 < 2.5) AS bucket3,
(SELECT /*+ ORDERED INDEX Businesses(stars2_idx) */ count(*) FROM Businesses WHERE stars2 >= 2.5 AND stars2 < 3) AS bucket4,
(SELECT /*+ ORDERED INDEX Businesses(stars2_idx) */ count(*) FROM Businesses WHERE stars2 >= 3 AND stars2 < 3.5) AS bucket5,
(SELECT /*+ ORDERED INDEX Businesses(stars2_idx) */ count(*) FROM Businesses WHERE stars2 >= 3.5 AND stars2 < 4) AS bucket6,
(SELECT /*+ ORDERED INDEX Businesses(stars2_idx) */ count(*) FROM Businesses WHERE stars2 >= 4 AND stars2 < 4.5) AS bucket7,
(SELECT /*+ ORDERED INDEX Businesses(stars2_idx) */ count(*) FROM Businesses WHERE stars2 >= 4.5 AND stars2 < 5) AS bucket8
FROM businesses WHERE rownum =1;
@sibnerian
Copy link
Author

Runs in 00:00:00.15

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment