SQL & PL/SQL :: Interval Partitioning And NULL Column Values

Mar 12, 2010

Our organization is attempting to learn more about the partitioning features of Oracle 11g. I've been reading the partitioning manuals, and I have not found a clear answer on this topic, but I suspect I know the answer.

If you create a range partitioned table; using interval partitioning, say something like this:

CREATE table range_parti (
CODE NUMBER(5),
DESCRIPTION VARCHAR2(50),
CREATED_DATE DATE)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION my_parti VALUES LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);

but you try to insert a null value as the partition key, you get the following error:

SQL> INSERT INTO range_parti VALUES (1,'one',NULL);
INSERT INTO range_parti VALUES (1,'one',NULL)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Elapsed: 00:00:00.07

Is there no way to tell it to use a default partition for NULL values? Or specifically designate a partition for NULL values WITHOUT having to manually list out each partition? It seems it works if you don't use the INTERVAL keyword, list out your partitions, and use MAXVALUE. However, our hope to avoid having that as it creates monstrously huge DDL statements for tables that have lots of date ranges, and we will be forced to manually add new partitions each month as data is added/time passes.

It appears from my experience so far, if your column can allow nulls, you cannot use interval range partitioning on that column.

View 5 Replies


ADVERTISEMENT

Interval Partitioning On Number Column?

Sep 19, 2013

11gr2, We need to do partition a existing table of size 20g, But partition key column is NUMBER type and data stored in unix date format.I would like to create a monthly partition table as below. But not able to create.

create table student (  ENTRY_ID number(5,1),  NAME varchar2(30 BYTE) )  partition by range ( fun_unix_to_date (ENTRY_ID) ) --> fun_unix_to_date is a customized function to convert unix time stamp to date format.   

INTERVAL (100)   ( PARTITION CATCH_ALL values LESS THAN (to_date('01-MAR-12','DD-MON-YY'))); 

ERROR at line 5:ORA-00907: missing right parenthesis

View 5 Replies View Related

PL/SQL :: Interval Partitioning On Table?

Mar 28, 2013

If a Interval Partitioning can be created on a table for every fortnight ? db version is 11g.

View 3 Replies View Related

PL/SQL :: ORA-14037 on Interval Partitioning

Jan 7, 2013

Interval partitioning I keep getting the below error on a table.A more discerning eye is needed

PARTITION DEC_2012 VALUES LESS THAN (TO_DATE('01-01-2013', 'DD-MM-YYYY')),
*
ERROR at line 26:
ORA-14037: partition bound of partition "DEC_2012" is too high

CREATE TABLE STATISTICS_PART
(
ID_KEY NUMBER(10) NOT NULL,
LUD DATE DEFAULT sysdate,
[code]....

View 5 Replies View Related

Date Range Vs Interval Partitioning

Dec 16, 2010

Other than the obvious to me, where interval partitioning creates partitions as needed. Is there any performance benefit from using interval partitions vs date range partitions.

One draw back for me is that developers do access the partition name in some of their queries, so if I use date range partitioning this will not break their code. I could not find a way to assign a name to a partition when using intervals, is this always system generated or can this be over-ridden.

I am running Oracle 11.1.0.7 soon to be running on 11.2.0.0

View 6 Replies View Related

SQL & PL/SQL :: Interval Partitioning With Local Index?

Nov 25, 2011

I Have created a interval partitioned table with local index.But when automatic partitions are created based record insertion, whether local indexes will be created for each newly created partition or not? If created, how to check

Below is the code which I tried

CREATE TABLE interval_date
(
date1 date,
days VARCHAR2(50)
)
PARTITION BY RANGE(date1)
INTERVAL (NUMTODSINTERVAL(45,'DAY'))

[code]....

View 7 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

PL/SQL :: Use Securefile For New Partitions Made By Interval Partitioning

Oct 19, 2012

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.I have a range partitioned table having lobs as basicfile. Due to storage issue and other business constraints , it is determined not to change existing lobs to securefile.

However ,we want new lobs to be in securefile and alter table to have  interval partition+.While researching, I found sql to change lob in range partition to securefile by using

alter table t1 add partition t1_p2 value less than (10000) lob (col3) store as securefile (tablespace tbs_sf1)

What to do similar  in case of interval partition.

View 3 Replies View Related

PL/SQL :: Dropping Partition Dynamically In Interval Partitioning

Aug 22, 2012

I have created an Interval Partition Table as show below, Is their any way, i can drop the partition dynamically when i truncate the table as oracle creates them with system generated name? Instead Alter table drop partition !

Create Table Script :
------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE TBL_EMP_SALES
(
EMP_ID NUMBER(38),
EMPNAME VARCHAR2(9),
EMP_SALES_AMOUNT NUMBER(5),
EMP_SALES_DATE DATE,

[Code]....

Insert Script :
------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO TBL_EMP_SALES VALUES (1001,'A',50,SYSDATE);
INSERT INTO TBL_EMP_SALES VALUES (1002,'B',100,SYSDATE+1);
INSERT INTO TBL_EMP_SALES VALUES (1003,'C',80,SYSDATE+2);

[Code]....

Partitions Created :
-------------------------------------------------------------------------------------------------------------------------------------------------
select segment_name,partition_name,bytes from user_segments where segment_name = 'TBL_EMP_SALES';
segment_name partition_name bytes

TBL_EMP_SALES     P0     8388608
TBL_EMP_SALES     SYS_P602     8388608
TBL_EMP_SALES     SYS_P603     8388608

[Code]....

View 5 Replies View Related

SQL & PL/SQL :: Query To Return Not Null Column Values With Priority

Dec 22, 2011

I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.

I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.

WITH TMP_TBL AS
(
SELECT * FROM (
SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual
UNION ALL
SELECT 'A','2','A122','A23',NULL,NULL FROM dual
UNION ALL
SELECT 'A','3','A32','A33',NULL,'A35' FROM dual
[code].......

DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes

The results I am expecting is

DUNCOL1 COL2 COL3 COL4
AA21 A23 A41 A35
BB12 B23 B15
CC12 C13 C33 C14

I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.

SELECT DUN,
row_nvl(rownvl_param_type(RNK,col1)),
row_nvl(rownvl_param_type(RNK,col2)),
row_nvl(rownvl_param_type(RNK,col3)),
row_nvl(rownvl_param_type(RNK,col4))
FROM TMP_TBL
GROUP BY DUN

But I don't think my manager will allow me to deploy a Multi Row function .

View 2 Replies View Related

SQL & PL/SQL :: Query To Display Repeated Column Values As Null?

Dec 13, 2012

I have table with the values as below.

C1C2C3C4
NAMEJOHN10ABC
NAMESMITH30DEF
NAMEROBERT60XYZ

I dont want to print the repeated value(NAME) of C1 multiple times as below.

C1C2C3C4
NAMEJOHN10ABC
SMITH30DEF
ROBERT60XYZ

I could do it using the below query using union with the rownum.

select * from (
select rownum rn, c1,c2,c3,c4 from table_new
) where rn =1
union
select * from (
select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new
) where rn between 2 and 3

Is there any other way of displaying using a single sql query.

View 17 Replies View Related

SQL & PL/SQL :: Entering Values As Not Null Constraint To Column In Oracle Without Disturbing Old Records

Jul 1, 2013

previously i set null constraint to the column and creating some rows and need to change new entering values as not null constraint to the column in oracle without disturbing the old records. how can I do that.

View 5 Replies View Related

Replacing Null Values Of Outer Join With Meaningful Values

Dec 3, 2010

I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.

Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......

The result of this is:

Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868

As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.

I need the output to be

Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868

View 3 Replies View Related

SQL & PL/SQL :: Date Field - Not Null Column To NULL

Mar 16, 2011

I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.

I am using below query.

ALTER TABLE T_test
modify (paid_to_date null)

View 9 Replies View Related

Range Partitioning With Number Column

Apr 21, 2011

i have a requirement To partition a Table byRange partition with a Number column. but the issue is the range Must be in a Date datatype ,
For example

partition by range (date_key)
( PARTITION DEF VALUES LESS THAN ('01-SEP-10'))

the date_key column has values of date in number format. like "20101014"

View 2 Replies View Related

SQL & PL/SQL :: How To Modify Null Column To Not Null

Jan 9, 2012

when i follow this steps mention on this website

[URL].........

to modify column from null to not null i got this error and on this website its show successful

my steps are

first i create a table

SQL> create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10),
2 Stu_Class varchar(10));

Table created.

Then insert some rows into Stu_Table

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(1,'Komal');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(2,'Ajay');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(3,'Rakesh');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(4,'Bhanu');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(5,'Santosh');

1 row created.

SQL> select * from Stu_Table;

ST STU_NAME STU_CLASS
-- ---------- ----------
1 Komal
2 Ajay
3 Rakesh
4 Bhanu
5 Santosh

Table Structure is like this

SQL> Describe Stu_Table
Name Null? Type
----------------------------------------- -------- ----------------------------
STU_ID VARCHAR2(2)
STU_NAME VARCHAR2(10)
STU_CLASS VARCHAR2(10)

now when i try to modify this Stu_id column to not null its give me error.

SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null;
ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

and when i try to add new column with not null its also gives me error

SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL;
ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

View 6 Replies View Related

Select Into With Null Values?

Apr 20, 2007

i have a stored proc where i am selecting a value into a variable like so:

SELECT FUNCTION
INTO V_FUNCTION
FROM FUNCTION_TABLE
WHERE FUNCTION = P_INPUT;

Now, my problem lies in where there is no value returned (oracle will throw an error).

View 3 Replies View Related

Handling NULL Values?

Jan 23, 2013

I have 2 tables, AFF_TEMP and COUNTY
AFF_TEMP has the following columns FNAME, LNAME, EMAIL and COUNTY
COUNTY has 2 columns COUNTY_ID and CNAME

Both tables have the following test data
AFF_TEMP
Joe, Bloggs, joe@gmail.com, ''
Ann, Bloggs, anne@gmail.com,Donegal

and COUNTY column in AFF_TEMP can contain a NULL value

County table has the following Test data,
1, Dublin
2, Donegal
3, Tipperary,
4, Galway

I am trying to select the following from both tables FNAME, LNAME, EMAIL, COUNTY_ID.Tried the following queries
select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A LEFT OUTER JOIN COUNTY C ON A.COUNTY=C.CNAME
OR (A.COUNTY IS NULL)
select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A, COUNTY C
WHERE C.CNAME IN (SELECT UPPER(A.COUNTY) FROM TEMP_AFF A)

[code]...

View 3 Replies View Related

Replacing Null Values

Apr 23, 2010

Is there a way to replace a field with another if the particular value returned is null?

View 11 Replies View Related

SQL & PL/SQL :: Columns With NULL Values?

Dec 17, 2010

We are working on a migration project and we need to move 75 million rows from source system to target system.

Total number of columns in source system - 90 cols.

Out of the 90 columns 10 cols are system fields and rest 80 are properties for each record.

We are required to migrate all system cols and some required properties. In total we will migrate around 25 columns[10+15] for each record.

Before actaul migration , we need to do a data cleansing activity and hence we move the data to a staging table.

To create the staging table, we considered the below appraoches.

1. Create the staging table with around 30 coloumns so as to fit the data from source system[map the columns based on datatype]

2.Create the staging table with actual columns[90 columns] and import only the required properties. The rest all columns will remain NULL.

Do the data cleansing and move to target system.

My question here is, if we go with approach 2, We will not mix the data, as there will be a one-to-one mapping. But many columns will not have data and remain NULL. Will it affect the performance since we deal with 75 million rows.

View 3 Replies View Related

SQL & PL/SQL :: Replace Zero With Null Values

Jul 28, 2010

I have a table abc with two column (marks,id) both can have any value.

Value of the id column is zero at several places.When I divide marks by id. I get divide by zero error.

how to replace zero with null.

View 22 Replies View Related

PL/SQL :: How To Restrict Null Values

Jul 20, 2012

I have main query..And i written a Query for Issued Quantity. The query is retrieving exact data. And when i add the Query in main query the data is coming with extra null values.

for ex. Query for Issued Qty fetch 16 records. and i run the main query it fetch 20 records...there r 4 records which issued Qty is null. How can i restrict that records.

View 14 Replies View Related

Query Ignore Null Values

Aug 5, 2010

My query is ignoring the null value

CODEselect count(*) as COUNT from EMP where EMP_ID in '1001,1002,1003'

returns the result as

CODECOUNT
----------------------
0
1
2

EMP_ID '1001' has 0 records(i.e., EMP_ID 1001 doesnt exists in the table)
EMP_ID '1001' has 2 records
EMP_ID '1001' has 3 records

where as the query

CODEselect count(*) as COUNT, EMP_ID from EMP where EMP_ID in '1001,1002,1003' group by EMP_ID

returns the result as

CODECOUNT | EMP_ID
---------------------
1     | 1002
2     | 1003

I want the null value also to be counted when I run the second query i.e., the output should be like

CODECOUNT | EMP_ID
---------------------
0     | 1001
1     | 1002
2     | 1003

View 2 Replies View Related

SQL & PL/SQL :: Order By DATE Which Has NULL Values

Jun 11, 2010

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have Event_month, Event_date in SELECT statement. Results of the query has NULL values as well.. I am doing ORDER BY Event_month, and I want NULL values to be at the top..

SELECT d.event_code_dt, count(d.event_number) count1
FROM main_event a,sec_event d
WHERE a.event_id = d.event_id(+)
GROUP BY d.event_code_dt
ORDER BY d.event_code_dt, count(d.event_number) desc

Results are like this

11-MAY-10
21-MAY-10
22-MAY-10
NULL

And I want results to be like this:
NULL
11-MAY-10
21-MAY-10
22-MAY-10

Same thing happens with Event_month
NULL
Feb 2009
Aug 2009

View 2 Replies View Related

SQL & PL/SQL :: How To Use Or Condition To Check If 6 Col Values In A Row Is NULL

Aug 23, 2010

How can I use OR condition in sql to check whether if the 6 col values in a row is NULL, I need something like below, but idk the syntax of checking if either of the mentioned cols is null using an or condition.

select a.mid
FROM table_a a
JOIN table_b b
ON a.dept = b.dept
JOIN table_c c
ON b.eid = c.eid
WHERE a.tid = :tid
AND (a.i1 or a.i2 or a.i3 or a.n1 or a.n2 or a.n3 or a.t1 or a.t2 or a.t3 ) IS NULL

View 1 Replies View Related

SQL & PL/SQL :: Count Function Over NULL Values

Jun 17, 2010

There is a table with column holding 3 NULL values one each in every record. When a count function is applied to the column with a filter on NULLs,it returns 0 instead of 3.

But when count(*)is applied,expected result is returned. Would be interested in knowing about this strange behavior of NULL/Count.

View 3 Replies View Related

PL/SQL :: String Having Null Values Between Commas

Dec 29, 2012

My DB version is 10.1.0.5.0

I want extract the values from the string using below query but i am unable to bring the correct result.

WITH t  AS ( select '123,1,3,22' col FROM DUAL
        UNION ALL
        SELECT '123,,2,1' FROM DUAL
        UNION ALL
        SELECT '5,1,2,,' FROM DUAL
        )

[Code]...

My required result like below.

123     1      3      22
123            2       1
5       1      2       

get required result using regular expressions.

View 3 Replies View Related

Precompilers, OCI & OCCI :: NULL Values In C Structures?

May 18, 2010

I use a cursor to select records from a database table into a C structure as follows...

{
int iLoop = 0;
int iResult = 0;

[Code]....

The otc_multiplier field is NULL. As is the to_date sometimes. However, when I output the records later, the entries where the to_date is NULL come out fine (no value). But the otc_multiplier is getting output as 0.0 using...

// this is output later in another fuction using the following..
sprintf(newRecord, "%.1f",daServiceRecs->itemMultiplier);

If using c structures in this manner, what is the method for ensuring that numeric values are set to NULL when required?

View 2 Replies View Related

Reports & Discoverer :: Ignoring Null Values?

Jun 21, 2010

I have a report in report 6 which has static field where values are shown for previous year and for current year. the output of the report will be like this

sl no department prev year cur year
01 revenue 2000 5000
02 costing 500 1000
03 excise 8000 900
and so on....

I have a created a fomula field for each and every department and get the value from a particular table. What i require is if suppose the department revenue does not have any value then that row should not be visible. for eg if revenue has a null value then the report should be like this

sl no department prev year cur year
01 costing 500 1000
02 excise 8000 900

View 6 Replies View Related

ODP.NET :: Null Values In (ref) Attributes - C# Odp Type Factory?

Nov 22, 2012

I'm using the odp.net type factory (class IOracleCustomType and the specific annotations...) to map udts to c# classes.By now I had no problems with it, because I've never used "ref" attributes within the udts.Now I need the use of "ref" attributes within udts but it seems that the odp factory cannot handle those references.

For example:_*

A Simple Type:
create or replace type bookcontent_t force as object (
    id number(20)
  , name varchar2(1000)
);*And a collection with references to objects of this type:*

create or replace type fms_bookcontent_ref_cln force as table of *ref* bookcontent_t;The c# class is generated by odp.net.

When I retrieve data by using odp.net I get a collection with the correct number of elements but the values are null.What can I do to solve this problem with "ref" attributes/lists? How can I get the referenced object instances?

View 0 Replies View Related







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