RE: A question about performance

Hi,

Not to flog a dead horse, (but I will anyway)...

If he's returning every row, of course a full table scan will be faster!

I was comparing an index range scan vs. full table scan (rule-based vs. cost based) when returning a subset of the table.

Didn't mean to confuse the issue...

• Jerry

If 99% of the records have sub_id < 0 , then our buddy should not even compare these two for the performace. He is trying to use where clause just for enforcing index scan. I am assuming that there are no records with sub_id <0. I think most of the guys are trying to explain the general concepts of Oracle which are written in the manuals. I am trying to answer this specific case and not the generic optimizer rules.

-----Original Message-----
Sent: Wednesday, June 21, 2000 2:04 PM
To: Multiple recipients of list ORACLE-L

good point.. I should have said "if you expect to return > 5-20% of the
rows"

>From: "Jerry Cunningham" <cunninghamjerry_at_visto.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
>Subject: RE: A question about performance
>Date: Wed, 21 Jun 2000 08:38:52 -0800
>
>Not necessarily.
>
>What if 99% of the records have subscr_id <= 0? Full
table scan would be
>slower.
>
>I just had a situation like this where the cost based
optimizer chose a
>full table scan, even though indices where present.
It was much slower than

>rule-based, due to the cardinality of the index.
>
>
>- Jerry
>
>
>
>
>-----Original Message-----
>Sent: Wed, 21 Jun 2000 06:30:06 -0800
>To: ORACLE-L_at_fatcity.com
>
>
> Full table scan will be faster.
> Reason: If it go for index scan it will get the
rowids and come back
>with those
> rowids to scan the table because u are selecting
all the columns so it
>has
>to do full
> scan of table also for getting everything.
> Index scan + full scan of table for actual data.
(If going for index
>scan)
> ----------- Only table scan for actual blocks
(Full scan)
>
> Cheers.
>
>-----Original Message-----
>Sent: Tuesday, June 20, 2000 11:35 PM
>To: Multiple recipients of list ORACLE-L
>
>
> Hi All:
> There is a question about performance.
> Table name : subscriber , primary
key : subscr_id , with
>over 5000000 records .
>
> CASE 1 : select *
> from
subscriber ;
> (FULL TABLE
SCAN)
>
> CASE 2 : select *
> from
subscriber
> where
subscr_id > 0 ;
> (USE INDEX)
>
> Could anyone tell me which ones
performance is better , or
>they are the same ??
>
> Thank you in advance.
>
>
>Tiffany
>E-mail tiffanydu_at_pcdc.com.tw
>
>