SQL & PL/SQL

The obvious question is:

Why?

Leading on from that, what business problem are you trying to solve with this?

Using a PL/SQL loop to insert billions of rows, is pure fantasy.

Once you have your billions of rows in your "dynamic" table(s), what then?

Obviously the size of your table is somewhat of a concern, but (regardless of the why's etc), I think I'd be possibly looking at using partitioning for this. Depending on your database version of course (i.e. 12.2 introduced automatic list partitioning).

SQL> create table xxjktst as (select owner, object_name, object_type from dba_objects);
Table created.
SQL>
SQL> create table xxjktst_part (
  2    owner varchar2(128),
  3    object_name varchar2(128),
  4    object_type varchar2(23)
  5  ) partition by list (owner)
  6  (partition apps values ('APPS'),
  7   partition applsys values ('APPLSYS'),
  8   partition pub values ('PUBLIC'),
  9   partition the_rest values (default));
Table created.
SQL>
SQL>
SQL> insert into xxjktst_part select * from xxjktst;
432966 rows created.
SQL>
SQL> select count(*) from xxjktst_part partition(apps);
  COUNT(*)
----------
    178654
SQL>
SQL> drop table xxjktst_part;
Table dropped.
SQL>
SQL> drop table xxjktst;
Table dropped.
SQL>

You might also want to look into dbms_redefinition etc. But as you can see, you effectively now have many "small" tables, and you can query them either using the partition key, or by using "partition(partition_name)" syntax.

Just to note, partitioning is typically a costed option on the oracle licence, however I would expect a company storing so many billions of rows of data would be running a full enterprise database and have a full blown licence for everything to allow them to do something like partitioning.  That sort of thing would have (obviously) been picked up by the infrastructure architects who designed the requirements for the technology, knowing that so much data was going to be stored in the database.

Yes, I made the assumption that anywhere storing 300 billion rows would have pretty much most options they needed

What is your database version?  The full 4 digits.

Run this query:

select * from v$version;

Well, the query generates 300 billion rows, the QA needs to be able to test different small chunks. Once the testing is completed, the data needs to sent out as csv files via FTP. hence, this is why multiple tables are needed so that each table will be converted as a csv file and sent otu

1) Create a view / MView for the query.

2) Create filtered view(s) / MView(s) based on 1) by adding a where clause.  This can be used for testing.  You can even use synonyms to name these views to whatever you want.

3) Do testing based on 2), drop view(s) in 2.

Done.  Not difficult and not a hack.

Hi Paulzip;

I tried doing that but I was getting serious performance issue. For instance, the 300 billion rows takes a long time to run and consumes so much tablespace and memory on the server, but then I realized during a test if i filtered out the 300 billions to create a smaller  chunk of 1 billions rows in several tables and utilize some of the powerful optimized processes you can apply to a table, I am able to retrieve 1 billions rows from each table in about a 1 min.

Furthermore, during the extraction to convert to csv files to send out, it was faster to extract via a table which was heavily optimized

Hence, that is why i am trying to go through the table route instead unless I am missing something

I am on oracle 12.1.0.1

Of course it will.  Even if each row takes one microsecond to fetch, it'll still take 83 hours.

Skip step 1 and go with step 2 with a large amount of filtering.

thanks paulzip for the prompt response, just to confirm so when you say filter view, you mean a view with all the columns and basically the user can call the view and use the any required associated column as a filter for instance

create or replace view v_emp as

select * from employees

calling  view with filter

select * from v_emp pv

where pv.ename = 'John'

@John_K

THanks John_k for the posted example, i thought  of creating one table with the 300 billions record and partitioning it based on used filters. The concern was if we have so many QA staffs trying to access the table, it might really slow down things or create traffic. I do not know if i am right  but i am just looking for the most effective and efficient way.

user9294681 wrote:
thanks paulzip for the prompt response, just to confirm so when you say filter view, you mean a view with all the columns and basically the user can call the view and use the any required associated column as a filter for instance
create or replace view v_emp as
select * from employees
calling view with filter
select * from v_emp pv
where pv.ename = 'John'
@John_K 

THanks John_k for the posted example, i thought of creating one table with the 300 billions record and partitioning it based on used filters. The concern was if we have so many QA staffs trying to access the table, it might really slow down things or create traffic. I do not know if i am right but i am just looking for the most effective and efficient way. 

IF multiple sessions are looking at the same 100 billion rows, there will be "traffic", regardless of if those rows are in a partition or a flat table. And all 300 billion will take the same amount of space regardless of portioning or not. 

thanks