一个SQL查询缓慢的问题

Question

Oracle10g，默认安装后没有做任何优化。内存1G.
我的系统是通过用户输入查询条件来拼出SQL，在Oracle中执行查询。各种条件之间是内联合的SQL。
在测试过程中发现，如果用户输入的参数越来越多的话，查询效率降低得很快。
有一个测试项要求输入100个查询条件，实际查询花了10分钟才返回！同样的条件在SQL Server中1分钟返回。
因为是程序生成的SQL，SQL内容不好修改，所以我想通过优化Oracle的配置来解决问题。
请各位高手给点建议。
下面是我的查询例子的SQL（输入70个查询条件，用了98秒返回。），大家可以试一下，真的很慢。
由于SQL太长又不能发附件，SQL发在二楼了。建表SQL:
DROP TABLE A_BOOKINFO CASCADE CONSTRAINTS ; CREATE TABLE A_BOOKINFO ( 
  BOOKTYPE      VARCHAR2 (30)  NOT NULL, 
  BOOKID        NUMBER (30)   NOT NULL, 
  SCHEMAVERSION  VARCHAR2 (10), 
  CONSTRAINT PRI_BOOKID
  PRIMARY KEY ( BOOKID ) 
   );  DROP TABLE A_BOOKINFO_STRING CASCADE CONSTRAINTS ; CREATE TABLE A_BOOKINFO_STRING ( 
  BOOKID     NUMBER (30)   NOT NULL, 
  FIELDNAME   VARCHAR2 (100)  NOT NULL, 
  FIELDVALUE  VARCHAR2 (100)  NOT NULL
 );
DROP TABLE A_BOOKINFO_TIME CASCADE CONSTRAINTS ; CREATE TABLE A_BOOKINFO_TIME ( 
  BOOKID     NUMBER (30)   NOT NULL, 
  FIELDNAME   VARCHAR2 (100)  NOT NULL, 
  FIELDVALUE  CHAR(17)
);CREATE INDEX ix_BOOKINFO_BOOKtype ON A_BOOKINFO(BOOKTYPE);CREATE INDEX ix_string_BOOKid ON A_BOOKINFO_STRING(BOOKID);
CREATE INDEX ix_string_fieldname ON A_BOOKINFO_STRING(FIELDNAME);
CREATE INDEX ix_string_fieldvalue ON A_BOOKINFO_STRING(FIELDVALUE);CREATE INDEX ix_time_BOOKid ON A_BOOKINFO_TIME(BOOKID);
CREATE INDEX ix_time_fieldname ON A_BOOKINFO_TIME(FIELDNAME);
CREATE INDEX ix_time_fieldvalue ON A_BOOKINFO_TIME(FIELDVALUE);commit;

Answer 1

执行慢的SQL:
select a.BOOKID,a.BOOKTYPE,b.FIELDNAME,b.FIELDVALUE from 
A_BOOKINFO a,A_BOOKINFO_TIME b  ,
(select distinct a2.BOOKID from  
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String1' and (FIELDVALUE='00001')) a0 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String2' and (FIELDVALUE='00001')) a1 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String3' and (FIELDVALUE='00001')) a2 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String4' and (FIELDVALUE='00001')) a3 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String5' and (FIELDVALUE='00001')) a4 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String6' and (FIELDVALUE='00001')) a5 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String7' and (FIELDVALUE='00001')) a6 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String8' and (FIELDVALUE='00001')) a7 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String9' and (FIELDVALUE='00001')) a8 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String10' and (FIELDVALUE='00001')) a9 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String11' and (FIELDVALUE='00001')) a10 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String12' and (FIELDVALUE='00001')) a11 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String13' and (FIELDVALUE='00001')) a12 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String14' and (FIELDVALUE='00001')) a13 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String15' and (FIELDVALUE='00001')) a14 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String16' and (FIELDVALUE='00001')) a15 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String17' and (FIELDVALUE='00001')) a16 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String18' and (FIELDVALUE='00001')) a17 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String19' and (FIELDVALUE='00001')) a18 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String20' and (FIELDVALUE='00001')) a19 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String21' and (FIELDVALUE='00001')) a20 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String22' and (FIELDVALUE='00001')) a21 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String23' and (FIELDVALUE='00001')) a22 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String24' and (FIELDVALUE='00001')) a23 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String25' and (FIELDVALUE='00001')) a24 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String26' and (FIELDVALUE='00001')) a25 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String27' and (FIELDVALUE='00001')) a26 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String28' and (FIELDVALUE='00001')) a27 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String29' and (FIELDVALUE='00001')) a28 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String30' and (FIELDVALUE='00001')) a29 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String31' and (FIELDVALUE='00001')) a30 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String32' and (FIELDVALUE='00001')) a31 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String33' and (FIELDVALUE='00001')) a32 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String34' and (FIELDVALUE='00001')) a33 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String35' and (FIELDVALUE='00001')) a34 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String36' and (FIELDVALUE='00001')) a35 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String37' and (FIELDVALUE='00001')) a36 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String38' and (FIELDVALUE='00001')) a37 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String39' and (FIELDVALUE='00001')) a38 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String40' and (FIELDVALUE='00001')) a39 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String41' and (FIELDVALUE='00001')) a40 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String42' and (FIELDVALUE='00001')) a41 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String43' and (FIELDVALUE='00001')) a42 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String44' and (FIELDVALUE='00001')) a43 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String45' and (FIELDVALUE='00001')) a44 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String46' and (FIELDVALUE='00001')) a45 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String47' and (FIELDVALUE='00001')) a46 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String48' and (FIELDVALUE='00001')) a47 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String49' and (FIELDVALUE='00001')) a48 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String50' and (FIELDVALUE='00001')) a49 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String51' and (FIELDVALUE='00001')) a50 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String52' and (FIELDVALUE='00001')) a51 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String53' and (FIELDVALUE='00001')) a52 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String54' and (FIELDVALUE='00001')) a53 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String55' and (FIELDVALUE='00001')) a54 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String56' and (FIELDVALUE='00001')) a55 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String57' and (FIELDVALUE='00001')) a56 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String58' and (FIELDVALUE='00001')) a57 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String59' and (FIELDVALUE='00001')) a58 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String60' and (FIELDVALUE='00001')) a59 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String61' and (FIELDVALUE='00001')) a60 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String62' and (FIELDVALUE='00001')) a61 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String63' and (FIELDVALUE='00001')) a62 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String64' and (FIELDVALUE='00001')) a63 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String65' and (FIELDVALUE='00001')) a64 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String66' and (FIELDVALUE='00001')) a65 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String67' and (FIELDVALUE='00001')) a66 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String68' and (FIELDVALUE='00001')) a67 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String69' and (FIELDVALUE='00001')) a68 ,
(select BOOKID from A_BOOKINFO_STRING where FIELDNAME='String70' and (FIELDVALUE='00001')) a69 

Answer 2

where a2.BOOKID=a0.BOOKID and a2.BOOKID=a1.BOOKID and a2.BOOKID=a3.BOOKID and a2.BOOKID=a4.BOOKID and a2.BOOKID=a5.BOOKID and a2.BOOKID=a6.BOOKID and a2.BOOKID=a7.BOOKID and a2.BOOKID=a8.BOOKID and a2.BOOKID=a9.BOOKID and a2.BOOKID=a10.BOOKID and a2.BOOKID=a11.BOOKID and a2.BOOKID=a12.BOOKID and a2.BOOKID=a13.BOOKID and a2.BOOKID=a14.BOOKID and a2.BOOKID=a15.BOOKID and a2.BOOKID=a16.BOOKID and a2.BOOKID=a17.BOOKID and a2.BOOKID=a18.BOOKID and a2.BOOKID=a19.BOOKID and a2.BOOKID=a20.BOOKID and a2.BOOKID=a21.BOOKID and a2.BOOKID=a22.BOOKID and a2.BOOKID=a23.BOOKID and a2.BOOKID=a24.BOOKID and a2.BOOKID=a25.BOOKID and a2.BOOKID=a26.BOOKID and a2.BOOKID=a27.BOOKID and a2.BOOKID=a28.BOOKID and a2.BOOKID=a29.BOOKID and a2.BOOKID=a30.BOOKID and a2.BOOKID=a31.BOOKID and a2.BOOKID=a32.BOOKID and a2.BOOKID=a33.BOOKID and a2.BOOKID=a34.BOOKID and a2.BOOKID=a35.BOOKID and a2.BOOKID=a36.BOOKID and a2.BOOKID=a37.BOOKID and a2.BOOKID=a38.BOOKID and a2.BOOKID=a39.BOOKID and a2.BOOKID=a40.BOOKID and a2.BOOKID=a41.BOOKID and a2.BOOKID=a42.BOOKID and a2.BOOKID=a43.BOOKID and a2.BOOKID=a44.BOOKID and a2.BOOKID=a45.BOOKID and a2.BOOKID=a46.BOOKID and a2.BOOKID=a47.BOOKID and a2.BOOKID=a48.BOOKID and a2.BOOKID=a49.BOOKID and a2.BOOKID=a50.BOOKID and a2.BOOKID=a51.BOOKID and a2.BOOKID=a52.BOOKID and a2.BOOKID=a53.BOOKID and a2.BOOKID=a54.BOOKID and a2.BOOKID=a55.BOOKID and a2.BOOKID=a56.BOOKID and a2.BOOKID=a57.BOOKID and a2.BOOKID=a58.BOOKID and a2.BOOKID=a59.BOOKID and a2.BOOKID=a60.BOOKID and a2.BOOKID=a61.BOOKID and a2.BOOKID=a62.BOOKID and a2.BOOKID=a63.BOOKID and a2.BOOKID=a64.BOOKID and a2.BOOKID=a65.BOOKID and a2.BOOKID=a66.BOOKID and a2.BOOKID=a67.BOOKID and a2.BOOKID=a68.BOOKID and a2.BOOKID=a69.BOOKID) subt 
where a.BOOKID=b.BOOKID and a.BOOKID = subt.BOOKID

Answer 3

1楼+2楼的SQL是执行慢的SQL，贴完了

Answer 4

因为是程序自动生成的SQL，而且受表结构的限制，只能利用子查询来查。能不能通过优化Oracle性能来实现提高查询效率呢？

Answer 5

太多子查询了，语句可不能这么写啊，会自取灭亡的。 
我猜想是否你的FIELDNAME 都是 “String+数字”的形式 ？
我用下面的语句来代替你的 subt 子查询你看是否可以？
select bookid  from A_BOOKINFO_STRING where 
FIELDNAME like 'String%' and to_number(substr(FIELDNAME,7)) between 1 and 70 and (FIELDVALUE='00001') 
group by bookid
having  count(distinct FIELDNAME) =70

Answer 6

你想吧oracle弄糊涂呀，要吧业务和实现分开。
建议改改你的设计吧

Answer 7

谢谢大家参与讨论。
首先表的设计不是我做的，
其次，当初设计成这样的目的主要是考虑扩展性。举个例子，一个BOOK对象可能的属性是不确定的（属性名称和个数都不定），所以用了TABLE A_BOOKINFO_STRING 表来专门存储属性，FIELDNAME是属性名称（String+数字是测试数据，实际应用时是不定长的字符串），FIELDVALUE是属性值。
即TABLE A_BOOKINFO 与TABLE A_BOOKINFO_STRING是一对多的关系。
而查询的时候要根据TABLE A_BOOKINFO_STRING中的属性名称和值来查询，这个例子中就是要查询符合以上所有属性的BOOK对象，当然个数较多比较极端。既然大家对表的设计有异议，请问为了满足这样的需求该如何设计表呢？
如果不更改表的设计和程序实现，只是从优化Oracle的角度如何设置呢？毕竟同样的SQL在SQLServer中执行速度明显快于Oracle。

Answer 8

直接拿这条语句来对比两个不同的数据库，其实没有太大的可比性。
你还是老老实实修改下语句吧，其实你的表结构也没有什么问题，主要是语句太糟糕。
还是要简化你的子查询。将 属性的名称写入下面的in list 中，并且最后 将所有属性的个数 放到 :num 中。
你的程序可以这样改吗？select bookid  from A_BOOKINFO_STRING where
FIELDNAME in ("String1",...."String70")  and (FIELDVALUE='00001')
group by bookid
having  count(distinct FIELDNAME) = :num

Answer 9

to 飘流瓶
CC各位:
FIELDNAME in ("String1",...."String70")  and (FIELDVALUE='00001') 
好像是不行的。
因为要实现下面的查询要求
FIELDNAME = String1时要求FIELDVALUE="A"
FIELDNAME = String2时要求FIELDVALUE="B"
.....也就是FIELDNAME 与FIELDVALUE是成对出现的查询条件，而查询时又要多对条件同时查询。例子上比较简单都用了0001

Answer 10

DROP TABLE A_BOOKINFO CASCADE CONSTRAINTS ; CREATE TABLE A_BOOKINFO ( 
  BOOKTYPE      VARCHAR2 (30)  NOT NULL, 
  BOOKID        NUMBER (30)  NOT NULL, 
  SCHEMAVERSION  VARCHAR2 (10), 
  CONSTRAINT PRI_BOOKID 
  PRIMARY KEY ( BOOKID ) 
  );  DROP TABLE A_BOOKINFO_STRING CASCADE CONSTRAINTS ; CREATE TABLE A_BOOKINFO_STRING ( 
  BOOKID    NUMBER (30)  NOT NULL, 
  FIELDNAME  VARCHAR2 (100)  NOT NULL, 
  FIELDVALUE  VARCHAR2 (100)  NOT NULL 
); 
DROP TABLE A_BOOKINFO_TIME CASCADE CONSTRAINTS ; CREATE TABLE A_BOOKINFO_TIME ( 
  BOOKID    NUMBER (30)  NOT NULL, 
  FIELDNAME  VARCHAR2 (100)  NOT NULL, 
  FIELDVALUE  CHAR(17) 
); CREATE INDEX ix_BOOKINFO_BOOKtype ON A_BOOKINFO(BOOKTYPE); CREATE INDEX ix_string_BOOKid ON A_BOOKINFO_STRING(BOOKID); 
CREATE INDEX ix_string_fieldname ON A_BOOKINFO_STRING(FIELDNAME); 
CREATE INDEX ix_string_fieldvalue ON A_BOOKINFO_STRING(FIELDVALUE); CREATE INDEX ix_time_BOOKid ON A_BOOKINFO_TIME(BOOKID); 
CREATE INDEX ix_time_fieldname ON A_BOOKINFO_TIME(FIELDNAME); 
CREATE INDEX ix_time_fieldvalue ON A_BOOKINFO_TIME(FIELDVALUE); commit; 

Answer 11

在没有数据的情况下，ORA首次执行花费了30S出结果下次执行花费时间0.016S………………

Answer 12

呵呵！学习，以后我绝不写成这样的SQL语句！