X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=schema%2Fhuman_9606_schema%2Fhuman_9606_index.sql;fp=schema%2Fhuman_9606_schema%2Fhuman_9606_index.sql;h=c935c662f03365e1eb3d2b27dc7709b16260e462;hb=77ec8a32a887e1eb6364f327f55049cae8e69f95;hp=5602cb23db2bb72d38f0d53e6d4b0fff66dd57f5;hpb=6e51d2d339036d36e6294b1bbed27e5c18a6a2f5;p=dbsnp.git diff --git a/schema/human_9606_schema/human_9606_index.sql b/schema/human_9606_schema/human_9606_index.sql index 5602cb2..c935c66 100644 --- a/schema/human_9606_schema/human_9606_index.sql +++ b/schema/human_9606_schema/human_9606_index.sql @@ -1,45 +1,37 @@ - CREATE INDEX [i_tax_id] ON [Batch]([tax_id]) +CREATE NONCLUSTERED INDEX [i_tax_id] ON [Batch] ([tax_id] ASC) GO - - CREATE INDEX [i_submitted_time] ON [Batch]([submitted_time]) +CREATE NONCLUSTERED INDEX [i_submitted_time] ON [Batch] ([submitted_time] ASC) GO - - CREATE INDEX [i_handle_loc_batch_id] ON [Batch]([handle], [loc_batch_id]) +CREATE NONCLUSTERED INDEX [i_handle_loc_batch_id] ON [Batch] ([handle] ASC,[loc_batch_id] ASC) GO - - CREATE INDEX [i_pop_id] ON [Batch]([pop_id]) +CREATE NONCLUSTERED INDEX [i_pop_id] ON [Batch] ([pop_id] ASC) GO - - CREATE INDEX [i_success_rate_int] ON [Batch]([success_rate_int]) +CREATE NONCLUSTERED INDEX [i_success_rate_int] ON [Batch] ([success_rate_int] ASC) GO - - CREATE INDEX [i_method_id] ON [Batch]([method_id]) +CREATE NONCLUSTERED INDEX [i_method_id] ON [Batch] ([method_id] ASC) GO - - CREATE INDEX [i_lbid_u] ON [Batch]([loc_batch_id_upp]) +CREATE NONCLUSTERED INDEX [i_lbid_u] ON [Batch] ([loc_batch_id_upp] ASC) GO - - CREATE INDEX [i_last_updated] ON [Batch]([last_updated_time]) +CREATE NONCLUSTERED INDEX [i_last_updated] ON [Batch] ([last_updated_time] ASC) GO - - CREATE INDEX [i_pub_id] ON [BatchCita]([pub_id]) +CREATE NONCLUSTERED INDEX [i_pub_id] ON [BatchCita] ([pub_id] ASC) GO - - CREATE INDEX [i_BatchCultivar2] ON [BatchCultivar]([line]) +CREATE NONCLUSTERED INDEX [i_BatchCultivar2] ON [BatchCultivar] ([line] ASC) GO - - CREATE INDEX [i_strain] ON [BatchStrain]([line]) +CREATE NONCLUSTERED INDEX [i_strain] ON [BatchStrain] ([line] ASC) GO +CREATE CLUSTERED INDEX [t_hwp_chisq] ON [FreqSummaryBySsPop] ([subsnp_id] ASC,[pop_id] ASC) +GO @@ -48,236 +40,200 @@ GO - CREATE UNIQUE INDEX [i_curator_ped_id] ON [Pedigree]([curator], [curator_ped_id]) +CREATE NONCLUSTERED INDEX [i_curator_ped_id] ON [Pedigree] ([curator] ASC,[curator_ped_id] ASC) GO - - CREATE UNIQUE INDEX [i_ind_ped] ON [PedigreeIndividual]([ind_id], [ped_id]) +CREATE NONCLUSTERED INDEX [i_ind_ped] ON [PedigreeIndividual] ([ind_id] ASC,[ped_id] ASC) GO - - CREATE UNIQUE CLUSTERED INDEX [i_pid_line_num] ON [PopMandLine]([pop_id], [line_num]) +CREATE CLUSTERED INDEX [i_pid_line_num] ON [PopMandLine] ([pop_id] ASC,[line_num] ASC) GO - - CREATE UNIQUE INDEX [i_handle_loc_pop_id_upp] ON [Population]([handle], [loc_pop_id_upp]) +CREATE NONCLUSTERED INDEX [i_handle_loc_pop_id_upp] ON [Population] ([handle] ASC,[loc_pop_id_upp] ASC) GO - - CREATE UNIQUE INDEX [i_handle_loc_pop_id] ON [Population]([handle], [loc_pop_id]) +CREATE NONCLUSTERED INDEX [i_handle_loc_pop_id] ON [Population] ([handle] ASC,[loc_pop_id] ASC) GO - - - CREATE UNIQUE CLUSTERED INDEX [i_rs] ON [SNP]([snp_id]) +CREATE CLUSTERED INDEX [i_rsL] ON [RsMergeArch] ([rsLow] ASC) GO - - CREATE INDEX [i_exemplarSs] ON [SNP]([exemplar_subsnp_id], [snp_id]) +CREATE NONCLUSTERED INDEX [i_rsH] ON [RsMergeArch] ([rsHigh] ASC) GO - - CREATE INDEX [i_rs] ON [SNP3D]([snp_id]) +CREATE CLUSTERED INDEX [i_rs] ON [SNP] ([snp_id] ASC) +GO +CREATE NONCLUSTERED INDEX [i_exemplarSs] ON [SNP] ([exemplar_subsnp_id] ASC,[snp_id] ASC) GO +CREATE NONCLUSTERED INDEX [i_rs] ON [SNP3D] ([snp_id] ASC) +GO - CREATE INDEX [i_hist_time] ON [SNPHistory]([history_create_time]) -GO - - CREATE UNIQUE CLUSTERED INDEX [i_ss] ON [SNPSubSNPLink]([subsnp_id]) +CREATE NONCLUSTERED INDEX [i_hist_time] ON [SNPHistory] ([history_create_time] ASC) GO - CREATE INDEX [i_rs] ON [SNPSubSNPLink]([snp_id], [subsnp_id], [substrand_reversed_flag]) +CREATE CLUSTERED INDEX [i_rs_ss_pmid] ON [SNPPubmed] ([snp_id] ASC,[subsnp_id] ASC,[pubmed_id] ASC,[type] ASC) GO - - CREATE CLUSTERED INDEX [i_snp_id] ON [SNPSubSNPLinkHistory]([snp_id]) +CREATE CLUSTERED INDEX [i_ss] ON [SNPSubSNPLink] ([subsnp_id] ASC) GO - - CREATE UNIQUE INDEX [i_ss_rs] ON [SNPSubSNPLinkHistory]([subsnp_id], [snp_id]) +CREATE NONCLUSTERED INDEX [i_rs] ON [SNPSubSNPLink] ([snp_id] ASC,[subsnp_id] ASC,[substrand_reversed_flag] ASC) GO - CREATE INDEX [i_build_id] ON [SNPSubSNPLinkHistory]([build_id]) +CREATE CLUSTERED INDEX [i_snp_id] ON [SNPSubSNPLinkHistory] ([snp_id] ASC) GO - - CREATE INDEX [i_build_id_when_history_made] ON [SNPSubSNPLinkHistory]([build_id_when_history_made]) +CREATE NONCLUSTERED INDEX [i_ss_rs] ON [SNPSubSNPLinkHistory] ([subsnp_id] ASC,[snp_id] ASC) GO - - - CREATE INDEX [i_rs] ON [SNPVal]([snp_id]) +CREATE NONCLUSTERED INDEX [i_build_id] ON [SNPSubSNPLinkHistory] ([build_id] ASC) GO - - - CREATE UNIQUE CLUSTERED INDEX [i_rs_hgvs] ON [SNP_HGVS]([snp_id], [hgvs_name]) +CREATE NONCLUSTERED INDEX [i_build_id_when_history_made] ON [SNPSubSNPLinkHistory] ([build_id_when_history_made] ASC) GO - CREATE INDEX [i_link_prop_b2] ON [SNP_bitfield]([link_prop_b2]) +CREATE NONCLUSTERED INDEX [i_rs] ON [SNPVal] ([snp_id] ASC) GO - - CREATE INDEX [i_ss] ON [SubPop]([subsnp_id]) +CREATE CLUSTERED INDEX [i_rs_h] ON [SNP_HGVS] ([snp_id] ASC,[hgvs_name] ASC) GO - CREATE INDEX [i_pop_ss] ON [SubPop]([pop_id], [subsnp_id]) +CREATE NONCLUSTERED INDEX [i_link_prop_b2] ON [SNP_bitfield] ([link_prop_b2] ASC,[snp_id] ASC) GO - CREATE INDEX [i_subpop_id] ON [SubPop]([subpop_id]) +CREATE NONCLUSTERED INDEX [i_ss] ON [SubPop] ([subsnp_id] ASC) GO - - - CREATE UNIQUE CLUSTERED INDEX [iuc_SubPopAllele] ON [SubPopAllele]([batch_id], [pop_id], [subsnp_id], [type], [allele], [other]) +CREATE NONCLUSTERED INDEX [i_pop_ss] ON [SubPop] ([pop_id] ASC,[subsnp_id] ASC) GO - - CREATE INDEX [iSubPopAllele1] ON [SubPopAllele]([freq]) +CREATE NONCLUSTERED INDEX [i_subpop_id] ON [SubPop] ([subpop_id] ASC) GO - CREATE INDEX [iSubPopAllele2] ON [SubPopAllele]([freq_max]) +CREATE CLUSTERED INDEX [iuc_SubPopAllele] ON [SubPopAllele] ([batch_id] ASC,[subsnp_id] ASC,[pop_id] ASC,[allele] ASC,[other] ASC,[type] ASC) GO - - CREATE INDEX [iSubPopAllele3] ON [SubPopAllele]([freq_min]) +CREATE NONCLUSTERED INDEX [iSubPopAllele1] ON [SubPopAllele] ([freq] ASC) GO - - CREATE INDEX [i_subsnp_id] ON [SubPopAllele]([subsnp_id]) +CREATE NONCLUSTERED INDEX [iSubPopAllele2] ON [SubPopAllele] ([freq_max] ASC) GO - - CREATE INDEX [i_type] ON [SubPopAllele]([type]) +CREATE NONCLUSTERED INDEX [iSubPopAllele3] ON [SubPopAllele] ([freq_min] ASC) GO - - CREATE INDEX [i_last_updated_time] ON [SubPopAllele]([last_updated_time]) +CREATE NONCLUSTERED INDEX [i_subsnp_id] ON [SubPopAllele] ([subsnp_id] ASC) GO - - CREATE INDEX [i_allele_id] ON [SubPopAllele]([allele_id]) +CREATE NONCLUSTERED INDEX [i_type] ON [SubPopAllele] ([type] ASC) GO - - CREATE INDEX [i_subpop_id] ON [SubPopAllele]([subpop_id]) +CREATE NONCLUSTERED INDEX [i_last_updated_time] ON [SubPopAllele] ([last_updated_time] ASC) GO - - CREATE UNIQUE INDEX [i_sp_ale] ON [SubPopAllele]([subpop_id], [type], [allele], [other]) +CREATE NONCLUSTERED INDEX [i_allele_id] ON [SubPopAllele] ([allele_id] ASC) GO - - - CREATE INDEX [i_subpop_id] ON [SubPopGty]([subpop_id]) +CREATE NONCLUSTERED INDEX [i_subpop_id] ON [SubPopAllele] ([subpop_id] ASC) GO - - CREATE INDEX [i_gty_id] ON [SubPopGty]([gty_id]) +CREATE NONCLUSTERED INDEX [i_sp_ale] ON [SubPopAllele] ([subpop_id] ASC,[type] ASC,[allele] ASC,[other] ASC) GO - - CREATE INDEX [i_loc_snp] ON [SubSNP]([loc_snp_id_upp], [subsnp_id]) +CREATE NONCLUSTERED INDEX [i_subpop_id] ON [SubPopGty] ([subpop_id] ASC) GO - - CREATE INDEX [i_bid_ss] ON [SubSNP]([batch_id], [subsnp_id]) +CREATE NONCLUSTERED INDEX [i_gty_id] ON [SubPopGty] ([gty_id] ASC) GO - - CREATE INDEX [i_acc_part_ind] ON [SubSNPAcc_ins]([acc_part], [acc_type_ind], [subsnp_id]) +CREATE NONCLUSTERED INDEX [i_loc_snp] ON [SubSNP] ([loc_snp_id_upp] ASC,[subsnp_id] ASC) +GO +CREATE NONCLUSTERED INDEX [i_bid_ss] ON [SubSNP] ([batch_id] ASC,[subsnp_id] ASC) +GO +CREATE NONCLUSTERED INDEX [i_var] ON [SubSNP] ([variation_id] ASC,[subsnp_id] ASC) GO +CREATE NONCLUSTERED INDEX [i_acc_part_ind] ON [SubSNPAcc_ins] ([acc_part] ASC,[acc_type_ind] ASC,[subsnp_id] ASC) +GO - CREATE INDEX [i_subsnp_id] ON [SubSNPMdFailLn]([subsnp_id]) +CREATE NONCLUSTERED INDEX [i_subsnp_id] ON [SubSNPMdFailLn] ([subsnp_id] ASC) GO - - CREATE INDEX [i_pmid] ON [SubSNPPubmed]([pubmed_id]) +CREATE NONCLUSTERED INDEX [i_pmid] ON [SubSNPPubmed] ([pubmed_id] ASC) GO - - CREATE UNIQUE INDEX [i_submitted_ind_id] ON [SubmittedIndividual]([submitted_ind_id]) +CREATE NONCLUSTERED INDEX [i_submitted_ind_id] ON [SubmittedIndividual] ([submitted_ind_id] ASC) GO - - CREATE INDEX [i_ind] ON [SubmittedIndividual]([ind_id]) +CREATE NONCLUSTERED INDEX [i_ind] ON [SubmittedIndividual] ([ind_id] ASC) GO - - CREATE UNIQUE INDEX [idxContigName] ON [b132_ContigInfo_37_1]([contig_name]) +CREATE NONCLUSTERED INDEX [i_asm] ON [b135_ContigInfo_37_3] ([asm_acc] ASC,[asm_version] ASC,[contig_start] ASC,[contig_end] ASC,[chr_gi] ASC) GO - - CREATE INDEX [idxContigLabel] ON [b132_ContigInfo_37_1]([contig_label]) +CREATE NONCLUSTERED INDEX [i_asm_chr_ctg] ON [b135_ContigInfo_37_3] ([asm_acc] ASC,[asm_version] ASC,[chr_gi] ASC,[contig_start] ASC,[contig_end] ASC) GO - - CREATE INDEX [idxGroupTerm] ON [b132_ContigInfo_37_1]([group_term]) +CREATE NONCLUSTERED INDEX [idxContigLabel] ON [b135_ContigInfo_37_3] ([contig_label] ASC) GO - - CREATE UNIQUE INDEX [idxCtgGi] ON [b132_ContigInfo_37_1]([contig_gi]) +CREATE NONCLUSTERED INDEX [ndx_ContigInfo_pc_37_3] ON [b135_ContigInfo_37_3] ([placement_status] ASC,[ctg_id] ASC) GO - CREATE INDEX [idxContigId] ON [b132_ContigInfo_37_1]([ctg_id]) -GO - CREATE INDEX [idxContigNameAcc] ON [b132_ContigInfo_37_1]([contig_name], [contig_acc]) +CREATE CLUSTERED INDEX [i_gi] ON [b135_MapLinkInfo_37_3] ([gi] ASC) GO - - CREATE INDEX [i_order_assem_132] ON [b132_GenomeAssemblyInfo_37_1]([rep_order], [assembly]) +CREATE CLUSTERED INDEX [i_rs_gi_pos] ON [b135_MapLink_37_3] ([snp_id] ASC,[gi] ASC,[offset] ASC) GO - - - CREATE INDEX [idxGi] ON [b132_MapLinkInfo_37_1]([gi]) +CREATE NONCLUSTERED INDEX [i_src] ON [b135_MapLink_37_3] ([source] ASC,[snp_type] ASC,[snp_id] ASC,[gi] ASC,[offset] ASC,[asn_to] ASC) GO - - CREATE CLUSTERED INDEX [i_rs_gi_pos] ON [b132_MapLink_37_1]([snp_id], [gi]) +CREATE CLUSTERED INDEX [i_gi] ON [b135_ProteinInfo_37_3] ([gi] ASC) GO - CREATE INDEX [idxSnpLoc] ON [b132_MapLink_37_1]([gi], [offset], [asn_to]) +CREATE CLUSTERED INDEX [i_rs_pos] ON [b135_Remapped_37_3] ([snp_id] ASC,[src_gi] ASC,[src_from] ASC,[tgt_gi] ASC) GO - - - - CREATE UNIQUE CLUSTERED INDEX [i_rs] ON [b132_SNPChrPosOnRef_37_1]([snp_id]) +CREATE NONCLUSTERED INDEX [i_tgt_gi] ON [b135_Remapped_37_3] ([tgt_gi] ASC,[snp_type] ASC,[snp_id] ASC) GO - - CREATE UNIQUE CLUSTERED INDEX [i_rs] ON [b132_SNPContigLoc_37_1]([snp_id], [ctg_id], [asn_from]) +CREATE CLUSTERED INDEX [i_rs] ON [b135_SNPChrPosOnRef_37_3] ([snp_id] ASC) GO - CREATE INDEX [i_ctgPos] ON [b132_SNPContigLoc_37_1]([ctg_id], [asn_from]) +CREATE CLUSTERED INDEX [i_rs] ON [b135_SNPContigLoc_37_3] ([snp_id] ASC,[ctg_id] ASC,[asn_from] ASC) GO - - CREATE INDEX [idxSnpId] ON [b132_SNPContigLoc_37_1]([snp_type], [snp_id]) +CREATE NONCLUSTERED INDEX [i_ctg] ON [b135_SNPContigLoc_37_3] ([ctg_id] ASC) GO - - CREATE INDEX [iCtgSnp] ON [b132_SNPContigLoc_37_1]([ctg_id], [snp_id]) +CREATE NONCLUSTERED INDEX [i_snp] ON [b135_SNPContigLoc_37_3] ([snp_type] ASC,[snp_id] ASC) GO - - - CREATE UNIQUE CLUSTERED INDEX [i_rsCtgMrna] ON [b132_SNPContigLocusId_37_1]([locus_id], [contig_acc], [mrna_gi], [snp_id], [asn_from], [allele], [mrna_start]) +CREATE NONCLUSTERED INDEX [idxAsnFrom] ON [b135_SNPContigLoc_37_3] ([asn_from] ASC) GO - CREATE INDEX [i_rs] ON [b132_SNPContigLocusId_37_1]([snp_id]) +CREATE CLUSTERED INDEX [i_rsCtgMrna] ON [b135_SNPContigLocusId_37_3] ([snp_id] ASC,[contig_acc] ASC,[asn_from] ASC,[locus_id] ASC,[allele] ASC,[mrna_start] ASC,[mrna_gi] ASC) +GO +CREATE NONCLUSTERED INDEX [i_rs] ON [b135_SNPContigLocusId_37_3] ([snp_id] ASC) GO +CREATE CLUSTERED INDEX [i_rs_gi] ON [b135_SNPContigProtein_37_3] ([snp_id] ASC,[contig_gi] ASC,[contig_start] ASC) +GO - CREATE UNIQUE CLUSTERED INDEX [i_rs] ON [b132_SNPMapInfo_37_1]([snp_id], [assembly]) +CREATE CLUSTERED INDEX [i_rs_asm] ON [b135_SNPMapInfo_37_3] ([snp_id] ASC,[asm_acc] ASC) +GO +CREATE NONCLUSTERED INDEX [i_asm] ON [b135_SNPMapInfo_37_3] ([asm_acc] ASC,[asm_version] ASC) +GO +CREATE NONCLUSTERED INDEX [iuc_rs_assembly_37_3] ON [b135_SNPMapInfo_37_3] ([asm_acc] ASC,[weight] ASC,[snp_id] ASC) GO +CREATE CLUSTERED INDEX [i_rs_pos] ON [b135_SNPMapLinkProtein_37_3] ([snp_id] ASC,[mrna_gi] ASC,[mrna_start] ASC,[mrna_stop] ASC) +GO +CREATE NONCLUSTERED INDEX [i_ctg] ON [b135_SNPMapLinkProtein_37_3] ([ctg_gi] ASC,[mrna_gi] ASC,[snp_id] ASC) +GO - CREATE CLUSTERED INDEX [i_pid] ON [dn_IND_batch_pop]([pop_id]) +CREATE CLUSTERED INDEX [i_pid] ON [dn_IND_batch_pop] ([pop_id] ASC) GO - - CREATE CLUSTERED INDEX [i_b_size] ON [dn_table_rowcount]([build_id] DESC , [reserved_KB_spaceused] DESC ) +CREATE CLUSTERED INDEX [i_b_size] ON [dn_table_rowcount] ([build_id] DESC,[reserved_KB_spaceused] DESC) GO -