RE: Slooow Select after Upgrade to 8.1.6 from 8.0.4

From: Boivin, Patrice J <BoivinP_at_mar.dfo-mpo.gc.ca>
Date: Mon, 06 Nov 2000 09:33:42 -0400
Message-Id: <10672.121195@fatcity.com>

Can you build local indexes instead of a global index?

In 8i the cost-based optimizer might be able to rule out partitions = before
it starts scanning the local indexes.

It has to be a prefixed index though, i.e. your index has to start with = the
partition key.

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Systems Admin & Operations | Admin. et Exploit. des syst=E8mes
Technology Services        | Services technologiques
Informatics Branch         | Direction de l'informatique=20
Maritimes Region, DFO      | R=E9gion des Maritimes, MPO

E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>=20

	-----Original Message-----
	From:	Tom Pall [SMTP:tom_at_cdproc.com]
	Sent:	Friday, November 03, 2000 7:05 PM
	To:	Multiple recipients of list ORACLE-L
	Subject:	Re: Slooow Select after Upgrade to 8.1.6 from 8.0.4

	How fortunate you've moved to 8i.

	Now you can avail yourself of all those lovely execution plan
	stability features available in 8i.  Part of the rationale for the
	features is that 3rd party software, like Crystal Reports, does not
	allow for hints. =20




	----- Original Message -----=20
	To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
	Sent: Friday, November 03, 2000 2:06 PM




	> Sun2.6; Oracle 8.0.4 partitioned and indexed table. Upgraded to

8.1.6


	> through full export/import.

	> Queries that returns 1200 rows ran before upgrade 5-7 sec. After

upgrade ~40


	> sec.

	> When dropped index table from 8.1 performance restored. TKPROF

shows that


	> optimizer in 8.0 goes to Partitions and does full scan there.

Optimizer in


	> 8.1 does full scanning of Index table that takes much longer.

	> Client uses Crystal Report that does not permit to enter hints to

Select


	> query.

	> Please advise work around.

	> Thank you.

	> Joseph

	>=20

	> --=20

	> Please see the official ORACLE-L FAQ: http://www.orafaq.com

	> --=20

	> Author: Joseph on Mediaone

	>   INET: josepha_at_mediaone.net

	>=20

	> 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).

	--=20
	Please see the official ORACLE-L FAQ: http://www.orafaq.com
	--=20
	Author: Tom Pall
	  INET: tom_at_cdproc.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 Mon Nov 06 2000 - 07:33:42 CST