Re: select query in from clause creates problem
Posted by:
Rick James
Date: April 10, 2010 12:00AM
"use >= or <= instead of between clause" -- they are equivalent.
SELECT TERABITZ_ID
FROM property_ptnf
WHERE latitude BETWEEN ...
AND longitude BETWEEN between -122.118 and -122.036
-- this will scan
INDEX(longitude, latitude, TERABITZ_ID)
from longitude = -122.118 to -122.036, checking each 'row' in the index for the desired value of latitude. When it finds a match it will grab the TERABITZ_ID, again from that index. This is an "index scan". Since an index is (usually) smaller than the data, it can get through it faster. As it gets the 6K TERABITZ_ID values, it will reach for the rest of the stuff (via the JOIN). This means only 6K reaches into the data.
Without the trick, it will gather 31K rows worth of fields that you ask for, only to toss all but 6K of them.
This trick would be a subquery inside your original subquery.
Subject
Views
Written By
Posted
4542
April 05, 2010 01:42AM
1302
April 06, 2010 09:47PM
1495
April 06, 2010 11:51PM
1503
April 07, 2010 08:00AM
1556
April 08, 2010 01:04AM
1410
April 08, 2010 07:53AM
1374
April 09, 2010 12:46AM
1411
April 09, 2010 09:55AM
1243
April 09, 2010 11:29PM
Re: select query in from clause creates problem
1261
April 10, 2010 12:00AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.