Home » SQL & PL/SQL » SQL & PL/SQL » merge does not use index
merge does not use index [message #242953] Tue, 05 June 2007 10:46 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
MERGE
  INTO IND_SU D
  USING
  (
    SELECT PPI_INDEX_SID,
           TREE_VERSION_SID,
           INTERIM_IRM,
           SURVEY_UNIT_CODE,
           SURVEY_UNIT_INDEX_WGT
      FROM TEMP_EST_INDEX_SU
      WHERE INTERIM_IRM IN
      (
        '200609','200605'
      )
      AND JOB_SID = 164
  ) S
  ON
  (
        D.TREE_VERSION_SID = S.TREE_VERSION_SID
    AND D.PPI_INDEX_SID = S.PPI_INDEX_SID
    AND D.SURVEY_UNIT_CODE = S.SURVEY_UNIT_CODE
    AND D.OFFICIAL_INDEX_IRM = S.INTERIM_IRM
  )
  WHEN MATCHED THEN
    UPDATE
      SET D.SURVEY_UNIT_INDEX_WGT = S.SURVEY_UNIT_INDEX_WGT
  WHEN NOT MATCHED THEN
    INSERT
    (
      D.PPI_INDEX_SID,
      D.TREE_VERSION_SID,
      D.OFFICIAL_INDEX_IRM,
      D.SURVEY_UNIT_CODE,
      D.SURVEY_UNIT_INDEX_WGT
    )
    VALUES
    (
      S.PPI_INDEX_SID,
      S.TREE_VERSION_SID,
      S.INTERIM_IRM,
      S.SURVEY_UNIT_CODE,
      S.SURVEY_UNIT_INDEX_WGT
    );

CREATE INDEX IDX1_TEMP_EST_INDEX_SU
    ON TEMP_EST_INDEX_SU(INTERIM_IRM)
TABLESPACE xx_DATA
LOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCOMPRESS
/

MERGE STATEMENT ()	[NULL]
 MERGE ()	INDEX_SU
  VIEW ()	[NULL]
   HASH JOIN (OUTER)	[NULL]
    TABLE ACCESS (FULL)	TEMP_EST_INDEX_SU
    TABLE ACCESS (FULL)	INDEX_SU



I am trying to do a merge and the explain plan shows that it does not use the index. could any one tell me why.

Ananthi
Re: merge does not use index [message #242961 is a reply to message #242953] Tue, 05 June 2007 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because it calculates that the hash join is a better access path than a nested loops with the index.

Btw, is this normal that your table is IND_SU and INDEX_SU in the plan?

Regards
Michel
Re: merge does not use index [message #242962 is a reply to message #242953] Tue, 05 June 2007 11:06 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
Thanks for the reply.

I should have typed index_su... but missed ex. sorry.


Ananthi
Re: merge does not use index [message #242975 is a reply to message #242953] Tue, 05 June 2007 11:28 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

Is hash join always better. Would there be any performance perblem when say for example index_su table has 6000000 rows.

This table grows with 6 million records every year.

Ananthi
Re: merge does not use index [message #242982 is a reply to message #242975] Tue, 05 June 2007 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Regularly gather statistics on your tables, Oracle will adapt the plan with them.

Regards
Michel
Re: merge does not use index [message #242991 is a reply to message #242953] Tue, 05 June 2007 12:16 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
one more question...

since these tables get around 6 million records... we want to partition by year.

our irm column is varchar2(6) - YYYYMM stored like '200606'.

CREATE TABLE index_su
( INDEX_SID NUMBER(9) NOT NULL,
tree_SID NUMBER(9) NOT NULL,
IRM VARCHAR2(6) NOT NULL,
SU_CODE VARCHAR2(11) NOT NULL,
SU_INDEX_WGT NUMBER NOT NULL)
PARTITION BY RANGE(IRM)
(
PARTITION irm_in_2006_or_before VALUES LESS THAN TO_DATE(‘2006’, ‘YYYYMM’) TABLESPACE year2006,
PARTITION irm_in_2007 VALUES LESS THAN TO_DATE(‘2007’, ‘YYYYMM’) TABLESPACE year2007,
PARTITION irm_in_2008 VALUES LESS THAN TO_DATE(‘2008’, ‘YYYYMM’) TABLESPACE year2008,
PARTITION irm_in_2009 VALUES LESS THAN TO_DATE(‘2009’, ‘YYYYMM’) TABLESPACE year2009,
PARTITION irm_in_2010 VALUES LESS THAN TO_DATE(‘2010’, ‘YYYYMM’) TABLESPACE year2010,
PARTITION irm_IN_2011_OR_LATER VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE) TABLESPACE year2011)
)
ENABLE ROW MOVEMENT;

would there be any perfomance problem because of the to_date convertion to find the records less than certain date.

Re: merge does not use index [message #242993 is a reply to message #242991] Tue, 05 June 2007 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
6 million rows is not so much, you likely does not need to partition for performances.

Regards
Michel
Re: merge does not use index [message #243068 is a reply to message #242993] Tue, 05 June 2007 22:17 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There will be no TO_DATE conversion at runtime. The date expressions you used when creating the partitions are cast to VARCHAR2 (ie. the same type as the partition key) when you build the partitions.

As you insert data, the VALUES LESS THAN work on a purely VARCHAR2 comparison.

Ross Leishman
Previous Topic: Complex Sql
Next Topic: Date field causing duplicate rows in output
Goto Forum:
  


Current Time: Fri Dec 09 15:55:20 CST 2016

Total time taken to generate the page: 0.31847 seconds