视图1:create or replace view accoinfotemp1 as
select
SYSDATE AS C_MODIFYDATE,
to_char(a.CUSTID) as c_custno,
from CRM_PERSONAL_CUST b,CRM_ACCOINFO a
where TO_CHAR(b.CUSTID)=TO_CHAR(a.CUSTID )
视图2:create or replace view accoinfotemp2 as
select
SYSDATE AS C_MODIFYDATE,
to_char(A.CUSTID) as c_custno,
from CRM_ORG_CUST d,CRM_ACCOINFO A
whereto_char(d.CUSTID)=to_char(A.CUSTID)
视图3:create or replace view customer.accoinfotemp as
select* from accoinfotemp1
union all
select* from accoinfotemp2
表的说明:
CRM_PERSONAL_CUST有大约90万记录,CUSTID是num型
CRM_ACCOINFO有大约100万记录,custid是num型。
这两个表上面分别建了custid索引和to_char(custid)索引。
sql语句1:
SELECT C_custno FROM ACCOINFOTEMP WHERE C_custno = '23223';
速度很快,使用了to_char(custid)索引
sql语句2:
SELECT C_CUSTNOFROM ACCOINFOtemp WHERE C_custno in
(SELECT C_custno FROM ACCOINFOTEMP WHERE C_custno = '23223')
速度很慢(5-6秒),在acconinfo上使用了全表扫描。
如果将视图1、2中to_char(custid)改为custid,执行速度很快。
外层视图中死活不使用函数索引。
请问各位如何解决这个问题,提高查询效率?
谢谢!
|