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 have a couple of external firewire hard drives attached to my mac which contain all my work files. My 6 yr son has started using my mac so I have set him up his own account and set up the parental controls etc. BUT - is there any way I can set thin [More]
I have two external drives....one that I am backing up to (500GB) and one that will hold all my iphoto/tunes files (1TB). Will TM automatically back up the photos/music drive directly to my TM drive? Also, the above drive I just bought is an iomega 1 [More]
Hello, I have a K9N2G Neo and I am looking to upgrade to a AMD Phenom II X4 925 but I have a couple questions. Will stepping/revision of a CPU effect compatibility? I see in the CPU Support that Phenom II X4 925 C2 is supported but would the same CPU [More]
Hello alltogether, my son has a new MacBook (System 10.5) for his school and he learn with the computer in all school-subjects. So I create two users, admin and one for him. After he has install all applications that he need for school, I gave the ad [More]
My daughter's mini is sounding pretty bad. Sound is scratchy and distorted. Searching the Web, found some sights saying that the "connector pins" to the head phone jack were of faulty design. Apparently they're rigid, not welded properly and ten [More]
Last night I put my mac to sleep, like I usually do. This morning I hit the keyboard to have it wake up again, but nothing happened. After a few tries I touched the computer to feel if it was even warm, and it was just cold. It wasn't on at all. So t [More]
Dear Team , My redolog01,redolog02,redolog03 size is 50mb . Can I resize to 250 or 500 mb and its not affact anything on my database .. and restart databse required or not . Thanks Abu shahdhttp://gavinsoorma.com/2009/07/drop-and-recreate-online-redo [More]
Hello Friends, Need some help . I am currently working on BI7 and there is one requierment for Report Designer. Actaully user is looking for the selection creteria after execution of the report like I have executed the report designer application wit [More]
i was trying to transfer a file to air drop but by accident i dragged airdrop into the word document and now it seems my airdrop is gone... i tried searching for it in finder and nothing comes upOne thing to try: Finder, Preferences, Sidebar, make su [More]
As the title say: How can I change the color of a progress bar?. The background is very important and the foreground (or ProgressIndicator) is just for style Thanks!The thing to do is to extract caspian.css from javafx-ui-controls.jar, find the relev [More]