RE: How to calculate cardinality for inequality in WHERE

Martin: If it were worthwhile in your situation to be extremely fast at finding just the relevant rows, then:

alter table junk23 add (id1_lt_id2 number as (decode(id1-id2,-1,id1-id2,0,null,null)));

(Where I've substituted id1 for qty1, etc. and my table is junk23 to easily test I have not fat fingered the syntax on my little test database),

and then if you index id1_lt_id2, you will have a sparse index containing only the row references you seek, and the CBO would be hard pressed to miss this on any dataset worth indexing at all.

create index id1_lt_id2_n1 on junk23(id1_lt_id2);

and then

select * from junk23 where id1_lt_id2 < 0; and
select * from junk23 where id1_lt_id2 is null;

both give excellent plans, but the inequality will kick in sooner on marginally big enough tables. For the condition you describe this should minimize the space actually required to ever contain the index on the virtual column, since the null entries are not stored.

If you're trying to get the CBO to get the correct cardinality without changing the table in this, I am also stumped. In fairness to the CBO builders, it seems like a corner case to have a pre-built answer for, but maybe SQLMaria will drive by and have an idea.

I suppose building a functional index directly also works to get a good plan and at the margin I'm not sure whether that is more efficient. Adding the virtual column and indexing it is essentially the same thing except that you can see the instantiated values with a query in the case the virtual column definition is more complicated and you might want to more easily debug it.

mwf

mwf
-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Iotzov, Iordan Sent: Thursday, July 11, 2013 9:54 AM
To: Martin.Klier_at_klug-is.de; oracle-l_at_freelists.org Subject: RE: How to calculate cardinality for inequality in WHERE

You can create a virtual column DIFF defined as (QTY1 - QTY2). You should gather stats on the table, including the new DIFF column. Add histograms to the DIFF column if needed. Then you should rewrite the statement like this : select * from TABLE
where DIFF < 0;

The CBO should have come with better cardinality numbers.

Iordan Iotzov
http://iiotzov.wordpress.com/

Check out SmartSource Xpress, our new iPad app! Follow us on Twitter | Like us on Facebook

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Klier Sent: Thursday, July 11, 2013 9:28 AM
To: oracle-l_at_freelists.org
Subject: How to calculate cardinality for inequality in WHERE

Hello listers,

today again, a question merely for understanding than for solving a current problem.

DB-Version 11.2.0.3.6 SE on Linux x86_64

select * from TABLE
where QTY1 < QTY2;

There are approx. 1.5 million rows in this table. QTY1 < QTY2 means 0, 1 or 2 rows at max

Both columns have unpredictable numbers of values, so we see a Height Balanced Histogram for both. No NOT NULL constraints on QTY1/QTY2, but de-facto no NULLs.

Creating extended statistics for QTY1, QTY2 does not change anything, it seems the CBO cannot combine them for an inequality (maybe not for an equality, either).

So what's the best way to help Oracle to calculate the correct cardinality for WHERE QTY1 < QTY2?

Thanks in advance and best reagrds
Martin Klier

PS:
(For the practical folks:
In my real-life case, it was possible to change the SQL, so I solved the issue with:

select * from TABLE
where QTY1 - QTY2 <0;

and a function based index (QTY1 - QTY2) that creates a virtual column where we can create statistics for.)

--

Mit freundlichem Gruß
Best regards

Martin Klier
Senior Oracle Database Administrator

--

http://www.freelists.org/webpage/oracle-l

This message and its attachments may contain legally privileged or confidential information. It is intended solely for the named addressee. If you are not the addressee indicated in this message (or responsible for delivery of the message to the addressee), you may not copy or deliver this message or its attachments to anyone. Rather, you should permanently delete this message and its attachments and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of News America Incorporated or its subsidiaries must be taken not to have been sent or endorsed by any of them. No warranty is made that the e-mail or attachment(s) are free from computer virus or other defect.
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jul 11 2013 - 17:37:03 CEST