👈🏾 🕵🏽 🤓 "डेंस_क्रैंक ()" बनाम "मैक्स ()" या एक अप्रत्याशित अंत के साथ एक जांच 💑 🧔🏿 👩🏻‍⚕️

नमस्कार साथियों!

इस लेख में मैं अपने शोध के बारे में बात करूंगा: "और क्या बेहतर है: dense_rank () या अधिकतम ()" और, निश्चित रूप से, यह शोध अप्रत्याशित परिणाम के साथ समाप्त हो गया, कम से कम मेरे लिए।

पृष्ठभूमि:

इसलिए ऐसे सितारे थे जिन्हें अब काम करने की जरूरत है। प्रत्येक साक्षात्कार से पहले, मैं उस कंपनी का अध्ययन करता हूं जिसे मुझे आमंत्रित किया गया था, ताकि यह समझने के लिए कि कंपनी क्या कर रही है, अगर वे मुझे एक प्रस्ताव बनाते हैं, तो मैं क्या सीखूंगा। और इसलिए, एक ही पल में, मुझे एक अद्भुत कंपनी से पीएल / एसक्यूएल डेवलपर की स्थिति के लिए एक साक्षात्कार का निमंत्रण मिला। उसके बारे में पढ़ने के बाद, मुझे ऐसा लगा कि मैं प्यार में हूँ और मैं वहाँ काम करना चाहता हूँ। जब मैं स्वयं साक्षात्कार के लिए आया था और उस क्षण जब सब कुछ साक्षात्कार के लिए तैयार था, लेकिन यह सिर्फ इसलिए शुरू नहीं हुआ क्योंकि लोग मिले, घंटा कॉफी की पेशकश की, आदि, मैं पहले से ही समझ गया था कि मैं क्या चाहता हूं, वास्तव में यहां चाहता हूं। काम करना।

टीम लीडर के साथ सभी साक्षात्कारों के लिए, मुझे एक बार एक साधारण अनुरोध लिखने के लिए कहा गया, अन्यथा सब कुछ मोड में चला गया: सवाल-जवाब।

कार्य इस प्रकार था:

"हमारे पास संचालन की एक तालिका है, इसमें 4 कॉलम हैं: ऑपरेशन आईडी, क्लाइंट आईडी, ऑपरेशन की तारीख, लेनदेन राशि। "प्रत्येक ग्राहक के लिए एक निश्चित अवधि के लिए अधिकतम राशि के साथ नवीनतम लेनदेन को वापस लेना आवश्यक है।"

और मैं, निश्चित रूप से, इस तरह से लिखने के बारे में सोचने लगा कि यह कैसे सुंदर और प्रभावी है। और ओरेकल के अलावा, मैंने टेराडाटा के साथ भी काम किया और उस समय मेरे मस्तिष्क ने यह अनुरोध जारी किया:

छिपा हुआ पाठ

/*id  - oper_id, id  - client_id,   - input_date,   - amount*/ select t.* , max(t.amount) over (partition by t.client_id) as m_a from some_table t qualify oper_id = max(t.oper_id) over (partition by t.client_id) where m_a = amount

और टेराडाटा के मामले में, यह काम करेगा, लेकिन अलंकृत के साथ नहीं। और स्पष्ट रूप से याद रखना कि ओरेकल में कोई "योग्य" नहीं है, मैंने कागज के एक टुकड़े पर लिखा जैसे कुछ:

छिपा हुआ पाठ

 select t.* from some_table t where amount = max(t.oper_id) over (partition by t.client_id)

जिसके लिए मुझसे यह सवाल पूछा गया था: "अधिकतम (क्यों) का उपयोग किया गया था, और घने_रैंक ()?", मुझे याद नहीं है कि मैंने क्या उत्तर दिया है, लेकिन यह इस तरह लग रहा था: "मैंने अधिकतम () का उपयोग अधिक बार किया और अधिक या कम कर सकता है। बस कल्पना कीजिए कि वह मेरे पास क्या लौटेगा, dense_rank () के विपरीत। " मैंने साक्षात्कार के बारे में आगे नहीं बताया, मैं बस इतना ही कहूंगा कि, उन्होंने मुझे मना कर दिया। बाद में, घर पर, सब कुछ का विश्लेषण करने और गलतियों को समझने के प्रयास में, मैं इस निष्कर्ष पर पहुंचा कि मैं वहां बहुत अधिक काम करना चाहता था और चिंतित था, अन्यथा मैं साक्षात्कार के दौरान मेरे सिर में हो रही गंदगी की व्याख्या नहीं कर सकता। यह कुछ सनसनीखेज था जब एक छात्र एक लड़की के साथ बात करने की कोशिश करता है जिसे वह बालवाड़ी से गुप्त रूप से प्यार करता है, लेकिन इन प्रयासों ने उसे एक अजीब स्थिति में डाल दिया। उसी तरह, शांत और पर्याप्त दिखने की कोशिश करते हुए, मैंने खुद को एक बेकार विशेषज्ञ साबित किया। सामान्य तौर पर, मैंने अपने लिए यह पता लगाने का निर्णय लिया कि इस तरह की समस्या को हल करने के लिए घने_कर () या अधिकतम () का उपयोग करना बेहतर है।

अध्ययन

यदि आप अपनी आँखों से सब कुछ देखना चाहते हैं जो मैं लिखूंगा और इसे अपने हाथों से छू सकता हूँ - मैंने परीक्षण के लिए डेटा बनाने के लिए स्क्रिप्ट का एक सेट तैयार किया है:

छिपा हुआ पाठ

 /* */ create table habr_test_table_220414 ( oper_id number, client_id number, input_date date, amount number, constraint habr_test_table_220414_pk primary key (oper_id) ); grant all on habr_test_table_220414 to public; /* ,   oper_id   -  */ create sequence habr_test_sequence_220414 increment by 1; grant all on habr_test_sequence_220414 to public; /* ,     ,    oper_id    */ create trigger habr_test_trigger_220414 before insert on habr_test_table_220414 for each row begin :new.oper_id := habr_test_sequence_220414.nextval; end; /*           */ /*     ,     10- */ /* ,     cost = 3,   20000 ,    */ /*   -          counter */ declare counter number := 10000; i number := 0; begin loop insert into habr_test_table_220414 ( client_id , input_date , amount ) values ( trunc (dbms_random.value (1, 11)) , to_date(trunc(dbms_random.value(to_char(date '2013-01-01','j'),to_char(date '2013-12-31','j'))),'j') , trunc (dbms_random.value (1, 100000)) ); exit when (i = counter); i := i + 1; end loop; commit; /*   ,         */ /*  :*/ insert into habr_test_table_220414 select * from habr_test_table_220414; commit; /*   id   */ end;

तो, परीक्षण डेटा बनाया गया है, यह शुरू करने का समय है, वास्तव में, स्वयं प्रश्न। हमारी 20,000 पंक्तियों को न काटने के लिए, हम अपने चयन को किसी विशेष अवधि तक सीमित नहीं करेंगे, क्योंकि हमारे लिए यह समझना महत्वपूर्ण है कि कौन सी विधि बेहतर और अधिक कुशल है, और

 where input_date between to_date('01.01.2013','dd.mm.yyyy') and to_date('01.05.2013','dd.mm.yyyy')

हम बाद में जोड़ सकते हैं।

अधिकतम का उपयोग करके क्वेरी ()

 select * from ( select c.* , max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/ from ( select t.* , max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/ from habr_test_table_220414 t ) c where c.m_a = c.amount ) where m_o = oper_id;

Dense_rank () का उपयोग करके क्वेरी

 select * from ( select c.* , dense_rank() over (partition by c.client_id order by c.oper_id desc) as m_o/*max_operation*/ from ( select t.* , dense_rank() over (partition by t.client_id order by t.amount desc) as m_a/*max_amount*/ from habr_test_table_220414 t ) c where c.m_a = 1 ) where m_o = 1;

इन प्रश्नों की प्रारंभिक योजनाएँ (pl / sql डेवलपर से प्राप्त):

अधिकतम:

छिपा हुआ पाठ

Dense_rank:

छिपा हुआ पाठ

लेकिन ये प्रारंभिक योजनाएं हैं, वास्तविक लोगों को SQLTUNE उपयोगिता का उपयोग करके प्राप्त किया जाएगा:

तैयारी:

 /*   max()*/ DECLARE my_task_name varchar2(30);my_sqltext clob;rep_tuning clob; BEGIN Begin DBMS_SQLTUNE.DROP_TUNING_TASK('my_sql_tuning_task_max'); exception when others then NULL; end; MY_SQLTEXT:= 'select * from ( select c.* , max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/ from ( select t.* , max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/ from habr_test_table_220414 t ) c where c.m_a = c.amount ) where m_o = oper_id'; MY_TASK_NAME:=DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => my_sqltext, TIME_LIMIT => 60, --     TASK_NAME =>'my_sql_tuning_task_max', DESCRIPTION=> my_task_name , SCOPE => DBMS_SQLTUNE.scope_comprehensive); begin DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning_task_max'); exception when others then null; end; END; /*   dense_rank()*/ DECLARE my_task_name varchar2(30);my_sqltext clob;rep_tuning clob; BEGIN Begin DBMS_SQLTUNE.DROP_TUNING_TASK('my_sql_tuning_task_dense'); exception when others then NULL; end; MY_SQLTEXT:= 'select * from ( select c.* , dense_rank() over (partition by c.client_id order by c.oper_id desc) as m_o/*max_operation*/ from ( select t.* , dense_rank() over (partition by t.client_id order by t.amount desc) as m_a/*max_amount*/ from habr_test_table_220414 t ) c where c.m_a = 1 ) where m_o = 1'; MY_TASK_NAME:=DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => my_sqltext, TIME_LIMIT => 60, --     TASK_NAME =>'my_sql_tuning_task_dense', DESCRIPTION=> my_task_name , SCOPE => DBMS_SQLTUNE.scope_comprehensive); begin DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning_task_dense'); exception when others then null; end; END; /* ,              */ /* ,     */

और ये वास्तविक योजनाएं इस तरह दिखती हैं:

छिपा हुआ पाठ

 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_max') FROM DUAL;

 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_dense') FROM DUAL;

वास्तविक योजना के अलावा, SQLTUNE स्क्रिप्ट को अनुकूलित करने के लिए सिफारिशें भी देता है, हमारे मामले में, यह आंकड़े एकत्र करने की सिफारिश करता है, लेकिन चूंकि हमारे पास एक प्लेट है, इसलिए प्रश्न समान स्थितियों में हैं।

प्रारंभिक परिणाम

इन सभी जोड़तोड़ के बाद, यह मेरे लिए स्पष्ट है, जैसा कि मैं आपसे उम्मीद करता हूं, कि इस समस्या को हल करते समय, dense_rank () की तुलना में अधिकतम () 2 गुना तेजी से काम करता है और आधे प्रोसेसर समय को खाता है। ठीक है, यह बिना योजनाओं और अन्य चीजों के समझ में आता है, क्योंकि अधिकतम () केवल सबसे बड़ी खोज है, जबकि dense_rank (), सबसे पहले, छंटाई, और उसके बाद ही क्रमांकन होता है।

लेकिन वह नहीं था जिसने मुझे एक लेख लिखने के लिए प्रेरित किया।

एकाएक

परीक्षण के लिए प्रारंभिक रूप से तालिका भरने की प्रक्रिया में, मैंने लेख के लिए लिपियों का पता लगाया, और पहली बार सब कुछ लगभग मैन्युअल रूप से हुआ, और प्रयोगात्मक तालिका की स्थिति की जांच करने के लिए मैंने order by

साथ अनुरोध का उपयोग order by

।

छिपा हुआ पाठ

 /*  10   */ insert into habr_test_table_220414...; .... .... insert into habr_test_table_220414...; commit; select * from habr_test_table_220414 t order by t.client_id; /*    :*/ insert into habr_test_table_220414 select * from habr_test_table_220414; commit; select * from habr_test_table_220414 t order by t.client_id; /*       */

उसके बाद, मैंने इस अनुरोध को अंतिम स्थिति "अधिकतम () के साथ अनुरोध" order by

को हटाने के बिना संशोधित order by

।

यहाँ क्या हुआ:

 select * from ( select c.* , max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/ from ( select t.* , max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/ from habr_test_table_220414 t order by t.client_id ) c where c.m_a = c.amount ) where m_o = oper_id

बाद में मैंने "घने_रैंक () के साथ एक क्वेरी लिखी" और योजनाओं की तुलना करना शुरू कर दिया, लेकिन अधिकतम () के साथ एक प्रश्न के order by

इस दुर्भावनापूर्ण order by

को सूचित करते हुए, मैंने order by

हटा दिया, लेकिन मैंने पहले से ही लागत को देखा और याद किया। और जब मैंने order by

बिना अधिकतम () के साथ अनुरोध में लागत देखी order by

बहुत आश्चर्य हुआ, क्योंकि:

प्रारंभिक योजना

SQLTUNE से वास्तविक योजना

वैसे भी, यह कहने के लिए कि मैं बहुत हैरान था - कुछ भी नहीं कहने के लिए ... यह कैसे हुआ? 10 बार अनुरोध order by

गति order by

क्यों दिया? मैंने ट्रेस में जवाब खोजने का फैसला किया। मैं ठीक से नहीं लिखूंगा कि ओरेकल में ट्रैक को कैसे शूट किया जाए, क्योंकि यह एक अलग लेख के लिए एक विषय है, और इस प्रक्रिया के विवरण के साथ लेख वर्ल्ड वाइड वेब पर ढूंढना आसान है। मैं केवल स्क्रिप्ट का एक सेट प्रदान करूंगा, जिसके साथ मैंने पता लगाया है और इस तरह के एक लेख के लिए एक लिंक है, मैंने इसे लंबे समय तक पाया, तब से इसने मेरी मदद की:

छिपा हुआ पाठ

ट्रेसिंग सक्षम करने पर लेख का लिंक

 alter system set timed_statistics=true; alter session set tracefile_identifier='test_for_habr_220414'; alter session set events '10046 trace name context forever, level 12'; select * from ( select c.* , max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/ from ( select t.* , max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/ from habr_test_table_220414 t order by client_id ) c where c.m_a = c.amount ) where m_o = oper_id; alter session set events '10046 trace name context off'; select value from v$parameter p where name='user_dump_dest'; /*      tkprof*/ /*     'test_for_habr_220414'*/

ट्रैक में, हम एक ऐसे टुकड़े में रुचि रखते हैं जो अनुरोध को निष्पादित करते समय ओरेकल की क्रियाओं का वर्णन करता है:

छिपा हुआ पाठ

 select * from ( select c.* , max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/ from ( select t.* , max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/ from habr_test_table_220414 t order by client_id ) c where c.m_a = c.amount ) where m_o = oper_id call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.03 0.02 0 84 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.04 0.03 0 85 0 10 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Rows Row Source Operation ----- --------------------------------------------------- 10 VIEW (cr=84 pr=0 pw=0 time=28155 us cost=23 size=1592850 card=21525) 20 WINDOW BUFFER (cr=84 pr=0 pw=0 time=28145 us cost=23 size=1313025 card=21525) 20 VIEW (cr=84 pr=0 pw=0 time=21628 us cost=23 size=1313025 card=21525) 22010 WINDOW SORT(cr=84 pr=0 pw=0 time=24393 us cost=23 size=1033200 card=21525) 22010 TABLE ACCESS FULL HABR_TEST_TABLE_220414(cr=84 pr=0 pw=0 time=5172 us cost=23 size=1033200 card=21525)

परिणाम

इससे हम देखते हैं कि प्रारंभिक और वास्तविक योजना दोनों ही गलत नहीं हैं, ऐसा लगता है कि कोई पकड़ नहीं है, और आप दस गुना त्वरण का आनंद ले सकते हैं। क्या ऐसा है?

पुनश्च मैं अभी भी इस सवाल का जवाब नहीं दे सका और अभी भी विश्वास नहीं करता कि आदेश द्वारा सहायता के साथ अनुरोध को वास्तव में त्वरित किया जा सकता है। मैं इस पल का पता लगाने की कोशिश जारी रखूंगा, जिसके लिए मैं आपसे आग्रह करता हूं। और क्या ओरेकल के छिपे हुए रहस्य हमारे सामने आ सकते हैं!

PPS आपका ध्यान देने के लिए आप सभी का धन्यवाद! यदि आपने मेरे साथ एक परीक्षण किया है - तो अपने लिए आधार साफ करना न भूलें, खासकर अगर यह किसी बैंक का ठेस है।

छिपा हुआ पाठ

 drop trigger habr_test_trigger_220414; drop sequence habr_test_sequence_220414; drop table habr_test_table_220414;

"डेंस_क्रैंक ()" बनाम "मैक्स ()" या एक अप्रत्याशित अंत के साथ एक जांच

पृष्ठभूमि:

अध्ययन

प्रारंभिक परिणाम

एकाएक

परिणाम

More articles: