I added functionality that checks a table, that I can only read from, and it has 27 million records and it has added a lot of time to my process. I was thinking on possibly using my source query to get all the records I need and then put those records into a local table. From that local table(typically 1.7 million records) I can bring in the ID of this huge table. That's all it does...It checks for an ID passing in parameters from the source query and if it has an ID I mark the Destination column as 1 or 0....
I put all my source query records into a local table and I was thinking maybe I can put the ID of this other large table into this local table as well? When I do a Left Join it gives me back way more than the 1.7 I already have and that's the number I need from the source query. All I want to do is add a field into this temp table from another large table where the POLNBRs equal...
---Here is my query to make my local table
INSERT INTO COVERAGETEST(
CARRID, POLNBR , COV_EFFDT , COV_EXPDT , CVG_ID , CVG_POL_SEQ , STATUS , CARR_ISSUING_ADDR_TYPE , CARR_ISSUING_STREET , CARR_ISSUING_CITY , CARR_ISSUING_STATE_CD , CARR_ISSUING_ZIP , WRAP_UP_IND
, TXN_CODE , LCF_IND , NAME, e.ENDORSE_ID)
SELECT c.CARRID, c.POLNBR , c.COV_EFFDT , c.COV_EXPDT , c.CVG_ID , c.CVG_POL_SEQ , c.STATUS , c.CARR_ISSUING_ADDR_TYPE , c.CARR_ISSUING_STREET , c.CARR_ISSUING_CITY , c.CARR_ISSUING_STATE_CD , c.CARR_ISSUING_ZIP
, c.WRAP_UP_IND , c.TXN_CODE , c.LCF_IND , car.NAME
from COVERAGE c
INNER JOIN CARR car ON c.CARRID = car.CARRID
WHERE c.COV_EXPDT > '25-JUN-09' AND (STATUS != 'V')
ORDER BY c.CARRID ASC, c.COV_EFFDT ASC;
--This yields the 1.7 records
--I want to add the ENDORSEID below to the query above, if I can....
--I tried a LEFT JOIN But it obviously returned me many more rows then I was looking for
SELECT ENDORSE.ENDORSE_ID from ENDORSE where ENDORSE.POLNBR= :pPOLNBR AND ENDORSE.COV_EFFDT= :pCOV_EFFDT AND ENDORSE.ENDORSE_CD_NBR = '290307'
When I put this in my process the way it is now it added 2 hours to a 2.5 hour process. Im assuming its because of the large table being queried.
I really need help with this one.
Any help would really be appreciated!!