3、 那个SQL有可能用到索引(),不能用到索引的请说明原因
SQL> DESC TEST
Name Type Nullable
---- ------------ --------
ID NUMBER
NAME VARCHAR2(20) Y
a) SELECT COUNT(NAME) FROM TEST;
b) SELECT * FROM TEST WHERE ID=’1’;
c) SELECT * FROM TEST WHERE UPPER(NAME)=UPPER(‘ORACLE’);
d) SELECT ID FROM TEST; 4、 在一个业务繁忙的系统更新一个有5000万行的表T的列C2的值为1,如何做?5、 表关联更新
有如下两个表:
SQL> desc t1
Name Type
---- ------------
ID INTEGER
NAME VARCHAR2(20) SQL> desc t2
Name Type
---- ------------
ID NUMBER
NAME VARCHAR2(20)T1和T2的ID都是主键。现需要根据ID将T2中NAME的值更新到T1的NAME列中,请写出SQL。6、 尽管表都经过正确的分析,但下面的SQL执行起来仍非常缓慢,请分析调优。
SQL> SELECT OOR.*
2 FROM NDMAIN.ORD_ORDER_ITEM_CEN OOI, NDMAIN.ORD_ORDER_RECEIVE_CEN OOR
3 WHERE OOI.PLAT_ID IN
4 ('FR20T0000020000400000002', 'FR20T0000020000400000131',
5 'FR20T0000020000400000048')
6 AND OOI.RECORD_ID = OOR.ORDER_ITEM_ID;Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3805 Card=369821 Byt
es=96153460) 1 0 MERGE JOIN (Cost=3805 Card=369821 Bytes=96153460)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ORD_ORDER_RECEIVE_CEN'
(Cost=826 Card=9145040 Bytes=1920458400) 3 2 INDEX (FULL SCAN) OF 'TU_ORD_ORDER_RECEIVE_ORDER_ID' (
NON-UNIQUE) (Cost=26 Card=9145040) 4 1 SORT (JOIN) (Cost=2979 Card=358466 Bytes=17923300)
5 4 INLIST ITERATOR
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'ORD_ORDER_ITEM_CEN
' (Cost=28 Card=358466 Bytes=17923300) 7 6 INDEX (RANGE SCAN) OF 'TU_ORD_ORD_I_PLAT_CREATE_DA
TE' (NON-UNIQUE) (Cost=2 Card=31)7、 下面是一个SQL和它对应的执行计划及执行计划统计信息,请分析存在的问题以及改进的方法:
suk@ORACLE9I> select count(1) from t; COUNT(1)
----------
32768
suk@ORACLE9I> SELECT * FROM T WHERE OBJECT_ID=97;已选择32768行。执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=76)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
76) 2 1 INDEX (RANGE SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card=
1)统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4792 consistent gets
0 physical reads
0 redo size
462033 bytes sent via SQL*Net to client
24396 bytes received via SQL*Net from client
2186 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32768 rows processed===============================================================
以前从没接触过Oracle,希望大虾给予指点。。谢谢
SQL> DESC TEST
Name Type Nullable
---- ------------ --------
ID NUMBER
NAME VARCHAR2(20) Y
a) SELECT COUNT(NAME) FROM TEST;
b) SELECT * FROM TEST WHERE ID=’1’;
c) SELECT * FROM TEST WHERE UPPER(NAME)=UPPER(‘ORACLE’);
d) SELECT ID FROM TEST; 4、 在一个业务繁忙的系统更新一个有5000万行的表T的列C2的值为1,如何做?5、 表关联更新
有如下两个表:
SQL> desc t1
Name Type
---- ------------
ID INTEGER
NAME VARCHAR2(20) SQL> desc t2
Name Type
---- ------------
ID NUMBER
NAME VARCHAR2(20)T1和T2的ID都是主键。现需要根据ID将T2中NAME的值更新到T1的NAME列中,请写出SQL。6、 尽管表都经过正确的分析,但下面的SQL执行起来仍非常缓慢,请分析调优。
SQL> SELECT OOR.*
2 FROM NDMAIN.ORD_ORDER_ITEM_CEN OOI, NDMAIN.ORD_ORDER_RECEIVE_CEN OOR
3 WHERE OOI.PLAT_ID IN
4 ('FR20T0000020000400000002', 'FR20T0000020000400000131',
5 'FR20T0000020000400000048')
6 AND OOI.RECORD_ID = OOR.ORDER_ITEM_ID;Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3805 Card=369821 Byt
es=96153460) 1 0 MERGE JOIN (Cost=3805 Card=369821 Bytes=96153460)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ORD_ORDER_RECEIVE_CEN'
(Cost=826 Card=9145040 Bytes=1920458400) 3 2 INDEX (FULL SCAN) OF 'TU_ORD_ORDER_RECEIVE_ORDER_ID' (
NON-UNIQUE) (Cost=26 Card=9145040) 4 1 SORT (JOIN) (Cost=2979 Card=358466 Bytes=17923300)
5 4 INLIST ITERATOR
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'ORD_ORDER_ITEM_CEN
' (Cost=28 Card=358466 Bytes=17923300) 7 6 INDEX (RANGE SCAN) OF 'TU_ORD_ORD_I_PLAT_CREATE_DA
TE' (NON-UNIQUE) (Cost=2 Card=31)7、 下面是一个SQL和它对应的执行计划及执行计划统计信息,请分析存在的问题以及改进的方法:
suk@ORACLE9I> select count(1) from t; COUNT(1)
----------
32768
suk@ORACLE9I> SELECT * FROM T WHERE OBJECT_ID=97;已选择32768行。执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=76)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
76) 2 1 INDEX (RANGE SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card=
1)统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4792 consistent gets
0 physical reads
0 redo size
462033 bytes sent via SQL*Net to client
24396 bytes received via SQL*Net from client
2186 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32768 rows processed===============================================================
以前从没接触过Oracle,希望大虾给予指点。。谢谢
6可能是在NDMAIN.ORD_ORDER_ITEM_CEN上建PLAT_ID、RECORD_ID的复合索引。
7可能是错误的使用了索引IDX_T,措施是表分析或删除该索引。
3由于没有说索引是怎么建的,所以我看2,3,4都有可能用到索引,1没有把握是否可以用到索引,
update (select t1.name t1_name,t2.name t2_name from t1,t2 where t1.id=t2.id) set t1_name=t2_name;
2 FROM NDMAIN.ORD_ORDER_ITEM_CEN OOI, NDMAIN.ORD_ORDER_RECEIVE_CEN OOR
3 WHERE OOI.PLAT_ID IN
4 ('FR20T0000020000400000002', 'FR20T0000020000400000131',
5 'FR20T0000020000400000048')
6 AND OOI.RECORD_ID = OOR.ORDER_ITEM_ID;
试验一下,你可以使用一下toad的Optimize sql帮你产生优化方案,它会产生几个基于成本的优化过的sql语句给你.
http://www.onlineseo.cn/catalog/9/index_1.htm