non-prefixed global partitioned index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: non-prefixed global partitioned index

Hybrid View

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    non-prefixed global partitioned index

    non-prefixed global partitioned indexes are not supported because they don't have application in reality. Why is it so? Can it be explained with an example.

    As per my understanding of the concept, I feel it is possible to have non-prefixed global partitioned index.
    Suppose we have a table sales with columns:
    week_no----values r like 1,2,3,etc
    sales_amount---total sales in a particular.

    Suppose we create a partitioned table using week_no as partition key and partition is based on range in slots like 1-4,5-8,9-12 week_no.
    Then we can create a non-prefixed global partitioned index on cloumn sales_amount of table sales that is range partitioned in slots like values less than 5000,10000,15000,etc.
    I feel such indexing would be useful in certain scenarios. I have also created such an index.

    Please correct me if I am wrong on concept or misinterpreted something.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Firstly, Non-prefixed global partitioned indexes are not supported by Oracle. Period. This is an Oracle forum then, end of discussion.

    Secondly, what would be the advantage in terms of storage, administration or performance in deploying something like what you have described as opposed as creating a normal index? -by the way, I don't even want to start arguing about why you may want an index on the sales_amount column
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    thanks for reply. N its abt discussion n not arguments.

    Well i m new to partitioned objects world.if sb else can put this notion into my mind, i shall be obliged.

    commands i executed are:

    SQL> create table sales (
    2 amount number(5),
    3 week_no number(2))
    4 partition by range (week_no)
    5 (partition p1 values less than (2) tablespace users,
    6 partition p2 values less than (6) tablespace example,
    7 partition p3 values less than (maxvalue) tablespace xdb);

    Table created.

    SQL> ed
    Wrote file afiedt.buf

    1 create index test on sales(amount)
    2 global
    3 partition by range(amount)
    4 (partition p11 values less than (4000) tablespace users,
    5 partition p22 values less than (14000) tablespace example,
    6* partition p33 values less than (maxvalue) tablespace xdb)
    SQL> /

    Index created.

    SQL> @E:\oracle\ora92\rdbms\admin\utlxplan

    Table created.

    SQL> explain plan for select * from sales where amount=13000;

    Explained.

    SQL> select operation,options from plan_table;

    OPERATION OPTIONS
    ------------------------------ -----------------------------------------
    SELECT STATEMENT
    TABLE ACCESS BY GLOBAL INDEX ROWID
    INDEX RANGE SCAN


    didnt get it--not supported by oracle.

    Secondly, it wud be beneficial for queries that use where condition on sales_amount column. Though partitions were made in lieu of queries using week_no; but if v have queries that also use sales_amoun, then the index wud be beneficial. I feel this is a better option, as entries in one partition of index will point to rowids in different table partitions, than going for non-prefixed local partitioned index,where we will have m:n index-table partition mapping. The benefits seem to be clear to me this way.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Lets go to the basics.

    You partition tables for one of two -or the two of them - reasons next:

    Condition #1- To help queries
    Condition #2- To help purging

    1- Your "partitioned" index is not providing more help than a normal b-tree index on the same column so, condition #1 is false.

    2- Purging -as in truncate or drop - of "one" table partition would probably invalidate "all" the partitions of your "partitioned" index then your purge process is not getting any help from it so, condition #2 is false.

    false/false means you don't do it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    now this is of help. thanks Pablo (Paul) Berzukov.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width