Brilliant - Now I am trying to force it to make that inference!
Ahmed Hussain, November 09, 2012 - 8:32 am UTC
Brilliant!
I did eventually twig this after asking the question, so have been attacking the problem on those lines.
Of course, Oracle has no way of knowing that my function isn't just something like NVL
I have had some success with this approach, but am seeing different behaviour depending on exactly how I code the query and how I force it to figure out that the column with the domain predicate cannot be NULL.
My actual problem is a little more complex than I specified above - my domain column is a BLOB with our own domain index on it, but I simplified the problem for purposes of comparison.
Also, the domain column, as a LOB, is actually stored in a third table with a simple numerical primary key to link it into the pivot table.
So we have
CREATE TABLE t1
(TUPLE_ID varchar2(32) PRIMARY KEY,
somecol1 VARCHAR2(50),
somecol2 NUMBER...)
CREATE TABLE t2 -- EAV pivot table :-(
(TUPLE_ID varchar2(32),
PROPERTY_ID varchar2(32),
PROPERTY_VALUE_NUM NUMBER,
-- ...String and other property types
BLOB_ID NUMBER(9));
ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY(TUPLE_ID, PROPERTY_ID)
CREATE TABLE t3 -- blob table
(DOM_ID NUMBER(9) PRIMARY KEY,
DOM_VALUE BLOB);
CREATE INDEX MY_DOM_IDX ON t3(CHEM_VALUE) INDEXTYPE IS MY_DOM_IDT
The actual view I am using for testing is like
create view weird as
select t.*, b.property_value_blob_id, b.property_value_num, c.DOM_ID, c.DOM_VALUE
from
t1 t,
t2 b,
t3c
where
t.tuple_id = b.tuple_id (+) -- Not every row in t1 has rows in t2
and
'22986A0707B743a5AF5761361734B2AF' = b.property_id (+)
and
b.BLOB_ID = c.DOM_ID(+) -- Not every property in t2 is a BLOB property...
Now, on my dev box,
I can do either
SELECT * FROM weird x
WHERE MY_OP(x.DOM_VALUE, ...) =1
AND x.DOM_VALUE IS NOT NULL
and it works perfectly
I can alternatively use
x.DOM_ID IS NOT NULL
so use the PK on the domain table t3. This is less ideal as the DOM_ID is a pure join artifice of the model that I don't really want in the views or for the application layer to have to be aware of.
However, on a different test server, it doesn't work (i.e. refuses to transform to an inner join to permit the domain index scan) with DOM_VALUE IS NOT NULL, but does with DOM_ID IS NOT NULL.
BUT - it works either way if I expand the SELECT * FROM weird into SELECT * FROM (... definition of WEIRD view) WHERE MY_OP(...)= 1 AND DOM_VALUE IS NOT NULL
Its obviously something to do with view expanding so I'm looking at the 10053 trace to try and find (a) what's different about the environments between the databases (11.2 on mine, 11.3 on the test server), (b) ways of encouraging the unnesting, (c) why it figures it out either way with the DOM_ID NOT NULL predicate but not the DOM_VALUE NOT NULL.
If I find a solution I'll post it, but any pointers most gratefully received.
[Apologies if this follow-up is a bit too far off-topic]
Many thanks again,
Ahmed
Correction - Definition of view
Ahmed Hussain, November 09, 2012 - 8:38 am UTC
Sorry, just to be clear
Sorry everyone, in my follow up above I wrote
create view weird as
select t.*, b.property_value_blob_id, b.property_value_num, c.chem_data_id, c.property_value_chem
should have read
select t.*, b.blob_id, ...
^^^^
I wasn't following an entirely consistent algorithm in renaming everything to try and simplify the test case to the essentials. Oops.
Correction - Difference between inline and full view reproducible between environments
Ahmed Hussain, November 09, 2012 - 10:25 am UTC
I have rechecked and think i was wrong that I saw different behaviour on 11.2 and 11.3
It's quite consistent:
If I do SELECT * from weird WHERE MY_OP(DOM_VALUE...) AND DOM_VALUE IS NOT NULL
it doesn't transform to an inner join,
but it does work if I use the domain/BLOB table's PK
SELECT * from weird WHERE MY_OP(DOM_VALUE ...) AND DOM_ID IS NOT NULL
However, if I replace weird with the text of the view definition (i.e. use an inline view) then it works either way.
Investigations continue...
Join Elimination - probable bug with LOB columns and views
Ahmed Hussain, November 13, 2012 - 8:58 am UTC
Much digging later, I think I have found a bug and raised an SR about it.
The problem is with query transformation, when the optimizer applies view merging and join elimination and the predicate is on a lob column.
Can reproduce very simply as
CREATE TABLE t1
AS SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 10000;
ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY(OBJECT_ID);
CREATE INDEX t1_idx ON t1(data_object_id);
CREATE TABLE t2_varchar
(id primary key,
interesting_col)
AS
SELECT object_id, OBJECT_NAME
FROM ALL_OBJECTS WHERE ROWNUM < 10000 and DATA_OBJECT_ID < 10000; -- make child table slightly sparse
CREATE TABLE t2_clob
(id primary key,
interesting_col)
AS
SELECT id, to_clob(interesting_col) FROM t2_varchar; -- clone table but with a CLOB
[gather stats]
CREATE OR REPLACE VIEW v1_varchar
AS
SELECT
a.*, b.id, b.interesting_col
FROM t1 a,
t2_varchar b
WHERE
a.object_type = 'TABLE' AND
a.data_object_id = b.id(+); -- bake in outer join
CREATE OR REPLACE VIEW v1_clob
AS
SELECT
a.*, b.id, b.interesting_col
FROM t1 a,
t2_clob b
WHERE
a.object_type = 'TABLE' AND
a.data_object_id = b.id(+); -- bake in outer join
-- Test Case 1:
-- Query against view with varchar2 as interesting column
-- Correctly transforms to a nested loop inner join
-- because of the predicate t.interesting_col is not null
select /*+ FIRST_ROWS */ * from
v1_varchar t
where length(t.interesting_col) = 5
and t.interesting_col is not null;
-- Test Case 2:
-- Join elimination fails - performs a nested loop outer join
-- Thus if length were a user function which could make use
-- of a domain index on t2_clob(interesting_col), the domain
-- index could not be used because the query is still left as
-- an outer join
select /*+ FIRST_ROWS */ * from
v1_clob t
where length(t.interesting_col) = 5
and t.interesting_col is not null;
-- Test Case 3
-- Exactly as Test Case 2, except with the text of the view v1_clob
-- embedded as an inline view
select /*+ FIRST_ROWS */ * from
(SELECT
a."OWNER",a."OBJECT_NAME",a."SUBOBJECT_NAME",a."OBJECT_ID",a."DATA_OBJECT_ID",a."OBJECT_TYPE",a."CREATED",a."LAST_DDL_TIME",a."TIMESTAMP",a."STATUS",a."TEMPORARY",a."GENERATED",a."SECONDARY",a."NAMESPACE",a."EDITION_NAME", b.id, b.interesting_col
FROM t1 a,
t2_clob b
WHERE
a.object_type = 'TABLE' AND
a.data_object_id = b.id(+)) t
where length(t.interesting_col) = 5
and t.interesting_col is not null;