SQL语句查询优化问题

Question

本人在设计数据库的时候现在遇到一个问题，希望大家能给予指点和建议。本人的设计是这样：表A中每条记录是一个人，表B中存放的是人身上的物品。表A中除了ID，任何一个字段，都连接了表B的ID。也就是说，表A中除了ID，任何一个字段的ID都是指向表B的主键。现在本人以一个例子来说明：表结构如下Tbl_tempA                                                  
tempAID   hand    arm    shoes
1001      001     003    005
1002      002     004    006
Tbl_tempB
testid     type     testname
001        手       苹果
002        手       荔枝
003        衣服     外套
004        衣服     大衣
005        鞋子     皮鞋
006        鞋子     运动鞋查询结果是
Result
testno     hand     arm     shoes
1001       苹果     外套    皮鞋
1002       荔枝     大衣    运动鞋本人的查询语句是
SELECT b.testno,b.hand AS hand,a.testname AS arm,a.testname AS shoes FROM atest a INNER JOIN(
SELECT b.testno,b.hand AS hand,a.testname AS arm,b.shoes  FROM atest a INNER JOIN(
SELECT b.testno,a.testname AS hand,b.arm,b.shoes 
FROM atest a INNER JOIN btest b ON a.testid = b.hand
) b ON a.testid = b.arm) b ON a.testid = b.shoes
这样是可以查询出这种结果，但是如果 Tbl_tempA 表中对于 Tbl_tempB 表中的关联字段很多，也就是说，如果表A中存放了70-80个字段，每个字段的ID都是从表B的主键中取的。那么必然查询语句会很麻烦，所以希望大家能给予指点和意见。

Answer 1

sql是这样写的，不过你的Tbl_tempB是个信息的基表，如果两边都建立了index的话，性能是应该没有问题的。

Answer 2

我的想法是Tbl_tempB.testid最好改成number，可以用序列生成
如果要编号的话，另外再加个字段结构没法优化了，索引性能稍微提高些
如果编号能表示特殊意义的话，楼主这样设计就比较合理

Answer 3

表A为什么不设计成
Tbl_tempA 
KEY_ID 主键
tempAID 
testid这样的结构呢，按你的设计，如果一个人有１００件物品，另一个人有２件物品，岂不是表A要有101个字段，而对于第二个人来说，９８个都没有用，这对空间等都浪费了。
个人意见

Answer 4

建议对表结构进行改造：Tbl_tempA 包含以下字段，tempAID,testid,typecode,数据按以下该项保存：
1001      001   hand
1001      003   arm
1002      002   hand
1002      004   arm
1001      005   shoes 
1002      006   shoes
这样查询语句可以构造存储过程如下：create or replace procedure RetrieveResult(strSql out varchar2) is
     v_sql varchar2(5000);
     v_temptable varchar2(50);
     v_count  number;
     v_colno  number;
     v_testid varchar2(50);
     v_testname varchar2(50);
     v_typecode varchar2(50);
     type v_refcur is ref cursor;
     V_CUR v_refcur;
begin
     v_temptable:='TMP_TAB';
     select count(*) into v_count from user_tables where table_name =v_temptable;
     if v_count>0 then
         execute immediate 'drop table ' || v_temptable;
     end if;
     execute immediate 'create table '|| v_temptable || '(testno  varchar2(10))';
     v_temptable:='TMP_COL';
     select count(*) into v_count from user_tables where table_name =v_temptable;
     if v_count>0 then
         execute immediate 'drop table ' || v_temptable;
     end if;
     execute immediate 'create table '|| v_temptable || '(colno number,typecode varchar2(50))';
     execute immediate 'insert into TMP_COL select rownum , typecode from (select typecode from Tbl_tempA a where exists(select 1 from  Tbl_tempB b where a.testid=b.testid) group by typecode order by typecode) ';     execute immediate 'select count(*) from TMP_COL ' into  v_count ;
     v_colno:=1;
     loop
         exit when v_count<v_colno;
         execute immediate 'alter table TMP_TAB add col' || to_char(v_colno)
                         || ' varchar2(50)';
         v_colno:=v_colno+1;
     end loop;
     v_sql:='insert into tmp_tab(testno) select tempAID from Tbl_tempA group by tempAID ';
     execute immediate v_sql;
     commit;
     OPEN V_CUR FOR 'select a.tempAID,a.typecode,b.testname  from Tbl_tempA a,Tbl_tempB b where a.testid=b.testid';
     loop
         fetch V_CUR into v_testid,v_typecode,v_testname;
         exit when V_CUR%notfound;
         execute immediate 'select colno from TMP_COL where typecode =:1' into v_colno using v_typecode;
         execute immediate 'update tmp_tab set col'|| v_colno || '=:1 where testno=:2' using v_testname,v_testid;
     end loop;
     close V_CUR;
     v_sql:='select testno';
     open V_CUR for 'select colno,typecode from tmp_col order by colno';
     loop
         fetch V_CUR into v_colno,v_typecode;
         exit when V_CUR%notfound;
         v_sql:=v_sql ||',col' || v_colno || ' as '|| v_typecode;
     end loop;
     close V_CUR;
     v_sql:=v_sql ||' from tmp_tab ';
     strSql:=v_sql;
     execute immediate strSql;
     commit;
end;

Answer 5

只有在关联的表很多时，或者在存储过程中多次扫描一张表时，或者重复进行同一个操作时，才需要优化SQL语句

Answer 6

表的结构有问题啊，设计的不怎么合理个人感觉如下设计应该稍微好点人员表
人员id 人员name
人员物品表
人员id  物品id物品表
物品id  物品name这样，所有的表的列的数目比较少，顶多是行的累计

Answer 7

如果真有70，80个columns需要关联B表，那你的表结构有问题的，还不如把B表的内容全部放到A表。
另外有个办法减少SQL的复杂度就是对B表建一些View，比如HAND_VW,FOOT_VW，这样可以使你的SQL结构和逻辑都比较清晰

Answer 8

要写SQL简单的话,把Tbl_tempB的testname冗余到Tbl_tempA 中去把..这样你只需要select * from Tbl_tempA 就OK了.否则真是Tbl_tempA 你就得关联几次Tbl_tempB.有时候适当的冗余是有必要的,也没必要一味的追求范式