Oracle-L: RE: HELP! Index Debate!

From: Smith, Ron L. <rlsmith_at_kmg.com>
Date: Thu, 31 Jul 2003 12:49:26 -0800
Message-ID: <F001.005C808B.20030731124926@fatcity.com>

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 - 15:49:26 CDT