Is it something wrong (Index) ?
Hi,
Basically I have a table with a composite index on scn, bl_no,
other_bl, however, when I try to select some data out, it seems like the
database is not using the index ... I'm just wondering if anyone could
explain to me what's happening ?
Below is the output of the sqexplain.out :-
QUERY:
------
select * from man_hdr
where scn = "123"
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) nasa.man_hdr: INDEX PATH
(1) Index Keys: scn bl_no other_bl
Lower Index Filter: nasa.man_hdr.scn = '123'
QUERY:
------
select * from man_hdr
where bl_no = "123"
Estimated Cost: 2
Estimated # of Rows Returned: 5
1) nasa.man_hdr: SEQUENTIAL SCAN ---> How come it is not using index
path ???
Filters: nasa.man_hdr.bl_no = '123'
QUERY:
------
select * from man_hdr
where other_bl = "123"
Estimated Cost: 2
Estimated # of Rows Returned: 5
1) nasa.man_hdr: SEQUENTIAL SCAN ---> How come it is not using index
path ???
Filters: nasa.man_hdr.other_bl = '123'
QUERY:
------
select * from man_hdr
where scn = "123"
and bl_no = "123"
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) nasa.man_hdr: INDEX PATH
(1) Index Keys: scn bl_no other_bl
Lower Index Filter: (nasa.man_hdr.scn = '123' AND
nasa.man_hdr.bl_no = '
123' )
QUERY:
------
select * from man_hdr
where scn = "123"
and other_bl = "123"
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) nasa.man_hdr: INDEX PATH
(1) Index Keys: scn bl_no other_bl (Key-First)
Lower Index Filter: nasa.man_hdr.scn = '123'
Key-First Filters: (nasa.man_hdr.other_bl = '123' )
QUERY:
------
select * from man_hdr
where bl_no = "123"
and other_bl = "123"
Estimated Cost: 2
Estimated # of Rows Returned: 3
1) nasa.man_hdr: SEQUENTIAL SCAN ---> How come it is not using index
path ???
Filters: (nasa.man_hdr.bl_no = '123' AND nasa.man_hdr.other_bl =
'123' )
From the above sqexplain.out, as long as the select statement
contains the first column of the composite index, it will then use the
index path, else it will be a sequential path !???
The engine can only use an index if the beginning of that index's key is
included in
the filter conditions so yes, if the first column is named it is used otherwise
not.
So the index can be used to filter on the first column, the first two columns,
or on
all three columns but not on the second or third column or both and if the
filter is
on the first and third columns the index may only be used to filter for the
first
column and the third column compared to the resulting records for further
filtering.
You could create three versions of the index with each column as the first
column and
one of the others as second, or even all six possible indexes with those three
columns, but that will take lots of space and be slow for inserts, deletes, and
updates.
Art S. Kagel
Wu wrote:
>
> Hi,
>
> Basically I have a table with a composite index on scn, bl_no,
> other_bl, however, when I try to select some data out, it seems like the
> database is not using the index ... I'm just wondering if anyone could
> explain to me what's happening ?
>
> Below is the output of the sqexplain.out :-
>
> QUERY:
> ------
> select * from man_hdr
> where scn = "123">
> Estimated Cost: 1
> Estimated # of Rows Returned: 1
>
> 1) nasa.man_hdr: INDEX PATH
>
> (1) Index Keys: scn bl_no other_bl
> Lower Index Filter: nasa.man_hdr.scn = '123'
>
> QUERY:
> ------
> select * from man_hdr
> where bl_no = "123">
> Estimated Cost: 2
> Estimated # of Rows Returned: 5
>
> 1) nasa.man_hdr: SEQUENTIAL SCAN ---> How come it is not using index
> path ???
>
> Filters: nasa.man_hdr.bl_no = '123'
>
> QUERY:
> ------
> select * from man_hdr
> where other_bl = "123">
> Estimated Cost: 2
> Estimated # of Rows Returned: 5
>
> 1) nasa.man_hdr: SEQUENTIAL SCAN ---> How come it is not using index
> path ???
>
> Filters: nasa.man_hdr.other_bl = '123'
>
> QUERY:
> ------
> select * from man_hdr
> where scn = "123"
> and bl_no = "123">
> Estimated Cost: 1
> Estimated # of Rows Returned: 1
>
> 1) nasa.man_hdr: INDEX PATH
>
> (1) Index Keys: scn bl_no other_bl
> Lower Index Filter: (nasa.man_hdr.scn = '123' AND
> nasa.man_hdr.bl_no = '
> 123' )
>
> QUERY:
> ------
> select * from man_hdr
> where scn = "123"
> and other_bl = "123">
> Estimated Cost: 1
> Estimated # of Rows Returned: 1
>
> 1) nasa.man_hdr: INDEX PATH
>
> (1) Index Keys: scn bl_no other_bl (Key-First)
> Lower Index Filter: nasa.man_hdr.scn = '123'
> Key-First Filters: (nasa.man_hdr.other_bl = '123' )
>
> QUERY:
> ------
> select * from man_hdr
> where bl_no = "123"
> and other_bl = "123">
> Estimated Cost: 2
> Estimated # of Rows Returned: 3
>
> 1) nasa.man_hdr: SEQUENTIAL SCAN ---> How come it is not using index
> path ???
>
> Filters: (nasa.man_hdr.bl_no = '123' AND nasa.man_hdr.other_bl =
> '123' )
>
> From the above sqexplain.out, as long as the select statement
> contains the first column of the composite index, it will then use the
> index path, else it will be a sequential path !???