Select FirstName, LastName, CountryID, Address, Phone
From Test2.Customer
Where UserID Between 1 and 5000
and CustomerID in (Select CustId from Cust_Details Where CustName like 'Mi%')
嵌套查询可能会为每一行重复。您可以检查此正在运行的 EXPLAIN PLAN + 所有 SELECT 查询。我猜想“like”运算符是针对非索引列使用的。在这种情况下（如“xyz%”），简单的索引可以显着提高性能。
Select FirstName, LastName, CountryID, Address, Phone
From Test2.Customer c, Cust_Details cd
Where c.UserID Between 1 and 5000
and c.CustomerID=cd.CustId
and left(cd.CustName) = 'Mi'
The heaviest part in your query is the SELECT:
Select FirstName, LastName, CountryID, Address, Phone
From Test2.Customer
Where UserID Between 1 and 5000
and CustomerID in (Select CustId from Cust_Details Where CustName like 'Mi%')
The nested query probably is repeated for each row. You can check this running EXPLAIN PLAN + all the SELECT query. I guess the 'like' operator is used against a non-indexed column. In this case (like 'xyz%') a simple index can improve performance a lot.
[Added: moreover, SELECT CustId ... must output id's that are greater than 5000, that aren't needed at all. A composite index (CustId, CustName) on Cust_Details must also be useful.]
Try usign a join instead:
Select FirstName, LastName, CountryID, Address, Phone
From Test2.Customer c, Cust_Details cd
Where c.UserID Between 1 and 5000
and c.CustomerID=cd.CustId
and left(cd.CustName) = 'Mi'
发布评论
评论（1）
查询中最重的部分是 SELECT：
嵌套查询可能会为每一行重复。您可以检查此正在运行的
EXPLAIN PLAN+ 所有 SELECT 查询。我猜想“like”运算符是针对非索引列使用的。在这种情况下（如“xyz%”），简单的索引可以显着提高性能。[补充：此外，SELECT CustId ... 必须输出大于 5000 的 id，但根本不需要。 Cust_Details 上的复合索引（CustId、CustName）也一定很有用。]
尝试使用连接：
The heaviest part in your query is the SELECT:
The nested query probably is repeated for each row. You can check this running
EXPLAIN PLAN+ all the SELECT query. I guess the 'like' operator is used against a non-indexed column. In this case (like 'xyz%') a simple index can improve performance a lot.[Added: moreover, SELECT CustId ... must output id's that are greater than 5000, that aren't needed at all. A composite index (CustId, CustName) on Cust_Details must also be useful.]
Try usign a join instead: