Inflectional and/or not returning the correct results

When doing the following search (SearchAllProducts, keyword, 'FORMSOF (INFLECTIONAL, pens) and FORMSOF (INFLECTIONAL, pads)'), we get 170 records returned. But if we switch the and and replace it with an OR we get 1294 records returned. Whats the part w
e don't understand is that the OR is not pulling back any of the 170 records where both words are in the keyword field. Even though we do a pens search and we get 748 records and for pads we get 716 records. We would expect to get back 1464 records. I
s this a bug or we not doing something right? We are using Enterprise Edition running SP3a on Windows 2000 running SP4.
Any ideas?
Kris
Kris,
Could you post some of the actual data in the FT-enable column(s) from your
FT-enabled table(s) along with the actual FTS query and table structure?
Also, do you have multiple FT-enabled tables in one FT Catalogs or do you
use one FT Catalog for each FT-enabled table?
Thanks,
John
"Kris" <kmccarty@.distributorcentral.com> wrote in message
news:81F1AFB5-679F-4963-B8B9-77A0FAFB6A7E@.microsoft.com...
> When doing the following search (SearchAllProducts, keyword, 'FORMSOF
(INFLECTIONAL, pens) and FORMSOF (INFLECTIONAL, pads)'), we get 170 records
returned. But if we switch the and and replace it with an OR we get 1294
records returned. Whats the part we don't understand is that the OR is not
pulling back any of the 170 records where both words are in the keyword
field. Even though we do a pens search and we get 748 records and for pads
we get 716 records. We would expect to get back 1464 records. Is this a
bug or we not doing something right? We are using Enterprise Edition
running SP3a on Windows 2000 running SP4.
> Any ideas?
> Kris

Indexing questions

Conceptual questions here...In BOL it mentions 'Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified.'. I'm wondering how this works as non-clustered indexes are built on top of clustered ones - is there in this case a significant slowdown in the speed of accessing non-clustered indexes? Also, I'm guessing here, but is the reason this might be useful is for when you want to disable and rebuild the nonclustered indexes in order to save space?
Cheers,
Paul Ibison, SQL Server MVP

Conceptually speaking, the leaf row of a non-clustered index needs to identify the data row uniquely. There are two ways to do it. First,you can use RID. The down side is that if the data row moves, the non-clustered index entry needs to be updated. Second, you can use clustered index key, if it is unique. The benefit is that if the data row moves say due to rebuild of a clustered index, nothing needs to be changed in the non-clustered index. Of course, in this case, to access data using non-clustered idnex, you will need to traverse the non-clustered index and then need to traverse the clustered index tree. Assuming that clustered index tree will be frequently accessed, it is lkely to be in the buffer pool. So you will incur few extra logical IOs per row access.

I doubt if the second method can be used to save space as it is likely that the clustered index key is longer than RID. The main benefit of the second method is that data row move does not cause the update of non-clustered index as long as the clustered key column value is not changed.

Let us see what others say.

Thanks

|||

Index rebuild (except for disabled index) does not change the logical structure of an index
(like changing a key, adding a new column, or change the sort order), it only changes the physical layout of an index on the disk. You can think of a physical index restructure, restoring

the original fillfactor and allocating the index pages in a right order removing fragmentation and compacting the index space. Therefore there is no need to rebuild the non-clustered index(es) when rebuilding a clustered index. This functionality is fully implemented in SQL Server 2005, and was already partially implemented in SQL Server 2000. We have added the keyword ALL to make sure customers can rebuild all indexes for a given table using one command (ease of use purpose). Clustered or non-clustered index rebuild may increase performance if the index is fragmented, especially when queries use

index scans.
Disabling a clustered index does not really save space. We still need to keep the old clustered index (marked as disabled) to make sure we can rebuild it. Disabling a non-clustered (NC) index brings some space benefit since we drop the old index and keep only its metadata, so when we rebuild an NC index we use either the base table or a clustered index. However this benefit can be easily reached by using two commands DROP NC index and CREATE NC index.

Thanks,

Mirek

|||Thanks Sunil,
this makes perfect sense. I hadn't seen it in this way before - if rebuilding the clustered index is only done to reorder the data (not to remove fragmentation) then there might be no performance gain from also rebuilding the non-clustered index, as the indexes are unlikely to be ordered in the same way.
Cheers,
Paul|||Thanks Mirek,
this makes sense. So in some cases where the table is large (disk space small) it might be useful to rebuild the clustered index without the ALL option, then subsequently disable and rebuild each non-clustered index.
Cheers,
Paul