Is SQL 7 optimiszer smart enough ?!

Thread View: alt.comp.databases

1 messages

[T] Back to Threads [V] Tree View [G] Group Articles

1 total messages Started by Dan G Fri, 03 Sep 1999 00:00

#43

Author: Dan G
Date: Fri, 03 Sep 1999 00:00

33 lines
2173 bytes

Hello all,

I propose all of you one puzzle:
Problem:
Consider one table with col1,col2 (with 23.682 records) with non-cluster
index on col2

Run this query with show stat i/o on (in SQL 7):

select col1
from
 table
order by
col2


The result was:
(23682 row(s) affected)

1) Table 'POS'. Scan count 1, logical reads 23782, physical reads 1175,
read-ahead reads 94.
in 22 seconds
2) Table 'POS'. Scan count 1, logical reads 1132, physical reads 628,
read-ahead reads 1136.
in 7 seconds

Query:

1)Which of the above results have been returned by using table scan and
which one by using the index on col2 ? .
2)Does the optimiser used table scan or the index ?.

I am looking forward ...

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads