Home » RDBMS Server » Performance Tuning » Perfomance tuning of a view (Oracle 10g)
Perfomance tuning of a view [message #346679] |
Tue, 09 September 2008 06:07 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
anantha
Messages: 1 Registered: September 2008
|
Junior Member |
|
|
Hi guys,
I have 2 tables created like this
CREATE TABLE table_name2
(
ACCOUNT_NO NUMBER(12),
DATE_CREATED DATE,
BILL_PERIOD VARCHAR2(6 BYTE),
CREDIT_LIMIT NUMBER(10,2),
CNAME VARCHAR2(255 BYTE),
BILL_COMPANY VARCHAR2(255 BYTE),
BILL_ADDRESS1 VARCHAR2(255 BYTE),
BILL_ADDRESS2 VARCHAR2(255 BYTE),
BILL_ADDRESS3 VARCHAR2(255 BYTE),
BILL_CITY VARCHAR2(75 BYTE),
BILL_ZIP VARCHAR2(25 BYTE),
CUST_PHONE1 VARCHAR2(25 BYTE),
CUST_PHONE2 VARCHAR2(25 BYTE),
CONTACT1_PHONE VARCHAR2(25 BYTE),
CONTACT2_PHONE VARCHAR2(25 BYTE),
CUST_ADDRESS1 VARCHAR2(255 BYTE),
CUST_ADDRESS2 VARCHAR2(255 BYTE),
CUST_ADDRESS3 VARCHAR2(255 BYTE),
CUST_CITY VARCHAR2(50 BYTE),
CUST_ZIP VARCHAR2(25 BYTE),
ACCOUNT_CATEGORY VARCHAR2(75 BYTE),
LAST_BILL_REFNO NUMBER(12),
LAST_BILLAMT NUMBER(10,2),
LAST_BILLDATE DATE,
LASTPAYMENT NUMBER(10,2),
LASTPAYDATE DATE,
PAY_METHOD VARCHAR2(75 BYTE),
TOTAL_DUE NUMBER(10,2),
OS_BILLCOUNT NUMBER(6),
DEPOSIT_AMT NUMBER(10,2),
BILLED_EXTID VARCHAR2(19 BYTE),
CONNECTNAME VARCHAR2(30 BYTE),
MOBNO VARCHAR2(10 BYTE),
STATUS VARCHAR2(15 BYTE)
);
CREATE INDEX ACC_INDX ON table_name2
(ACCOUNT_NO);
CREATE INDEX BILL_EX_ID234 ON table_name2
(BILLED_EXTID);
CREATE INDEX BILL_MOBNO111 ON table_name2
(MOBNO);
CREATE BITMAP INDEX BILL_STATUS1111 ON table_name2
(STATUS);
CREATE BITMAP INDEX table_name2_ACCTCAT ON table_name2
(ACCOUNT_CATEGORY);
CREATE BITMAP INDEX table_name2_BILLPER ON table_name2
(BILL_PERIOD);
Table :
CREATE TABLE table_name1
(
MOBNO VARCHAR2(10 BYTE),
BILL_PLAN VARCHAR2(175 BYTE),
EXTID VARCHAR2(20 BYTE),
BILLED_EXTID VARCHAR2(20 BYTE),
DISCONNECT_DESC VARCHAR2(200 BYTE),
CREDITLIMIT NUMBER(10,2),
ACCOUNT_CATEGORY VARCHAR2(75 BYTE),
ACCOUNT_NO NUMBER(12),
BILLED_AC NUMBER(12),
SUBSCR_NO NUMBER(12),
STATUS VARCHAR2(15 BYTE),
SERVICE_START DATE,
SERVICE_END DATE,
XFER_REASON VARCHAR2(255 BYTE),
DISCONNECT_REASON VARCHAR2(255 BYTE),
CONNECTNAME VARCHAR2(70 BYTE),
ORIG_SERVICE_START DATE,
PARENT_EXTID VARCHAR2(20 BYTE),
IMSI VARCHAR2(35 BYTE),
SUSPEND_COUNT NUMBER(6),
VAS_DETAILS VARCHAR2(75 BYTE)
);
CREATE INDEX ACC_INDX2 ON table_name1
(BILLED_AC);
CREATE INDEX sdf ON table_name1
(ORIG_SERVICE_START);
CREATE INDEX adsf_PARENT ON table_name1
(PARENT_EXTID);
CREATE BITMAP INDEX table_name1_ACCTCAT ON table_name1
(ACCOUNT_CATEGORY);
CREATE BITMAP INDEX table_name1_BILLPLAN ON table_name1
(BILL_PLAN);
CREATE INDEX EXID_INDX3 ON table_name1
(BILLED_EXTID);
CREATE INDEX EXID_INDXEXT3 ON table_name1
(EXTID);
CREATE INDEX MOB_INDX1231231 ON table_name1
(MOBNO);
CREATE BITMAP INDEX MOB_INDX12312312 ON table_name1
(STATUS);
CREATE INDEX MOB_INDX1231231452 ON table_name1
(SERVICE_END);
CREATE INDEX MOB_INDX12312322212 ON table_name1
(SERVICE_START);
I have a view created like this
CREATE OR REPLACE VIEW table_name1
(MOBNO, BILL_PLAN, EXTID, BILLED_EXTID, DISCONNECT_DESC,
CREDITLIMIT, ACCOUNT_CATEGORY, ACCOUNT_NO, BILLED_AC, SUBSCR_NO,
STATUS, SERVICE_START, SERVICE_END, XFER_REASON, DISCONNECT_REASON,
CONNECTNAME, ORIG_SERVICE_START, PARENT_EXTID, IMSI, SUSPEND_COUNT,
VAS_DETAILS, DATE_CREATED, BILL_PERIOD, CNAME, BILL_COMPANY,
BILL_ADDRESS1, BILL_ADDRESS2, BILL_ADDRESS3, BILL_CITY, BILL_ZIP,
CUST_PHONE1, CUST_PHONE2, CONTACT1_PHONE, CONTACT2_PHONE, CUST_ADDRESS1,
CUST_ADDRESS2, CUST_ADDRESS3, CUST_CITY, CUST_ZIP, LAST_BILL_REFNO,
LAST_BILLAMT, LAST_BILLDATE, LAST_PAYMENT, LAST_PAYDATE, PAY_METHOD,
TOTAL_DUE, OS_BILLCOUNT, DEPOSIT_AMT)
AS
select
a.MOBNO,
a.BILL_PLAN,
a.EXTID,
a.BILLED_EXTID,
a.DISCONNECT_DESC,
a.CREDITLIMIT,
a.ACCOUNT_CATEGORY,
a.ACCOUNT_NO,
a.BILLED_AC,
a.SUBSCR_NO,
a.STATUS,
a.SERVICE_START,
a.SERVICE_END,
a.XFER_REASON,
a.DISCONNECT_REASON,
a.CONNECTNAME,
a.orig_service_start,
a.parent_extid,
a.imsi ,a.suspend_count,a.vas_details,
(select b.DATE_CREATED from table_name2 b where b.account_no=a.Billed_ac) as DATE_CREATED ,
(select b.BILL_PERIOD from table_name2 b where b.account_no=a.Billed_ac) as BILL_PERIOD ,
(select b.CNAME from table_name2 b where b.account_no=a.Billed_ac) as CNAME ,
(select b.BILL_COMPANY from table_name2 b where b.account_no=a.Billed_ac) as BILL_COMPANY ,
(select b.BILL_ADDRESS1 from table_name2 b where b.account_no=a.Billed_ac) as BILL_ADDRESS1 ,
(select b.BILL_ADDRESS2 from table_name2 b where b.account_no=a.Billed_ac) as BILL_ADDRESS2 ,
(select b.BILL_ADDRESS3 from table_name2 b where b.account_no=a.Billed_ac) as BILL_ADDRESS3 ,
(select b.BILL_CITY from table_name2 b where b.account_no=a.Billed_ac) as BILL_CITY ,
(select b.BILL_ZIP from table_name2 b where b.account_no=a.Billed_ac) as BILL_ZIP ,
(select b.CUST_PHONE1 from table_name2 b where b.account_no=a.Billed_ac) as CUST_PHONE1 ,
(select b.CUST_PHONE2 from table_name2 b where b.account_no=a.Billed_ac) as CUST_PHONE2 ,
(select b.CONTACT1_PHONE from table_name2 b where b.account_no=a.Billed_ac) as CONTACT1_PHONE ,
(select b.CONTACT2_PHONE from table_name2 b where b.account_no=a.Billed_ac) as CONTACT2_PHONE ,
(select b.CUST_ADDRESS1 from table_name2 b where b.account_no=a.Billed_ac) as CUST_ADDRESS1 ,
(select b.CUST_ADDRESS2 from table_name2 b where b.account_no=a.Billed_ac) as CUST_ADDRESS2 ,
(select b.CUST_ADDRESS3 from table_name2 b where b.account_no=a.Billed_ac) as CUST_ADDRESS3 ,
(select b.CUST_CITY from table_name2 b where b.account_no=a.Billed_ac) as CUST_CITY ,
(select b.CUST_ZIP from table_name2 b where b.account_no=a.Billed_ac) as CUST_ZIP ,
(select b.LAST_BILL_REFNO from table_name2 b where b.account_no=a.Billed_ac) as LAST_BILL_REFNO ,
(select b.LAST_BILLAMT from table_name2 b where b.account_no=a.Billed_ac) as LAST_BILLAMT ,
(select b.LAST_BILLDATE from table_name2 b where b.account_no=a.Billed_ac) as LAST_BILLDATE ,
(select b.LASTPAYMENT from table_name2 b where b.account_no=a.Billed_ac) as LAST_PAYMENT ,
(select b.LASTPAYDATE from table_name2 b where b.account_no=a.Billed_ac) as LAST_PAYDATE ,
(select b.PAY_METHOD from table_name2 b where b.account_no=a.Billed_ac) as PAY_METHOD ,
(select b.TOTAL_DUE from table_name2 b where b.account_no=a.Billed_ac) as TOTAL_DUE ,
(select b.OS_BILLCOUNT from table_name2 b where b.account_no=a.Billed_ac) as OS_BILLCOUNT ,
(select b.DEPOSIT_AMT from table_name2 b where b.account_no=a.Billed_ac) as DEPOSIT_AMT
from table_name1 a
where service_start =(select max(service_start) from table_name1 v where v.mobno=a.mobno)
/
desc :
Table 1 has data of around 6.5 lakhs and table 2 has data of around 11.8 lakhs....
I map table1's Billed_ac column with table2's account_no column(if some data for table2 is needed)
The view was fast until a week ago i am facing real pain is getting data from the view.
Is there any way to optimize the view or the tables??if so kindly suggest me on the same...
Note: i cannot put any equi-join since i need all the rows from table_name1 matching or not with table_name2.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:32:05 CDT 2024
|