有点奇怪的问题,与索引有关.

areswang 2010-07-16 10:56:05
相同的查询语句:
SELECT product_id,
transnum
FROM trout_detail
WHERE transnum= 2103102
AND product_id = 3061
在重建聚集索引前查不到数据,重建之后可以查到。操作过程中没有数据插入。

表结构如下:

CREATE TABLE [dbo].[TROUT_DETAIL](
[TRANSDATE] [char](8) COLLATE Chinese_PRC_CI_AS NOT NULL,
[STORE_CODE_ID] [int] NOT NULL,
[TILL_ID] [int] NOT NULL,
[TRANSNUM] [int] NOT NULL,
[TRANSTIME] [char](6) COLLATE Chinese_PRC_CI_AS NOT NULL,
[SEQUENCE] [int] NOT NULL,
[SKU] [char](11) COLLATE Chinese_PRC_CI_AS NULL,
[QUANTITY] [numeric](16, 6) NULL,
[CESSION_PRICE] [numeric](12, 2) NULL,
[TRANSMIT] [char](2) COLLATE Chinese_PRC_CI_AS NULL,
[programname] [varchar](25) COLLATE Chinese_PRC_CI_AS NULL,
[username] [nvarchar](638) COLLATE Chinese_PRC_CI_AS NOT NULL,
[stationid] [varchar](15) COLLATE Chinese_PRC_CI_AS NULL,
[ts_id] [datetime] NOT NULL,
[Bin_id] [int] NULL,
[product_id] [int] NOT NULL,
[size_id] [int] NOT NULL,
[color_id] [int] NOT NULL,
[TROD_PriceCost] [numeric](16, 6) NULL,
[Reason_Id] [int] NULL,
[InterCompanyCost] [numeric](16, 6) NULL,
[TROD_SkuAddedInReception] [tinyint] NULL,
[Po_Number] [int] NULL,

索引如下:
ALTER TABLE [dbo].[TROUT_DETAIL] ADD CONSTRAINT [PK_TROUT_DETAIL] PRIMARY KEY CLUSTERED
(
[TRANSDATE] ASC,
[STORE_CODE_ID] ASC,
[TILL_ID] ASC,
[TRANSNUM] ASC,
[TRANSTIME] ASC,
[SEQUENCE] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


...全文
140 13 点赞 打赏 收藏 举报
写回复
13 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
areswang 2010-07-16
确实是页面链接的问题。学习了
  • 打赏
  • 举报
回复
areswang 2010-07-16
to Kg;

DBCC results for 'TROUT_DETAIL'.
Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 523864933, index ID 2, partition ID 72057594963820544, alloc unit ID 72057594982301696 (type In-row data). The previous link (1:157419) on page (1:134112) does not match the previous page (1:205598) that the parent (1:198332), slot 57 expects for this page.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 523864933, index ID 2, partition ID 72057594963820544, alloc unit ID 72057594982301696 (type In-row data). Page (1:134112) is missing a reference from previous page (1:157419). Possible chain linkage problem.
Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 523864933, index ID 2, partition ID 72057594963820544, alloc unit ID 72057594982301696 (type In-row data). The previous link (1:205598) on page (1:157418) does not match the previous page (1:134112) that the parent (1:198332), slot 58 expects for this page.
Msg 8936, Level 16, State 1, Line 1
Table errora: Object ID 523864933, index ID 2, partition ID 72057594963820544, alloc unit ID 72057594982301696 (type In-row data). B-tree chain linkage mismatch. (1:134112)->next = (1:157418), but (1:157418)->Prev = (1:205598).
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 523864933, index ID 2, partition ID 72057594963820544, alloc unit ID 72057594982301696 (type In-row data). Keys out of order on page (1:198332), slots 56 and 57.
There are 47194 rows in 1031 pages for object "TROUT_DETAIL".
CHECKTABLE found 0 allocation errors and 5 consistency errors in table 'TROUT_DETAIL' (object ID 523864933).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (xsm_test1.dbo.TROUT_DETAIL).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  • 打赏
  • 举报
回复
andy_liucj 2010-07-16
好诡异,试试8楼说的
  • 打赏
  • 举报
回复
Garnett_KG 2010-07-16
页面链接有问题吧?

DBCC CHECKTABLE(trout_detail)

返回啥?
  • 打赏
  • 举报
回复
永生天地 2010-07-16
[Quote=引用 7 楼 areswang 的回复:]

[/Quote]
确实应该有 3061
  • 打赏
  • 举报
回复
小_爱 2010-07-16
好诡异
SELECT product_id,
transnum
FROM trout_detail
WHERE product_id = 3061

有数据没有?

  • 打赏
  • 举报
回复
areswang 2010-07-16
  • 打赏
  • 举报
回复
areswang 2010-07-16
我也觉得不可能,但事实确实出现了,马上上图片。
  • 打赏
  • 举报
回复
everglow 2010-07-16
不可能吧,看是不是出现锁了
  • 打赏
  • 举报
回复
东那个升 2010-07-16
应该不会吧
  • 打赏
  • 举报
回复
obuntu 2010-07-16
感觉应该是不可能的。

能不能把结果也贴出来看看呢。
  • 打赏
  • 举报
回复
htl258_Tony 2010-07-16
不太可能吧
  • 打赏
  • 举报
回复
路过,我只是个菜鸟,不懂。。。
  • 打赏
  • 举报
回复
相关推荐
发帖
智能路由器
加入

645

社区成员

智能路由器通常具有独立的操作系统,包括OpenWRT、eCos、VxWorks等,可以由用户自行安装各种应用,实现网络和设备的智能化管理。
申请成为版主
帖子事件
创建了帖子
2010-07-16 10:56
社区公告
暂无公告