SQL & PL/SQL :: Query Not Using Partition Range?

Dec 8, 2011

I have a table partitioned by Range and subparitioned by hash.

i am issuing the following query .Bcos of security purposes i cannot post the full query.

Select <most of the columns>
FROM qosTesthr PARTITION(PARTITION_ON_2011_11_17)
WHERE starttime BETWEEN to_date('1111170000', 'yymmddHH24mi') AND to_date('1111172359', 'yymmddHH24mi')

Explain plan is

---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 826K| 171M| 2330 (4)| 00:00:28 | | | | | |
|* 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 826K| 171M| 2330 (4)| 00:00:28 | | | Q1,00 | P->S | QC (RAND)

[code]...

When i use INDEX_SS hint in the query then only the partition scan is happening.

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 826K| 171M| 810K (1)| 02:42:06 | | |
|* 1 | FILTER | | | | | | | |

[code]...

Performance while using the index is very very slow.Is there any way without using the index and just the partition full scan can be forced by using some hint..

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Dropping Sub-partition / Range-partition And List Sub-partition

May 28, 2010

i have table with range partition and list sub-partition..can i add one more list sub-partition if it is not possible , i have to drop first sub-partition.

View 6 Replies View Related

SQL & PL/SQL :: Create Normal Partition Range On Date And Sub Partition List On Batch ID

Mar 17, 2011

I Know we can create dynamic partitions on table in oracle 11g. Is it possible to create normal partition and sub partition both dynamically.I have to create Normal partition range on date and sub partition list on Batch ID (varchar).

View 3 Replies View Related

SQL & PL/SQL :: Apply Redefinition And Create Range Partition And Hash Sub-partition?

Apr 3, 2013

At present we have a non partitioned table.

Can we apply redefinition and create range partition and hash sub partition on it?

View 2 Replies View Related

SQL & PL/SQL :: Add Range Sub-Partition To Hash Partition Table

Jan 2, 2013

Can I add range sub partition to a hash partition table. Example like this.

CREATE TABLE test
(
test_id VARCHAR2(10 ) ,
test_TYPE VARCHAR2(5) ,
CREATE_DATE date
)
partition by hash (test_id, test_type)
Partitions 3
SUBPARTITION BY RANGE (CREATE_DATE);

When Tried, I am getting syntax error as invalid option.

View 8 Replies View Related

SQL & PL/SQL :: How To Add Range Partition

Dec 5, 2011

In my prod table few partition need to be added.

I tried below both syntax, but I am getting error

1)
Alter table STS.DNA_ACCESSION add PARTITION DNA_ACC_P143 VALUES LESS THAN (286000000);
ORA-14074: partition bound must collate higher than that of the last partition

2)
ALTER TABLE STS_RO.DNA_ACCESSION
SPLIT PARTITION DNA_ACC_P999 AT VALUES LESS THAN (286000000)
INTO (PARTITION DNA_ACC_P143,

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: Create Range Partition?

May 3, 2012

I have to create partition on 1 table where i have date field and id.

i want to create range parttion for both the column.

View 2 Replies View Related

Can Create Range Partition Of Like 10 Minutes?

Oct 18, 2012

I want to ask whether I can create range partition of like 10 minutes? Like I have data of 1 hour and I want to make 10 partition?

View 12 Replies View Related

PL/SQL :: Range Partition Using Interval Partitioning?

May 2, 2013

I am trying to create a partitioned table so that a number (which date converted to number ) partition is created on inserting a new row for release_date column.

note that release_date column is having number data type (as per design) and people want to create an interval based partition on this.

They want data type NOT to be altered.

create table product(
prod_id number,
prod_code varchar2(3),
release_date number)
partition by range(release_date)
interval(NUMTOYMINTERVAL (1,'MONTH'))
(partition p0 values less than (20120101))

View 11 Replies View Related

Script To Add Range Partition Automatically - Every Month

Mar 3, 2012

Script to add range partition automatically. This script should be running every month and add the partitions for next 3 months

View 3 Replies View Related

Performance Tuning :: Converted Non-partitioned Table (1 Million Data) Into Range-partition

Mar 28, 2013

As per Article mentioned in Oracle Base,I have converted non-partitioned table (1 million data) into range-partition table,but,I don't see performance improvement in explain .

View 9 Replies View Related

SQL & PL/SQL :: Query On Range Wise

Jul 26, 2013

I want output like given below. Using EMp table . if sal is <1000 means count of Emp's who are having lessthan 1000 like that.

Deptno sal sal sal sal
<=1000 <=2000 <=3000 <= 5000

10 2 4
20 2.

View 2 Replies View Related

SQL & PL/SQL :: Calculate Count Of Range By A Query?

Jun 30, 2010

I have a table which contains two columns containing the range of some products. These range are alphanumeric and i want to calculate the count of this range by a query. .

test case:
CREATE TABLE MARRIAGE_FILE_NAME
(
START_SERIAL_NO VARCHAR2(10 BYTE) NOT NULL,
END_SERIAL_NO VARCHAR2(10 BYTE) NOT NULL,
CATCODE VARCHAR2(10 BYTE) NOT NULL,

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Replace Date Range In Query?

Aug 14, 2013

I am trying to replace a date range in a query. Don't understand why it's not working, below is the dummy query for the same:

/* v_Employee:= 'select emp_name from employee_table where Join_Year=~Replace1~ and Release_Month <=~Replace2~';
v_Replace1:=2013;
v_Replace2:=7;
pos := 1;
WHILE (INSTR(v_Employee, '~', 1, pos) <> 0) LOOP
IF (pos = 1) THEN

[code]....

Since actual code is like CLOB type, so I have not provided the same. Here after executing this query i found that v_Employee query is getting replaced by v_Replace1 at both of his position i.e "select emp_name from employee_table where Join_Year=2013 and Release_Month <=2013"

But my actual result should: "select emp_name from employee_table where Join_Year=2013 and Release_Month <=7"

View 5 Replies View Related

SQL & PL/SQL :: Query Using Analytical Functions-date Range

Nov 30, 2010

I have a table which has the attached data.

Sample data is here

LOGON_DATE NUMBER_OF_LOGINS
11/28/2010 02:00:001
11/28/2010 03:00:001
11/28/2010 04:00:002
11/28/2010 06:00:004
11/28/2010 07:00:002
11/28/2010 08:00:003
11/28/2010 09:00:006
[Code] ........

I am trying to do a report like this.

Date PeakUsersBetween6AMand6PM AVGUsersBetween6AMand6PM PeakUsersBetween6PMand6AM AVGUsersBetween6PMand6AM

Output should be
11/28/2010 25 11 49 27
11/29/2010...

I am using analytical function to do this, It throws an error range cannot be used for dates.

View 5 Replies View Related

SQL & PL/SQL :: Date Query - Input Year In Specified Range

Dec 26, 2012

Getting problem with below query.

select to_date('30-DEC-00','DD-MON-YYYY') from dual

Error starting at line 1 in command:

select to_date('30-DEC-00','DD-MON-YYYY') from dual

Error report:

SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"

*Cause: Illegal year entered
*Action: Input year in the specified range

View 1 Replies View Related

PL/SQL :: Number Range Query Causes Repeated Scan By Index

Oct 16, 2012

I have a query that seems to repeatedly call an index scan on a table for reasons I'm not sure about. Why it would be doing the index scan on totaldwellingarea in the dimensions table (DIMEN_PID_TDWELLAREA) repeatedly? This only seems to happen when I put on the range clause d.totaldwellingarea between scr.lowvalue and scr.highvalue.

I am using Oracle version 9.2.0.3.

select d.propertyid,d.totaldwellingarea, e.size_,  scr.size_
from  eqid e, dimensions d,  brt_eval.size_code_ranges scr
where e.style not in ('1','A','G','L') and e.size_  = '0'
and d.propertyid = e.propertyid and e.style = scr.style and d.totaldwellingarea between scr.lowvalue and  scr.highvalue;

CREATE
  TABLE "BRT_ADMIN"."EQID"
  (
    "PROPERTYID"   VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "EQID"         CHAR(10 BYTE),
    "ZONE_"        CHAR(1 BYTE),
    "AREA"         CHAR(1 BYTE),
[code].....

View 3 Replies View Related

PL/SQL :: Query To Find First And Last Call Made By Selected Number For Date Range

Apr 27, 2013

create table call(id number(10) primary key,mobile_no number(10), other_no number(10), call_type varchar2(10),call_date_time date, duration number(10));

insert into call values(1,9818764535,9899875643,'IN','24-APR-13 02:10:43',10);
insert into call values(1,9818764535,9898324234,'IN','24-APR-13 05:06:78',10);
insert into call values(1,9818764535,9215468734,'IN','24-APR-13 15:06:78',10);
insert into call values(1,9818764535,9899875643,'OUT','25-APR-13 01:06:78',10);
insert into call values(1,9899875643,9899875643,,'OUT','25-APR-13 22:06:78',10);

Query : need to find first and last call of '9818764535' mobile number and of call_date between '24-apr-13' and '25-apr-13';

Result :

date ,mobile_no , other_no, call_type, duration
24/apr/13 , 9818764535,9899875643,'IN',10
24/apr/13 ,9818764535,9215468734,'IN',10

[Code]....

View 5 Replies View Related

SQL Query To Club Data If Range Data Is Continuous

Nov 26, 2006

For e.g.

if my data is as follows

EX_START_DATE EX_END_DATE EX_CHARGE

5/1/2005 5/5/2005 11:59:59 PM 10
5/6/2005 5/7/2005 11:59:59 PM 10
5/10/2005 5/15/2005 11:59:59 PM 10
5/17/2005 5/19/2005 11:59:59 PM 10
5/20/2005 5/25/2005 11:59:59 PM 20

the record
5/6/2005 5/7/2005 11:59:59 PM 10
is in continuation for
5/1/2005 5/5/2005 11:59:59 PM 10 with the same ex_charge
so i need the output as

5/1/2005 5/7/2005 11:59:59 PM 10
5/10/2005 5/15/2005 11:59:59 PM 10
5/17/2005 5/19/2005 11:59:59 PM 10
5/20/2005 5/25/2005 11:59:59 PM 20

and the remaining rows as it is
(Note :date in mm/dd/yyyy format)

View 6 Replies View Related

Performance Tuning :: Select Partition Table With Non-partition Key Condition?

Jun 26, 2010

I have a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk

when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)

View 3 Replies View Related

SQL & PL/SQL :: Tables Activity - Inserted Partition Key Does Not Map To Any Partition

Sep 22, 2011

I have two tables Activity and Activity1.

Activity Structure
ACTIVITY_TYPE CHAR (1) NOT NULL,
ACTIVITY_DATE DATE DEFAULT sysdate NOT NULL,
ACTIVITY_ON VARCHAR2 (30) NOT NULL,
REFERENCE_NO VARCHAR2 (19),
CHILD_REFERENCE_NO VARCHAR2 (19),
USER_ID VARCHAR2 (30) DEFAULT user NOT NULL,
TERMINAL VARCHAR2 (30) DEFAULT userenv ('TERMINAL') NOT NULL )

Activity1 Structure Which I have Done Partitioning When I insert data from Activity to Activity1 it gives that error ORA-14400: inserted partition key does not map to any partition what I am doing wrong

CREATE TABLE ACTIVITY1(
ACTIVITY_TYPE CHAR (1) NOT NULL,
ACTIVITY_DATE DATE DEFAULT sysdate NOT NULL,
ACTIVITY_ON VARCHAR2 (30) NOT NULL,
REFERENCE_NO VARCHAR2 (19),

[code]....

Insert Statement

insert into ACTIVITY1(ACTIVITY_TYPE,
ACTIVITY_DATE,
ACTIVITY_ON,
REFERENCE_NO,
CHILD_REFERENCE_NO,

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Adding A New Partition To Existing Partition Table

Jun 14, 2011

i want to create a new partition for version 2

existing table is as below

create table test
(
name varchar2(100),
version NUMBER(12)
)

[Code]....

View 15 Replies View Related

SQL & PL/SQL :: Can Drop Partition Based Indexes From A Particular Partition

Mar 3, 2011

I have partition based table one the basis of year month. And we have 8 local indexes on this table. Every month we have to create a new partition and load data into this partition and the volume of the data is around 14million and the load process is taking long time due to indexes. Is it possible to drop the indexes from particular partition?

View 8 Replies View Related

SQL & PL/SQL :: Partitioned Table - Inserted Partition Key Does Not Map To Any Partition

Jul 10, 2012

I have two tables in which one is partitioned table with the following details.

CREATE TABLE "SCOTT"."TBL_MITTAL"
("ACCOUNT_NAME" VARCHAR2(50 BYTE),
"BILL_NO" VARCHAR2(50 BYTE),
"BILL_DATE" VARCHAR2(50 BYTE),
"CLI" VARCHAR2(50 BYTE),
"ANI" VARCHAR2(50 BYTE),

[code].....

When I am trying to insert record from tbl_mittal into tbl_temp table. I am facing "ORA-14400: inserted partition key does not map to any partition" error

SQL> insert into tbl_temp select * from tbl_mittal;
insert into tbl_temp select * from tbl_mittal
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

AS tbl_mittal is having hugh number of records so I am providing only few rows from tbl_mittal table as test data.

ACCOUNT_NAMEBILL_NOBILL_DATECLIANICHARGE_START_DATEDURFROM_LOCATIONTO_LOCATIONINVOICE_IDCIRCLE
10000010357423128271095119301-Feb-111723006000931488182328-JAN-11 11.30.54.000000000 AM59.04CHANDIGARHJAIPUR271095119
10000011844187128348720198715-Jun-121409470011825531896615-MAY-12 09.10.36.000000000 AM28.03CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531927115-MAY-12 09.10.41.000000000 AM38.32CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531933015-MAY-12 09.10.46.000000000 AM28.81CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531930215-MAY-12 09.10.53.000000000 AM28.96CHANDIGARHBANTWAL348720198

[code].....

I also tried to upload the same data using sqlldr.

sqlldr log file contents is as follows:

Total logical records skipped: 0
Total logical records read: 1857532
Total logical records rejected: 801092
Total logical records discarded: 37

[code].....

So some sqlldr bad file contents is as follows.

100000118441871,283487201987,15-JUN-12,1723958000,9355115251,10-JUN-12 05.56.05.000000 PM,36.99,CHANDIGARH,AMBALA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7520533825,10-JUN-12 05.56.14.000000 PM,44.12,CHANDIGARH,AGRA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9356452151,10-JUN-12 05.56.17.000000 PM,116.83,CHANDIGARH,JALANDHAR,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9331223048,10-JUN-12 05.56.21.000000 PM,28.33,CHANDIGARH,KOLKATA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7827927893,10-JUN-12 05.56.24.000000 PM,3384.33,CHANDIGARH,DELHI,348720198,,

[code].....

View 2 Replies View Related

SQL & PL/SQL :: Updated PartitionID With Partition Number For Each Partition Set

Apr 12, 2010

needed in stored procedure to achieve this...how to get this..(stored procedure)

table:
studentid,sname,partitonid
901,x,null
902,y,null
903,z,null
904,p,null
905,q,null
906,a,null
907,b,null
908,d,null
909,f,null
910,m,null

For the above data set i need to divide into 5 partittions and need to updated the partitonid with the partition number for each partition set,like the below result set

studentid,sname,partitonid
901,x,p1
902,y,p1
903,z,p2
904,p,p2
905,q,p3
906,a,p3
907,b,p4
908,d,p4
909,f,p5
910,m,p5

View 3 Replies View Related

PL/SQL :: Alter Script For Adding Partition And Sub-partition

Mar 28, 2013

write ALTER STATEMENT for adding new partition P1 and SUBPARTITION P1_201001 and P1_201002.

TABLE
=======
CREATE TABLE TEST
(
"REPORT_ID"    NUMBER,
"MONTH_ID"      NUMBER,
"GROUP_ID"      NUMBER,
"AGE_GROUP_ID"  NUMBER,
[code]...........  

View 4 Replies View Related

Partition Size In A Partition Table

Jun 8, 2011

How to find the size pf a partition in a partition table?I guess we need to query views like dba_tab_partitions but I am not very sure. will running dbms_stats.gather_table_stats('schema_name,'table_name,'partition_name')

View 3 Replies View Related

Performance Tuning :: How To Assure Partition Table Is Better Than Non-partition Table

Mar 25, 2011

I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?

I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?

View 11 Replies View Related

SQL & PL/SQL :: How To Add Sub-partition To Existing Partition

Jul 17, 2010

I have a partitioned table in which at the first level it is been partitioned based on date column(C1) and within in each partition it is again sub-partitioned based on a column(C1) which is a numeric value now at first level(C1 column level) there are 6 partitions and in the last partition i want to add another partition how can i do that.

View 8 Replies View Related

SQL & PL/SQL :: Add Partition To Table Without Partition?

May 5, 2011

I am trying to add partition to table without partition

with following code

ALTER TABLE ACC_LOC1_TAB
ADD PARTITION testpart BY RANGE (ALT_AUTHDT)
(PARTITION UPTO_2010 values less than (31-mar-2010),
PARTITION APR_JUN_10 VALUES less than (30-JUN-2010),
PARTITION JUL_SEP_10 VALUES less than (30-SEP-2010),
PARTITION OCT_DEC_10 VALUES less than (31-DEC-2010),
PARTITION JAN_MAR_11 VALUES less than (31-MAR-2011))

it will raise error ora-14020

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved