Re: Is it something wrong (Index) ?

With your method, it's still used the SEQUENTIAL SCAN !!! Radhika wrote: > something like this > scn <> " " and the usual condition > now it will go in for index > > At 03:38 PM 7/26/99 +0800, you wrote: > >What kind of dummy search ??? Is it using the matches statement ??? > > > >Radhika wrote: > > > >> Hi, > >> If you have a composite index, it is necessary that you should given in same > >> order, if the second column search should take a index create a singular > >> index on that column or else you add a dummy search on first indexed column > >> > >> Sandy > >> > >> At 11:49 AM 7/26/99 +0800, you 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 !??? > >> > > >> > > >

Did you do a full suite of UPDATE STATISTICS commands, like running dostats, after creating those indexes? If not the optimizer may not know enough about them to use them. Art S. Kagel Wu wrote: > > With your method, it's still used the SEQUENTIAL SCAN !!! > > Radhika wrote: > > > something like this > > scn <> " " and the usual condition > > now it will go in for index > > > > At 03:38 PM 7/26/99 +0800, you wrote: > > >What kind of dummy search ??? Is it using the matches statement ??? > > > > > >Radhika wrote: > > > > > >> Hi, > > >> If you have a composite index, it is necessary that you should given in same > > >> order, if the second column search should take a index create a singular > > >> index on that column or else you add a dummy search on first indexed column > > >> > > >> Sandy > > >> > > >> At 11:49 AM 7/26/99 +0800, you 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 !??? > > >> > > > >> > > > >