OraFAQ Forum: Performance Tuning » Perfomance tuning of a view

Home » RDBMS Server » Performance Tuning » Perfomance tuning of a view (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Perfomance tuning of a view [message #346679]

Tue, 09 September 2008 06:07

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.

Report message to a moderator

Re: Perfomance tuning of a view [message #346681 is a reply to message #346679]

Tue, 09 September 2008 06:16

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Hi.

1. In your view for EACH retrived row of table_name1 you are accessing the same data in table_name2 27 times.

Use OUTER join.

2. Use inline view to reduce the number of joins.

Michael

Report message to a moderator

Re: Perfomance tuning of a view [message #346693 is a reply to message #346679]

Tue, 09 September 2008 07:19

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in plan.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

Regards
Michel

Report message to a moderator

Re: Perfomance tuning of a view [message #346727 is a reply to message #346693]

Tue, 09 September 2008 09:09

Olivia
Messages: 519
Registered: June 2008

Senior Member

Also, Oracle database concept manual(For 10g, search through google)
http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/toc.htm It helps.

Regards,
Oli

[Updated on: Tue, 09 September 2008 09:10]

Report message to a moderator

Previous Topic:	to find out time taken
Next Topic:	Before Asking the Index Question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 22:19:32 CST 2024