我有一个事实表 CardTransactionFact
表结构
TABLE [dbo].[CardTransactionFact]
[CardTransactionID] [int] IDENTITY(1,1) NOT NULL,
[TransactionTerminalID] [int] NOT NULL,
[SourceAccountTypeID] [int] NULL,
[DestinationAccountTypeID] [int] NULL,
[RimNo] [varchar](15) NULL,
[CaptureCodeID] [int] NOT NULL,
[RoutingCodeID] [int] NOT NULL,
[ProcessingCodeID] [int] NOT NULL,
[ActionCodeID] [int] NOT NULL,
[NetworkCodeID] [int] NOT NULL,
[ProductCodeID] [int] NOT NULL,
[AcquiringCountryCodeID] [int] NOT NULL,
[IssuingCountryCodeID] [int] NOT NULL,
[TransactionCurrencyCodeID] [int] NOT NULL,
[AmountBD] [decimal](18, 3) NOT NULL,
[LocalCurrencyCodeID] [int] NOT NULL,
[CardIssuerBank] [int] NOT NULL,
[CardTypeID] [int] NOT NULL,
[SuspectTransactionFlag] [char](1) NOT NULL,
[ReversalTransactionFlag] [char](1) NOT NULL,
[LocalTransactionDateKey] [int] NOT NULL,
[LocalTransactionHourKey] [int] NOT NULL,
[BBKRole] [char](1) NOT NULL,
[AmountRangeKey] [int] NULL,
[CustomerKey] [int] NULL
大小:11GB 行数:56,959,828
现在访问这个表变得非常困难,一个简单Select count(*) from CardTransactionFact
的需要几个小时才能执行。
表中的大多数列只是整数,这就是我没有做任何索引的原因。
你认为我应该做些什么来改进这个表,并提高查询这个表的速度
- 如果索引我应该索引哪些列以及为什么
- 对表进行分区是个好主意吗
- 任何其他建议