MVSFORUMS.com

[basheerbaba]

Hi ,

I have two tables called table1, table2.

In both tables I have indexed columns and as well as non indexed columns.

in the select statement

if I give some index and non-index columns from table 1 and table2 then table 1 is getting Index scan ; table2 is getting table scan.

My doubt is why table2 only is getting table scan?

I think so I am clear with my doubt.

Please let me know any thing not clear.

Thanks for help in advance.

Basheer.

[acevedo]

you should post your INDEX declarations and your WHERE...
what about the FIELDs definitions? are the Working Storage fields definitions EXACTLY as those declared in the DB2 Table? what about Statistics?

[basheerbaba]

Hi acevedo

Thanks for replay!!!!!!!

Please find the declarations of tables and indexes and as well as query

Table1 declaration:

[basheerbaba]

Hi acevedo

Thanks for replay!!!!!!!

Please find the declarations of tables and indexes and as well as query

Table1 declaration:

EXEC SQL DECLARE Table1 TABLE
( col1 CHAR(10) NOT NULL,
col2 CHAR(50) NOT NULL,
col3 TIMESTAMP NOT NULL
) END-EXEC.



Table2 declaration:

EXEC SQL DECLARE TABLE2 TABLE
( col1 CHAR(10) NOT NULL,
col2 CHAR(6) NOT NULL,
col3 CHAR(6) NOT NULL,
col4 CHAR(1) NOT NULL,
col5 CHAR(4) NOT NULL,
col6 CHAR(8 ) NOT NULL,
col7 CHAR(6) NOT NULL,
col8 DECIMAL(4, 0) NOT NULL,
col9 CHAR(1) NOT NULL,
col10 CHAR(50) NOT NULL,
col11 CHAR(65) NOT NULL,
col12 CHAR(56) NOT NULL,
col13 CHAR(3) NOT NULL,
col14 TIMESTAMP NOT NULL
) END-EXEC.


In Table1 Col1 indexed by Index1
Col2 indexed by Index2
Col3 indexed by Index3
Col6 indexed by Index6
Col7 indexed by Index7
Col8 indexed by Index8

In Table2 Col2 indexed by Indexa



SELECT T1.col1,
T2.col6,
T2.col7,
T2.col3,
T2.col1,
T2.col8,
T2.col4 , -------------------------> Non-indexed column
T1.col1, -------------------------> Non-indexed column

FROM Table2 T2, Table1 T1
WHERE T2.col1 = T1.col1
AND ( T2.col1 >= ?
AND T2.col1 <= ? )
AND ( T2.col2 >= ?
AND T2.col2 <= ? )
AND ( T2.col3 >= ?
AND T2.col3 <= ? )
AND
( T2.col1 > ?
OR ( T2.col1 >= ? AND
T2.col2 > ? )
OR ( T2.col1 >= ? AND
T2.col2 >= ? AND
T2.col3 > ? )
OR ( T2.col1 >= ? AND
T2.col2 >= ? AND
T2.col3 >= ? AND
T2.col6 > ? )
OR ( T2.col1 >= ? AND
T2.col2 >= ? AND
T2.col3 >= ? AND
T2.col6 >= ? AND
T2.col7 > ? )
OR ( T2.col1 >= ? AND
T2.col2 >= ? AND
T2.col3 >= ? AND
T2.col6 >= ? AND
T2.col7 >= ? AND
T2.col8 > ? )
)
ORDER BY T2.col1 ASC,
T2.col2 ASC,
T2.col3 ASC,
T2.col6 ASC,
T2.col7 ASC,
T2.col8 ASC
FOR FETCH ONLY WITH UR

[videlord]

1. the select columns have no impact on index scan unless you want to index scan only.
2. focus on your where clause