RE: A question about performance

From: Singla, Sanjeev <SSingla_at_oxhp.com>
Date: Wed, 21 Jun 2000 13:35:34 -0400
Message-Id: <10535.110033@fatcity.com>

His intention is to use the entire table. And he is using where clause only to force the index scan. Hope u understand the current scenareo.

-----Original Message-----
From: Jerry Cunningham [mailto:cunninghamjerry_at_visto.com] Sent: Wednesday, June 21, 2000 12:39 PM
To: Multiple recipients of list ORACLE-L Subject: RE: A question about performance

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

Received on Wed Jun 21 2000 - 12:35:34 CDT