Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL Query

597023Sep 12 2007 — edited Sep 12 2007
Hi,

I have a table with a single column of number type.
and the data is as follows

col1
----------
12
22
33
-4
-6
-4

Now i need a query which gives the output as follows
(Two columns positive and negative)

positive negative
---------- ------------
12 -4
22 -6
33 -4

Comments

181444
Where is the SQL?
Where is the explain plan?

I would think that responders will want a little more information before hazarding a meaningful reply.

HTH -- Mark D Powell --
Centinul
Added the extra information...
chris_c
could you post the rest of the explain plan, I'm guessing this is a nested loop and is being executed for a number of values of doc_number and doc_type.

Chris
Centinul
Do you mean to imply that a particular join operation affect the estimated cardinality of a table access in the steps prior to actually performing the join?
Jonathan Lewis
10.2.0.4 introduced a number of changes to the optimizer - including changes relating to the use of distinct_keys in indexes, non-referenced values in frequency histograms, and general histogram behaviour.

If you have a two-column index on (doc_number, doc_type) what does it show for distinct_keys. (If the index has extra columns beyond those two, it's irrelevant).

You'll notice that the table cardinality is not consistent with the index cardinality - Oracle is using two different strategies for two calculations that ought to give the same answer ! (How can I finish volume 2, Oracle keeps changing the code ;) In 10.2.0.4 (and no earlier) the optimizer can use distinct_key from the index for the index cardinality, but then uses the product of the column selectivities for the table - hence my question about the index - but there's also some inconsistency about allowing for null values.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

"For every expert there is an equal and opposite expert."
Arthur C. Clarke
1 person found this helpful
26741
the optimizer can use distinct_key from the index for the index cardinality, but then uses the product of the column selectivities for the table
Is that a "feature" or a "bug" or "missing functionality" ?!

We'll never get to see Volume 2. Some of us are saddened.
181444
I like that quote on experts.

Mark D Powell
Jonathan Lewis
Don't be too shocked: in 9i a FILTER operation would affect the cardinality of the table that was going to call the filter - before filtering could have taken place ! Sometimes with terrible consequences: http://jonathanlewis.wordpress.com/2006/11/08/subquery-selectivity/

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

"For every expert there is an equal and opposite expert."
Arthur C. Clarke
Jonathan Lewis
Hemant K Chitale wrote:
the optimizer can use distinct_key from the index for the index cardinality, but then uses the product of the column selectivities for the table
Is that a "feature" or a "bug" or "missing functionality" ?!
I think it's a partial backport from 11g of an optimizer enhancement to "index sanity checks".

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

"For every expert there is an equal and opposite expert."
Arthur C. Clarke
Centinul
The DISTINCT_KEYS value for the index (doc_number, doc_type) shown in the explain plan is:
DISTINCT_KEYS
-------------
        19588
Here is the other information on that index:
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  76672.66  Resp: 76672.66  Degree: 0
      Cost_io: 76179.00  Cost_cpu: 7404297916
      Resp_io: 76179.00  Resp_cpu: 7404297916
  Access Path: index (AllEqRange)
    Index: MNTN_PCOMP#IE3
    resc_io: 10.00  resc_cpu: 122964
    ix_sel: 8.4530e-006  ix_sel_with_filters: 8.4530e-006
    Cost: 3.00  Resp: 3.00  Degree: 1
If I use the ix_sel_with_filters value to get an estimate of the cardinality it isn't quite the same (off by > 5%) as Oracle is quoting but, you did mention special handling with NULLs
SQL> SELECT 8.4530e-006*9001968 from dual;

8.4530E-006*9001968
-------------------
         76.0936355
Jonathan Lewis
Answer
Centinul wrote:
The DISTINCT_KEYS value for the index (doc_number, doc_type) shown in the explain plan is:
DISTINCT_KEYS
-------------
19588
Thanks, just wanted to check.
I have a note to myself that the index sanity check is bypassed if there are histograms on the columns. That was from 10.2.0.3, but your index seems to confirm it for 10.2.0.4/.

You'll notice that the two lines you've printed are silly - the table row count is HIGHER than the index row count for a single indexed access path - this is clearly an error.

Don't ask WHY Oracle is doing the folliowing, but this is what it appears to be doing (when you've got your particular set of circumstances - which is one frequency histogram and one height-balanced histogram).

Table rows:
Density1 * 1/NDV2 * table.num_rows
1.7065 / 10000 * 1/19 * 9001968 = 80.85 rounds to 81

We use the density on the column with the height-balanced histogram, and 1/NDV where we have the frequency histogram.

Index
Density1 factored by nulls * 1/NDV2 factored by nulls * table.num_rows
1.7065 / 10000 * (9001968 - 731186)/9001968 *
1/19 * (9001968 - 731186)/9001968 *
9001968 = 68.25 rounds to 68

Same rules about density and 1/NDV, but this time we throw in a factor about the null values - so there a multiplier of (num_rows - num_nulls)/num_rows for each value.

Just don't ask !
a) I might be wrong
b) I didn't write the specification or the code.


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

"For every expert there is an equal and opposite expert."
Arthur C. Clarke
Marked as Answer by Centinul · Sep 27 2020
Centinul
Jonathan Lewis wrote:
You'll notice that the two lines you've printed are silly - the table row count is HIGHER than the index row count for a single indexed access path - this is clearly an error.
Maybe it's because it's Friday, but can you point me to where the "index row count" is?
26741

but can you point me to where the "index row count" is?

The Explain Plan output

| 5   |     TABLE ACCESS BY INDEX ROWID     | MNTN_PCOMP     |    81 |  1539 |     3 |  00:00:01 |
| 6   |      INDEX RANGE SCAN               | MNTN_PCOMP#IE3 |    68 |       |     1 |  00:00:01 |


shows an expected row count of 68 from the Index Range Scan but 81 from the table.
1 person found this helpful
Centinul
Man I must have been tired yesterday. Thanks for pointing that out :)
1 - 14
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 10 2007
Added on Sep 12 2007
3 comments
1,461 views