Performing fast search in huge oracle database

[vikas000000a]

Hi All,
I am new to this forum as far as posting a question is concerned, although I have quite frequently visiting this site as a guest.

My quertion relates to preforming fast searched in a very large oracle database. This is my company database. I am having two very huge tables containing crores of records. I have to match each record from one table (I'll call it base table from here onwards) to another table which may contain one or more (not more than 10) corresponding record(s). And accordingly, I need to update certain flags and fields in both the tables to indicate that the record in question has been matched (found) in the base table and vice-versa.

This is basically a re-conciliation type of activity I am doing. For this I have made a PL-SQL procedure, which works quite file as far as functionality is concerned. But the problem is that it takes too much time to do the matching. Usually, I run it at the time leaving office and it gets completed after the lunch on next day.

Also, I am having index on both the tables comprising of fields which are involved in matching. What I need is some speeding up techniques applicable to real big databases.

Although, I don't feel it is necessary, even then I am providing the structure of both the tables:

1. Base table:

CREATE TABLE TEMP_SMD_6404
(
  SODATE            DATE,
  CODE              VARCHAR2(4),
  PCD               CHAR(2),
  ORIGIN_DATE       DATE,
  REFERENCE_NO      VARCHAR2(10),
  AMOUNT            NUMBER(15,2),
  BM_POSTDATE       DATE,
  BM_TXNCNTR        NUMBER(5),
  NARRATION         VARCHAR2(100),
  CORE_POSTDATE     DATE,
  CORE_TXNCNTR      VARCHAR2(9),
  NPHASE            CHAR(1),
  NPROD             CHAR(2),
  CORE_FLAG         CHAR(1),
  NBRANCH           VARCHAR2(4),
  SYSTEM_INDICATOR  VARCHAR2(1),
  CIRCLE            NUMBER,
  MIG_DATE          DATE,
  EXTRA_FLAG        VARCHAR2(2)
)
LOGGING 
NOCACHE
NOPARALLEL;

First six fields comprising of a unique index and are involved in matching.


2. Table to be matched:

CREATE TABLE THMASTER_6404
(
  CODE          CHAR(4),
  PCD           CHAR(2),
  POSTDATE      NUMBER(8),
  TXNCNTR       NUMBER,
  TTYPE         CHAR(2),
  REFERENCE_NO  VARCHAR2(10),
  AMOUNT        NUMBER(15,2),
  USERID        CHAR(3),
  NARRATION     VARCHAR2(25),
  PROCESSED     CHAR(1),
  SUPID         CHAR(3),
  RECON_CNTR    NUMBER(7),
  SODATE        DATE,
  NEWFLAG       CHAR(1),
  NPROCESSED    CHAR(1),
  NRECON_CNTR   VARCHAR2(20),
  NPROD         CHAR(2),
  NPHASE        CHAR(1),
  NREFNO        VARCHAR2(10),
  NBRANCH       CHAR(4),
  SOUSERID      VARCHAR2(3)
)
LOGGING 
NOCACHE
NOPARALLEL;
 

First seven fields comprising of a unique index and are involved in matching.


Thanks in anticipation

[amitpatel66]

Hi Vikas,

Welcome to TSDN!!

Please make sure you follow POSTING GUIDELINES when ever you post in this forum

MODERATOR

[vikas000000a]

No reply as yet!!!
I am starting to loose my hope

[amitpatel66]

No reply as yet!!!
I am starting to loose my hope

Could you please post your procedure that is performing UPDATE/INSERT on these tables for my refernce, so that we could suggest why the procedure is taking so long?

I am aware that huge data is one cause but we would also like to check yur procedure?