|
View previous topic :: View next topic
|
| Author |
Message |
praveenbl22
Currently Banned
New User
Joined: 26 Sep 2007
Posts: 24
Location: Bangalore
|
|
|
|
Hi,
How to avoid the DB2 Optimizer perfroming the Table scan
as the table data is very large. The table query will be
accessed online.
Select A.C1,A.C2,A.C3, A.C4
From tbl1 A,tbl2 b ,tbl3 C
Where C.REGION= :DECLGEN-region and
B.date <> '00000000'
and( (B.date < :DCLGEN-VAR1)
oR
(B.date < :DCLGEN-VAR1
and A.IND = 'Y')
In the above table columns C1 C2 C3 and C4 are Indexed
The Primary key to all the table is C1 and C2
There will be any performance improvements if we index the coulmns
REGION,IND and DATE to perform fetch faster.
But IND wil have only two values 'y' or 'n'
Date can greater than Current date by 2 months
and less than Current Date by 6 months
In the table tbl1 there 1,38000 records and 69,000 pages to perform the table scan
And the Cardinality for the tbl2 is 209685.
Can you help in performance tuning.
Other way is to get the coulmns statistic by runing the RUNSTATS
Can body give any hints on how will this improve the performance |
|
| Back to top |
|
 |
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
That is a tiny table.... our production tables run 2,000,000 plus on the row counts.
But anyway, indexing your date files should help your performance somewhat. |
|
| Back to top |
|
|
praveenbl22
Currently Banned
New User
Joined: 26 Sep 2007
Posts: 24
Location: Bangalore
|
|
|
|
Hi stodolas,
Do Indexing the Columns like B.DATE, A.IND and C.REGION is a Better option. The A.IDN columns contains only two values 'Y' or 'N" |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
I saw that B.Date was the used twice. B.Date must not be a date format in DB2, because '00000000' couldn't possibly be stored in a date field there.
Indexes should improve the performance slightly, but I can't say how much. Indexes should be build on columns that are used in the WHERE of an SQL. |
|
| Back to top |
|
|
mahsug
New User
Joined: 01 Jul 2003
Posts: 15
Location: Phoenix , AZ
|
|
|
|
Hi Praveen,
I have a little bit difficulty in understanding the scenario , but with the limited understanding , here it goes.
The below query is not exhibiting any join conditions , I could see functional logics , but not the join conditions. If there is no join conditions , then the query wud result in cartition product.
I would recommend you to look at the access path of your query.
Regards,
Mahesh |
|
| Back to top |
|
|
praveenbl22
Currently Banned
New User
Joined: 26 Sep 2007
Posts: 24
Location: Bangalore
|
|
|
|
Hi mahsug,
Can you see the changed query below with join predicate
Plan uses tablespace scan which may take long time.
The Columns C.REGION, A.IND and B.DATE are not indexed
Do indexing this coulmns will have performance imporvement.
Plan also does Sequential prefetch has the coulmns are not indexed.
Do prefetch actualy executes the query before the programs run and keeps the result in the buffer pool?
How DB2 evaluates the cost when Host variables are used in the query?
What is REPOPT?
| Code: |
SELECT A.C1,A.C2,A.C3, A.C4
FFROM TBL1 A, TBL2 b ,TBL3 C
WHERE
-- Join Predicates the coulmns C1 and C2 are the Primary keys of the
-- three tables and TBL1 is indexed by C1 , C2 , C3 and C4
(A.C1 = B.C1 AND
A.C2 = B.C2 AND
A.C1 = C.C1 AND
A.C2 = C.C2 AND )
C.REGION= :DECLGEN-REGION
AND B.DATE <> '00000000'
AND (( B.DATE < :DCLGEN-VAR1)
OR
(B.DATE < :DCLGEN-VAR1
AND A.IND = 'Y')
|
|
|
| Back to top |
|
|
|
|
