Which sql code is more appropriate 1

[dbalearner]

This is a simple SQL that tries to replace 1024*1024 with a bind variable.

Finding out table and index size for a table in MB

The easy one

1 select substr(segment_name,1,30) AS "Object",
2 sum(bytes)/1024/1024 AS "Size/MB"
3 from user_extents where segment_name in ('NULLTEST','NULLTEST_ID1')
4* group by segment_name;

Object Size/MB
------------------------------ ----------
NULLTEST_ID1 4
NULLTEST 9


Using bind variable directly

1 variable unt number;
2 exec :unt :=1024*1024;

1 select substr(s.segment_name,1,30) as "Object",
2 sum(s.bytes)/:unt AS "Size/MB"
3 from user_extents s
4 where s.segment_name in ('NULLTEST','NULLTEST_ID1')
5* group by s.segment_name;

Object Size/MB
------------------------------ ----------
NULLTEST 9
NULLTEST_ID1 4


Or using the bind variable in the select statement

1 variable unt number;
2 exec :unt :=1024*1024;

1 select substr(s.segment_name,1,30) as "Object",
2 sum(s.bytes/b.unit) AS "Size/MB"
3 from user_extents s, (select :unt AS unit from dual) b
4 where s.segment_name in ('NULLTEST','NULLTEST_ID1')
5* group by s.segment_name;

Object Size/MB
------------------------------ ----------
NULLTEST_ID1 4
NULLTEST 9

I guess the last method is clearer for everyone.

Thanks,

Learner

 

[SantaMufasa]

Learner,

When you ask, "Which sql code is more appropriate," I must ask "Appropriate for what?"? Are you talking readability/ease of understanding, performance, something else?

For readability/ease of understanding, I suggest that "1024/1024" is preferrable. For performance, bind variables avoid reparsing over literals.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[dbalearner]

Hi Santa,

Thanks for reply. Just needed a second opinion.

Regards,

Learner

 

[Thargy]

learner,

I don't quite see the benefit of a bind variable (or any kind of variable for that matter) in this situation.

Since 1024* 1024 is a constant, why are you trying to make it into a variable? It's like defining a variable called NUMBER_TWO and setting it equal to 2. Since 2 will be the same today, tomorrow and for ever, why make it a variable?

Regards

T