sql - oracle multiple hash partitions parallel? -


i have pretty huge table using hash partitons:

create table test  (       custid              varchar2 (16) not null       ,channel                number(10)       ,agent_id             char(8)       ,timestamp           date  ) cache partition hash(agent_id) ( partition e1  tablespace td_rtdm, partition e12 tablespace td_rtdm, partition e13 tablespace td_rtdm, partition e14 tablespace td_rtdm, partition e15 tablespace td_rtdm, partition e16 tablespace td_rtdm, partition e17 tablespace td_rtdm, partition e18 tablespace td_rtdm );

this works pretty good, when filter on agent_id. have new level on need filter, not include agent_id. guess need n new parallel hash partitions this:

create table test      (           custid             varchar2 (16) not null           ,channel               number(10)           ,agent_id            char(8)           ,timestamp           date      ) cache partition hash(agent_id) ( partition e1  tablespace td_rtdm, partition e12 tablespace td_rtdm, partition e13 tablespace td_rtdm, partition e14 tablespace td_rtdm, partition e15 tablespace td_rtdm, partition e16 tablespace td_rtdm, partition e17 tablespace td_rtdm, partition e18 tablespace td_rtdm ) partition hash(channel) ( partition e1  tablespace td_rtdm, partition e12 tablespace td_rtdm, partition e13 tablespace td_rtdm );

but not work, since not allowed have multiple partition statements. googled , found lots of example using subpartitions, think not need.

do have idea, how tune table both queries performant? e.

partitioning not performance activity.

partitioning offers data management advantages, allowing load subsets of data through partition exchange or remove them dropping partitions.

partitioning offers high availability options, provided spread our partitions across multiple tablespaces.

now, partitioning can improve performance of queries, through partition pruning or elimination, queries aligned partition key. other queries, ones run against grain of our partitioning strategy perform neutrally or worse against unpartitioned table.

this because partitioning physical organization of table's data. rows grouped in partition key order. data cannot sorted in 2 different orders simultaneously, , that's why cannot have parallel partition keys.

there no easy solution this. global index on channel may help, because ignore partition keys, come own burdens; places forbid use of global indexes. subpartitioning can answer if key within key makes sense. rebuilding table new partitioning scheme theoretically possible never done in real life. try think outside box: maybe separate table (perhaps index-organized) help? perhaps queries might benefit different key run not worth tuning.

in short, need understand data , use cases.


Comments

Post a Comment

Popular posts from this blog

PHP and MySQL WP -

i trying accomplish 2 things here code. writing php directly wp page using insert php plugin. 1) query mysql database , return results 1 column 4 columns. 2) make each result own hyperlink directs user new wp page runs new query on page. i able query show results , turn results dynamic hyperlink, cannot formatting down. right returns result 1 column. here have: [insert_php] $servername = "localhost"; $username = "login"; //edited privacy $password = "password"; //edited privacy $database = "database"; //edited privacy // create connection $conn = new mysqli($servername, $username, $password, $database); // check connection if ($conn->connect_error) { die("connection failed: " . $conn->connect_error); } echo "connected successfully"; //get results database $result = mysqli_query($conn,"select mine mines order mine"); $all_property = array(); //declare array saving property //showing proper...
Read more

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

go - golang pprof for c library code -

i have been trying profile golang application pprof, seems details of c code(linked static library) missed in output result. flame graph from frame graph can see c code time consuming reported within "runtime._externalcode", no details @ all. , sure static library compiled debug enable. so there way make pprof details(like stack trace) of 3rd party c code? or there other golang profiling tool this?
Read more