Comments on ေလာ ရွည္ - က တဲ့ ပြဲ: SQL Statement Level တြင္ Optimize လုပ္၍ Query Exec...

@NightPro : No. The inner query will returns all...

2011-01-16T20:44:32.773-08:00

@NightPro :

No. The inner query will returns all customers who has bought non-furniture. In some cases where a customer bought both furniture and non-furniture, he will be included - which is semantically not what we intend to do.

SELECT cust_id,cust_name,cust_add FROM customer_ta...

2011-01-12T00:28:51.081-08:00

SELECT cust_id,cust_name,cust_add
FROM customer_table
WHERE cust_id IN (SELECT cust_id FROM order_table WHERE ord_type <> '‘furniture’')

is that more improve ?

NOT IN နဲ႕ NOT EXISTS ANY ျဖစ္ပါတယ္။ IN နဲ႕ EXISTS...

2010-12-07T18:56:06.430-08:00

NOT IN နဲ႕ NOT EXISTS ANY ျဖစ္ပါတယ္။ IN နဲ႕ EXISTS ALL မဟုတ္ပါ။

NOT IN နဲ႕ NOT EXISTS ANY က Worst Case Time Complexity တူပါတယ္။ ဒါေပမဲ့ NOT EXISTS ANY ဆုိရင္ အျပင္ Query က Looping ပတ္ေနတဲ့ current key ေပၚ မူတည္ၿပီး အျပင္ Query က key တစ္ခုခ်င္းစီအတြက္ sub-query တစ္ခုစီ formulate ေနတာမုိ႕လုိ႕ cache management မွာ NOT IN က NOT EXISTS ထက္ပုိၿပီး efficient ျဖစ္ႏုိင္ပါတယ္။ (တကယ္လုိ႕ sub-query မွာ တုိက္စစ္တဲ့ field အတြက္ index မရွိရင္ေပါ့ေလ။)

ေနာက္ဆက္တြဲအေနနဲ႕က ဘယ္ဟာပုိေကာင္းသလဲကုိ Query Optimizer အေနနဲ႕ သူ႕ဘာသာသူ ဆံုးျဖတ္ႏုိင္ေအာင္ (ဆုိလုိတာက ေစာေစာက index ရွိမရွိ ေပၚမူတည္ၿပီး Algorithm ေျပာင္းႏုိင္ေအာင္) လုိ႕ Sub-query မသံုးပဲ Outer Join ကုိ သံုးသင့္တယ္လုိ႕ ဆရာတစ္ေယာက္ကလဲ အီးေမးလ္နဲ႕ ေထာက္ျပထားပါတယ္။ ဒီမွာ မူရင္းပါ။

"Personally, I prefer joins to nested selects. (because I am scared of
recursion and stuffs like that.)

If I am to slove the problem, I will "outer join" the table customers
and the view (or temp table) of subset of orders which are not
furniture, and report the ones where customerid in this order subset
is null."

ေနာက္ဆံုး စာေရးသူရဲ႔ အေျဖမွန္မွာ IN အစား EXISTS ကိ...

2010-12-02T04:15:25.582-08:00

ေနာက္ဆံုး စာေရးသူရဲ႔ အေျဖမွန္မွာ IN အစား EXISTS ကိုေျပာင္းသံုးမယ္ဆိုရင္ time complexity တူမတူ သိခ်င္ပါတယ္.. သိထားတာက EXISTS ကလဲ sub query က record တခုနဲ႔ ေျပလည္ျပီဆိုတာနဲ႔ true ျပန္တယ္လို႔ သိထားလို႔ပါ.. ဘယ္ဟာက ပုိျပီး efficient ျဖစ္မလဲသိခ်င္ပါတယ္