SELECT SUM(C_QUANTITY)
FROM CARS JOIN ORDERS
ON C_ORDERKEY = O_ORDERKEY;
I have this query that aggregate sum of L_QUANTITY from the JOIN tables. The query cost, by using EXPLAIN PLAN
is 12147
. The objective is to improve this SELECT
statement by implementing a more efficient SELECT
statement that will get the same result.
I have tried
SELECT SUM(C_QUANTITY)
FROM CARS
It returned the same result but the query cost is exactly the same as the original. I thought that by removing the JOIN
, the SELECT
query will improve.
Is there a way to reduce the cost by simply modify the SELECT
statement only?
Edit:
Original query plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2287326370
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 12147 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| CARS | 1800K| 5273K| 12147 (1)| 00:00:01 |
-------------------------------------------------------------------------------
9 rows selected.
With the second query
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2287326370
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 12147 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| CARS | 1800K| 5273K| 12147 (1)| 00:00:01 |
-------------------------------------------------------------------------------
9 rows selected.
I suggest adding the following index:
CREATE INDEX idx ON ORDERS (O_ORDERKEY, C_QUANTITY);
Presumably, the ORDERS
table would be much larger than CARS
. If so, Oracle would likely satisfy the query by scanning CARS
and then would be able to use the above index to lookup in the ORDERS
table. I add the C_QUANTITY
column to the end of the index, to cover the summation in the select clause.