Interesting things about index cardinality and cost calculation
For the following statement, 9i, 10g and 11g output the different cardinality and cost.
create table t1 nologging as select trunc(dbms_random.value(0,25)) n1, rpad('x',40) ind_pad, trunc(dbms_random.value(0,20)) n2, lpad(rownum,10,'0') small_vc, rpad('x',200) padding from all_objects where rownum <= 10000 ; update t1 set n1 = n2 where n2 in (1,3,5,7,11,13,15,17,19); create index t1_i1 on t1(n1, ind_pad, n2) nologging pctfree 91 ; -- gather statistics TABLE_NAME BLOCKS NUM_ROWS -------------------- ---------- ---------- T1 371 10000 1 row selected. NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY ---------- ------------- ---------- ----------- ----------------- ----------------------- ----------------------- 10000 284 2 1111 7830 3 27 1 row selected. COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY LOW_VALUE HIGH_VALUE -------------------- ---------- ------------ ---------- ------------------------ ------------------------ IND_PAD 0 1 1 782020202020202020202020 782020202020202020202020 202020202020202020202020 202020202020202020202020 2020202020202020 2020202020202020 N1 0 25 .04 80 C119 N2 0 20 .05 80 C114 set autot trace explain select small_vc from t1 where n1 = 2 and ind_pad = rpad('x',40) and n2 = 4 ;
9i:
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=21 Card=20 Bytes=1160) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=21 Card=20 Bytes=1160) 2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=5 Card=20)
10gR2:
--------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 1160 | 34 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 20 | 1160 | 34 | |* 2 | INDEX RANGE SCAN | T1_I1 | 35 | | 6 | --------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("N1"=2 AND "IND_PAD"='x ' AND "N2"=4)
11g:
--------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 35 | 2030 | 34 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 35 | 2030 | 34 | |* 2 | INDEX RANGE SCAN | T1_I1 | 35 | | 6 | ---------------------------------------------------------------------
For 9i:
cost: 5 = blevel + LEAF_BLOCKS* (1/20)*(1/25)
21 - 5 = clustering_factor * (1/20)*(1/25)
Cardinality: 20 = 10000 * (1/20)*(1/25)
For 10gR2:
cost: 6 = blevel + LEAF_BLOCKS * (1/DISTINCT_KEYS)
34 - 6 = clustering_factor *(1/DISTINCT_KEYS)
Cardinality: 35 = 10000 * (1/DISTINCT_KEYS)
20 = 10000 * (1/20)*(1/25)
For 11g:
cost: 6 = blevel + LEAF_BLOCKS * (1/DISTINCT_KEYS)
34 - 6 = clustering_factor *(1/DISTINCT_KEYS)
Cardinality: 35 = 10000 * (1/DISTINCT_KEYS)
So, in 9i, the distinct_keys of user_indexes view is not used when calculate the cardinality or cost for index access. In 10gR2, the cost and cardinality for index line is calculate using distinct_keys, so is the cost for table access cost. In 11G, the cardinality for table access is also calculated with distinct_keys.
Actually, the cost and cardinality are becoming more and more accurate, when the index columns have some dependence with each other.

It’s too difficult. The easy way is just enter your blog name of the blog to calculate its worth. Worth Estimator is the best place to estimate the worth of this great website, blog, forum or portal. http://www.worthestimator.com
Herve leger dress are the best design dress for us to wear.The herve leger dresses are made by top quality.When you try the Herve Leger Bandage Dress on,you will become the focus of the party.Welcome to our herve leger dress to choose what you like.
nike shox,nike shox
When I become rich, I will go to the timberland boots outlet to choose the timberland boots to wear.When I try these timberland boots on, I will become fashion.
This summer wear the birkenstock sandals to enjoy the sunshine is the best comfortable things.Welcome to our birkenstock outlet to choose all kinds of shoes you need here.
Luxury wallets store supply all kinds of Louis Vuitton Wallet Louis Vuitton Wallet in a low price with high quality.Our online store sell a lot of famous brands wallets such as Chanel Wallets Chanel Wallets and gucci wallets.
Every time I stumble upon a great blog post I do some things:1.Share it with all the relevant contacts.2.keep it in some of the favorite sharing sites.3.Make sure to visit the website where I first read the article.After reading this article I’m really concidering doing all of them…
I really like your blog.. very nice colors & theme. Did you make this website
yourself or did you hire someone to do it for you? Plz answer back as I’m looking
to create my own blog and would like to know where u got this from. cheers
Drupal Website Developers
Order the cheap and high quality coach bags from coach factory outlet coach factory outlet now is your best choose.Coach handbags are the best designer coach outlet store and fashion bags for us to carry in this season.
Thanks for sharing with us.Welcome to our abercrombie outlet online store to choose the Abercrombie and Fitch Jeans to wear.You may love this style shirts.
Coach Tote Bags are great discounts and affordable.Coach Bags just like what you can find in trendy counterfeit Coach Stores that you can find in the market. Coach women’s handbags were made out of sturdy cowhide, which was of much better quality than others.Coach Online Store are famous for the discounts they offer.
Our online Juicy Couture Sale is consistent quality to customers as the first professional production, with professional technical staff and QC.2011 new style Juicy Couture Bags for latest fashion women for cheap on our juicy couture outlet,cheap for sale online store,pay by visa.
Thank you for sharing so great to us.Welcome to my own blog beats by dre to have a look.There are many kind of Beats By Dre Studio for you to choose.
Thank you for sharing with us.I love this blog very much.Welcome to my own blog abercrombie and fitch outlet to have look.In our own blog,there are many kinds of abercrombie outlet dress for you to choose.
Order the top quality abercrombie and fitch clothes from abercrombie and fitch outlet to get the best quality coach factory outlet now,you will get a lot of profit.
This blog is very good.Thank you for sharing with us.Welcome to our timberland outlet online store to have a look.There are a lot of herve leger sale dress here.You must could find what you like.
I like this blog very much.Welcome to moncler down jackets to have a look.You could find a lot of Womens Moncler Jackets you need here.
very content to research your blog.[url=http://www.hats-wholesale.net/New-Era-Hats-Series-c632.html]New era caps[/url]Thank you for sharingthis article.It is great![url=http://www.hats-wholesale.net/New-era-hats-c637.html]Adjustable Hat[/url]
Love this blog very much.Welcome to my own blog supra for sale to have a look.There are a lot of beautiful Supra Skytop III Shoes on sale here.All of you could find you like here.
I love the abercrombie outlet online very much.If you are looking for a good online store,just choose the abercrombie outlet to order the abercrombie outlet clothes you need.
How good a website Christian Louboutin Discount, if you like this site to see the products Christian Louboutin Pumps, welcome to order, build first-class professional services site
Not that you have money can not buy a good product Christian Louboutin heels, is that we must choose to spend the Christian Louboutin Outlet least money to the best product, it is a successful shopping
That spring, the sky is so blue, the site is so good Christian Louboutin Outlet, the customer is so much good Oh Wow Christian Louboutin Pumps , that was a popular customer favorite sites
Often in online shopping, Christian Louboutin Pumps selling a few high heels no good if you depressed the matter, then try to Christian Louboutin Boots, you will have unexpected gains
Welcome to my own blog coach factory outlet to choose what you like.The coach handbags outlet supply all kinds cheap coach handbags in a low price.You cuold find all of the bags you need here.
So great blog,I like the abercrombie and fitch outlet very much,weclome to my blog abercrombie & fitch outlet to have a look,you will gain a lot form our online store.
i like it very much moncler jackets Moncler jackets on sale
moncler mens jackets moncler mens jackets
Thank you for shairing with us.Wear the nike air max shoes to enjoy sports will be your best choose.The Nike Air Max 2012 Mens shoes are the newest style shoes in this year,never miss it.
Thanks for all your efforts on this website. My mum loves managing internet research and it is simple to grasp why. We all notice franklin outlet all about the compelling way you give functional tips on your blog and as well as invigorate contribution from other individuals on that point while our MENS F&M Sweater own child is undoubtedly understanding a great deal. Enjoy the remaining portion of the year. You’re the one carrying out a splendid job.
What’s wrong with you?
[url=http://www.supreme-hat.com/Adjustable-new-era-hats-c1815.html]Adjustable Hats[/url]
[url=http://www.supreme-hat.com/Monster-Energy-hats-c805.html]monster energy hats[/url]
I love this blog very much.Welcome to my lovely blog abercrombie & fitch outlet to have a look.In my blog,I share a lot of discount information to each other,such as the abercrombie and fitch outlet store sale good quality things.
I want to show my admiration of your writing skill and ability to make audience, browse the whole thing to the end. I’d really like to read more of your blogs and to talk about my views with you. I’ll be your frequent website visitor, that’s for sure.
There are always ups and downs whenever you have a new release. Just hang in there !!! Thanks for the great read
NFL Jerseys Cheap
传奇私服
Wear the Tory Burch Boots are so comfortable and beautiful.When you wear the tory burch shoes and take the Tory Burch Handbags to go outside,you are the most fashion women.
Gucci Outlet Gucci Outlet
Gucci store Gucci store
Welcome to abercrombie outlet online store to choose what you like.In the abercrombie and fitch outlet store, you can find all you like here.
I am happy to find this post very useful for me,baseball hats as it contains lot of information Nice,59fifty hats and thanks for sharing this info with us.Good Luck!
chi hair straightener lady gaga beats by dre
you idea is good! I believe that there is a point in all of this.
great blog ,i like it very much!
nice article,,vrey instructive
I would like to consider the ability of saying thanks to you for your professional instruction I have always enjoyed checking out your site.