Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49

    Unanswered: How can we increase CLUSTERRATIO of a regular index??

    How can we increase CLUSTERRATIO of a regular index??

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    there can be only 1 ci
    if the table is also organized by this index, the ratio will increase
    in most tables - data is organized according ci and other indexes are not part of organization
    they will be used for specific predicates
    if you don't have ci - unload the data with order by the clmns from this index and reload data. data will be organized according the index and ratio will increase. the data will not be maintained according the index
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Just a simple example to clarify this:
    suppose you have a table with two columns: country names and city names.
    suppose that you define two indexes, one per column.
    finally suppose that your table data is physically sorted on the city names, and then (for equal cities) on the country name.

    The cluster ratio of the "city names" index will then be 100%, while it would be very unlikely that the cluster ratio of the other index is higher than 10% (unless there are very few different cities or countries): country names will appear in seemingly random order.

    The other way around, and assuming several cities per country, the cluster ratio of the "cities" index *could* be around 50% or even higher, e.g. when rows would be physically sorted on city for equal countries. But you'll never get both indexes have cluster ratio 100%, unless there's just a single country, or in the very unlikely event when the first few characters of city names equal their country name.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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