Wes, True and I do not have to worry about that really as we only have varchar2 type fields as character fields here..
Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]]on Behalf Of Nichols, Wesley Sent: Thursday, April 22, 2010 3:35 PM To: [email protected] Subject: Re: Indexing violations in the Remedy database.. ** Joe, I just wanted to note, that if you have any nchar or nvarchar columns indexed on sql server you would only get 450 chars. Due to the key limit being in bytes not actual characters. I assume you would get similar results on all the DB's when dealing with multi-byte character sets. Regards, Wes From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Joe D'Souza Sent: Thursday, April 22, 2010 1:51 PM To: [email protected] Subject: Re: Indexing violations in the Remedy database.. ** Hi Wesley, Thank you for that link. There certainly don't seem to be any violations with more than 900 characters in indexed fields.. That leaves me with trying to find if there are compound indexes that violate that. Might take a while to write up that query but at some point on my free time I do intend to do that.. Cheers Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]]on Behalf Of Nichols, Wesley Sent: Thursday, April 22, 2010 2:27 PM To: [email protected] Subject: Re: Indexing violations in the Remedy database.. ** Here is the official MS word on it: http://technet.microsoft.com/en-us/library/ms143432.aspx From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Joe D'Souza Sent: Thursday, April 22, 2010 1:14 PM To: [email protected] Subject: Re: Indexing violations in the Remedy database.. ** Guillaume, I'll need to find information such as this for MS-SQL 2008. If you (or any of you readers) happen to have that handy, please share.. Cheers Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]]on Behalf Of Guillaume Rheault Sent: Thursday, April 22, 2010 1:27 PM To: [email protected] Subject: Re: Indexing violations in the Remedy database.. ** hi Joe, I can't tell for SQL Server or big blue (DB2), however her's the story for oracle In Oracle, the maximum length of the index is determined primarily by the block size parameter DB_BLOCK_SIZE: If 2K block size then maximum index key length=758 If 4K block size then maximum index key length=1578 If 8K block size then maximum index key length=3218 If 16K block size then maximum index key length=6498 How the maximum index key length is measured by? Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte) The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block, the size of the index depends. In fact, it is required that any index block must contain at least TWO index entries per block. So we can say that the maximum key length for an index will be less than half of the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE. I don't know if SQL Server or big blue's maximum index size depends on the equivalent of oracle's block size, which is essentially a data storage parameter, but that would be something to check... In Oracle 10g, we have indexes that have numerous columns in our ITSM app, for instance for the "APR:Approver Lookup" form, and we have not run into issues, probably we have a large block size Guillaume -------------------------------------------------------------------------- From: Action Request System discussion list(ARSList) [[email protected]] on behalf of Joe D'Souza [[email protected]] Sent: Wednesday, April 21, 2010 7:47 PM To: [email protected] Subject: Indexing violations in the Remedy database.. ** I need to collaborate with some of you SQL pro's out here to work with me to fetch a list of forms that may have indexes defined in Remedy that may violate a very basic rule in most standard RDBMS's. Indexes in most RDBMS's (Oracle, MS-SQL, Informix etc) that a Remedy server might be using, need to have indexed fields of less than 255 characters in length to the best of my knowledge. First of all I want to CONFIRM that this rule is true for all RDBMS's.. If it is true, then I ran a query to find any fields that may be indexed and violating this basic rule.. I can share this query with you if you want to join me on a collaborated effort to list all schemas that have fields that are indexed that may indeed be greater than 254 characters. Any volunteers? I have a ready query I can share with you.. Joe _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
