Can somebody tell me why these queries are written two different results and why?
1)select * from employees;
2)select job_id,salary,email,first_name from employees
In second query it is accessing only few fields that's why it has accessed only few blocks, but oracle normal does block wise read not row or column wise read , so why difference has come.
Are there other columns in the table? Do you see row_chaining in the table?
select * from chained_rows where table_name='EMPLOYEES'
Originally Posted by skhanal
That means no chained rows..i think so
It depends on how statistics are gathered. Analyze command updates chained_rows but dbms_stats doesn't.
Originally Posted by jayasankar
Eitherway buffer_gets should be identical for both queries - assuming query is doing full table scan for each one of them.
You don't have index on (job_id,salary,email,first_name) - don't you?
Would you mind in doing/posting...
set autotrace traceonly;
select * from employees;
select job_id,salary,email,first_name from employees;
Last edited by PAVB; 10-04-2010 at 05:25 PM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width