我有一条语句在相同的数据库结构下,但是在不同的数据量下,oracle应用索引不一样,令人郁闷的是在大数据量环境下oracle没有听从我的命令应用合理索引,各位高手可曾遇到?
UPDATE tsharecurrents a
SET (c_bonustype) = (SELECT /*+ INDEX (tstaticshares istaticshares) */
c_bonustype
FROM tstaticshares b
WHERE b.c_fundacco = a.c_fundacco
AND b.c_fundcode = a.c_fundcode
AND b.c_sharetype = a.c_sharetype
AND b.c_agencyno = a.c_agencyno
AND b.c_netno = a.c_netno)
WHERE c_tano = '48'
AND c_custno = '0'
---------------------------------------------------------------------------
小数据量的分析结果
UPDATE STATEMENT, GOAL = CHOOSE 2 1 48
UPDATE CUSTOMER TSHARECURRENTS
TABLE ACCESS BY INDEX ROWID CUSTOMER TSTATICSHARES 3 1 32
INDEX RANGE SCAN CUSTOMER ISTATICSHARES 2 1
TABLE ACCESS FULL CUSTOMER TSHARECURRENTS 2 1 48
----------------------------------------------------------------------------
大数据量的分析结果
UPDATE STATEMENT, GOAL = CHOOSE 5 2 82
UPDATE GYRX TSHARECURRENTS
TABLE ACCESS BY INDEX ROWID GYRX TSHARECURRENTS 5 2 82
INDEX RANGE SCAN GYRX ISHARECURRENTS_CUSTNO 3 2
TABLE ACCESS BY INDEX ROWID GYRX TSTATICSHARES 2 1 30
INDEX RANGE SCAN GYRX ISTATICSHARES_FUNDCODE 1 1 另外我在小数据量环境下,对于上面的update强制指定索引ISTATICSHARES_FUNDCODE也无效。就是说我的那条index命令(在大小两个数据量环境下都没有生效)根本就没有生效。下面附上两个表的结构。
---------------------------------------------------------------------------
-- Create table
create table TSHARECURRENTS
(
D_CDATE DATE not null,
C_CSERIALNO VARCHAR2(20) not null,
C_BUSINFLAG CHAR(2),
D_REQUESTDATE DATE,
C_REQUESTNO VARCHAR2(24),
C_CUSTNO VARCHAR2(12),
C_CUSTTYPE CHAR(1),
C_FUNDACCO VARCHAR2(12),
C_TRADEACCO VARCHAR2(17),
C_FUNDCODE VARCHAR2(6),
C_SHARETYPE CHAR(1),
C_AGENCYNO CHAR(3),
C_NETNO VARCHAR2(9),
F_OCCURSHARES NUMBER(16,2),
F_OCCURBALANCE NUMBER(16,2),
F_LASTSHARES NUMBER(16,2),
F_OCCURFREEZE NUMBER(16,2),
F_LASTFREEZESHARE NUMBER(16,2),
C_SUMMARY VARCHAR2(36),
F_GAINBALANCE NUMBER(16,2),
D_SHAREVALIDDATE DATE,
C_BONUSTYPE CHAR(1),
C_TANO CHAR(2),
C_BOURSEFLAG CHAR(1)
);
create unique index ISHARECURRENTS on TSHARECURRENTS (C_CSERIALNO,D_CDATE,C_TANO);
create index ISHARECURRENTS_CDATE on TSHARECURRENTS (D_CDATE);
create index ISHARECURRENTS_CUSTNO on TSHARECURRENTS (C_CUSTNO,C_CUSTTYPE);
create index ISHARECURRENTS_FUNDACCO on TSHARECURRENTS (C_FUNDACCO);
create index ISHARECURRENTS_FUNDCODE on TSHARECURRENTS (C_FUNDCODE,C_SHARETYPE,C_AGENCYNO,C_NETNO);
create index ISHARECURRENTS_VALIDDATE on TSHARECURRENTS (D_SHAREVALIDDATE,C_TANO);-- Create table
create table TSTATICSHARES
(
C_FUNDACCO VARCHAR2(12) not null,
C_TRADEACCO VARCHAR2(17) not null,
C_FUNDCODE VARCHAR2(6) not null,
C_SHARETYPE CHAR(1) not null,
C_AGENCYNO CHAR(3) not null,
C_NETNO VARCHAR2(9) not null,
F_REALSHARES NUMBER(16,2) not null,
F_FROZENSHARES NUMBER(16,2) not null,
F_TRADEFREEZE NUMBER(16,2),
F_LASTSHARES NUMBER(16,2),
C_BONUSTYPE CHAR(1),
D_LASTMODIFY DATE,
F_INCOME NUMBER(16,2),
F_FROZENINCOME NUMBER(16,2),
C_TANO CHAR(2),
C_CUSTNO VARCHAR2(12),
C_CUSTTYPE CHAR(1),
C_SHARECLASS CHAR(1),
C_BOURSEFLAG CHAR(1)
);
create unique index ISTATICSHARES on TSTATICSHARES (C_FUNDACCO,C_TRADEACCO,C_FUNDCODE,C_SHARETYPE,C_AGENCYNO,C_NETNO);
create index ISTATICSHARES_CUSTNO on TSTATICSHARES (C_CUSTNO);
create index ISTATICSHARES_FUNDCODE on TSTATICSHARES (C_FUNDCODE,C_SHARETYPE,C_AGENCYNO,C_NETNO);
create index ISTATICSHARES_LASTMODIFY on TSTATICSHARES (D_LASTMODIFY);
UPDATE tsharecurrents a
SET (c_bonustype) = (SELECT /*+ INDEX (tstaticshares istaticshares) */
c_bonustype
FROM tstaticshares b
WHERE b.c_fundacco = a.c_fundacco
AND b.c_fundcode = a.c_fundcode
AND b.c_sharetype = a.c_sharetype
AND b.c_agencyno = a.c_agencyno
AND b.c_netno = a.c_netno)
WHERE c_tano = '48'
AND c_custno = '0'
---------------------------------------------------------------------------
小数据量的分析结果
UPDATE STATEMENT, GOAL = CHOOSE 2 1 48
UPDATE CUSTOMER TSHARECURRENTS
TABLE ACCESS BY INDEX ROWID CUSTOMER TSTATICSHARES 3 1 32
INDEX RANGE SCAN CUSTOMER ISTATICSHARES 2 1
TABLE ACCESS FULL CUSTOMER TSHARECURRENTS 2 1 48
----------------------------------------------------------------------------
大数据量的分析结果
UPDATE STATEMENT, GOAL = CHOOSE 5 2 82
UPDATE GYRX TSHARECURRENTS
TABLE ACCESS BY INDEX ROWID GYRX TSHARECURRENTS 5 2 82
INDEX RANGE SCAN GYRX ISHARECURRENTS_CUSTNO 3 2
TABLE ACCESS BY INDEX ROWID GYRX TSTATICSHARES 2 1 30
INDEX RANGE SCAN GYRX ISTATICSHARES_FUNDCODE 1 1 另外我在小数据量环境下,对于上面的update强制指定索引ISTATICSHARES_FUNDCODE也无效。就是说我的那条index命令(在大小两个数据量环境下都没有生效)根本就没有生效。下面附上两个表的结构。
---------------------------------------------------------------------------
-- Create table
create table TSHARECURRENTS
(
D_CDATE DATE not null,
C_CSERIALNO VARCHAR2(20) not null,
C_BUSINFLAG CHAR(2),
D_REQUESTDATE DATE,
C_REQUESTNO VARCHAR2(24),
C_CUSTNO VARCHAR2(12),
C_CUSTTYPE CHAR(1),
C_FUNDACCO VARCHAR2(12),
C_TRADEACCO VARCHAR2(17),
C_FUNDCODE VARCHAR2(6),
C_SHARETYPE CHAR(1),
C_AGENCYNO CHAR(3),
C_NETNO VARCHAR2(9),
F_OCCURSHARES NUMBER(16,2),
F_OCCURBALANCE NUMBER(16,2),
F_LASTSHARES NUMBER(16,2),
F_OCCURFREEZE NUMBER(16,2),
F_LASTFREEZESHARE NUMBER(16,2),
C_SUMMARY VARCHAR2(36),
F_GAINBALANCE NUMBER(16,2),
D_SHAREVALIDDATE DATE,
C_BONUSTYPE CHAR(1),
C_TANO CHAR(2),
C_BOURSEFLAG CHAR(1)
);
create unique index ISHARECURRENTS on TSHARECURRENTS (C_CSERIALNO,D_CDATE,C_TANO);
create index ISHARECURRENTS_CDATE on TSHARECURRENTS (D_CDATE);
create index ISHARECURRENTS_CUSTNO on TSHARECURRENTS (C_CUSTNO,C_CUSTTYPE);
create index ISHARECURRENTS_FUNDACCO on TSHARECURRENTS (C_FUNDACCO);
create index ISHARECURRENTS_FUNDCODE on TSHARECURRENTS (C_FUNDCODE,C_SHARETYPE,C_AGENCYNO,C_NETNO);
create index ISHARECURRENTS_VALIDDATE on TSHARECURRENTS (D_SHAREVALIDDATE,C_TANO);-- Create table
create table TSTATICSHARES
(
C_FUNDACCO VARCHAR2(12) not null,
C_TRADEACCO VARCHAR2(17) not null,
C_FUNDCODE VARCHAR2(6) not null,
C_SHARETYPE CHAR(1) not null,
C_AGENCYNO CHAR(3) not null,
C_NETNO VARCHAR2(9) not null,
F_REALSHARES NUMBER(16,2) not null,
F_FROZENSHARES NUMBER(16,2) not null,
F_TRADEFREEZE NUMBER(16,2),
F_LASTSHARES NUMBER(16,2),
C_BONUSTYPE CHAR(1),
D_LASTMODIFY DATE,
F_INCOME NUMBER(16,2),
F_FROZENINCOME NUMBER(16,2),
C_TANO CHAR(2),
C_CUSTNO VARCHAR2(12),
C_CUSTTYPE CHAR(1),
C_SHARECLASS CHAR(1),
C_BOURSEFLAG CHAR(1)
);
create unique index ISTATICSHARES on TSTATICSHARES (C_FUNDACCO,C_TRADEACCO,C_FUNDCODE,C_SHARETYPE,C_AGENCYNO,C_NETNO);
create index ISTATICSHARES_CUSTNO on TSTATICSHARES (C_CUSTNO);
create index ISTATICSHARES_FUNDCODE on TSTATICSHARES (C_FUNDCODE,C_SHARETYPE,C_AGENCYNO,C_NETNO);
create index ISTATICSHARES_LASTMODIFY on TSTATICSHARES (D_LASTMODIFY);
题目中使用了别名。