RE: A question about performance

actually, both of those queries will probably do a full table scan, at least if the table and index(es) have been analyzed and you are using CBO.. the optimizer will choose a full table scan if it thinks that enough rows will be returned to make it more efficient to do so.

Rachel

>From: "Singla, Sanjeev" <SSingla_at_oxhp.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 06:30:06 -0800
>
> 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).