Oracle-L: RE: HELP! Index Debate!

From: Smith, Ron L. <rlsmith_at_kmg.com>
Date: Thu, 31 Jul 2003 13:14:23 -0800
Message-ID: <F001.005C809C.20030731131423@fatcity.com>

Yes, the first column on each table is the column referenced in the SQL. They all have indexes on the first column.

	-----Original Message-----
	From: Jamadagni, Rajendra [mailto:Rajendra.Jamadagni_at_espn.com] 
	Sent: Thursday, July 31, 2003 3:59 PM
	To: Multiple recipients of list ORACLE-L
	Subject: RE: HELP! Index Debate!
	
	
	do you have corresponding indexes on referenced columns on T1-5

tables ??

Raj

	Rajendra dot Jamadagni at nospamespn dot com 
	All Views expressed in this email are strictly personal. 
	QOTD: Any clod can have facts, having an opinion is an art ! 

		-----Original Message-----
		From: Smith, Ron L. [mailto:rlsmith_at_kmg.com]
		Sent: Thursday, July 31, 2003 4:49 PM
		To: Multiple recipients of list ORACLE-L
		Subject: RE: HELP! Index Debate!
		
		
		No answer for that.  

			-----Original Message-----
			From: Jamadagni, Rajendra
[mailto:Rajendra.Jamadagni_at_espn.com] 
			Sent: Thursday, July 31, 2003 3:44 PM
			To: Multiple recipients of list ORACLE-L
			Subject: RE: HELP! Index Debate!
			
			

			How does one know that only one column is being

used in an index and others are being ignored ... ?? This is new to me.

Raj

			Rajendra dot Jamadagni at nospamespn dot com 
			All Views expressed in this email are strictly
personal. 
			QOTD: Any clod can have facts, having an opinion

is an art !

			-----Original Message----- 
			From: Smith, Ron L. [mailto:rlsmith_at_kmg.com] 
			Sent: Thursday, July 31, 2003 4:34 PM 
			To: Multiple recipients of list ORACLE-L 
			Subject: RE: HELP! Index Debate! 



			Please help resolve this dispute. 
			We have a query that runs over 5 hours.
Sections of the query are 
			listed below. 

			The table QOH_DAY_FACT table had only on index
and that was on the 
			TIME_ID column.  I propose adding an index with
PROD_ID, WHS_ID, LOT_ID, 
			WHS_LOC, TIME_ID and QUALITY_ID.  My test shows
the runtime was reduced 
			to about 1.5 hours. 

			The developer said all the columns in the index
except PROD_ID were 
			being ignored.  He says there should be six
separate indexes, one for 
			each column before Oracle will use them. 

			Also, does the order of the columns in the index
have to match the order 
			of the columns in the WHERE CLAUS or is it more
important to match the 
			WHERE CLAUS to the data content (least number of

rows first)?

I will be running more tests, but I would like some input on this if

anyone has an opinion.

			Thanks! 
			Ron 


			select T1."COUNTRY_NAME" "c1" 


			  , T2."PRODTN_PROC_NAME" "c2" 

			  , T2."PLANT_NAME" "c3" 

			  , T1."WHS_NAME" "c4" 

			  , T1."WHS_CMPLX_NAME" "c5" 

			  , T3."WHS_LOC_NAME" "c6" 

			  , T4."GRADE_DESC" "c7" 

			  , T4."PACK_DESC" "c8" 

			  , T5."FULL_DT" "c9" 

			  , T6."QOH_MT" "c10"....... 


			....from "DWMART"."DISTRIB_FCLTY_DIM" T1 


			  , "DWMART"."MFG_FCLTY_DIM" T2 

			  , "DWMART"."DISTRIB_LOC_DIM" T3 

			  , "DWMART"."TIME_DIM" T5 

			  , "DWMART"."QUALITY_DIM" T7 

			  , "DWMART"."QOH_DAY_FACT" T6 

			  , "DWMART"."PROD_DIM" T4 

			where T6."PROD_ID"=T4."PROD_ID"(+) 
			   and T6."WHS_ID"=T1."WHS_ID" 
			   and T6."LOT_ID"=T2."LOT_ID" 
			   and T6."WHS_LOC_ID"=T3."WHS_LOC_ID" 
			   and T6."TIME_ID"=T5."TIME_ID" 
			   and T6."QUALITY_ID"=T7."QUALITY_ID" 
			order by "c9" asc 
			-- 
			Please see the official ORACLE-L FAQ:
http://www.orafaq.net 
			-- 
			Author: Smith, Ron L. 
			  INET: rlsmith_at_kmg.com 

			Fat City Network Services    -- 858-538-5051
http://www.fatcity.com 
			San Diego, California        -- Mailing list and

web hosting services

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Smith, Ron L.
  INET: rlsmith_at_kmg.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Thu Jul 31 2003 - 16:14:23 CDT