Home » SQL & PL/SQL » SQL & PL/SQL » WHICH ONE IS FASTER?
WHICH ONE IS FASTER? [message #260540] Mon, 20 August 2007 04:48 Go to next message
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
Junior Member
Hi,

I am transfering data from one table to another table using view.

case 1: In that view i am populating the data for one column using a query with self join.

CREATE OR REPLACE VIEW DMPRESTO.CARD_VW
(CARD_NUM, CYC_CODE, CARD_TYPE,
CARD_MEM_TYPE,
PRIMARY_CARD_NUM,
ACT_CARD_NUM,
BILL_FLAG,
TX_CODE, TX_DESC, DR_CR_CODE, AMOUNT_RS)
AS
SELECT PARENT_CARD_NUM, CYC_CODE, CARD_TYPE,
CARD_MEM_TYPE,
(SELECT TO_CHAR(B.CARD_NUM) FROM CDMST A,CDMST B WHERE A.RECARD_NUM=STG_UNBILL.CARD_NUM AND SUBSTR(A.CARD_NUM,1,14)=SUBSTR(B.CARD_NUM,1,14) AND B.NEXT_CARD_NO IS NULL),
ACT_CARD_NUM,
BILL_FLAG,
TXN_CODE, ' ', DR_CR_CODE, AMOUNT_RS
FROM UNBILLED_TXN, CDMST
WHERE STG_UNBILL.CARD_NUM = TGT_CDMST.RECARD_NUM


case 2: In same view for same column i am populating the data using function.Means i created one separate
function.that function m calling in this view with paasing parameter.

CREATE OR REPLACE VIEW DMPRESTO.CARD_VW
(CARD_NUM, CYC_CODE, CARD_TYPE,
CARD_MEM_TYPE,
PRIMARY_CARD_NUM,
ACT_CARD_NUM,
BILL_FLAG,
TX_CODE, TX_DESC, DR_CR_CODE, AMOUNT_RS)
AS
SELECT CDMST.PARENT_CARD_NUM, CYC_CODE, CARD_TYPE,
CARD_MEM_TYPE,
GEN_PRIMARY_CARD(UNBILL.CARD_NUM,0),
ACT_CARD_NUM,
BILL_FLAG,
TXN_CODE, ' ', DR_CR_CODE, AMOUNT_RS
FROM UNBILLED_TXN UNBILL, CDMST
WHERE UNBILL.CARD_NUM = CDMST.RECARD_NUM

and in this source table i have 10 million data .Please suggest me for this much data which case will
be good function or query with join condition.
Re: WHICH ONE IS FASTER? [message #260546 is a reply to message #260540] Mon, 20 August 2007 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/mv/msg/86779/256408/102589/#msg_256408

Regards
Michel
Re: WHICH ONE IS FASTER? [message #260780 is a reply to message #260546] Mon, 20 August 2007 22:27 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Both will perform terribly, because the Select/Function is nested - it runs once for each row returned. If you return 10M rows, it runs 10M times.

You need to work out a way to merge the nested query in with the main SELECT in the FROM clause so that Oracle can use joins instead of nesting.

Ross Leishman
Previous Topic: PLS-00201
Next Topic: How To recall old data
Goto Forum:
  


Current Time: Sat Dec 10 09:05:18 CST 2016

Total time taken to generate the page: 0.09480 seconds