No of records in ASUK – 6236353
No of records in TBLM – 61351411
No of records in ACH - 28445032
Please find below the index details.
Code:
INDEXNAME UNIQUERULE COLCOUNT CLUSTERING CLUSTERED PGSIZE SPACE CLUSTERRATIO
UKI1UMV1 P 5 Y Y 4096 1844640 97
UKI1UMV2 D 5 N N 4096 990000 92
UKI1CON1 P 2 N N 4096 0 0
UKI1CON2 D 6 Y Y 4096 0 0
UKI1CON3 D 7 N N 4096 0 0
UKI1CON4 U 4 N N 4096 0 0
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
You are dealing with tables with data in millions ... there will be performance issues ... asked for definitions of index which are used as per your explain ... try the below query too .. use the conditions on each table based on the index defined on them .. if not remove the conditions where you can improve performance.
Code:
SELECT COLS FROM
(SELECT COLS FROM ASUK
WHERE COMPANY = 'AB'
AND CODE IN ('RRR','TRE','PRP','CON',
'RRT','RRU','RRV','RRW')) AS U
,
(SELECT COLS FROM ACH
WHERE COMPANY = 'AB'
AND CODE IN ('RRR','TRE','PRP','CON',
'RRT','RRU','RRV','RRW')
AND DDATE >= :S_DATE ) AS C
,(SELECT COLS FROM TBLM WHERE CODE
IN ('RRR','TRE','PRP','CON',
'RRT','RRU','RRV','RRW')) AS M
WHERE
M.CODE = U.CODE
AND M.UH_NBR = U.UH_NBR
AND C.UH_NBR = U.UH_NBR
Select only those columns which are required, the idea is to reduce the number of rows required for the final join.
Please find below the additional details of PLAN_TABLE report and index details as you requested.
Code:
QUERYNO QBLOCKNO PLANNO METHOD TNAME
1 1 1 0 ASUK
1 1 2 1 TBLM
1 1 3 2 ACH
1 1 4 3 ----
ACCESSTYPE MATCHCOLS INDEXONLY SORTC_UNIQ
N 2 Y N
I 3 N N
I 2 Y N
- 0 N Y
SORTC_ORDERBY TSLOCKMODE PREFETCH MIXOPSEQ
N IS -- 0
N IS -- 0
N IS -- 0
Y -- -- 0
CORRELATION_NAME PAGE_RANGE QBLOCK_TYPE ACCESSNAME
U Y SELECT TTYNFBC3
C - SELECT TTYNCO4
M - SELECT TTYNUHA1
- - SELECT -------
Code:
INDEXNAME UNIQUERULE COLCOUNT CLUSTERING CLUSTERED PGSIZE SPACE CLUSTERRATIO FIRSTKEYCARD FULLKEYCARD
TTYNUHA1 P 5 Y Y 4096 1844640 97 2217 36525187
TTYNCO4 U 4 N N 4096 0 0 -1 -1
TTYNFBC3 U 6 N N 4096 216000 94 43
FROM ACH
WHERE COMPANY = 'AB'
AND CODE IN ('RRR','TRE','PRP','CON',
'RRT','RRU','RRV','RRW')
AND DDATE >= :S_DATE ) AS C
,(SELECT 1 FROM TBLM WHERE CODE
IN ('RRR','TRE','PRP','CON',
'RRT','RRU','RRV','RRW')) AS M
WHERE
M.CODE = U.CODE
AND M.UH_NBR = U.UH_NBR
AND C.UH_NBR = U.UH_NBR
Can you please let me know whether the above query is qhat you are referring to? Also we do not select any columns from TBLM table so I have selected 1 is it fine?
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
Did you atleast try the query you have posted above ? If you are selecting 1 how do you plan to join M with U ?
No more spoon feeding; try try and try until you find a good improved SQL.
If you face any difficulties ppl are here to help ; you should be doing your part too.