![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » DBISAM Technical Support » Product Manuals » DBISAM Version 4 Manual for RAD Studio 2010 (Delphi) » SQL Reference » Optimizations |
SELECT OrderHdr.Cust_ID, OrderHdr.Order_Num, OrderDet.Model_Num, OrderDet.Cust_Item FROM OrderHdr, OrderDet WHERE OrderHdr.Order_Num=OrderDet.Order_Num AND OrderHdr.Cust_ID='C901' ORDER BY 1,2,3
OrderHdr.Cust_ID='C901'
OrderHdr.Order_Num=OrderDet.Order_Num
SELECT OrderHdr.Cust_ID, OrderHdr.Order_Num, OrderDet.Model_Num, OrderDet.Cust_Item FROM OrderHdr INNER JOIN OrderDet ON OrderHdr.Order_Num=OrderDet.Order_Num AND OrderHdr.Cust_ID='C901' ORDER BY 1,2,3
OrderHdr.Cust_ID='C901'
OrderHdr.Order_Num=OrderDet.Order_Num
SELECT c.Company, o.OrderNo, e.LastName, p.Description, v.VendorName FROM Customer c, Orders o, Items i, Vendors v, Parts p, Employee e WHERE c.CustNo=o.CustNo AND o.OrderNo=i.OrderNo AND i.PartNo=p.PartNo AND p.VendorNo=v.VendorNo AND o.EmpNo=e.EmpNo ORDER BY e.LastName
================================================================================ SQL statement ================================================================================ SELECT c.Company, o.OrderNo, e.LastName, p.Description, v.VendorName FROM Customer c, Orders o, Items i, Vendors v, Parts p, Employee e WHERE c.CustNo=o.CustNo AND o.OrderNo=i.OrderNo AND i.PartNo=p.PartNo AND p.VendorNo=v.VendorNo AND o.EmpNo=e.EmpNo ORDER BY e.LastName Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows Result set will be ordered by the following column(s) using a case-sensitive temporary index: LastName ASC Join Ordering ------------- The driver table is the Customer table (c) The Customer table (c) is joined to the Orders table (o) with the INNER JOIN expression: c.CustNo = o.CustNo The Orders table (o) is joined to the Items table (i) with the INNER JOIN expression: o.OrderNo = i.OrderNo The Orders table (o) is joined to the Employee table (e) with the INNER JOIN expression: o.EmpNo = e.EmpNo The Items table (i) is joined to the Parts table (p) with the INNER JOIN expression: i.PartNo = p.PartNo The Parts table (p) is joined to the Vendors table (v) with the INNER JOIN expression: p.VendorNo = v.VendorNo Optimized Join Ordering ----------------------- The driver table is the Vendors table (v) The Vendors table (v) is joined to the Parts table (p) with the INNER JOIN expression: v.VendorNo = p.VendorNo The Parts table (p) is joined to the Items table (i) with the INNER JOIN expression: p.PartNo = i.PartNo The Items table (i) is joined to the Orders table (o) with the INNER JOIN expression: i.OrderNo = o.OrderNo The Orders table (o) is joined to the Customer table (c) with the INNER JOIN expression: o.CustNo = c.CustNo The Orders table (o) is joined to the Employee table (e) with the INNER JOIN expression: o.EmpNo = e.EmpNo Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: v.VendorNo = p.VendorNo is OPTIMIZED The expression: p.PartNo = i.PartNo is OPTIMIZED The expression: i.OrderNo = o.OrderNo is OPTIMIZED The expression: o.CustNo = c.CustNo is OPTIMIZED The expression: o.EmpNo = e.EmpNo is OPTIMIZED ================================================================================
Customer table Field Data Type Index --------------------------------------------------- ID Integer Primary Index Name String[30] State String[2] Secondary, case-sensitive, non-unique, ascending, index TotalOrders BCD[2]
SELECT * FROM customer WHERE (TotalOrders > 10000) and (State='CA')
Customer table Field Data Type Index --------------------------------------------------- ID Integer Primary Index Name String[30] State String[2] Secondary, case-insensitive, non-unique, ascending, index
SELECT * FROM customer WHERE (UPPER(State)='CA')
Customer table Field Data Type Index --------------------------------------------------- ID String[10] Primary, case-insensitive index Name String[30] State String[2]
Orders table Field Data Type Index --------------------------------------------------- OrderNum String[20] Primary, case-insensitive index CustID String[10] Secondary, case-insensitive index TotalAmount BCD[2]
SELECT * FROM Customer, Orders WHERE (UPPER(Customer.ID)=UPPER(Orders.CustID))
Optimized Condition = Fully-Optimized WHERE or JOIN clause Un-Optimized Condition = Un-Optimized WHERE or JOIN clause Optimized Condition AND Optimized Condition = Fully- Optimized WHERE or JOIN clause Optimized Condition AND Un-Optimized Condition = Partially- Optimized WHERE or JOIN clause Un-Optimized Condition AND Optimized Condition = Partially- Optimized WHERE or JOIN clause Un-Optimized Condition AND Un-Optimized Condition = Un- Optimized WHERE or JOIN clause Optimized Condition OR Optimized Condition = Fully- Optimized WHERE or JOIN clause Optimized Condition OR Un-Optimized Condition = Un- Optimized WHERE or JOIN clause Un-Optimized Condition OR Optimized Condition = Un- Optimized WHERE or JOIN clause Un-Optimized Condition OR Un-Optimized Condition = Un- Optimized WHERE or JOIN clause
Employee Field Data Type Index ---------------------------------------------------------------------- LastName String[30] Primary Index (both fields are part of the FirstName String[20] Primary Index primary index)
SELECT * FROM Employee WHERE (LastName='Smith') and (FirstName='John')
This web page was last updated on Thursday, November 16, 2023 at 10:39 AM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |