Home » SQL & PL/SQL » SQL & PL/SQL » Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast
Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #260139] Fri, 17 August 2007 10:36 Go to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi ,

please advice me.


Thanx & reds
Thangam.
Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260141 is a reply to message #260139] Fri, 17 August 2007 10:42 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

This is my query it will take more time.please advice me how can make fast.


SELECT DISTINCT a.cd_supplr_code,
  REPLACE(d.na_supplr_name,   '&',   'AND')
FROM demand_ppc_requirements a,
  demand_sourcings b,
  demand_assy_plants c,
  demand_ppc_suppliers d,
  demand_detail_volumes ddv 
WHERE a.cd_supplr_code = d.cd_supplr_code
 AND a.no_vol_seq = b.no_vol_seq
 AND b.pc_pct_business > 0
 AND b.cd_assy_plant = c.cd_assy_plant
 and ddv.cd_assy_plant = c.cd_assy_plant
 AND b.dt_eff_date >= sysdate
 AND c.cd_assy_region = 'NA'
 AND NOT(ddv.qt_cpv_norm = 0 OR ddv.qt_cpv_norm IS NULL)
AND trunc(ddv.dt_time_period) >=to_date((select cpv2_ppc_shortfall_pkg.getQtrDates_FUNC (to_char(sysdate,'mm/dd/yyyy')) from dual),'mm/dd/yyyy')

UNION

SELECT distinct e.cd_supplr_code ,
  REPLACE(e.na_supplr_name,   '&',   'AND') 
FROM (select	x.no_vol_seq 	 no_vol_seq,
			max(x.dt_eff_date) dt_eff_date
			from medemand.demand_sourcings x,
			     medemand.demand_assy_plants y
     where 	x.cd_assy_plant 	= y.cd_assy_plant
		        and		y.cd_assy_region 	= 'NA'
			group
			by		x.no_vol_seq)a1,
    demand_ppc_suppliers e,
    demand_sourcings b,
    demand_detail_volumes t1,
    DEMAND_PPC_REQUIREMENTS dpr
WHERE 
a1.no_vol_seq = b.no_vol_seq
and a1.dt_eff_date = b.dt_eff_date
AND b.pc_pct_business > 0
AND b.no_vol_seq = dpr.no_vol_seq
AND dpr.cd_supplr_code = e.cd_supplr_code
and t1.no_part_base=dpr.no_part_base
and t1.no_part_prefix=dpr.no_part_prefix
and t1.no_part_suffix=dpr.no_part_suffix
and t1.qt_cpv_norm>0
AND trunc(t1.dt_time_period) >=to_date((select cpv2_ppc_shortfall_pkg.getQtrDates_FUNC (to_char(sysdate,'mm/dd/yyyy')) from dual),'mm/dd/yyyy')


[Updated on: Fri, 17 August 2007 13:49]

Report message to a moderator

Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260159 is a reply to message #260141] Fri, 17 August 2007 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove "rule" hint and compute statistics on your objects.

Regards
Michel
Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260162 is a reply to message #260159] Fri, 17 August 2007 11:06 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Yes Miche i did that also still it is taking too much time.
please advice me.

The Tables and total data's

desc medemand.DEMAND_DETAIL_VOLUMES_TEMP ======= 1674228
desc medemand.DEMAND_PARTVOL_TEMP  ==============  6562901
desc demand_ppc_requirements  ========== 1175171
desc demand_sourcings ========= 13137147
desc demand_assy_plants ====== 62
desc demand_ppc_suppliers ================= 14961
desc DEMAND_detail_volumes      ===================== 4003585




Function

Function

FUNCTION getQtrDates_FUNC (p_qtr_enddate IN VARCHAR2) 
	RETURN VARCHAR2
	IS
	ls_qtr_startdate VARCHAR2(11) := '';
	std_temp_year VARCHAR2(4) := '' ;
	
	BEGIN
	-- p_p_qtr_enddate -format will be '08/01/2008'
	std_temp_year := SUBSTR(p_qtr_enddate,7,10);
	
	IF p_qtr_enddate BETWEEN CONCAT(g_firstqtr,std_temp_year) AND CONCAT(g_firstqtrend,std_temp_year) THEN
  		ls_qtr_startdate:=CONCAT(g_firstqtr,std_temp_year);
		--dbms_output.put_line('Date = '||max_eff_date);
	ELSIF p_qtr_enddate BETWEEN CONCAT(g_secondqtr,std_temp_year) AND CONCAT(g_secondqtrend,std_temp_year) THEN
  		ls_qtr_startdate:=CONCAT(g_secondqtr,std_temp_year);
		--dbms_output.put_line('Date = '||max_eff_date);
	ELSIF p_qtr_enddate BETWEEN CONCAT(g_thirdqtr,std_temp_year) AND CONCAT(g_thirdqtrend,std_temp_year) THEN
  		ls_qtr_startdate:=CONCAT(g_thirdqtr,std_temp_year);
		--dbms_output.put_line('Date = '||max_eff_date);
	ELSIF p_qtr_enddate BETWEEN CONCAT(g_fourthqtr,std_temp_year) AND CONCAT(g_fourthqtrend,std_temp_year) THEN
  		ls_qtr_startdate:=CONCAT(g_fourthqtr,std_temp_year);
		--dbms_output.put_line('Date = '||max_eff_date);
  	END IF;

    RETURN ls_qtr_startdate;
    EXCEPTION
    WHEN OTHERS THEN
    ls_qtr_startdate := 'FAILURE' ;
    RETURN ls_qtr_startdate; 
	END ; -- end of getqtrdates_func

[Updated on: Fri, 17 August 2007 11:12]

Report message to a moderator

Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260165 is a reply to message #260162] Fri, 17 August 2007 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So why did you add the "rule" hint?
Post all the relevant informations:
- indexes
- statistics
- execution plans
- oracle version
...

Remove both DISTINCT as UNION also does it.
You are calling procedure. Are you sure they are optimized?

Regards
Michel
Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260174 is a reply to message #260139] Fri, 17 August 2007 11:39 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Just we analyzed all tables and Indexs.

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260176 is a reply to message #260139] Fri, 17 August 2007 11:45 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
SELECT /*+ rule */ DISTINCT a.cd_supplr_code,
  REPLACE(d.na_supplr_name,   '&',   'AND')
FROM demand_ppc_requirements a,
  demand_sourcings b,
  demand_assy_plants c,
  demand_ppc_suppliers d,
  demand_detail_volumes ddv 
WHERE a.cd_supplr_code = d.cd_supplr_code
 AND a.no_vol_seq = b.no_vol_seq
 AND b.pc_pct_business > 0
 AND b.cd_assy_plant = c.cd_assy_plant
 and ddv.cd_assy_plant = c.cd_assy_plant
 AND b.dt_eff_date >= sysdate
 AND c.cd_assy_region = 'NA'
 AND NOT(ddv.qt_cpv_norm = 0 OR ddv.qt_cpv_norm IS NULL)
AND trunc(ddv.dt_time_period) >=to_date((select cpv2_ppc_shortfall_pkg.getQtrDates_FUNC (to_char(sysdate,'mm/dd/yyyy')) from dual),'mm/dd/yyyy')


Since only A & D tables contribute to SELECT clause,
B,C,D, & DDV should NOT be in the FROM clause.
They can & should be subordinated into the WHERE clause.

How much time is spent by each of the Big SELECT statements
on either side of the UNION?

[Updated on: Fri, 17 August 2007 11:47] by Moderator

Report message to a moderator

Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260177 is a reply to message #260174] Fri, 17 August 2007 11:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 17 August 2007 18:11
So why did you add the "rule" hint?
Post all the relevant informations:
- indexes
- statistics
- execution plans
- oracle version
...

Remove both DISTINCT as UNION also does it.
You are calling procedure. Are you sure they are optimized?

Regards
Michel


Convert your varchar2 into date and replace your package function by SQL expression.

Regards
Michel
Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260184 is a reply to message #260162] Fri, 17 August 2007 12:11 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
iamdurai wrote on Fri, 17 August 2007 12:06


FUNCTION getQtrDates_FUNC (p_qtr_enddate IN VARCHAR2) 

	-- p_p_qtr_enddate -format will be '08/01/2008'
	std_temp_year := SUBSTR(p_qtr_enddate,7,10);
	
	IF p_qtr_enddate BETWEEN CONCAT(g_firstqtr,std_temp_year) AND CONCAT(g_firstqtrend,std_temp_year) THEN



In this case, 01/02/2099 would fall between 01/01/2001 and 01/03/2001, as would 01/02/1982.

[Updated on: Fri, 17 August 2007 12:12]

Report message to a moderator

Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260208 is a reply to message #260139] Fri, 17 August 2007 13:12 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

The below given query is old logic.It does not consider partvol>0(qt_cpv_norm>0) and current and future model years.


It is working fast.



SELECT
 DISTINCT a.cd_supplr_code,
 REPLACE(d.na_supplr_name, '&', 'AND')
FROM
   demand_ppc_requirements a,
   demand_sourcings b,
   demand_assy_plants c,
   demand_ppc_suppliers d
WHERE
            a.cd_supplr_code = d.cd_supplr_code
    AND a.no_vol_seq = b.no_vol_seq
    AND b.pc_pct_business > 0
    AND b.cd_assy_plant = c.cd_assy_plant
    AND b.dt_eff_date >= sysdate
    AND c.cd_assy_region = 'NA'

UNION

SELECT
    DISTINCT e.cd_supplr_code,
    REPLACE(e.na_supplr_name, '&', 'AND')
FROM
  (SELECT x.no_vol_seq no_vol_seq, MAX(x.dt_eff_date) dt_eff_date
   FROM demand_sourcings x, demand_assy_plants y
   WHERE x.cd_assy_plant = y.cd_assy_plant AND y.cd_assy_region = 'NA'
   GROUP BY x.no_vol_seq) a,
 demand_sourcings b,
 demand_ppc_requirements c,
 demand_ppc_suppliers e
WHERE
          a.no_vol_seq = b.no_vol_seq
  AND a.dt_eff_date = b.dt_eff_date
  AND b.pc_pct_business > 0
  AND b.no_vol_seq = c.no_vol_seq
  AND c.cd_supplr_code = e.cd_supplr_code 

[Updated on: Fri, 17 August 2007 13:20]

Report message to a moderator

Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260216 is a reply to message #260177] Fri, 17 August 2007 13:34 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Michel.

-- statistics - analyzed all tables and Indexs.
-- execution - plansSELECT STATEMENT
-- oracle version - 
       Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
       PL/SQL Release 9.2.0.8.0 - Production
       CORE    9.2.0.8.0       Production
       TNS for Solaris: Version 9.2.0.8.0 - Production
       NLSRTL Version 9.2.0.8.0 - Production
 


Thanx & Reds
Thangam.
Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260218 is a reply to message #260216] Fri, 17 August 2007 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you think:
Quote:
-- execution - plansSELECT STATEMENT

is an execution plan?

And you still don't post the index list.
But the first step is to change your statement as I said.
If you don't want to do it, tell us.

Regards
Michel

[Updated on: Fri, 17 August 2007 13:38]

Report message to a moderator

Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260220 is a reply to message #260218] Fri, 17 August 2007 13:38 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Sorry Michel, just select purpose only this query.

This is my table DESC
desc medemand.DEMAND_DETAIL_VOLUMES_TEMP 
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
STD_MODEL_YEAR                          NUMBER(4)                                                                                                                                                                                     
CD_ASSY_PLANT                           VARCHAR2(5)                                                                                                                                                                                   
NO_PART_BASE                            VARCHAR2(9)                                                                                                                                                                                   
NO_PART_PREFIX                          VARCHAR2(7)                                                                                                                                                                                   
NO_PART_SUFFIX                          VARCHAR2(8)                                                                                                                                                                                   
QT_CPV_NORM                             NUMBER(9)                                                                                                                                                                                     
DT_TIME_PERIOD                 NOT NULL DATE                                                                                                                                                                                          

7 rows selected


desc medemand.DEMAND_PARTVOL_TEMP  
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
CD_SUPPLR_CODE                 NOT NULL VARCHAR2(5)                                                                                                                                                                                   
PC_PCT_BUSINESS                         NUMBER(3)                                                                                                                                                                                     
CD_ASSY_PLANT                           VARCHAR2(5)                                                                                                                                                                                   

3 rows selected


desc demand_ppc_requirements  
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
NO_VOL_SEQ                     NOT NULL NUMBER(10)                                                                                                                                                                                    
NO_PART_BASE                   NOT NULL VARCHAR2(9)                                                                                                                                                                                   
NO_PART_PREFIX                 NOT NULL VARCHAR2(7)                                                                                                                                                                                   
NO_PART_SUFFIX                 NOT NULL VARCHAR2(8)                                                                                                                                                                                   
CD_SUPPLR_CODE                 NOT NULL VARCHAR2(5)                                                                                                                                                                                   
DT_CREATION_DATE               NOT NULL DATE                                                                                                                                                                                          
DT_MODIFICATION_DATE           NOT NULL DATE                                                                                                                                                                                          
CD_ORIGINATION                          CHAR(2)                                                                                                                                                                                       
NA_PART                                 VARCHAR2(34)                                                                                                                                                                                  
NA_SUPPLR_NAME                          VARCHAR2(60)                                                                                                                                                                                  

10 rows selected


desc demand_sourcings 
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
NO_VOL_SEQ                     NOT NULL NUMBER(10)                                                                                                                                                                                    
CD_ASSY_PLANT                  NOT NULL VARCHAR2(5)                                                                                                                                                                                   
DT_EFF_DATE                    NOT NULL DATE                                                                                                                                                                                          
PC_PCT_BUSINESS                         NUMBER(3)                                                                                                                                                                                     
DT_CREATION_DATE               NOT NULL DATE                                                                                                                                                                                          
DT_MODIFICATION_DATE           NOT NULL DATE                                                                                                                                                                                          
CD_ORIGINATION                          CHAR(2)                                                                                                                                                                                       
CD_SOURCING_TYPE                        CHAR(1)                                                                                                                                                                                       
CD_MANUAL_ZERO                          CHAR(2)                                                                                                                                                                                       

9 rows selected


desc demand_assy_plants 
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
CD_ASSY_PLANT                  NOT NULL VARCHAR2(5)                                                                                                                                                                                   
CD_ASSY_REGION                          VARCHAR2(3)                                                                                                                                                                                   
CD_CMMS                                 VARCHAR2(5)                                                                                                                                                                                   
NA_ASSY_PLANT                           VARCHAR2(50)                                                                                                                                                                                  
CD_CMMS_LAUNCHED                        CHAR(1)                                                                                                                                                                                       
CD_WERS_PROCUREMENT                     CHAR(5)                                                                                                                                                                                       
DT_CREATION_DATE                        DATE                                                                                                                                                                                          
DT_MODIFICATION_DATE                    DATE                                                                                                                                                                                          
CD_CREATE_USERID                        VARCHAR2(8)                                                                                                                                                                                   
CD_UPDATE_USERID                        VARCHAR2(8)                                                                                                                                                                                   

10 rows selected


desc demand_ppc_suppliers 
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
CD_SUPPLR_CODE                 NOT NULL VARCHAR2(5)                                                                                                                                                                                   
NA_SUPPLR_NAME                          VARCHAR2(60)                                                                                                                                                                                  
NO_SUPPLR_PHONE                         VARCHAR2(10)                                                                                                                                                                                  
NO_SUPPLR_FAX                           VARCHAR2(10)                                                                                                                                                                                  
CD_PARENT_COMPANY                       CHAR(5)                                                                                                                                                                                       
NA_SUPPLR_PARENT                        VARCHAR2(60)                                                                                                                                                                                  
DT_CREATION_DATE               NOT NULL DATE                                                                                                                                                                                          
DT_MODIFICATION_DATE           NOT NULL DATE                                                                                                                                                                                          
CD_ORIGINATION                          CHAR(2)                                                                                                                                                                                       

9 rows selected


desc DEMAND_detail_volumes      
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
CD_VEHICLE_LINE                NOT NULL CHAR(3)                                                                                                                                                                                       
STD_MODEL_YEAR                 NOT NULL NUMBER(4)                                                                                                                                                                                     
DT_TIME_PERIOD                 NOT NULL DATE                                                                                                                                                                                          
CD_ASSY_PLANT                  NOT NULL VARCHAR2(5)                                                                                                                                                                                   
NO_PART_BASE                   NOT NULL VARCHAR2(9)                                                                                                                                                                                   
NO_PART_PREFIX                 NOT NULL VARCHAR2(7)                                                                                                                                                                                   
NO_PART_SUFFIX                 NOT NULL VARCHAR2(8)                                                                                                                                                                                   
ID_DEMAND                      NOT NULL VARCHAR2(1)                                                                                                                                                                                   
QT_CPV_NORM                             NUMBER(9)                                                                                                                                                                                     
QT_CPV_FLEX                             NUMBER(9)                                                                                                                                                                                     
QT_FPV                                  NUMBER(9)                                                                                                                                                                                     
QT_SERVICE                              NUMBER(9)                                                                                                                                                                                     
CD_INCOMPLETE                           CHAR(1)                                                                                                                                                                                       
CD_AS_REQD                              CHAR(1)                                                                                                                                                                                       
CD_CPSC                                 VARCHAR2(9)                                                                                                                                                                                   
DT_CREATION_DATE               NOT NULL DATE                                                                                                                                                                                          
DT_MODIFICATION_DATE           NOT NULL DATE                                                                                                                                                                                          
QT_CPV_NORM_BASE                        NUMBER(9)                                                                                                                                                                                     
QT_CPV_FLEX_BASE                        NUMBER(9)                                                                                                                                                                                     
SCENARIO                                VARCHAR2(1)                                                                                                                                                                                   
`

[Updated on: Fri, 17 August 2007 13:48]

Report message to a moderator

Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260242 is a reply to message #260220] Fri, 17 August 2007 14:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I asked for index list and get none.
I asked for execution plan and get none.
You ask for help and will get none.

Here's an example of execution plan:
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 1735466533

--------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |  3499 |  1923K|   453   (2)| 00:00:06 |
|*  1 |  HASH JOIN RIGHT OUTER          |          |  3499 |  1923K|   453   (2)| 00:00:06 |
|   2 |   TABLE ACCESS FULL             | SEG$     |  4400 |   189K|    29   (0)| 00:00:01 |
|*  3 |   HASH JOIN                     |          |  1510 |   765K|   423   (2)| 00:00:06 |
|   4 |    TABLE ACCESS FULL            | TS$      |    14 |   266 |     5   (0)| 00:00:01 |
|*  5 |    HASH JOIN RIGHT OUTER        |          |  1510 |   737K|   418   (2)| 00:00:06 |
|   6 |     TABLE ACCESS FULL           | USER$    |    63 |   882 |     3   (0)| 00:00:01 |
|   7 |     NESTED LOOPS OUTER          |          |  1510 |   716K|   414   (2)| 00:00:05 |
|*  8 |      HASH JOIN OUTER            |          |  1510 |   672K|   327   (3)| 00:00:04 |
|   9 |       NESTED LOOPS              |          |  1510 |   660K|   196   (3)| 00:00:03 |
|  10 |        MERGE JOIN CARTESIAN     |          |  1945 |   687K|   133   (4)| 00:00:02 |
|* 11 |         HASH JOIN               |          |     1 |   326 |     1 (100)| 00:00:01 |
|* 12 |          FIXED TABLE FULL       | X$KSPPI  |     1 |    55 |     0   (0)| 00:00:01 |
|  13 |          FIXED TABLE FULL       | X$KSPPCV |   100 | 27100 |     0   (0)| 00:00:01 |
|  14 |         BUFFER SORT             |          |  1945 | 70020 |   133   (4)| 00:00:02 |
|* 15 |          TABLE ACCESS FULL      | OBJ$     |  1945 | 70020 |   133   (4)| 00:00:02 |
|* 16 |        TABLE ACCESS CLUSTER     | TAB$     |     1 |    86 |     1   (0)| 00:00:01 |
|* 17 |         INDEX UNIQUE SCAN       | I_OBJ#   |     1 |       |     0   (0)| 00:00:01 |
|  18 |       TABLE ACCESS FULL         | OBJ$     | 48631 |   379K|   130   (1)| 00:00:02 |
|  19 |      TABLE ACCESS BY INDEX ROWID| OBJ$     |     1 |    30 |     1   (0)| 00:00:01 |
|* 20 |       INDEX UNIQUE SCAN         | I_OBJ1   |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
              "T"."TS#"="S"."TS#"(+))
   3 - access("T"."TS#"="TS"."TS#")
   5 - access("CX"."OWNER#"="CU"."USER#"(+))
   8 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
  11 - access("KSPPI"."INDX"="KSPPCV"."INDX")
  12 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
  15 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND("O"."FLAGS",128)=0)
  16 - filter(BITAND("T"."PROPERTY",1)=0)
  17 - access("O"."OBJ#"="T"."OBJ#")
  20 - access("T"."BOBJ#"="CO"."OBJ#"(+))

Regards
Michel
Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260317 is a reply to message #260242] Sat, 18 August 2007 06:00 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Michel,

How can i get this execution plan information.can you tell me please.

And Index also limited only.


Reds
Thangam.

Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260321 is a reply to message #260317] Sat, 18 August 2007 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Index list: query all_indexes or dba_indexes
Execution plan: one way is: use "set autotrace traceonly explain" and execute the query.

Regards
Michel

Re: Instead of using DISTINCT is there any other way to easy filter the data's. [message #260558 is a reply to message #260321] Mon, 20 August 2007 05:22 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hai Michal,

Sorry for the delay. Here with i have pasted index list.



SQL> SELECT TABLE_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE INDEX_NAME='DPR_UK';

TABLE_NAME                     COLUMN_NAME                                      
------------------------------ ------------------------------                   
DEMAND_PPC_REQUIREMENTS        NO_PART_BASE                                     
DEMAND_PPC_REQUIREMENTS        NO_PART_PREFIX                                   
DEMAND_PPC_REQUIREMENTS        NO_PART_SUFFIX                                   
DEMAND_PPC_REQUIREMENTS        CD_SUPPLR_CODE                                   

SQL> SELECT TABLE_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE INDEX_NAME='DS_PK';

TABLE_NAME                     COLUMN_NAME                                      
------------------------------ ------------------------------                   
DEMAND_SOURCINGS               NO_VOL_SEQ                                       
DEMAND_SOURCINGS               CD_ASSY_PLANT                                    
DEMAND_SOURCINGS               DT_EFF_DATE                                      

SQL> SELECT TABLE_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE INDEX_NAME='DAP_PK';

TABLE_NAME                     COLUMN_NAME                                      
------------------------------ ------------------------------                   
DEMAND_ASSY_PLANTS             CD_ASSY_PLANT                                    

SQL> SELECT TABLE_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE INDEX_NAME='DPS_PK';

TABLE_NAME                     COLUMN_NAME                                      
------------------------------ ------------------------------                   
DEMAND_PPC_SUPPLIERS           CD_SUPPLR_CODE                                   

SQL> SELECT TABLE_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE INDEX_NAME='DDV_PK';

TABLE_NAME                     COLUMN_NAME                                      
------------------------------ ------------------------------                   
DEMAND_DETAIL_VOLUMES          CD_VEHICLE_LINE                                  
DEMAND_DETAIL_VOLUMES          STD_MODEL_YEAR                                   
DEMAND_DETAIL_VOLUMES          DT_TIME_PERIOD                                   
DEMAND_DETAIL_VOLUMES          CD_ASSY_PLANT                                    
DEMAND_DETAIL_VOLUMES          NO_PART_BASE                                     
DEMAND_DETAIL_VOLUMES          NO_PART_PREFIX                                   
DEMAND_DETAIL_VOLUMES          NO_PART_SUFFIX                                   
DEMAND_DETAIL_VOLUMES          ID_DEMAND                                        





                                                                   Cost              carnalict       byts
SELECT STATEMENT	 CHOOSE	 187963789	 45424046	 5950550051	 					
SORT(UNIQUE)	 	187963789	 45424046	 5950550051	 					
UNION-ALL	 									
HASH JOIN	 	50623	 8557901362	 1121085078422	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_DETAIL_VOLUMES_TEMP	 ANALYZED	 21703	 83706	 1590414	 					
TABLE ACCESS(FULL) SYS.DUAL	 	17	 8168	 						
HASH JOIN	 	21176	 4089500	 458024000	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_PPC_SUPPLIERS	 ANALYZED	 35	 14961	 1002387	 					
HASH JOIN	 	19125	 4089500	 184027500	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_PPC_REQUIREMENTS	 ANALYZED	 3198	 1175171	 14102052	 					
HASH JOIN	 	12702	 4089500	 134953500	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_ASSY_PLANTS	 ANALYZED	 2	 25	 250	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_SOURCINGS	 ANALYZED	 12681	 8649320	 198934360	 					
HASH JOIN	 	57753	 1	 156	 					
TABLE ACCESS(BY INDEX ROWID) MEDEMAND.DEMAND_SOURCINGS	 ANALYZED	 7	 11	 187	 					
NESTED LOOPS	 	25676	 1	 141	 					
NESTED LOOPS	 	25669	 1	 124	 					
HASH JOIN	 	25668	 1	 57	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_DETAIL_VOLUMES_TEMP	 ANALYZED	 21703	 83706	 2427474	 					
TABLE ACCESS(FULL) SYS.DUAL	 	17	 8168	 						
TABLE ACCESS(FULL) MEDEMAND.DEMAND_PPC_REQUIREMENTS	 ANALYZED	 3198	 1175171	 32904788	 					
TABLE ACCESS(BY INDEX ROWID) MEDEMAND.DEMAND_PPC_SUPPLIERS	 ANALYZED	 1	 1	 67	 					
INDEX(UNIQUE SCAN) MEDEMAND.DPS_PK	 ANALYZED	 	1	 						
INDEX(RANGE SCAN) MEDEMAND.DS_DPR_FK_I	 ANALYZED	 2	 14	 						
VIEW	 	32075	 965327	 14479905	 					
SORT(GROUP BY)	 	32075	 965327	 28959810	 					
HASH JOIN	 	10646	 6211396	 186341880	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_ASSY_PLANTS	 ANALYZED	 2	 25	 250	 					
INDEX(FAST FULL SCAN) MEDEMAND.DS_PK	 ANALYZED	 10618	 13137147	 262742940	 					



Reds
Thangam.

[Updated on: Mon, 20 August 2007 06:46]

Report message to a moderator

How can i make Fast [message #260692 is a reply to message #260139] Mon, 20 August 2007 13:31 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Oracle Version

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production



Table Desc 


desc medemand.DEMAND_DETAIL_VOLUMES_TEMP 
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- ----------------------
STD_MODEL_YEAR                          NUMBER(4)                                                                                                                                                                                     
CD_ASSY_PLANT                           VARCHAR2(5)                                                                                                                                                                                   
NO_PART_BASE                            VARCHAR2(9)                                                                                                                                                                                   
NO_PART_PREFIX                          VARCHAR2(7)                                                                                                                                                                                   
NO_PART_SUFFIX                          VARCHAR2(8)                                                                                                                                                                                   
QT_CPV_NORM                             NUMBER(9)                                                                                                                                                                                     
DT_TIME_PERIOD                 NOT NULL DATE                                                                                                                                                                                          

7 rows selected


desc medemand.DEMAND_PARTVOL_TEMP  
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- ------------------------
CD_SUPPLR_CODE                 NOT NULL VARCHAR2(5)                                                                                                                                                                                   
PC_PCT_BUSINESS                         NUMBER(3)                                                                                                                                                                                     
CD_ASSY_PLANT                           VARCHAR2(5)                                                                                                                                                                                   

3 rows selected


desc demand_ppc_requirements  
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- ------------------------
NO_VOL_SEQ                     NOT NULL NUMBER(10)                                                                                                                                                                                    
NO_PART_BASE                   NOT NULL VARCHAR2(9)                                                                                                                                                                                   
NO_PART_PREFIX                 NOT NULL VARCHAR2(7)                                                                                                                                                                                   
NO_PART_SUFFIX                 NOT NULL VARCHAR2(8)                                                                                                                                                                                   
CD_SUPPLR_CODE                 NOT NULL VARCHAR2(5)                                                                                                                                                                                   
DT_CREATION_DATE               NOT NULL DATE                                                                                                                                                                                          
DT_MODIFICATION_DATE           NOT NULL DATE                                                                                                                                                                                          
CD_ORIGINATION                          CHAR(2)                                                                                                                                                                                       
NA_PART                                 VARCHAR2(34)                                                                                                                                                                                  
NA_SUPPLR_NAME                          VARCHAR2(60)                                                                                                                                                                                  

10 rows selected


desc demand_sourcings 
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- -------------------------
NO_VOL_SEQ                     NOT NULL NUMBER(10)                                                                                                                                                                                    
CD_ASSY_PLANT                  NOT NULL VARCHAR2(5)                                                                                                                                                                                   
DT_EFF_DATE                    NOT NULL DATE                                                                                                                                                                                          
PC_PCT_BUSINESS                         NUMBER(3)                                                                                                                                                                                     
DT_CREATION_DATE               NOT NULL DATE                                                                                                                                                                                          
DT_MODIFICATION_DATE           NOT NULL DATE                                                                                                                                                                                          
CD_ORIGINATION                          CHAR(2)                                                                                                                                                                                       
CD_SOURCING_TYPE                        CHAR(1)                                                                                                                                                                                       
CD_MANUAL_ZERO                          CHAR(2)                                                                                                                                                                                       

9 rows selected


desc demand_assy_plants 
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- -----------------------------
CD_ASSY_PLANT                  NOT NULL VARCHAR2(5)                                                                                                                                                                                   
CD_ASSY_REGION                          VARCHAR2(3)                                                                                                                                                                                   
CD_CMMS                                 VARCHAR2(5)                                                                                                                                                                                   
NA_ASSY_PLANT                           VARCHAR2(50)                                                                                                                                                                                  
CD_CMMS_LAUNCHED                        CHAR(1)                                                                                                                                                                                       
CD_WERS_PROCUREMENT                     CHAR(5)                                                                                                                                                                                       
DT_CREATION_DATE                        DATE                                                                                                                                                                                          
DT_MODIFICATION_DATE                    DATE                                                                                                                                                                                          
CD_CREATE_USERID                        VARCHAR2(8)                                                                                                                                                                                   
CD_UPDATE_USERID                        VARCHAR2(8)                                                                                                                                                                                   

10 rows selected


desc demand_ppc_suppliers 
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- -----------------------------
CD_SUPPLR_CODE                 NOT NULL VARCHAR2(5)                                                                                                                                                                                   
NA_SUPPLR_NAME                          VARCHAR2(60)                                                                                                                                                                                  
NO_SUPPLR_PHONE                         VARCHAR2(10)                                                                                                                                                                                  
NO_SUPPLR_FAX                           VARCHAR2(10)                                                                                                                                                                                  
CD_PARENT_COMPANY                       CHAR(5)                                                                                                                                                                                       
NA_SUPPLR_PARENT                        VARCHAR2(60)                                                                                                                                                                                  
DT_CREATION_DATE               NOT NULL DATE                                                                                                                                                                                          
DT_MODIFICATION_DATE           NOT NULL DATE                                                                                                                                                                                          
CD_ORIGINATION                          CHAR(2)                                                                                                                                                                                       

9 rows selected


desc DEMAND_detail_volumes      
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- ----------------------------
CD_VEHICLE_LINE                NOT NULL CHAR(3)                                                                                                                                                                                       
STD_MODEL_YEAR                 NOT NULL NUMBER(4)                                                                                                                                                                                     
DT_TIME_PERIOD                 NOT NULL DATE                                                                                                                                                                                          
CD_ASSY_PLANT                  NOT NULL VARCHAR2(5)                                                                                                                                                                                   
NO_PART_BASE                   NOT NULL VARCHAR2(9)                                                                                                                                                                                   
NO_PART_PREFIX                 NOT NULL VARCHAR2(7)                                                                                                                                                                                   
NO_PART_SUFFIX                 NOT NULL VARCHAR2(8)                                                                                                                                                                                   
ID_DEMAND                      NOT NULL VARCHAR2(1)                                                                                                                                                                                   
QT_CPV_NORM                             NUMBER(9)                                                                                                                                                                                     
QT_CPV_FLEX                             NUMBER(9)                                                                                                                                                                                     
QT_FPV                                  NUMBER(9)                                                                                                                                                                                     
QT_SERVICE                              NUMBER(9)                                                                                                                                                                                     
CD_INCOMPLETE                           CHAR(1)                                                                                                                                                                                       
CD_AS_REQD                              CHAR(1)                                                                                                                                                                                       
CD_CPSC                                 VARCHAR2(9)                                                                                                                                                                                   
DT_CREATION_DATE               NOT NULL DATE                                                                                                                                                                                          
DT_MODIFICATION_DATE           NOT NULL DATE                                                                                                                                                                                          
QT_CPV_NORM_BASE                        NUMBER(9)                                                                                                                                                                                     
QT_CPV_FLEX_BASE                        NUMBER(9)                                                                                                                                                                                     
SCENARIO                                VARCHAR2(1) 


The Tables and total data's

desc medemand.DEMAND_DETAIL_VOLUMES_TEMP ======= 1674228
desc medemand.DEMAND_PARTVOL_TEMP  ============  6562901
desc demand_ppc_requirements  ================== 1175171
desc demand_sourcings ========================== 13137147
desc demand_assy_plants ======================== 62
desc demand_ppc_suppliers ====================== 14961
desc DEMAND_detail_volumes ===================== 4003585


Index List

MEDEMAND 	DAP_PK 	NORMAL 	MEDEMAND 	DEMAND_ASSY_PLANTS 	TABLE 	UNIQUE
MEDEMAND 	DDV_DPP_FK_I 	NORMAL 	MEDEMAND 	DEMAND_DETAIL_VOLUMES 	TABLE 	NONUNIQUE
MEDEMAND 	DDV_IDX_1 	NORMAL 	MEDEMAND 	DEMAND_DETAIL_VOLUMES 	TABLE 	NONUNIQUE
MEDEMAND 	DDV_IDX_2 	NORMAL 	MEDEMAND 	DEMAND_DETAIL_VOLUMES 	TABLE 	NONUNIQUE
MEDEMAND 	DDV_PK 	NORMAL 	MEDEMAND 	DEMAND_DETAIL_VOLUMES 	TABLE 	UNIQUE
MEDEMAND 	DPR_IDX_1 	NORMAL 	MEDEMAND 	DEMAND_PPC_REQUIREMENTS 	TABLE 	NONUNIQUE
MEDEMAND 	DPR_PK 	NORMAL 	MEDEMAND 	DEMAND_PPC_REQUIREMENTS 	TABLE 	UNIQUE
MEDEMAND 	DPR_UK 	NORMAL 	MEDEMAND 	DEMAND_PPC_REQUIREMENTS 	TABLE 	UNIQUE
MEDEMAND 	DPS_PK 	NORMAL 	MEDEMAND 	DEMAND_PPC_SUPPLIERS 	TABLE 	UNIQUE
MEDEMAND 	DS_DPR_FK_I 	NORMAL 	MEDEMAND 	DEMAND_SOURCINGS 	TABLE 	NONUNIQUE
MEDEMAND 	DS_PK 	NORMAL 	MEDEMAND 	DEMAND_SOURCINGS 	TABLE 	UNIQUE


Execution plan

SELECT STATEMENT	 CHOOSE	 187963789	 45424046	 5950550051	 					
SORT(UNIQUE)	 	187963789	 45424046	 5950550051	 					
UNION-ALL	 									
HASH JOIN	 	50623	 8557901362	 1121085078422	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_DETAIL_VOLUMES_TEMP	 ANALYZED	 21703	 83706	 1590414	 					
TABLE ACCESS(FULL) SYS.DUAL	 	17	 8168	 						
HASH JOIN	 	21176	 4089500	 458024000	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_PPC_SUPPLIERS	 ANALYZED	 35	 14961	 1002387	 					
HASH JOIN	 	19125	 4089500	 184027500	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_PPC_REQUIREMENTS	 ANALYZED	 3198	 1175171	 14102052	 					
HASH JOIN	 	12702	 4089500	 134953500	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_ASSY_PLANTS	 ANALYZED	 2	 25	 250	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_SOURCINGS	 ANALYZED	 12681	 8649320	 198934360	 					
HASH JOIN	 	57753	 1	 156	 					
TABLE ACCESS(BY INDEX ROWID) MEDEMAND.DEMAND_SOURCINGS	 ANALYZED	 7	 11	 187	 					
NESTED LOOPS	 	25676	 1	 141	 					
NESTED LOOPS	 	25669	 1	 124	 					
HASH JOIN	 	25668	 1	 57	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_DETAIL_VOLUMES_TEMP	 ANALYZED	 21703	 83706	 2427474	 					
TABLE ACCESS(FULL) SYS.DUAL	 	17	 8168	 						
TABLE ACCESS(FULL) MEDEMAND.DEMAND_PPC_REQUIREMENTS	 ANALYZED	 3198	 1175171	 32904788	 					
TABLE ACCESS(BY INDEX ROWID) MEDEMAND.DEMAND_PPC_SUPPLIERS	 ANALYZED	 1	 1	 67	 					
INDEX(UNIQUE SCAN) MEDEMAND.DPS_PK	 ANALYZED	 	1	 						
INDEX(RANGE SCAN) MEDEMAND.DS_DPR_FK_I	 ANALYZED	 2	 14	 						
VIEW	 	32075	 965327	 14479905	 					
SORT(GROUP BY)	 	32075	 965327	 28959810	 					
HASH JOIN	 	10646	 6211396	 186341880	 					
TABLE ACCESS(FULL) MEDEMAND.DEMAND_ASSY_PLANTS	 ANALYZED	 2	 25	 250	 					
INDEX(FAST FULL SCAN) MEDEMAND.DS_PK	 ANALYZED	 10618	 13137147	 262742940


This is Function

FUNCTION getQtrDates_FUNC (p_qtr_enddate IN DATE) 
	RETURN DATE
	IS
	ls_qtr_startdate DATE := '';
	std_temp_year DATE := '' ;
	
	BEGIN
	-- p_p_qtr_enddate -format will be '08/01/2008'
	std_temp_year := SUBSTR(p_qtr_enddate,7,10);
	
	IF p_qtr_enddate BETWEEN CONCAT(g_firstqtr,std_temp_year) AND CONCAT(g_firstqtrend,std_temp_year) THEN
  		ls_qtr_startdate:=CONCAT(g_firstqtr,std_temp_year);
		--dbms_output.put_line('Date = '||max_eff_date);
	ELSIF p_qtr_enddate BETWEEN CONCAT(g_secondqtr,std_temp_year) AND CONCAT(g_secondqtrend,std_temp_year) THEN
  		ls_qtr_startdate:=CONCAT(g_secondqtr,std_temp_year);
		--dbms_output.put_line('Date = '||max_eff_date);
	ELSIF p_qtr_enddate BETWEEN CONCAT(g_thirdqtr,std_temp_year) AND CONCAT(g_thirdqtrend,std_temp_year) THEN
  		ls_qtr_startdate:=CONCAT(g_thirdqtr,std_temp_year);
		--dbms_output.put_line('Date = '||max_eff_date);
	ELSIF p_qtr_enddate BETWEEN CONCAT(g_fourthqtr,std_temp_year) AND CONCAT(g_fourthqtrend,std_temp_year) THEN
  		ls_qtr_startdate:=CONCAT(g_fourthqtr,std_temp_year);
		--dbms_output.put_line('Date = '||max_eff_date);
  	END IF;

    RETURN ls_qtr_startdate;
    EXCEPTION
    WHEN OTHERS THEN
    ls_qtr_startdate := 'FAILURE' ;
    RETURN ls_qtr_startdate; 
	END ; -- end of getqtrdates_func


The below given query is old logic.It does not consider partvol>0(qt_cpv_norm>0) and current and future model years.

It is working fine fast.


SELECT
 DISTINCT a.cd_supplr_code,
 REPLACE(d.na_supplr_name, '&', 'AND')
FROM
   demand_ppc_requirements a,
   demand_sourcings b,
   demand_assy_plants c,
   demand_ppc_suppliers d
WHERE
            a.cd_supplr_code = d.cd_supplr_code
    AND a.no_vol_seq = b.no_vol_seq
    AND b.pc_pct_business > 0
    AND b.cd_assy_plant = c.cd_assy_plant
    AND b.dt_eff_date >= sysdate
    AND c.cd_assy_region = 'NA'

UNION

SELECT
    DISTINCT e.cd_supplr_code,
    REPLACE(e.na_supplr_name, '&', 'AND')
FROM
  (SELECT x.no_vol_seq no_vol_seq, MAX(x.dt_eff_date) dt_eff_date
   FROM demand_sourcings x, demand_assy_plants y
   WHERE x.cd_assy_plant = y.cd_assy_plant AND y.cd_assy_region = 'NA'
   GROUP BY x.no_vol_seq) a,
 demand_sourcings b,
 demand_ppc_requirements c,
 demand_ppc_suppliers e
WHERE
          a.no_vol_seq = b.no_vol_seq
  AND a.dt_eff_date = b.dt_eff_date
  AND b.pc_pct_business > 0
  AND b.no_vol_seq = c.no_vol_seq
  AND c.cd_supplr_code = e.cd_supplr_code 


This is my query it will take more time.please advice me how can make fast.

SELECT DISTINCT a.cd_supplr_code,
  REPLACE(d.na_supplr_name,   '&',   'AND')
FROM demand_ppc_requirements a,
  demand_sourcings b,
  demand_assy_plants c,
  demand_ppc_suppliers d,
  demand_detail_volumes ddv 
WHERE a.cd_supplr_code = d.cd_supplr_code
 AND a.no_vol_seq = b.no_vol_seq
 AND b.pc_pct_business > 0
 AND b.cd_assy_plant = c.cd_assy_plant
 and ddv.cd_assy_plant = c.cd_assy_plant
 AND b.dt_eff_date >= sysdate
 AND c.cd_assy_region = 'NA'
 AND NOT(ddv.qt_cpv_norm = 0 OR ddv.qt_cpv_norm IS NULL)
AND trunc(ddv.dt_time_period) >=to_date((select cpv2_ppc_shortfall_pkg.getQtrDates_FUNC (to_char(sysdate,'mm/dd/yyyy')) 
from dual),'mm/dd/yyyy')

UNION

SELECT distinct e.cd_supplr_code ,
  REPLACE(e.na_supplr_name,   '&',   'AND') 
FROM (select	x.no_vol_seq 	 no_vol_seq,
			max(x.dt_eff_date) dt_eff_date
			from medemand.demand_sourcings x,
			     medemand.demand_assy_plants y
     where 	x.cd_assy_plant 	= y.cd_assy_plant
		        and		y.cd_assy_region 	= 'NA'
			group
			by		x.no_vol_seq)a1,
    demand_ppc_suppliers e,
    demand_sourcings b,
    demand_detail_volumes t1,
    DEMAND_PPC_REQUIREMENTS dpr
WHERE 
a1.no_vol_seq = b.no_vol_seq
and a1.dt_eff_date = b.dt_eff_date
AND b.pc_pct_business > 0
AND b.no_vol_seq = dpr.no_vol_seq
AND dpr.cd_supplr_code = e.cd_supplr_code
and t1.no_part_base=dpr.no_part_base
and t1.no_part_prefix=dpr.no_part_prefix
and t1.no_part_suffix=dpr.no_part_suffix
and t1.qt_cpv_norm>0
AND trunc(t1.dt_time_period) >=to_date((select cpv2_ppc_shortfall_pkg.getQtrDates_FUNC (to_char(sysdate,'mm/dd/yyyy')) 
from dual),'mm/dd/yyyy')




Anyone please advice me how can i make simple query.

Thanks & Reds
Thangam.
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #260815 is a reply to message #260139] Tue, 21 August 2007 00:51 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Please Look into this anyone.

Reds
Thangam.
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #260818 is a reply to message #260139] Tue, 21 August 2007 00:58 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Please Look into this anyone.
Nobody owes you an answer.
What answer/silver bullet do you expect/desire/deserve?
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #260879 is a reply to message #260818] Tue, 21 August 2007 03:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can replace this
to_date((select cpv2_ppc_shortfall_pkg.getQtrDates_FUNC (to_char(sysdate,'mm/dd/yyyy')) 
from dual),'mm/dd/yyyy')
with this
cpv2_ppc_shortfall_pkg.getQtrDates_FUNC (trunc(sysdate))

Your function takes and returnds a DATE datatype, so your current plan to pass it in a varchar2, and perfrom a TO_DATE on the value it returns is bothe perverse and guaranteed to trip you up with date formats at some point in the future.
This change will also stop your query from performing a SELECT per row returned, which will speed things up.
You could probably speed things up further by expressing the logic in GetQtrDates_Func as a CASE statement and building it straight into the query.

As you have already been advised - loose the DISTINCT's - the UNION statement will do a distinct anyway, and there's no point doing two of them.
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #260979 is a reply to message #260139] Tue, 21 August 2007 07:44 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi all,

Now i am getting


Error "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP"


[Updated on: Tue, 21 August 2007 07:46] by Moderator

Report message to a moderator

Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #260983 is a reply to message #260979] Tue, 21 August 2007 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So enlarge your TEMP tablespace.

Regards
Michel
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261386 is a reply to message #260983] Wed, 22 August 2007 08:08 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi,


Now i have enlarged TEMP tablespace.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=37937 Card=1 Bytes=162)
   1    0   HASH JOIN (Cost=37937 Card=1 Bytes=162)
   2    1    TABLE ACCESS (BY INDEX ROWID) OF 'DEMAND_SOURCINGS' (Cost=20 Card=12 Bytes=204)
   3    2       NESTED LOOPS (Cost=10617 Card=2 Bytes=280)
   4    3         NESTED LOOPS (Cost=10597 Card=1 Bytes=123)
   5    4           HASH JOIN (Cost=10596 Card=1 Bytes=56)
   6    5             TABLE ACCESS (FULL) OF 'DEMAND_DETAIL_VOLUMES' (Cost=6562 Card=199431 Bytes=5584068)
   7    6               TABLE ACCESS (FULL) OF 'DUAL' (Cost=17 Card=8168)
   8    5             TABLE ACCESS (FULL) OF 'DEMAND_PPC_REQUIREMENTS'(Cost=3198 Card=1175600 Bytes=32916800)
   9    4           TABLE ACCESS (BY INDEX ROWID) OF 'DEMAND_PPC_SUPPLIERS' (Cost=1 Card=1 Bytes=67)
  10    9             INDEX (UNIQUE SCAN) OF 'DPS_PK' (UNIQUE)
  11    3         INDEX (RANGE SCAN) OF 'DS_PK' (UNIQUE) (Cost=2 Card=24)
  12    1     VIEW (Cost=27318 Card=539117 Bytes=11860574)
  13   12       SORT (GROUP BY) (Cost=27318 Card=539117 Bytes=16173510)
  14   13         HASH JOIN (Cost=10534 Card=5051390 Bytes=151541700)
  15   14           TABLE ACCESS (FULL) OF 'DEMAND_ASSY_PLANTS' (Cost=2 Card=20 Bytes=200)
  16   14           INDEX (FAST FULL SCAN) OF 'DS_PK' (UNIQUE) (Cost=10506 Card=13133613 Bytes=262672260)



Now also it is taking more time.

Reds
Thangam.
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261398 is a reply to message #261386] Wed, 22 August 2007 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dbms_xplan.display function to output the execution plan, it will give more informations.

Where do you get this plan? autotrace, explain plan, sql trace?

Regards
Michel
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261417 is a reply to message #261386] Wed, 22 August 2007 09:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Of course it's taking longer.
Previously it got part way through and ran out of space - now it gets all the way through to the end.
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261441 is a reply to message #261398] Wed, 22 August 2007 11:02 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Michal,

"set autotrace traceonly explain"


While i am executing total query i am getting this.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=450992851 Card=14553369018 Bytes=1891937972372)
   1    0   SORT (UNIQUE) (Cost=450992851 Card=14553369018 Bytes=1891937972372)
   2    1     UNION-ALL
   3    2       HASH JOIN (Cost=33727 Card=14553369017 Bytes=1891937972210)
   4    3         TABLE ACCESS (FULL) OF 'DEMAND_DETAIL_VOLUMES' (Cost=6562 Card=199431 Bytes=3589758)
   5    4           TABLE ACCESS (FULL) OF 'DUAL' (Cost=17 Card=8168)
   6    3         HASH JOIN (Cost=20475 Card=3502780 Bytes=392311360)
   7    6           TABLE ACCESS (FULL) OF 'DEMAND_PPC_SUPPLIERS' (Cost=35 Card=14905 Bytes=998635)
   8    6           HASH JOIN (Cost=18724 Card=3502780 Bytes=157625100)
   9    8             TABLE ACCESS (FULL) OF 'DEMAND_PPC_REQUIREMENTS'(Cost=3198 Card=1175600 Bytes=14107200)
  10    8             HASH JOIN (Cost=12702 Card=3502780 Bytes=115591740)
  11   10               TABLE ACCESS (FULL) OF 'DEMAND_ASSY_PLANTS' (Cost=2 Card=20 Bytes=200)
  12   10               TABLE ACCESS (FULL) OF 'DEMAND_SOURCINGS' (Cost=12681 Card=9107227 Bytes=209466221)
  13    2       HASH JOIN (Cost=37937 Card=1 Bytes=162)
  14   13         TABLE ACCESS (BY INDEX ROWID) OF 'DEMAND_SOURCINGS'(Cost=20 Card=12 Bytes=204)
  15   14           NESTED LOOPS (Cost=10617 Card=2 Bytes=280)
  16   15             NESTED LOOPS (Cost=10597 Card=1 Bytes=123)
  17   16               HASH JOIN (Cost=10596 Card=1 Bytes=56)
  18   17                 TABLE ACCESS (FULL) OF 'DEMAND_DETAIL_VOLUMES' (Cost=6562 Card=199431 Bytes=5584068)
  19   18                   TABLE ACCESS (FULL) OF 'DUAL' (Cost=17 Card=8168)
  20   17                 TABLE ACCESS (FULL) OF 'DEMAND_PPC_REQUIREMENTS' (Cost=3198 Card=1175600 Bytes=32916800)
  21   16               TABLE ACCESS (BY INDEX ROWID) OF 'DEMAND_PPC_SUPPLIERS' (Cost=1 Card=1 Bytes=67)
  22   21                 INDEX (UNIQUE SCAN) OF 'DPS_PK' (UNIQUE)
  23   15             INDEX (RANGE SCAN) OF 'DS_PK' (UNIQUE) (Cost=2 Card=24)
  24   13         VIEW (Cost=27318 Card=539117 Bytes=11860574)
  25   24           SORT (GROUP BY) (Cost=27318 Card=539117 Bytes=16173510)
  26   25             HASH JOIN (Cost=10534 Card=5051390 Bytes=151541700)
  27   26               TABLE ACCESS (FULL) OF 'DEMAND_ASSY_PLANTS' (Cost=2 Card=20 Bytes=200)
  28   26               INDEX (FAST FULL SCAN) OF 'DS_PK' (UNIQUE) (Cost=10506 Card=13133613 Bytes=262672260)





And please tell me how can i use this
"Use dbms_xplan.display function to output the execution plan, it will give more informations"


Reds
Thangam.
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261449 is a reply to message #261441] Wed, 22 August 2007 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use "explain plan for <your query>".
After the message "explained", use
select * from table(dbms_xplan.display);

For example:
SQL> explain plan for select * from emp where empno = 1;

Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    87 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=1)

14 rows selected.

Regards
Michel
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261680 is a reply to message #261449] Thu, 23 August 2007 05:45 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Michal,



 
----------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name                    | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                          |    14G|  1761G|   450M|
|   1 |  SORT UNIQUE                      |                          |    14G|  1761G|   450M|
|   2 |   UNION-ALL                       |                          |       |       |       |
|   3 |    HASH JOIN                      |                          |    14G|  1761G| 33727 |
|   4 |     TABLE ACCESS FULL             | DEMAND_DETAIL_VOLUMES    |   199K|  3505K|  6562 |
|   5 |      TABLE ACCESS FULL            | DUAL                     |  8168 |       |    17 |
|   6 |     HASH JOIN                     |                          |  3502K|   374M| 20475 |
|   7 |      TABLE ACCESS FULL            | DEMAND_PPC_SUPPLIERS     | 14905 |   975K|    35 |
|   8 |      HASH JOIN                    |                          |  3502K|   150M| 18724 |
|   9 |       TABLE ACCESS FULL           | DEMAND_PPC_REQUIREMENTS  |  1175K|    13M|  3198 |
|  10 |       HASH JOIN                   |                          |  3502K|   110M| 12702 |
|  11 |        TABLE ACCESS FULL          | DEMAND_ASSY_PLANTS       |    20 |   200 |     2 |
|  12 |        TABLE ACCESS FULL          | DEMAND_SOURCINGS         |  9106K|   199M| 12681 |
|  13 |    HASH JOIN                      |                          |     1 |   162 | 37937 |
|  14 |     TABLE ACCESS BY INDEX ROWID   | DEMAND_SOURCINGS         |    12 |   204 |    20 |
|  15 |      NESTED LOOPS                 |                          |     2 |   280 | 10617 |
|  16 |       NESTED LOOPS                |                          |     1 |   123 | 10597 |
|  17 |        HASH JOIN                  |                          |     1 |    56 | 10596 |
|  18 |         TABLE ACCESS FULL         | DEMAND_DETAIL_VOLUMES    |   199K|  5453K|  6562 |
|  19 |          TABLE ACCESS FULL        | DUAL                     |  8168 |       |    17 |
|  20 |         TABLE ACCESS FULL         | DEMAND_PPC_REQUIREMENTS  |  1175K|    31M|  3198 |
|  21 |        TABLE ACCESS BY INDEX ROWID| DEMAND_PPC_SUPPLIERS     |     1 |    67 |     1 |
|  22 |         INDEX UNIQUE SCAN         | DPS_PK                   |     1 |       |       |
|  23 |       INDEX RANGE SCAN            | DS_PK                    |    24 |       |     2 |
|  24 |     VIEW                          |                          |   539K|    11M| 27318 |
|  25 |      SORT GROUP BY                |                          |   539K|    15M| 27318 |
|  26 |       HASH JOIN                   |                          |  5051K|   144M| 10534 |
|  27 |        TABLE ACCESS FULL          | DEMAND_ASSY_PLANTS       |    20 |   200 |     2 |
|  28 |        INDEX FAST FULL SCAN       | DS_PK                    |    13M|   250M| 10506 |
----------------------------------------------------------------------------------------------
 
Note: cpu costing is off, PLAN_TABLE' is old version



Reds
Thangam.
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261688 is a reply to message #261680] Thu, 23 August 2007 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
PLAN_TABLE' is old version

Please rebuild your plan_table with the one in your $ORACLE_HOME/rdbms/admin/utlxplan.sql script and retry.

Regards
Michel
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261694 is a reply to message #261680] Thu, 23 August 2007 06:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I notice you've not yet taken my advice and replaced the
select cpv2_ppc_shortfall_pkg.getQtrDates_FUNC (to_char(sysdate,'mm/dd/yyyy')) 
from dual
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261704 is a reply to message #261694] Thu, 23 August 2007 06:50 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi JRowbottom,

Sorry for the delay information.


AND trunc(d.dt_time_period) >=to_date((select
            cpv2_ppc_shortfall_pkg.getQtrDates_FUNC (to_char(sysdate,'mm/dd/yyyy'))
            from dual),'mm/dd/yyyy');


Even i have execute like this also taking time.

" d.dt_time_period >='08-01-2007' "


Reds
Thangam.
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261705 is a reply to message #261704] Thu, 23 August 2007 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the plan for this latter query with a new plan_table.
And use to_date function on your constant.

Regards
Michel
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261708 is a reply to message #260139] Thu, 23 August 2007 06:56 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I think you will get a feel for the cause of your problem if you do the following:

SELECT count(*)
FROM demand_ppc_requirements a,
  demand_sourcings b,
  demand_assy_plants c,
  demand_ppc_suppliers d,
  demand_detail_volumes ddv 
WHERE a.cd_supplr_code = d.cd_supplr_code
 AND a.no_vol_seq = b.no_vol_seq
 AND b.pc_pct_business > 0
 AND b.cd_assy_plant = c.cd_assy_plant
 and ddv.cd_assy_plant = c.cd_assy_plant
 AND b.dt_eff_date >= sysdate
 AND c.cd_assy_region = 'NA'
 AND NOT(ddv.qt_cpv_norm = 0 OR ddv.qt_cpv_norm IS NULL)
AND trunc(ddv.dt_time_period) >=to_date((select cpv2_ppc_shortfall_pkg.getQtrDates_FUNC (to_char(sysdate,'mm/dd/yyyy')) 
from dual),'mm/dd/yyyy')


followed by:

SELECT COUNT(*)
FROM (select	x.no_vol_seq 	 no_vol_seq,
			max(x.dt_eff_date) dt_eff_date
			from medemand.demand_sourcings x,
			     medemand.demand_assy_plants y
     where 	x.cd_assy_plant 	= y.cd_assy_plant
		        and		y.cd_assy_region 	= 'NA'
			group
			by		x.no_vol_seq)a1,
    demand_ppc_suppliers e,
    demand_sourcings b,
    demand_detail_volumes t1,
    DEMAND_PPC_REQUIREMENTS dpr
WHERE 
a1.no_vol_seq = b.no_vol_seq
and a1.dt_eff_date = b.dt_eff_date
AND b.pc_pct_business > 0
AND b.no_vol_seq = dpr.no_vol_seq
AND dpr.cd_supplr_code = e.cd_supplr_code
and t1.no_part_base=dpr.no_part_base
and t1.no_part_prefix=dpr.no_part_prefix
and t1.no_part_suffix=dpr.no_part_suffix
and t1.qt_cpv_norm>0
AND trunc(t1.dt_time_period) >=to_date((select cpv2_ppc_shortfall_pkg.getQtrDates_FUNC (to_char(sysdate,'mm/dd/yyyy')) 
from dual),'mm/dd/yyyy')


You will find that because of all the non-unique joins in the query, you are returning millions of more rows than you strictly need. You need to get rid of any tables you don't need and try to ensure all the tables are being joined on unique keys.

For example, in the second query you are not joining on the full key of DEMAND_DETAIL_VOLUMES, so there is likely an element of cartesian product in there. Also, you pick the maximum no_vol_seq from DEMAND_SOURCINGS where cd_assy_region = 'NA'. But when you join it back to DEMAND_SOURCINGS (b), you do not include this condition. As a consequence, you are probably getting additional rows you don't need.

You could look at the possibility of using "where exists" clauses (providing there are indexes on the joining columns) rather than joins for those tables which contribute nothing to the result set.

Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261709 is a reply to message #261705] Thu, 23 August 2007 07:05 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Michal,

----------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name                    | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                          |    14G|  1761G|   450M|
|   1 |  SORT UNIQUE                      |                          |    14G|  1761G|   450M|
|   2 |   UNION-ALL                       |                          |       |       |       |
|   3 |    HASH JOIN                      |                          |    14G|  1761G| 33727 |
|   4 |     TABLE ACCESS FULL             | DEMAND_DETAIL_VOLUMES    |   199K|  3505K|  6562 |
|   5 |     HASH JOIN                     |                          |  3502K|   374M| 20475 |
|   6 |      TABLE ACCESS FULL            | DEMAND_PPC_SUPPLIERS     | 14905 |   975K|    35 |
|   7 |      HASH JOIN                    |                          |  3502K|   150M| 18724 |
|   8 |       TABLE ACCESS FULL           | DEMAND_PPC_REQUIREMENTS  |  1175K|    13M|  3198 |
|   9 |       HASH JOIN                   |                          |  3502K|   110M| 12702 |
|  10 |        TABLE ACCESS FULL          | DEMAND_ASSY_PLANTS       |    20 |   200 |     2 |
|  11 |        TABLE ACCESS FULL          | DEMAND_SOURCINGS         |  9106K|   199M| 12681 |
|  12 |    HASH JOIN                      |                          |     1 |   162 | 37937 |
|  13 |     TABLE ACCESS BY INDEX ROWID   | DEMAND_SOURCINGS         |    12 |   204 |    20 |
|  14 |      NESTED LOOPS                 |                          |     2 |   280 | 10617 |
|  15 |       NESTED LOOPS                |                          |     1 |   123 | 10597 |
|  16 |        HASH JOIN                  |                          |     1 |    56 | 10596 |
|  17 |         TABLE ACCESS FULL         | DEMAND_DETAIL_VOLUMES    |   199K|  5453K|  6562 |
|  18 |         TABLE ACCESS FULL         | DEMAND_PPC_REQUIREMENTS  |  1175K|    31M|  3198 |
|  19 |        TABLE ACCESS BY INDEX ROWID| DEMAND_PPC_SUPPLIERS     |     1 |    67 |     1 |
|  20 |         INDEX UNIQUE SCAN         | DPS_PK                   |     1 |       |       |
|  21 |       INDEX RANGE SCAN            | DS_PK                    |    24 |       |     2 |
|  22 |     VIEW                          |                          |   539K|    11M| 27318 |
|  23 |      SORT GROUP BY                |                          |   539K|    15M| 27318 |
|  24 |       HASH JOIN                   |                          |  5051K|   144M| 10534 |
|  25 |        TABLE ACCESS FULL          | DEMAND_ASSY_PLANTS       |    20 |   200 |     2 |
|  26 |        INDEX FAST FULL SCAN       | DS_PK                    |    13M|   250M| 10506 |
----------------------------------------------------------------------------------------------
 
Note: cpu costing is off, PLAN_TABLE' is old version.


Reds
Thangam.
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261711 is a reply to message #261709] Thu, 23 August 2007 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 23 August 2007 13:52
Post the plan for this latter query with a new plan_table.
And use to_date function on your constant.

Regards
Michel
Re: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast [message #261728 is a reply to message #260139] Thu, 23 August 2007 07:48 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Looking at the join conditions in more detail, they don't seem to make a lot of sense.

In the second query, you join DEMAND_PPC_REQUIREMENTS to demand_ppc_suppliers on three* columns. However, in the first query, you don't join these tables at all. The only joining column with DEMAND_PPC_REQUIREMENTS is the cd_assy_plant from demand_assy_plants. I'd hazard a guess that this is not particularly unique, so you are probably getting a cartesian product with millions of rows.

If you try out my suggestion of running count(*) against both parts of the query, you may find that the first part returns a lot more rows than the second.

*I take that back. It's actually two columns. One of the conditions is duplicated for some reason.

[Updated on: Thu, 23 August 2007 07:51]

Report message to a moderator

Previous Topic: [SOLVED] Little help with concat_all
Next Topic: spooling to file SP2-0734
Goto Forum:
  


Current Time: Mon Dec 05 04:46:18 CST 2016

Total time taken to generate the page: 0.12712 seconds