I may occasionally be filtering by the time variable as well. When running future selects against this table, it's likely that I'll be filtering on client id as well as wanting to do some sampling (which I would like to do by the "Token" variable).
I have three columns that seem like reasonable candidates for partitioning or indexing: However, I'm getting confused on when I'd want to create a partition vs. Now, I need to have a way to access the data in this table quickly, so I'm researching partitions and indexes. Any assistance would be greatly appreciated.I'm very new to databases, so I apologize if my question is very basic.Īnyways, I'm creating a table with what seems like alot of data (500 million rows right now, and potentially twice as much in the future). I realize that this is a very general overview of what I am looking to accomplish. Send an email to DBA with list of tables that will require new tablespaces.
This is accomplished by checking every max_partition_date and partition_frequency in PARTITION_STAT table. Check to see if new tablespaces are required and send email notification to DBA if tablespaces are needed. For every new tablespace: create new partion and update the PARTITION_STAT tableģ. Create partitions for the new tablespaces. Update the last_tablespace_rowid in the TABLESPACE_STAT tableĢ. All rowid's greater than last_tablespace_rowid are new tablespaces. This is done by checking TABLESPACE_STAT.last_tablespace_rowid. Check to see if a new tablespace has been created. Need to create a procedure that will run automatically. Create a table that will contain the rowid of the last tablespace created. Note: This table will need to be udpated whenever a new table using partitioning is created.Ģ. daily, weekly, etc), partition_name, max_partition_date Create a table that will contain info about the partitioned tables.Ĭolumns: table_name, tablespace_name, partition_frequency (e.g. The partitions are stored in their own tablespaces.ġ. Several large tables need to be partitioned on a daily basis.
Has anyone done this before and if so, am I on the right path? Any examples, advice would be appreciated. I'm looking for some insight regarding the automation of table partitioning. With this in mind, I've included a "spec" of what I'd like to accomplish. in PL/SQL which can be sheduled via a UNIX crontab. 1.7K Training / Learning / CertificationĪfter further analysis, I've decided that I would like to code a procedure, etc.165.3K Java EE (Java Enterprise Edition).7.8K Oracle Database Express Edition (XE).3.7K Java and JavaScript in the Database.
0 kommentar(er)
