| if the data is already available in cache,will index be used if we query again? [message #231695] |
Wed, 18 April 2007 03:06  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
