If you have a large number of results after Conny's query, you may want to auto generate a script to create all the missing indexes using that same query to generate the result in the form of SQL statements to create an index..
Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of L G Robinson Sent: Monday, February 22, 2010 2:30 PM To: arslist@ARSLIST.ORG Subject: Re: AW: Help with Sybase indexes Thanks Conny... this is very helpful. Larry On Feb 22, 2010, at 9:00 AM, Conny Martin wrote: > Larry, > > you should compare the metatables with the indexes oracle knows. > > If one of these 2 statements gives you any rows, you have messed up your indexes. > > SELECT name,'pk_idx for T-Table' FROM arschema WHERE schematype = 1 AND NOT EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IT'||schemaid) > UNION ALL > SELECT name,'pk_idx for H-Table' FROM arschema WHERE schematype = 1 AND NOT EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IH'||schemaid) > UNION ALL > SELECT name,'pk_idx for B-Table' FROM arschema WHERE schematype = 1 AND NOT EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IB'||schemaid) > > > SELECT name,arschema.schemaid,schema_index.numfields,uniqueflag,indexname,f1,f2,f3 FROM arschema,schema_index WHERE indexname NOT IN (SELECT INDEX_name FROM user_indexes) AND schema_index.schemaid = arschema.schemaid > > HTH > > Kind Regards Conny > > -----Ursprüngliche Nachricht----- > Von: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] Im Auftrag von L G Robinson > Gesendet: Montag, 22. Februar 2010 14:33 > An: arslist@ARSLIST.ORG > Betreff: Re: Help with Sybase indexes > > Hi Joe, > > Thanks for the reply. I did compare the information in the AR System's meta-tables between the two systems (schema_index) and it matches up as expected. However, I believe it is possible for there to be a mismatch between what ARS thinks and what actually exists in the DB. The situation I am trying to detect is the one where ARS thinks there is an index, but for whatever reason, the index no longer exists in the DB. > > I have been using Misi's wonderful RRRchive utility and on occasion, I have to interrupt a copy operation before it finishes. In this case, I believe that the target table will be left without its index since RRRchive does not get the chance to recreate the index on the target table when the program is killed. I recreate the index(es) when this happens... I just want to make sure I have not missed any. > > Thanks again. > Larry > > On Feb 21, 2010, at 8:53 AM, Joe D'Souza wrote: > >> Larry, >> >> Why not look this same information up from the AR System's meta information? >> That way it would be easier to compare the two systems indexes if they >> should be identical.. >> >> Joe >> >> -----Original Message----- >> From: Action Request System discussion list(ARSList) >> [mailto:arsl...@arslist.org]on Behalf Of L G Robinson >> Sent: Friday, February 19, 2010 5:47 PM >> To: arslist@ARSLIST.ORG >> Subject: Help with Sybase indexes >> >> >> Hi Folks, >> >> I need some Sybase help regarding indexes. >> >> I am migrating from Sybase to Oracle and I want to compare the two >> systems to make sure that the Oracle system has all of the same >> indexes on the T-tables as currently exist in the Sybase system. I >> don't doubt that they were there originally as a result of the >> workflow import that I did to create the Oracle system. I just want to >> make sure that I have not inadvertently messed any of the indexes up >> while I have been working in the Oracle system and copying data over (Thanks Misi!!!). >> >> On the Oracle side, I can do the following: >> >> SELECT s.name as "Form Name", b.column_name, a.table_name, >> a.index_name, a.uniqueness,a.index_type FROM USER_INDEXES a, >> USER_IND_COLUMNS b, arschema s WHERE a.index_name = b.index_name AND >> cast(s.schemaId as varchar2(40)) = >> substr(a.table_name,2) AND substr(a.table_name,1,1) = 'T' >> ORDER by 1,2; >> >> This gives me exactly what I need. However, I have been spelunking >> through the Sybase system tables all day and I can not seem to locate >> all of the information I need. I can see "almost" everything in the "sysindexes" table. >> However, I do not see the name or id number of the column that the >> index is built upon. Maybe I'm just missing something obvious but I >> sure don't see it. >> >> Anyone have any insights into how to accomplish the above in Sybase? >> >> Thanks. >> Larry >> >> ARS: 5.1.2 >> Sybase: 12.5.2 >> Oracle: 10.2.0.4.0 >> >> Larry Robinson n...@ncsu.edu >> Office of Information Technology >> NC State University 919-515-5432 Voice >> Raleigh, NC 27695-7109 919-513-0877 FAX _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
