【大话IT】有哪些方法能比PK查询更快

netfairy

前提：只考虑纯Oralce DB的解决方案，不考虑Nosql（包括Oracle自己的）等。
假设的SQL：select * from table where pk=xxx，表的数据量是持续增长的。
如果已经是走PK的index unique scan了，但仍然不够快，还有哪些优化方式？
IOT？如果对于表Insert速度没有太多要求的情况下，可以考虑使用。
Hash聚簇？如果表的数据量可以预估出来的情况下，可以考虑使用。
还有哪些方案？不知道有没有在实际中去使用的，效果如何？

IT渔夫

关注。。。

wmxcn2000

LZ  可以考虑一下 IOT ， 索引组织表。

netfairy

在类似的设计中，使用过IOT，查询速度是加快了，对Insert性能的损伤比想象的要大。
根据应用的反馈，批量Insert的速度慢了5倍以上，远超出了我预计的值。

udfrog

select * from table where pk=xxx还不够快，现在是多快？你期望是多快？

dingjun123

多大啊？？？hash分区+unique index行不？？

netfairy

dingjun123 发表于 2014-12-8 10:46
多大啊？？？hash分区+unique index行不？？

数据量预估最大在2千万级别。
Hash分区的话，虽然减少了索引段大小，理论上来说，多了一次分区的计算（虽然能很快，但是避免不了cpu的消耗）；如果PK已经每天在业务低谷期rebuild的话。Hash分区能将查询性能提升多少，有经验值吗？
另外，多谢版主的回复！

netfairy

执行速度比较：
普通的PK：
create table TEST_PK
(
  owner          VARCHAR2(30),
  object_name    VARCHAR2(128),
  subobject_name VARCHAR2(30),
  object_id      NUMBER);
alter table TEST_PK add constraint pk_test_pk primary key(object_id);
INSERT INTO test_pk
  SELECT t.owner, t.object_name, t.subobject_name,rownum
    FROM test01 t
   WHERE rownum < 100001;
插入100000笔用时：3.219秒
PK做HASH 32分区：减少了索引段大小，理论上来说，多了一次分区的计算（虽然能很快，但是避免不了cpu的消耗）
create table TEST_PK_HASH
(
  owner          VARCHAR2(30),
  object_name    VARCHAR2(128),
  subobject_name VARCHAR2(30),
  object_id      NUMBER);
CREATE UNIQUE INDEX PK_TEST_PK_HASH ON TEST_PK_HASH (OBJECT_ID)
  global partition by hash(OBJECT_ID) partitions 32;
ALTER TABLE TEST_PK_HASH ADD CONSTRAINT PK_TEST_PK_HASH PRIMARY KEY (OBJECT_ID) USING INDEX PK_TEST_PK_HASH;
插入100000笔用时：6.671秒
单表Hash聚簇：
CREATE CLUSTER hash_obid(object_id NUMBER)
  SIZE 512
  SINGLE TABLE
  HASHKEYS 100000;
create table test_hash(
  owner          VARCHAR2(30),
  object_name    VARCHAR2(128),
  subobject_name VARCHAR2(30),
  object_id      NUMBER
)
CLUSTER hash_obid(object_id);
INSERT INTO test_hash
  SELECT t.owner, t.object_name, t.subobject_name,rownum
    FROM test01 t
   WHERE rownum < 100001;
插入100000笔用时：8.437秒
IOT表：
create table test_iot_table(
  owner          VARCHAR2(30),
  object_name    VARCHAR2(128),
  subobject_name VARCHAR2(30),
  object_id      NUMBER,
CONSTRAINT PK_TEST_IOT_TABLE PRIMARY KEY(object_id))
ORGANIZATION INDEX;
INSERT INTO test_iot_table
  SELECT t.owner, t.object_name, t.subobject_name,rownum
    FROM test01 t
   WHERE rownum < 100001;
插入100000笔用时：10.063秒
测试程序：
CREATE OR REPLACE PROCEDURE sp_test AS
  aa  TEST_PK%ROWTYPE;
  bb  NUMBER;
  cc1 TIMESTAMP;
  cc2 TIMESTAMP;
BEGIN
  -- PK
  cc1 := systimestamp;
  FOR i IN 1 .. 100000 LOOP
    bb := trunc(dbms_random.value(2, 99999));
    SELECT * INTO AA FROM TEST_PK WHERE object_id = bb;
  END LOOP;
  cc2 := systimestamp;
  dbms_output.put_line('PK:' || to_char(cc2 - cc1));
  -- PK by hash partition
  cc1 := systimestamp;
  FOR i IN 1 .. 100000 LOOP
    bb := trunc(dbms_random.value(2, 99999));
    SELECT * INTO AA FROM TEST_PK_HASH WHERE object_id = bb;
  END LOOP;
  cc2 := systimestamp;
  dbms_output.put_line('PK Hash:' || to_char(cc2 - cc1));
  -- hash cluster
  cc1 := systimestamp;
  FOR i IN 1 .. 100000 LOOP
    bb := trunc(dbms_random.value(2, 99999));
    SELECT * INTO AA FROM test_hash WHERE object_id = bb;
  END LOOP;
  cc2 := systimestamp;
  dbms_output.put_line('Hash:' || to_char(cc2 - cc1));
  -- iot
  cc1 := systimestamp;
  FOR i IN 1 .. 100000 LOOP
    bb := trunc(dbms_random.value(2, 99999));
    SELECT * INTO AA FROM test_iot_table WHERE object_id = bb;
  END LOOP;
  cc2 := systimestamp;
  dbms_output.put_line('IOT:' || to_char(cc2 - cc1));
END;
执行结果：看起来4者的性能差异非常小。也许是数据不够多，或者Index刚创建的原因。
PK:         +000000000 00:00:07.652509000
PK Hash: +000000000 00:00:07.967591000
Hash:      +000000000 00:00:07.469284000
IOT:        +000000000 00:00:07.497833000

xhr8334

是不是可以走rowid？

tyoukimei

一般考虑IOT和HASH聚簇，但如楼主所言，均有其局限性。
持续关注，期待达人解惑！