Current Position:Home > Creating DOMAIN INDEX on INTERVAL PARTITIONING

Creating DOMAIN INDEX on INTERVAL PARTITIONING

Update:10-11Source: network consolidation
Advertisement
Hi !
I hava a problem, and I hope someone can help me!
Two questions are asked below:
1. Main question: HOW CAN I SOLVE THIS PROBLEM, ARE THERE OTHER WAYS DOING THE SAME JOB (MAYBE FASTER) ?
2. Additionally: Is there a way to accelerate the deletion process
Step 1: Creating the table* For Information how I create the table:
CREATE TABLE LOC_EXAMPLE
COLUMN1 NUMBER
COLUMN2 NUMBER
COLUMN3 NUMBER
COLUMN4 NUMBER
START_TIME TIMESTAMP
GEOLOC MDSYS.SDO_GEOMETRY,
TABLESPACE DB_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
PARTITION BY RANGE (START_TIME)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
     PARTITION PART_LOC_EXAMPLE VALUES LESS THAN (TO_DATE('01-01-2008','dd-MM-yyyy'))
ALTER TABLE LOC_EXAMPLE
ADD CONSTRAINT PK_LOC_EXAMPLE PRIMARY KEY (COLUMN2,COLUMN4)
DELETE FROM USER_SDO_GEOM_METADATA VALUE WHERE TABLE_NAME = 'LOC_EXAMPLE'
INSERT INTO USER_SDO_GEOM_METADATA VALUES ('LOC_EXAMPLE','GEOLOC', MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.001111949), MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.001111949) ), 8307)
STEP 2: I TRY TO CREATE SPATIAL INDEX (ITS A DOMAIN INDEX IF I'M NOT WRONG) ON PARTITIONED TABLE*
(PARTITIONED TABLE is an extension of range partitioning)
CREATE INDEX LOC_EXAMPLE_idx ON LOC_EXAMPLE'(GEOLOC)
INDEXTYPE IS MDSYS.SPATIAL_INDEX LOCAL;
THE SECOND STEP IS NOT POSSIBLE AS THE ORACLE DOCUMENTATION SAYS:
When using interval partitioning, consider the following restrictions:
-You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
-Interval partitioning is not supported for index-organized tables.
-You cannot create a domain index on an interval-partitioned table.
1) I THINK IT IS IMPOSSIBLE FOR ME TO PASS ON INTERVAL PARTITIONING (AMOUNT OF DATA IS REALY BIG).
This partitioning is also used to delete datas from database once a mounth (scheduled on the basis of the partitions).
Is there a way to accelerate the deletion process?
2) I NEED A SPATIAL INDEX! NO WAY TO PASS ON IT!
HOW CAN I SOLVE THIS PROBLEM, ARE THERE OTHER WAYS DOING THE SAME JOB (MAYBE FASTER) ?
Why is it not possible to create a domain index on interval partitioning, any reason?
Will this be possible anytime?
I would be grateful to read any advise ...!
Thanking you in anticipation,
Ali

The Best Answer

Advertisement
There is a forum here at OTN for spatial. Please delete the contents of this post and ask your question there. Thanks.
  • Creating DOMAIN INDEX on INTERVAL PARTITIONING Update:10-11

    Hi ! I hava a problem, and I hope someone can help me! Two questions are asked below: 1. Main question: HOW CAN I SOLVE THIS PROBLEM, ARE THERE OTHER WAYS DOING THE SAME JOB (MAYBE FASTER) ? 2. Additionally: Is there a way to accelerate the deletion

  • Creating DOMAIN INDEX (SPATIAL) on INTERVAL PARTITIONING Update:10-11

    Hi ! I hava a problem, and I hope someone can help me! Two questions are asked below: 1. Main question: HOW CAN I SOLVE THIS PROBLEM, ARE THERE OTHER WAYS DOING THE SAME JOB (MAYBE FASTER) ? 2. Additionally: Is there a way to accelerate the deletion

  • Domain index on list-partitioned table? Update:10-11

    Hi, I have a list-partitioned table, and wanted to create a partitioned Oracle Text index on it. I keep getting an error, and am now wondering if it's possible to do. Here is my syntax: CREATE INDEX SCHEMA1.IDX_ALL_TEXT_LOCAL ON SCHEMA1.TABLE1(ALL_TE

  • Issue creating domain index Update:10-11

    what lob error? This is a domain index on a clob. I don't have any special settings. ERROR at line 1: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-20000: Oracle Text error: DRG-50857: oracle error in drvxtab.create_index_

  • HOW TO CREATE LOCAL INDEX ON BIG PARTITION TABLE Update:11-30

    Dear All, I have one big table 450GB stored on 9 partitions and same partitions I have created for the index. Now the problem is when i am trying to create local index it took one and half day and is still going on... is there any shortest way to cre

  • Create table interval partition on a column timestamp with local time zone Update:11-30

    Hi Does anyone have an example for 11g on how to create a table with interval partitioning on a column defined as timestamp with local time zone. I know it's possible. the following does not work. CREATE TABLE KOMODO_EXPIRED_RESULTS TEST_EVENT_KEY NU

  • How to create index on specific partition table? Update:11-30

    Hi Experts, we created 4 partitions on table . Table Name : test partitions : Test_prt1                    Test_prt2                    Test_prt3                   Test_prt4 Our requiremnt  create the index on specific partition (ex : Test_prt2) only

  • Local Domain Index  query fails with ora-01410: invalid rowid Update:11-30

    Hello! I have a task to implement partitioned domain index for range partitioned table. As I understood from reference http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/dom_idx.htm the main difference is to provide support for partitioni

  • ORA-06502: when creating a index Update:10-11

    Hi, i'm trying to create an index on a partitionned table, but i'm getting this error : CREATE INDEX IDX_NDI_WTSCDRHISTO ON WTS_CDRHISTO(SUBSTR(NDI, GREATEST(LENGTH(NDI)-8,1))) Compress Local ( PARTITION WTS_CDRHISTO_2009070412 NOLOGGING COMPRESS TAB

  • Creating local indexes Update:11-30

    Hi, we are using db partitioning (Daily partition, Range partition) for sub_eve (partitioned on timestamp field) table. the daily partition contains 173millians on rows daily 40gb. i want to create local indexes for on partitioned key field. 1 sugges