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
Hi Guys, We are installing solution manager 4.0 on windows/sql but we are getting following error and couldnt continue the installation FKD-00070  Error when opening table container file C:\PROGRA1\SAPINS1\SOLMAN\SYSTEM\MSS\CENTRAL\AS\keydb.xml for w [More]
Hi, As anyone used Drag and Drop for components? I mean, allowing the user to drag and drop a component around a container? Just curious, so far from what I've seen, the only things that can be dragged/dropped are properties of a given object, such a [More]
I'm beginning a photo project that requires a lot of scanning. I purchased a new scanner but I can't seem to get the photos to open directly into iphoto. I'm using an Epson scanner - the Epson Perfection V600 - and it offers only options to open into [More]
Hello, I am trying my first BPM process. I am using the send step while I need to specify the receiver using send context. The value of the send context should be: The send context is a freely definable string, which you specify in the send step. You [More]
I cannot open the SCSM console . I am getting the below error message . I am not sure if its network issue or some thing else. The sdk service will not remain in started state . But when I run telnet on the workflow server , I get the message that th [More]
if i dial from jabber . which display name will it detect?If both the users have an identical telephoneNumber in the directory, it will probably display the first one that the directory returns.Read other 3 answers [More]
I use BI in my ECC 6.0 system. I use tried both GUI 7.1 and GUI 6.40 to logon web application designer. But system display the message please logon to a Netweaver BI 7.x system. System A11 does not have a mimumium version required 7007. My system see [More]
hai to all my loving friends What r cross company code transcations. pls explain how this trancations r going to be done ,what r the advantages of it,pls any attachments. [email protected]Cross company transactions means, when more than one company c [More]
 My cell calls and texts are off-and-on not going through to people in more than one state and to more than one person--even though my phone reports the message as delivered and my calls as completed.  AT&T has missed THREE call back commitments!  Me [More]
Can you help please ? This message was received when I tried to download and install Photoshop CS6, 64 bit system, Windows XP: Exit Code: 6 Please see specific errors and warnings below for troubleshooting. For example,  ERROR: DF023, DW063 ... WARNI [More]