Why change the ordering of the tables in the FROM clause makes the SQL execution time different?

Question

Edit: I don't know why the hate for this question but maybe its because of the confusion about my question. I purposely used /*+ ORDERED */ to control the order of execution and changes the ordering of the tables in the FROM clause. I was wondering WHY the execution time can change. Is it because of the join order? is it because of the table size? Hope this clears out the confusion.

So I was just playing around SQL queries and realized the following: If I change the ordering of tables in the FROM clause, the execution time can be very different. The following query runs in about 0.966 sec. But if I move OrderDetails d to the last of the FROM clause, the execution is only 0.573 sec! Any reason behind this? I was using ORACLE SQL Developer

SELECT /*+ ORDERED */
    su.CompanyName, CategoryName, ProductName, c.CompanyName, c.country,
    FirstName, LastName, Quantity, d.UnitPrice, sh.CompanyName
FROM 
    OrderDetails d, Suppliers su, Shippers sh, Categories t, Products p,
    Employees e, Customers c, orders o
WHERE 
    t.CategoryID = p.CategoryID 
    AND c.CustomerID = o.CustomerID 
    AND e.EmployeeID = o.EmployeeID 
    AND o.OrderID = d.OrderID 
    AND p.ProductID = d.ProductID 
    AND sh.ShipperID = o.ShipVia 
    AND su.SupplierID = p.SupplierID 
    AND LOWER(ProductName) Like '%lager%' 
    AND LOWER(c.city) IN ('vancouver', 'london', 'charleroi', 'cunewalde') 
    AND d.Quantity BETWEEN 5 AND 100 
    AND (RequiredDate-ShippedDate > 10)
ORDER BY 
    c.CompanyName;

Answer 1

Uh, you are specifying the ordered hint. As described in the documentation:

The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.

Usually, the Oracle (or any other optimizer) finds an optimal ordering for the joins, so the ordering in the from clause does not matter. But with the ordered hint, you are specifying the order of the joins. Hence, changing the order of tables in the from clause can have a big impact on execution.

By the way, you should learn to use modern, explicit join syntax.