Re: Is it something wrong (Index) ?

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 !??? > > > >