Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 11g Ntile function on top of Dense_rank
Oracle 11g Ntile function on top of Dense_rank [message #657639] Wed, 16 November 2016 23:21 Go to next message
raj85844
Messages: 26
Registered: November 2016
Location: chennai
Junior Member
Hi


1) As I am using a Ntile(Cool over (order by a_column desc) on Top of a dense rank query
2) Dense_Rank basically partitions two columns(department and students) and keeps the rank 1 datas in the table
And deletes the rank >1

The result for above condition
---------------------------------
The above condition complied and executed with single excution for all 8 partitions using NTILE

example : eligible records for deleting 285600

Begin
exec sp_proc(1);
exec sp_proc(2);
exec sp_proc(3);
exec sp_proc(4);
exec sp_proc(5);
exec sp_proc(6);
exec sp_proc(7);
exec sp_proc(8);
End;

285600 rows deleted

Note: Resulting all 285600 records rank>1 deleted.


Now a new condition is set *
1) An department and students even if it has more than one rank/duplicates need to be retained back in table for one particular department
Along with rank=1 for all other departments the So after adding the condition

example : eligible records for deleting 2856000

Begin
exec sp_proc(1);
exec sp_proc(2);
exec sp_proc(3);
exec sp_proc(4);
exec sp_proc(5);
exec sp_proc(6);
exec sp_proc(7);
exec sp_proc(8);
End;

Note: Result not deleting all 285600 records but deleting only 166500 records
suppose to delete the records evenly in all eight partion of NTILE function but due the dense rank the querry count is changing for NITE(2) and so on
Seeking help in archiving this in single run

THE INPUT 1
35700 rows deleted
THE INPUT 2
31300 rows deleted
THE INPUT 3
27300 rows deleted
THE INPUT 4
23900 rows deleted
THE INPUT 5
20900 rows deleted
THE INPUT 6
18300 rows deleted
THE INPUT 7
16000 rows deleted
THE INPUT 8
14000 rows deleted
Re: Oracle 11g Ntile function on top of Dense_rank [message #657641 is a reply to message #657639] Wed, 16 November 2016 23:36 Go to previous message
raj85844
Messages: 26
Registered: November 2016
Location: chennai
Junior Member
please find the sample code below



CREATE OR REPLACE PROCEDURE "Delete_records_rank_greater_than_one" (
   myntilevalue IN NUMBER)
AS
   CURSOR cur
   IS
      SELECT Unique_id
        FROM (SELECT Sequence_ID,
                     NTILE (8) OVER (ORDER BY Customer_ID) myntile
                FROM (SELECT Customer_ID,
                             Unique_id,
                             Department_ID,
                             drank
                        FROM (SELECT tbl.Customer_ID Customer_ID,
                                     tbl.Unique_id Unique_id,
                                     tbl.Department_ID Department_ID,
                                     DENSE_RANK ()
                                     OVER (
                                        PARTITION BY ih.Customer_ID,
                                                     ih.Department_ID
                                        ORDER BY ih.Joindate DESC)
                                        AS drank
                                FROM Emp_Table tbl)
                       WHERE     drank > 1
                             AND SUBSTR (Department_ID, 1, 7) !=
                                    UPPER ('CSE001_')))
       WHERE myntile = myntilevalue;
BEGIN
   OPEN cur;

   FETCH cur
   BULK COLLECT INTO i;                                    --------limit 10000

   FORALL c1_rec IN i.FIRST .. i.LAST
      DELETE FROM oly_peg_data_owner.pr_data_ih_fact prih
            WHERE prih.pxfactid = i (c1_rec);


   DBMS_OUTPUT.PUT_LINE (TO_CHAR (SQL%ROWCOUNT) || ' rows deleted');
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (
         -20001,
            'Error_encountered in procedure Delete_records_rank_greater_than_one:: '
         || l_err_msg
         || '>>>>'
         || DBMS_UTILITY.format_error_backtrace,
         TRUE);
END;
Previous Topic: how to close open cursor when exception
Next Topic: Rows not populating
Goto Forum:
  


Current Time: Fri Jan 19 23:51:03 CST 2018

Total time taken to generate the page: 0.02484 seconds