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
Any idea's guys????You can still backup your library without opening Aperture. Your Library is typically in your Pictures folder while the application is in your Applications folder. So you should backup your Library first (drag it to an external HD [More]
I've done all of the steps here: Remove unwanted adware that displays pop-up ads and graphics on your Mac - Apple Support  but still have something that wants to be installed when I shut down my mac. I don't know what this is or how to get rid of it. [More]
We bought a new iPad Air and immediately synced it to our iTunes library, hand choosing 3000 songs. I went back later to add some books, and iTunes tells me that the device is synced to a different library (it's not, same library) and that to sync bo [More]
When I try to drag pictures from events into any folders or to add to albums it disappears from my events until I click out and back in. Why is this happening? I have one photo that I want to add to my ibook before publishing and I can't.Welcome to t [More]
After testing on Adobe's website and others, flash is definetly not working on 4b9 64 bit, but it does on 32 bit environment. It's also perfectly working with safari.Adobe Flash "Square" is a 64-bit beta version of Adobe Flash. [http://labs.adob [More]
Hai FI Consultants, I am doing a report fro pending bills for all type of purchase orders. i am using the following tables EKBE, EKBZ. ekbe - for all type of movements and Grir cleared value. ekbz - import purchace orders(asset) - only goods received [More]
Hello: I am a registered user of CS3 and have the full version of PhotoShop at work and, as allowed by license, at home. But I wanted to install an older copy of PhotoShop Elements (1.0.1) that came bundled with one of the several dozen Adobe product [More]
Hi All , we are loading data into Infocube from datasource ,one process chain for delta init with data transfor  ,which has data selections at infopackage say 06.2014 and other process chain for delta which has same selections at infopackage . Now I [More]
Hi, I want to pass some data generated from Java class to Matlab, then invoke Matlab from Java to run the computation program (file written in Matlab, myfile.m). I know Matlab can use classes generated from Java, how to drive Matlab from Java? I appr [More]
Hola, estoy preocupado porque no veo ningun mensaje en espa�ol, en todo caso si alguien puede leer esto y ayudarme le agradecere mucho. Estoy utilizando JDeveloper10g y he creado una Web Application, tengo una pagina JSP a la cual le he agregado un b [More]