SQL 2000 create index 問題 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

SQL 2000 create index 問題

SQL 2000 create index 問題# Database - 数据库

J*G2010-06-01 07:06

1 楼

这是披头士乐队最后一张以披头士乐队的名义发行的专辑，正如专辑名称Let It Be那
样，让一切该怎样就怎样吧。
顺便问一句，这里的Mother Mary指的是圣母玛利亚嘛？
和Yesterday一样，在中国大陆出版的欧美英文歌曲杂锦专辑里，Let It Be的入选率也是惊人地高，也是我的西洋启蒙歌曲之一。
Let It Be （1970 Let It Be）
听歌链接：
http://www.imusiccn.com/player/play.asp?id=400629|
When I find myself in times of trouble
Mother Mary comes to me
Speaking words of wisdom, let it be.
And in my hour of darkness
She is standing right in front of me
Speaking words of wisdom, let it be.
Let it be,
Let it be, let it be
Let it be
Whisper words of w

i*a2010-06-01 07:06

2 楼

need to query between TableA and TableB. and it's taking too long
because the columns on lookup do no have indexes
TableA.CustNum data can appear in any or all of the following columns in
TableB
TableB.CustNum1
TableB.CustNum2
TableB.CustNum3
about 240M rows in TableB, on index on the CustNum fields, no foreign
keys
I am thinking creating a separate table with only 3 CustNum1/2/3
columns, with indexes defined, then insert 3 columns of data into it.
but it takes 20 hours to insert with indexes de

L*k2010-06-01 07:06

3 楼

Mother Mary这里是麦卡特尼的母亲。 Mary McCartney在他少年时
死于癌症。

也是惊人地高，也是我的西洋启蒙歌曲之一。

【在 J*G 的大作中提到】

: 这是披头士乐队最后一张以披头士乐队的名义发行的专辑，正如专辑名称Let It Be那
: 样，让一切该怎样就怎样吧。
: 顺便问一句，这里的Mother Mary指的是圣母玛利亚嘛？
: 和Yesterday一样，在中国大陆出版的欧美英文歌曲杂锦专辑里，Let It Be的入选率也是惊人地高，也是我的西洋启蒙歌曲之一。
: Let It Be （1970 Let It Be）
: 听歌链接：
: http://www.imusiccn.com/player/play.asp?id=400629|
: When I find myself in times of trouble
: Mother Mary comes to me
: Speaking words of wisdom, let it be.

j*n2010-06-01 07:06

4 楼

you can still partition your table by using the old fashion. say 10 tables
that each holds 24M records and a view with UNION ALL.
would your limited log can handle 24M each time instead of 240M?

J*G2010-06-01 07:06

5 楼

哦。这样啊。。。

【在 L*****k 的大作中提到】

: Mother Mary这里是麦卡特尼的母亲。 Mary McCartney在他少年时
: 死于癌症。
:
: 也是惊人地高，也是我的西洋启蒙歌曲之一。

i*a2010-06-01 07:06

6 楼

that's an idea. will try this weekend. thx

tables

【在 j*****n 的大作中提到】

: you can still partition your table by using the old fashion. say 10 tables
: that each holds 24M records and a view with UNION ALL.
: would your limited log can handle 24M each time instead of 240M?

B*L2010-06-01 07:06

7 楼

Are you able to change data type for CustNum1/2/3 to reduce the size of your
index.

i*a2010-06-01 07:06

8 楼

no, they are INT. I was actually able to create index one-by-one by
issuing SQL commands
I found out doing this via managment studio interface will create all 3
indexes in one shoot in a single transaction... that's why it was taking
forever and filling up the log file

your

【在 B*********L 的大作中提到】

: Are you able to change data type for CustNum1/2/3 to reduce the size of your
: index.

a92010-06-01 07:06

9 楼

从来不敢用managment studio创建index

【在 i****a 的大作中提到】

: no, they are INT. I was actually able to create index one-by-one by
: issuing SQL commands
: I found out doing this via managment studio interface will create all 3
: indexes in one shoot in a single transaction... that's why it was taking
: forever and filling up the log file
:
: your

g*l2010-06-01 07:06

10 楼

INDEX在三个COLUMN上？这样对查询帮助不大吧。为什么不能一个COLUMN上一个INDEX呢
，你有PK么或者CLUSTERED INDEX么，如果没有的话，NONCLUSTED INDEX也不快。
240M太多了，应该SPLIT成多个TABLE,上面套个VIEW，用VIEW给用户查询,多个TABLE的话，一个一个加INDEX快多了