WWW.APPSERVGRID.COM

Phorum about AppServers & Grid Technologies
It is currently Wed May 22, 2019 12:30 pm

All times are UTC + 2 hours [ DST ]




Post new topic Reply to topic  [ 1 post ] 
Author Message
PostPosted: Wed Jan 30, 2013 5:02 pm 
Offline
Site Admin

Joined: Tue Jan 25, 2011 8:51 pm
Posts: 30
*************************
Using WHERE:
*************************


1)
Worse:
------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE SUBSTR( customer_n, 1, 3) = 'Luk';
------------------------------------------------
Better:
------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE customer_n LIKE 'Luk%';
-------------------------------------------------

2)
Worse:
-------------------------------------------------
var ls_n VARCHAR2( 20)
exec ls_n := 'Lukyanov'

SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE customer_n = NVL( :ls_n, customer_n);
--------------------------------------------------
Better:
--------------------------------------------------
var ls_n VARCHAR2( 20)
exec ls_n := 'Lukyanov'

SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE customer_n LIKE NVL( :ls_n, '%');
---------------------------------------------------

3)
Worse:
---------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE TRUNC( doc_dd) = TRUNC( sysdate);
---------------------------------------------------
Better (than more "9" than more preciously):
---------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE doc_dd BETWEEN TRUNC( sysdate)
AND TRUNC( sysdate) + 0.9999;
----------------------------------------------------

4)
Worse:
----------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE customer_n || doc_nd = 'Lukyanov29';
----------------------------------------------------
Better:
----------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE customer_n = 'Lukyanov'
AND doc_nd = ‘29';
-----------------------------------------------------

5)
Worse:
-----------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE total_doc + 500 < 2500;
------------------------------------------------------
Better:
------------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE total_doc < 2000;
------------------------------------------------------

6)
Worse:
-------------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE total_doc != 0;
-------------------------------------------------------
Better:
-------------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE total_doc > 0;
(when values of total_doc are positive)
--------------------------------------------------------

************************
Using HAVING
************************


7)
Worse:
---------------------------------------------------------
SELECT a.customer_n
, AVG( b.quant)
FROM exdoc a
, expos b
WHERE a.doc_nd = b.doc_nd
GROUP BY a.customer_n
HAVING customer_n != 'Lukyanov'
AND customer_n != 'Ivanov';
-----------------------------------------------------------
Better:
-----------------------------------------------------------
SELECT a.customer_n
, AVG( b.quant)
FROM exdoc a
, expos b
WHERE a.doc_nd = b.doc_nd
AND customer_n != 'Lukyanov'
AND customer_n != 'Ivanov';
GROUP BY a.customer_n
------------------------------------------------------------

*****************************
Combined SubQueries
*****************************


8)
Worse:
-------------------------------------------------------------
SELECT customer_n
FROM exdoc
WHERE total_doc =
(SELECT MAX( total_pos)
FROM expos)
AND doc_dd =
(SELECT MAX( doc_dd)
FROM expos);
-------------------------------------------------------------
Better:
-------------------------------------------------------------
SELECT customer_n
FROM exdoc
WHERE (total_doc, doc_dd) =
(SELECT MAX( total_pos)
, MAX( doc_dd)
FROM expos);
--------------------------------------------------------------

*********************************
Using EXISTS, IN, Join
*********************************


9-11)
Effectivity of these 3 Query variants depends from data inside the tables:
----------------------------------------------------------------------
SELECT customer_n
FROM exdoc a
WHERE EXISTS
(SELECT 1
FROM expos b
WHERE b.doc_nd = a.doc_nd
AND b.good_n = 'Igor');
-----------------------------------------------------------------------
SELECT customer_n
FROM exdoc
WHERE doc_nd IN
(SELECT doc_nd
FROM expos
WHERE good_n = 'Igor');
------------------------------------------------------------------------
SELECT a.customer_n
FROM exdoc a
, expos b
WHERE b.doc_nd = a.doc_nd
AND b.good_n = 'Igor';
------------------------------------------------------------------------

*********************
Using DISTINCT
*********************


12)
Worse:
-------------------------------------------------------------------------
SELECT DISTINCT a.doc_nd
, a.customer_n
FROM exdoc a
, expos b
WHERE a.doc_nd = b.doc_nd
AND b.good_n = 'Igor';
--------------------------------------------------------------------------
Better:
--------------------------------------------------------------------------
SELECT a.doc_nd
, a.customer_n
FROM exdoc a
WHERE EXISTS
(SELECT 1
FROM expos b
WHERE b.doc_nd = a.doc_nd
AND b.good_n = 'Igor');
----------------------------------------------------------------------------

************************
Using DECODE
************************


Decrease the quantity of looking the same tables by using DECODE

13) -------------------------------------------------------------------------
SELECT COUNT(*)
FROM exdoc
WHERE doc_nd = '139'
AND customer_n = 'Lukyanov';

...
SELECT COUNT(*)
FROM exdoc
WHERE doc_nd = '152'
AND customer_n = 'Lukyanov';
14)---------------------------------------------------------------------------
SELECT COUNT( DECODE( doc_nd, 139, 'X', NULL )) cnt_139
, COUNT( DECODE( doc_nd, 152, 'X', NULL )) cnt_152
FROM exdoc
WHERE customer_n = 'Lukyanov';
-------------------------------------------------------------------------------

*************************
Using UNION ALL
*************************


15)
Worse:
--------------------------------------------------------------------------------
SELECT doc_nd, sum_tax
FROM expos
WHERE doc_dd = '12-sep-12'
UNION
SELECT doc_nd, sum_tax
FROM exsvc
WHERE doc_dd = '12-sep-12';
--------------------------------------------------------------------------------
Better:
--------------------------------------------------------------------------------
SELECT doc_nd, sum_tax
FROM expos
WHERE doc_dd = '12-sep-2012'
UNION ALL
SELECT doc_nd, sum_tax
FROM exsvc
WHERE doc_dd = '12-sep-2012';
---------------------------------------------------------------------------------

************************
Using Anti-Join
************************


16)------------------------------------------------------------------------------
SELECT *
FROM exdoc
WHERE doc_nd NOT IN
(SELECT doc_nd
FROM expos
WHERE good_n = 'Igor');
17)--------------------------------------------------------------------------------
SELECT *
FROM exdoc a
WHERE NOT EXISTS
(SELECT NULL
FROM expos b
WHERE a.doc_nd = b.doc_nd
AND b.good_n = 'Igor');
18)---------------------------------------------------------------------------------
SELECT a.*
FROM exdoc a
, expos b
WHERE b.doc_nd (+) = a.doc_nd
AND b.good_n (+) = 'Igor'
AND b.ROWID IS NULL;


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC + 2 hours [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron