Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

What is the best way to delete millions of row from large table?

Sevdimali IsayevJul 23 2018 — edited Jul 24 2018

Hi everyone,

I have large table it has more than 5 million rows.

Table not partitioned.

on average 50-60 rows inserted in every second to my table.

it is 12 column (one blob and one clob) and three indexes on my table.

\

what is the best way to delete rows from this table?

If any other information needed please let me know.
This post has been answered by BrunoVroman on Jul 23 2018
Jump to Answer

Comments

AnnEdmund

That is dynamic pivoting.. Check below..

GregV

Hi,

You can use a subquery for the pivot_in_clause only in conjunction with XML. See:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#CHDFAFIE

AnnEdmund

Try the below.. Not tested...

CREATE OR REPLACE FUNCTION dynamic_fromtable


RETURN SYS_REFCURSOR


AS


v_cursor SYS_REFCURSOR;


v_sql VARCHAR2(2000);


BEGIN


 SELECT 'SELECT *


        FROM(SELECT pre_sap_cod


             FROM pre)


        PIVOT(COUNT(pre_sap_cod) FOR pre_sap_cod IN('||(SELECT LISTAGG(pre_sap_cod,',') WITHIN GROUP(ORDER BY pre_sap_cod) FROM (SELECT distinct pre_sap_cod FROM pre))||'))'


        INTO v_sql        


 FROM DUAL;


 OPEN v_cursor FOR v_sql;


 RETURN v_cursor;


END;

Thanks,

Ann

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 21 2018
Added on Jul 23 2018
#delete, #sql
23 comments
658 views