| creating global index [message #155488] |
Mon, 16 January 2006 09:02  |
Zakkhalid
Messages: 47
Registered: April 2005
|
Member |
|
|
Hi All,
I have table which is partitioned:
SQL> desc tbl_raw_radius
Name Null? Type
----------------------------------------- -------- ----------------------------
SESSIONSTART DATE
SESSIONSTOP DATE
COMMUNITY NUMBER(10)
SESSIONLENGTH NUMBER(10)
USERNAME VARCHAR2(100)
USERIP VARCHAR2(100)
CALLERID VARCHAR2(50)
CALLEDID NUMBER(10)
NASIP VARCHAR2(100)
NASPORT VARCHAR2(100)
NASID VARCHAR2(100)
ACCT_SESSION_ID VARCHAR2(100)
ACCT_MULTI_SESSION_ID VARCHAR2(100)
ACCT_TERMINATE_CAUSE NUMBER(10)
ACCT_INPUT_OCTETS NUMBER(10)
ACCT_OUTPUT_OCTETS NUMBER(10)
ACCT_INPUT_PKTS NUMBER(10)
ACCT_OUTPUT_PKTS NUMBER(10)
RADIUS_ID NUMBER(10)
SU_NUMBER NUMBER(10)
DELETION_KEY VARCHAR2(100)
PARTITION_NAME
------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
RADIUS_DEC05
TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
RADIUS_JAN06
TO_DATE(' 2006-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
RADIUS_NOV05
PARTITION_NAME
------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
TO_DATE(' 2005-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
RADIUS_OCT05
TO_DATE(' 2005-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
and I'm trying to create a global index on two columns
create index IDX_SUNUM_SESSTOP on TBL_RAW_RADIUS(sessionstop,su_number)
global partition by range (sessionstop,su_number)
(
partition RADIUS_OCT05 values less than (TO_DATE('2005-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
tablespace rawradius,
partition RADIUS_NOV05 values less than (TO_DATE('2005-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
tablespace rawradius,
partition RADIUS_DEC05 values less than (TO_DATE('2006-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
tablespace rawradius,
partition RADIUS_JAN06 values less than (TO_DATE('2006-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
tablespace rawradius
)
/
but I'm getting ORA-14018: partition bound list contains too few elements error... how to I create a global index on two columns...
thanking you all inadvance...
|
|
|
|
| Re: creating global index [message #155588 is a reply to message #155488] |
Tue, 17 January 2006 08:21  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
You are trying to partition based on two columns, but you are only specifying one value, the date, in your "values less than" clause.
|
|
|
|
| Re: creating global index [message #155601 is a reply to message #155588] |
Tue, 17 January 2006 10:50 |
Zakkhalid
Messages: 47
Registered: April 2005
|
Member |
|
|
Hi,
Thanks taking the time to respond. I understand the problem, but I'm sure of the syntax, the first column is a date and the second is a number.
thanking you all advance...
|
|
|
|
| Re: creating global index [message #156119 is a reply to message #155601] |
Sun, 22 January 2006 12:01 |
SQLAREA
Messages: 21
Registered: January 2006
Location: Belgium
|
Junior Member |
|
|
Dear,
As said above
global partition by range (sessionstop,su_number) >> TWO COLUMNS
(
partition RADIUS_OCT05 values less than (TO_DATE('2005-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) ONLY ONE VALUE
You should have something like
partition RADIUS_OCT05 values less than (TO_DATE('2005-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS'), number criteria)
Regards
Guy Lambregts
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
