Select ONLY those columns in a query which are required - - Oracle Performance Tuning

 

Select ONLY those columns in a query which are required. Extra columns which are not actually used, incur more I/O on the database and increase network traffic.

Main Concept

Bad practice:

SELECT * FROM emp;

Good practice:

SELECT empno, ename FROM emp;

Why?

Because Oracle reads:

• more data blocks
• more memory
• more network traffic
• more CPU

when unnecessary columns are selected.

---

EMP Table Example

Assume EMP table:

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-DEC-80	800	NULL	20
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30
7521	WARD	SALESMAN	7698	22-FEB-81	1250	500	30

---

Scenario 1 — Bad Query Using SELECT *

Suppose application screen only displays:

Employee Number
Employee Name

But developer writes:

SELECT * FROM emp WHERE deptno = 30;

---

What Oracle Does Internally

Oracle reads ALL columns:

EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO

Even though application only needs:

EMPNO
ENAME

---

Layman Example

Imagine:

You go to library and ask:

"I only need 2 pages."

But librarian gives:

• entire 1000-page book.

You carry:

• extra weight
• extra paper
• extra time

That is exactly what SELECT * does.

---

Explain Plan for SELECT *

EXPLAIN PLAN FOR
SELECT *
FROM emp
WHERE deptno = 30;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan:

----------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |    3 |   300 |    3 |
|  1 |  TABLE ACCESS FULL| EMP  |    3 |   300 |    3 |
----------------------------------------------------------------------------

Notice:

Bytes = 300

Oracle estimates more data movement.

---

Scenario 2 — Good Query

SELECT empno, ename FROM emp WHERE deptno = 30;

---

Explain Plan

EXPLAIN PLAN FOR
SELECT empno, ename
FROM emp
WHERE deptno = 30;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan:

----------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |    3 |    39 |    3 |
|  1 |  TABLE ACCESS FULL| EMP  |    3 |    39 |    3 |
----------------------------------------------------------------------------

Now notice:

Bytes = 39

instead of:

Bytes = 300

Huge reduction.

---

Why This Matters in Real Production

Imagine:

Table Size	Rows
EMP	14
Real Production Table	500 million

Now suppose each row contains:

• CLOB
• XML
• JSON
• large VARCHAR2 columns

Using:

SELECT *

can create:

• massive I/O
• network bottleneck
• slow application
• TEMP usage
• high CPU

---

Scenario 3 — Index Usage Difference

Suppose index exists:

CREATE INDEX emp_idx1 ON emp(deptno, empno, ename);

---

Good Query

SELECT empno, ename FROM emp WHERE deptno = 30;

Oracle can use:

INDEX FAST FULL SCAN

or

INDEX RANGE SCAN

WITHOUT accessing table.

This is called:

Covering Index

---

Explain Plan

--------------------------------------------------------------------------------
| Id | Operation            | Name     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |          |
|  1 |  INDEX RANGE SCAN    | EMP_IDX1 |
--------------------------------------------------------------------------------

Very fast.

---

But SELECT * Changes Everything

Now query:

SELECT * FROM emp WHERE deptno = 30;

Problem:

Index does NOT contain:

• JOB
• SAL
• HIREDATE
• COMM

So Oracle must:

1. Read index
2. Go back to table

This causes:

TABLE ACCESS BY INDEX ROWID

---

Explain Plan

--------------------------------------------------------------------------------
| Id | Operation                    | Name     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |
|  1 |  TABLE ACCESS BY INDEX ROWID | EMP      |
|  2 |   INDEX RANGE SCAN           | EMP_IDX1 |
--------------------------------------------------------------------------------

Extra table lookup = slower.

---

Layman Understanding of ROWID Lookup

Imagine:

Index says:

Employee data is in cupboard row 25

Now Oracle must physically go to table storage and fetch remaining columns.

Extra travel = extra time.

---

Real-Time Production Scenario

Suppose application dashboard only shows:

EMPNO	ENAME

But backend query uses:

SELECT * FROM emp;

If 10,000 users open dashboard:

• unnecessary GBs of data transferred
• high network usage
• slow UI response
• high database load

---

Golden Rule for Developers and DBAs

Never use:

SELECT *

in:

• production code
• procedures
• APIs
• views
• reports

Always select only required columns.

---

Oracle Interview Answer

Interviewer may ask:

Why should we avoid SELECT * ?

Answer:

SELECT * reads unnecessary columns, increases logical and physical I/O,
network traffic, CPU usage, memory usage and may prevent Oracle from using
covering indexes efficiently.

---

Easy Memory Trick

SELECT * = Bring entire luggage

SELECT required columns = Bring only required clothes

---

Most Important DBA Performance Logic

Performance depends on:

Rows fetched × Columns fetched

Most developers only think about rows.

But columns also matter heavily in Oracle performance.