RE: A question about performance

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
>
>
>--
>Author: =?big5?B?VGlmZmFueSBEdSAoIKf5p0qq4iAp?=
> INET: TiffanyDu_at_pcdc.com.tw
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>--
>Author: Singla, Sanjeev
> INET: SSingla_at_oxhp.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
>
>
>___________________________________________________________________________
>Visit http://www.visto.com/info, your free web-based communications center.
>Visto.com. Life on the Dot.
>
>--
>Author: Jerry Cunningham
> INET: cunninghamjerry_at_visto.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).