Anjani K Jha
New User
Joined: 20 Dec 2007 Posts: 2 Location: Jacksonville, FL
|
|
|
|
dick scherrer wrote: |
Hello and welcome to the forum,
Suggest you post a bit of sample data from the tables involved and the results you want from that sample data when your query is processed.
Mention the keys for the tables and the number of rows in each table. How many rows do you expect to be referenced in this query?
When posting data or code, use the "Code" tag to improve readability and preserve alignment. |
Hi Dick, As per your instructions I am attaching the file with code, table structure and other relevant info. Please let me know if I can provide further information. Looking for your insights to straighten this query.
Thanks in advance
attachment inlined ( with the code tags ) and deleted
Code: |
Table #1
personemail
no. of rows ==> more than 3million rows
PRSN_ID+EMAIL_AD --> primary key
sample data:
(PRSN_ID, EMAIL_AD, SYS_LOAD_DT, LOAD_DATAFILEVERSIONID) VALUES
('3159731', 'bbccdd@hotmail.com', '9/16/1998 10:09:41 AM', '239'),
('3166167', 'kexmona@msn.com', '9/23/1998 1:09:14 AM', '242'),
('2527483', 'jklmno@aol.com', '9/23/1998 1:09:16 AM', '242'),
('3156472', 'effgh@rocketmail.com', '9/23/1998 1:09:31 AM', '242'),
('2581476', 'abcdeff.morant@nationsbank.com', '9/30/1998 2:09:00 AM', '247')
Table #2
person
no. of rows ==> around 92Million
PRSN_ID --> primary key
sample data:
(PRSN_ID, PARTY_ID, ACC_TAXID_NO, ACC_TAXID_TYPE, DIR_MKT_BLK_ML_IN, DIR_MKT_BLK_EML_IN, DIR_MKT_BLK_OPH_IN, DIR_MKT_BLK_SS_IN, AFFL_SHARE_IN, DECS_IN, FTR_PRVCY_BLK_1, FTR_PRVCY_BLK_2, FTR_PRVCY_BLK_3, FTR_PRVCY_BLK_4, SYS_LOAD_DT, SYS_ASOF_DT, LOAD_DATAFILEVERSIONID, ASOF_DATAFILEVERSIONID, TRANS_TYPE, SSN_TIN, AFFL_MARKET_IN, AFFL_MARKET_ASOF_DT, SB1_IN, SB1_ASOF_DT, SB1_EXPIRY_DT, JOINT_MARKET_IN, JOINT_MARKET_ASOF_DT, JOINT_MARKET_EXPIRY_DT, AFFL_SHARE_EXPIRY_DT, AFFL_MARKET_EXPIRY_DT, EMAIL_ASOF_DT, PHONE_ASOF_DT, SS_ASOF_DT, MAIL_CAPTURE_DT, EMAIL_CAPTURE_DT, PHONE_CAPTURE_DT, SS_CAPTURE_DT, AFFL_SHARE_CAPTURE_DT, AFFL_MARKET_CAPTURE_DT, MAIL_REVERSAL_REQ_DT, EMAIL_REVERSAL_REQ_DT, PHONE_REVERSAL_REQ_DT, AFFL_SHARE_REVERSAL_REQ_DT, AFFL_MARKET_REVERSAL_REQ_DT, SB1_CAPTURE_DT, JOINT_MARKET_CAPTURE_DT, AFFL_SHARE_ASOF_DT, MAIL_ASOF_DT, DEC_CAPTURE_DT, DEC_REVERSAL_DT, FTR_PRVCY_BLK_1_CAPT_DT, FTR_PRVCY_BLK_1_ASOF_DT) VALUES
('1', NULL, NULL, 'S', '1', '1', '0', '1', '1', '0', '1', '1', '1', '1', '8/10/1997 6:08:11 PM', '11/13/2010 3:15:57 AM', '1', '17091', 'C', '503661431', '1', NULL, '1', NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '2/15/2005 12:00:00 AM', NULL, NULL, NULL, '2/15/2005 12:00:00 AM', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
('2', NULL, NULL, NULL, '0', '0', '0', '1', '0', '1', '1', '1', '1', '1', '8/10/1997 6:08:11 PM', '5/16/2010 9:03:22 AM', '1', '1', 'B', NULL, '0', '5/30/2008 12:00:00 AM', '0', '4/4/2009 12:00:00 AM', NULL, '0', '4/4/2009 12:00:00 AM', NULL, NULL, NULL, '9/22/1997 12:00:00 AM', '5/16/2010 12:00:00 AM', NULL, '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', NULL, '9/22/1997 12:00:00 AM', '5/30/2008 12:00:00 AM', NULL, NULL, NULL, NULL, NULL, '4/4/2009 12:00:00 AM', '4/4/2009 12:00:00 AM', '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', NULL, NULL, NULL, NULL),
('3', NULL, NULL, NULL, '0', '0', '0', '1', '0', '1', '1', '1', '1', '1', '8/10/1997 6:08:11 PM', '9/22/1997 5:09:47 PM', '1', '1', 'B', NULL, '0', '5/30/2008 12:00:00 AM', '0', '4/4/2009 12:00:00 AM', NULL, '0', '4/4/2009 12:00:00 AM', NULL, NULL, NULL, '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', NULL, '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', NULL, '9/22/1997 12:00:00 AM', '5/30/2008 12:00:00 AM', NULL, NULL, NULL, NULL, NULL, '4/4/2009 12:00:00 AM', '4/4/2009 12:00:00 AM', '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', NULL, NULL, NULL, NULL),
('4', NULL, NULL, 'S', '1', '1', '0', '1', '1', '0', '1', '1', '1', '1', '8/10/1997 6:08:12 PM', '9/22/1997 5:09:47 PM', '1', '689', 'C', '241729008', '1', NULL, '1', NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '9/22/1997 12:00:00 AM', NULL, NULL, NULL, '9/22/1997 12:00:00 AM', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
('6', NULL, NULL, 'S', '1', '1', '0', '1', '1', '0', '1', '1', '1', '1', '8/10/1997 6:08:12 PM', '9/22/1997 5:09:47 PM', '1', '2761', 'C', '246666478', '1', NULL, '1', NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '9/22/1997 12:00:00 AM', NULL, NULL, NULL, '9/22/1997 12:00:00 AM', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
Table #3
datafileversion
no. of rows ==> approx. 17K
DATAFILEVERSIONID --> primary key
sample data:
(DATAFILEVERSIONID, FILESIZE, FILEDATETIME, DATASOURCE_ID) VALUES
('4381', '27106512', '2/12/2005 1:58:27 AM', '104'),
('4382', '51165539', '2/10/2005 12:45:01 PM', '102'),
('4383', '60340525', '2/14/2005 3:27:12 AM', '13'),
('4384', '216805', '2/14/2005 8:00:22 AM', '107'),
('4385', '285653', '2/14/2005 11:59:33 AM', '26')
Table #4
bm_nonbm_email
no. of rows ==> approx. 34 million
no primary key --> primary key
sample data:
(SOURCE_ID, PARTY_ID, EMAIL_ADDRESS, CAPTURE_DT, SYS_LOAD_DT) VALUES
('6', '00000001119', 'XXXXdbm@aol.com', '6/7/2003 5:06:38 AM', '3/4/2004 11:03:24 AM'),
('6', '00000006441', 'yyyylltd@n-jcenter.com', '5/29/2003 2:05:17 PM', '3/4/2004 11:03:24 AM'),
('6', '00000011496', 'xyz2001cld@aol.com', '1/23/2004 1:01:20 PM', '3/4/2004 11:03:24 AM'),
('6', '00000012749', 'abcdtee@yahoo.com', '11/28/2008 10:17:36 AM', '12/4/2008 5:00:03 AM'),
('6', '00000015721', 'aaaa@XXX.com', '9/16/2008 11:50:34 PM', '9/18/2008 5:00:03 AM')
Result is expected as below:
('6', '00000001119', 'XXXXdbm@aol.com', '6/7/2003 5:06:38 AM', '3/4/2004 11:03:24 AM')
|
|
|