问题描述
我有一张约有1亿行的表格.必须使用BETWEEN运算符.我看到查询运行非常缓慢.
I have a table with about 100million rows. Have to use BETWEEN operator. I see that query is running very slow.
我在交易者表上添加了2个索引，第一个是t.name，第二个索引是t.amount和t.price.
I added 2 indexes on trader table one on t.name and second index is t.amount and t.price.
查询执行速度非常慢.使用BETWEEN运算符时，对价格和金额进行索引是否有帮助?另外，我该如何优化呢?
Query is performing very slow. Does indexing on price and amount help when using BETWEEN operator. Also, how can I optimize this?
select t.trader_id, t.name, t.city, t.state
from trader t where exists
(
SELECT null
FROM city_present p,city_state c
WHERE p.name = 'TEST_TEST'
AND c.city = p.city
AND c.state = p.state
AND t.city = c.city
AND t.state = c.state
AND t.price IS NOT NULL
AND t.price between (25.00000 , 58.000000)
AND t.amount IS NOT NULL
AND t.amount BETWEEN (-2500.0000 , 2800.000)
)
AND t.name = 'john test'
AND t.is_valid= 1
推荐答案
选择性是优化性能的关键-在city_present所在的城市/州中，您可能很少使用is_valid = 1的称为"john test"的交易者.name是"TEST_TEST"，价格在25到58之间，金额在-2500到2800之间.
Selectivity is the key to performance in optimisation - you probably have vastly fewer traders called 'john test', with is_valid = 1, in cities/states where the city_present.name is 'TEST_TEST', than have price between 25 and 58 and amount between -2500 and 2800.
因此，我建议在交易者表上按名称，城市，州和is_valid(按该顺序)设置索引，然后尝试以下查询:
Therefore, I recommend setting up an index on the trader table, on name, city, state and is_valid (in that order), and then try the following query:
SELECT t.trader_id, t.name, t.city, t.state
FROM trader t
JOIN (select distinct p.city, p.state
from city_present p
JOIN city_state s
ON s.city = p.city AND s.state = p.state
WHERE p.name = 'TEST_TEST') c
ON t.city = c.city AND t.state = c.state
WHERE t.price between (25.00000 , 58.000000)
AND t.amount BETWEEN (-2500.0000 , 2800.000)
AND t.name = 'john test'
AND t.is_valid= 1
这篇关于使用BETWEEN运算符的Oracle查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！