Sitemap

Amicuk Programming Answers

Sql to create logical partitions

2015-10-11   Views:0

Advertisement

Oracle: 10.2.0.5 I am working with another group and they are pulling data from one of the databases I work on. They are using what they call 'logical partitions'. Basically it is a sql statement with a MOD function in the where clause. select * from

Oracle: 10.2.0.5
I am working with another group and they are pulling data from one of the databases I work on. They are using what they call 'logical partitions'. Basically it is a sql statement with a MOD function in the where clause.
select *
from table
where mod(field,10) = 0This allows them to divide the table up into 10 chunks. So they run 10 sessions to pull data across the network. They are using array processing(1000 records at a time) in a 3rd party tool to pull the data and write it to teradata. I have no ability to change this process to something else. They are not using a cursor. its just a fetch of 1000 recorsd at a time. I checked that first.
The MOD function forces a full table scan. Before I go and and add a bunch of function based indexes to support this, does anyone know of another way to write these sqls without having to have a function on the left side of the where clause and get it to use an index? I want an index in part because 10 sessions is too slow to pull the data in an acceptable time so I want to increase the number of sessions i can handle. We are pulling from a number of tables so if its all full table scans I am far more constrained on my side.
I am hoping there is a way to on the fly chunk a table with buckets or something and use an index. So I can ramp this up to say 20-30 sessions per table so each session gets 1/20 or 1/30 of the table.

The replay answer
Advertisement
Guess2 wrote:
Oracle: 10.2.0.5
I am working with another group and they are pulling data from one of the databases I work on. They are using what they call 'logical partitions'. Basically it is a sql statement with a MOD function in the where clause.
select *
from table
where mod(field,10) = 0This allows them to divide the table up into 10 chunks. So they run 10 sessions to pull data across the network. They are using array processing(1000 records at a time) in a 3rd party tool to pull the data and write it to teradata. I have no ability to change this process to something else. They are not using a cursor. its just a fetch of 1000 recorsd at a time. I checked that first.
The MOD function forces a full table scan. Before I go and and add a bunch of function based indexes to support this, does anyone know of another way to write these sqls without having to have a function on the left side of the where clause and get it to use an index? I want an index in part because 10 sessions is too slow to pull the data in an acceptable time so I want to increase the number of sessions i can handle. We are pulling from a number of tables so if its all full table scans I am far more constrained on my side.
I am hoping there is a way to on the fly chunk a table with buckets or something and use an index. So I can ramp this up to say 20-30 sessions per table so each session gets 1/20 or 1/30 of the table.From the school of thought that if some is good, then more is better.
I suspect that the spindle upon which this table resides will be saturated
with I/O requests long before 20 is reached.
Session (CPU) is 100 - 1000 times faster than mechanical disk.
Sessions as few as a half dozen can overwhelm single disk drive.

Go to See the other 2 answers

Sql to create logical partitions

Category:DefaultRelease time:2015-10-11Views:130

Oracle: 10.2.0.5 I am working with another group and they are pulling data from one of the databases I work on. They are using what they call 'logical partitions'. Basically it is a sql statement with a MOD function in the where clause. select * from[More]

Logical Partition of Cube

Category:DefaultRelease time:-0001-11-30Views:130

Hi BW experts, Can anyone explain the steps to create logical partitioning of Cube data? I want to do it per fiscal year. If we create 05 cubes(Same structure) each for say fiscal year 04' 05'...08' now how will the data flow into respective cubes. K[More]

Impact of logical partitioning on BIA

Category:DefaultRelease time:-0001-11-30Views:130

We are on BI release 701, SP 5. We are planning to create logical partition for some of the infocubes in our system. These cubes are already BIA enabled, so will creation of logical indexes have any impact on the BIA or improve the BIA rollup runtime[More]

Cannot create another 2 logical partitions on another physical server

Category:DefaultRelease time:-0001-11-30Views:130

when i installed the BI 7.0 on AIX/DB2 9 platform. i can create 2 logical partition on the main server , yet i couldn't create another 2 logical parttions on the second server.  the following is the error message INFO       2008-02-21 03:49:03.490 "s[More]

PL/SQL- Problem in creating a partitioned fact table using select as syntax

Category:DefaultRelease time:2015-10-11Views:130

Hi All, I am trying to create a clone(mdccma.fact_pax_bkng_t) of existing fact table (mdccma.fact_pax_bkng) using dynamic pl/sql. However, pl/sql anonymous block errors out with following error: SQL> Connected. SQL> SQL> DECLARE ERROR at line 1:[More]

Creating a logical partition

Category:DefaultRelease time:-0001-11-30Views:130

Right now, I have 4 primary partition on my laptop. C, SYSTEM, RECVOVERY and HP-TOOLS. I want to create an extended partition, so I deleted the HP-TOOLS partition. The link below is to download the HP-TOOLS partition on my computer. (for BIOS updates[More]

How to SQL to create a new field reset by new value

Category:DefaultRelease time:2015-10-11Views:130

Table only got 1 field eg. company_name DELL DELL DELL HP HP IBM IBM IBM How create a SQL to create a running number field that reset for a value? Thanks. It should look something like this : SNO company_name 1 DELL 2 DELL 3 DELL 1 HP 2 HP 1 IBM 2 IB[More]

How to create extra partitions for windows 7 after installing boot camp

Category:DefaultRelease time:2015-10-11Views:130

hello i am pretty new at imac so bare with me and i really need help. IMAC OS X version  10.9.5  ( i think its os X mavericks ??) iMac 21. inch  , Late 2013 iMac model identifier  iMac 14,1 i installed boot camp windows 7  and it works perfectly, aft[More]

Unable to create logical dimension in bmm layer

Category:DefaultRelease time:2015-10-11Views:130

Hi all, I am not able to create a logical dimension hierarchy in the bmm layer of the rpd. when I right click on the table I am not able to find the create logical dimension option in the popup window. could you please help me out in this? In the log[More]

Use of Logical Partition in a Oracle Table...

Category:DefaultRelease time:2015-10-11Views:130

What is the use of Logical Partition in a Oracle Table as Target.  Techincal Manual does not say any significance. My question is: If the Table has no partitions and if we add Logical Partitions using Data Service, what purpose will it serve? We are[More]

Logical Partition of infocube

Category:DefaultRelease time:2015-10-11Views:130

We have a cube logical partitioned by sales org.  We now would like to add additional sales org's to the list. Is this possible??  Currently with data in the cube it is blocked.  Does anyone know steps to re-logical partition a cube??  With/or withou[More]

Hot
I did the auto-upgrade of iTunes, and now a couple of albums that I had downloaded the artwork for (AC/DC's Back In Black, Highway To ****, a couple of After Forever albums...) disappeared, and when I try to add them manually they still don't show up [More]
Hello, this concerns behaviour observed in Oracle 10g on Windows. As user "system", I execute from the command line the following: SQL> select COUNT(1) from sys.dba_sequences; COUNT(1) 645 Again I try the same thing from the command line: SQL [More]
I'm using a Flash file to make a comments section for my website but every time I hit the button my query only outputs blank fields to MySQL throguh the PHP file. I'm wondering if it's my actionscript or PHP file... and I feel it may be my actionscri [More]
Is that possible with a select statement in ABAP or do I need to sum each row by itself then add them together afterwords?Hi Craig, this is not possible with a select statement in ABAP. You can use the routine mentioned by you: DATA: C1 TYPE i,       [More]
I need to tag the punctuation in a string and this is the closest I think I've come:                     Pattern p = Pattern.compile("\\p{Punct}");                     Matcher matcher = p.matcher(text);                     while (matcher.find()) [More]
Hi there, On the Arch install I used the recommended 7GB for root and now with Arch installed running nice with KDE it seems I only have to 2GB left. Will I need to increase the size of root or providing I don't install to much more will it stay arou [More]
Guys I am novice in SAP PS  , Need Help can you please advice how to access this information from my  SAP Server , Appreciate your response Assembly Processing 1) Need a List of all materials that are impacted by assembly processing  ( Strategy group [More]
I have download the Oracle Developer from this website. I follow the instruction and install the Oracle Developer. After I finished install the Developer, a error message shows: "user1.pin(20):OS_ERROR whilw spawing if srv60-install Forms60Server -Or [More]
I am using a C3-00. It is an older device, but I lost my other phone and am using this one now. I'm trying to set up my Gmail account. I have the latest software version.  When I select 'Add account' in mail, I only get the options for 'Ovi by Nokia' [More]
Hi! Has anyone done any distributed programming with HTML DB? If so, are there any crucial factors to be taken into account? I thought of extracting the original application, importing it into my DB, changing and creating pages, exporting the single [More]