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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments