No question at this time
DBA Top 10
1 M. Cadot 5400
2 P. Wisse 1300
3 P. Tsongayinwe 1100
4 B. Vroman 500
4 J. Schnackenberg 500
6 M. Pagac 400
7 A. Deledda 200
7 D. Walgude 200
7 B. M 200
10 J. Alcroft 100
10 G. Trivedi 100
10 A. Hudspith 100
10 V. Sharma 100
10 L. Ywema 100
About
DBA-Village
Advertise here
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48742
Total active users1337
Act. users last 24h1
Act. users last hour0
Registered user hits last week108
Registered user hits last month471
Go up

More Efficient Procedure ?
Next thread: Interval Partitioning
Prev thread: catbundle.sql psu apply going to hung state after applying July PSU Patch

Message Score Author Date
Hey All, We need a procedure to get some data. S...... aqeel abbas Aug 18, 2015, 09:22
CREATE TABLE TEST (ID INT, A INT, B INT, ISACHK CH...... aqeel abbas Aug 18, 2015, 09:24
>>> <i> Is there any way to check execution plan...... Michel Cadot Aug 18, 2015, 11:19
Thank you Michel for your response. Actually we a...... aqeel abbas Aug 18, 2015, 11:25
My query is that, which one is less expensive and ...... aqeel abbas Aug 18, 2015, 11:37
The most efficient way is to not use PL/SQL, jus...... Michel Cadot Aug 18, 2015, 11:53
Michel we need to use PL/SQL, can you please tell ...... aqeel abbas Aug 18, 2015, 15:29
Why don't you tell us what the procedures intend...... Michel Cadot Aug 18, 2015, 15:31

Follow up by mail Click here

Subject: More Efficient Procedure ?
Author: aqeel abbas, Pakistan
Date: Aug 18, 2015, 09:22, 2846 days ago
Os info: RHEL 6.5
Oracle info: 11.2.0.4
Message: Hey All,
We need a procedure to get some data.
Script for test case is following.


CREATE TABLE TEST (ID INT, A INT, B INT, ISACHK CHAR(1), ISBCHK CHAR(1), ISCCHK CHAR(1), VALUE NUMBER(14,2));

Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (1,110,112,'1','1','1',550,1);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (2,110,112,'0','0','1',778,1);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (3,110,113,'0','0','0',778,2);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (4,210,114,'0','1','0',778,1);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (5,210,114,'1','1','1',778,2);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (6,310,114,'1','1','1',550,2);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (7,310,114,'1','0','0',550,3);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (8,310,114,'1','1','0',550,3);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (9,310,112,'1','1','1',550,1);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (10,310,111,'1','1','1',550,3);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (11,110,112,'1','1','1',550,1);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (12,110,112,'1','1','1',778,2);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (13,110,113,'1','1','1',778,4);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (14,210,114,'1','1','1',778,1);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (15,210,114,'1','1','1',778,3);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (16,310,114,'1','1','1',550,4);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (17,310,114,'1','1','1',550,3);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (18,310,114,'1','1','1',550,1);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (19,310,112,'1','1','1',550,4);
Insert into TEST.TEST (ID,A,B,ISACHK,ISBCHK,ISCCHK,VALUE,STATUSID) values (20,310,111,'1','1','1',550,3);

create or replace PROCEDURE PROC_TEST_1
(
v_Pram1 IN NUMBER,
v_Pram2 IN NUMBER,
p_DataSource OUT Sys_RefCursor
)
AS
BEGIN
IF v_Pram2 = 1 THEN
OPEN p_DataSource FOR
SELECT Id,
CASE WHEN ISACHK = 1 AND ISBCHK = 1 AND ISCCHK = 1
THEN 1 ELSE 0 END AS isChecked
FROM Test
WHERE StatusId != 3 and StatusId ! = 4
AND
a = v_Pram1;


ELSIF v_Pram2 = 2 THEN
OPEN p_DataSource FOR
SELECT Id,
CASE WHEN ISACHK = 1 AND ISBCHK = 1 AND ISCCHK = 1
THEN 1 ELSE 0 END AS isChecked
FROM Test
WHERE StatusId != 3 and StatusId ! = 4
AND
b = v_Pram1;
END IF;
END;


create or replace PROCEDURE PROC_TEST_2
(
v_Pram1 IN NUMBER,
v_Pram2 IN NUMBER,
p_DataSource OUT Sys_RefCursor
)
AS
BEGIN
OPEN p_DataSource FOR
SELECT Id,
CASE WHEN ISACHK = 1 AND ISBCHK = 1 AND ISCCHK = 1
THEN 1 ELSE 0 END AS isChecked
FROM Test
WHERE StatusId != 3 and StatusId ! = 4
AND
(CASE WHEN v_Pram2 = 1 THEN a
WHEN v_Pram2 = 2 THEN b
END) = v_Pram1;
END;

My question is that which one procedure will be more efficient and less expensive. Is there any way to check execution plan for it.
I think that we can check execution plan only for sql not pl/sql.

Thanks in advance...

Regards,
Aqeel.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: More Efficient Procedure ?
Author: aqeel abbas, Pakistan
Date: Aug 18, 2015, 09:24, 2846 days ago
Message: CREATE TABLE TEST (ID INT, A INT, B INT, ISACHK CHAR(1), ISBCHK CHAR(1), ISCCHK CHAR(1), VALUE NUMBER(14,2), STATUSID INT);
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: More Efficient Procedure ?
Author: Michel Cadot, France
Date: Aug 18, 2015, 11:19, 2846 days ago
Message:
>>> Is there any way to check execution plan for it.

There is no Oracle execution plan for PL/SQL. The actual execution plan is your algorithm.


>>> My question is that which one procedure will be more efficient and less expensive

Tell us what you want to get.
What the procedures are trying to achieve?

Regards
Michel

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: More Efficient Procedure ?
Author: aqeel abbas, Pakistan
Date: Aug 18, 2015, 11:25, 2846 days ago
Message: Thank you Michel for your response.
Actually we are getting some data from a very large table using the same algorithm as i mentioned in the example.
That's way i want to know which procedure is more efficient to use.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: More Efficient Procedure ?
Author: aqeel abbas, Pakistan
Date: Aug 18, 2015, 11:37, 2846 days ago
Message: My query is that, which one is less expensive and more efficient in following two ?

1. Using If--ElsIf.
2. Using Case in Where Clause.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: More Efficient Procedure ?
Author: Michel Cadot, France
Date: Aug 18, 2015, 11:53, 2846 days ago
Message:
The most efficient way is to not use PL/SQL, just SQL.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: More Efficient Procedure ?
Author: aqeel abbas, Pakistan
Date: Aug 18, 2015, 15:29, 2846 days ago
Message: Michel we need to use PL/SQL, can you please tell me in the above mentioned procedures which one will be most efficient and less expensive.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: More Efficient Procedure ?
Author: Michel Cadot, France
Date: Aug 18, 2015, 15:31, 2846 days ago
Message:
Why don't you tell us what the procedures intend to do?

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here