Home » RDBMS Server » Performance Tuning » if the data is already available in cache,will index be used if we query again?
if the data is already available in cache,will index be used if we query again? [message #231695] Wed, 18 April 2007 03:06 Go to next message
kssarayu
Messages: 18
Registered: March 2007
Junior Member
Hi,

if the data i want to retrieve is already available in cache, then say, my query could use index, will the index usage really happen??

for example i gave a query
SELECT * FROM EMP WHERE EMPNO = 5555; --> This used index.
This query is executed and data is in cache.

Again i executed the statement. will the index be used?


---

First time i executed one statement and the set autotrace on output is as below:


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=98)
1 0 TABLE ACCESS (BY INDEX ROWID) OF EMP' (Cost=3 Card=
1 Bytes=98)

2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) (Cost=2 Card=1
)





Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
6 consistent gets
3 physical reads

0 redo size
8247 bytes sent via SQL*Net to client
652 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

-- I am executing second time....immediately
SQL> /





Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=98)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=3 Card=
1 Bytes=98)

2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) (Cost=2 Card=1
)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads

0 redo size
8247 bytes sent via SQL*Net to client
652 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-----------------------------------------------------
Thanks
sarayu

Re: if the data is already available in cache,will index be used if we query again? [message #231698 is a reply to message #231695] Wed, 18 April 2007 03:25 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it will but it'll read it in cache if not how can he know which row to return?

Regards
Michel
Previous Topic: SQL*Net message from client - Event
Next Topic: Execution plan changed automatically. How does that happen?
Goto Forum:
  


Current Time: Sat Dec 03 01:01:26 CST 2016

Total time taken to generate the page: 0.07553 seconds