cancel
Showing results for 
Search instead for 
Did you mean: 

SAW 17: For some reason, the index is not used to join tables and views

0 Kudos
526

Hi,Hi,

SAP SQL AnyWhere 17.0.10 x64

    CREATE TABLE "dba"."TEXTS_CA_1" (
        "IDENTITY" INTEGER NOT NULL,
        "DOCDATE" DATE NOT NULL,
        CONSTRAINT "PK_TEXTS_CA_1" PRIMARY KEY ( "IDENTITY" ASC )
);

CREATE TABLE "dba"."TEXTS_CA_2" (
        "IDENTITY" INTEGER NOT NULL,
        "DOCDATE" DATE NOT NULL,
        CONSTRAINT "PK_TEXTS_CA_2" PRIMARY KEY ( "IDENTITY" ASC )
);

CREATE TABLE "dba"."DOCUMENTTEXT" (
        "IDENTITY" INTEGER NOT NULL,
        "DOCNAME" CHAR(50) NOT NULL,
        "TEXTCODE" INTEGER NOT NULL,
        "TEXTFILENUMBER" INTEGER NOT NULL,
        CONSTRAINT "PK_DOCUMENTTEXT" PRIMARY KEY ( "IDENTITY" ASC )
);

CREATE VIEW "dba"."CaTextView"
as
select
 TEXTS_CA_1.IDENTITY,
 cast(1 as integer) as TEXTFILENUMBER,
 TEXTS_CA_1.DOCDATE
from dba.TEXTS_CA_1    
union all    
select
 TEXTS_CA_2.IDENTITY,
 cast(2 as integer) as TEXTFILENUMBER,
 TEXTS_CA_2.DOCDATE,
from dba.TEXTS_CA_2
;

insert into dba.TEXTS_CA_1(IDENTITY,DOCDATE) values(10, current date);
insert into dba.TEXTS_CA_2(IDENTITY,DOCDATE) values(12, current date);

insert into dba.DOCUMENTTEXT(IDENTITY,DOCNAME,TEXTCODE,TEXTFILENUMBER)
values(1, 'test text', 10, 1);

commit;

I execute a query:

select * from dba.CaTextView
where IDENTITY = 10 and TEXTFILENUMBER = 1

A query is executed quickly (less than 1 second) and in "Plan Viewer" I see that is used index PK_TEXTS_CA_1

Plan Viewer: Index Scan (1 row) Scan TEXTS_CA_1 using index PK_TEXTS_CA_1

Here all be well.

Now I use dba.CaTextView in a query with an join:

select
 DOCUMENTTEXT.IDENTITY,
 DOCUMENTTEXT.DOCNAME,
 CaTextView.DOCDATE
from dba.DOCUMENTTEXT
join dba.CaTextView on (CaTextView.IDENTITY = DOCUMENTTEXT.TEXTCODE and CaTextView.TEXTFILENUMBER = DOCUMENTTEXT.TEXTFILENUMBER)
where DOCUMENTTEXT.IDENTITY = 1

Now a query is executed notedly longer (5-6 seconds) and in "Plan Viewer" I see, that index of PK_TEXTS_CA_1 why that is not used.

Plan Viewer: Table Scan (3.68M rows) Scan TEXTS_CA_1 sequentially

Question: Why, in the case of a join, the index ceased to be used ?

Indeed, in both cases, the request to dba.catextView is followed by the same conditions.

Accepted Solutions (0)

Answers (1)

Answers (1)

awitter
Participant
0 Kudos

Can't really test this one out, but I do notice this join in your example, which is, I think, not meant: CaTextView.IDENTITY = DOCUMENTTEXT.TEXTCODE

Have you tried inserting the complete view directly into the query? And then manually make it better? You will nodice that a view with union will corrupt many optimization plans. How would you notice? Because making the new query (wit hview integrated into the query) results in a full blows union all.

VolkerBarth
Contributor
0 Kudos

Have you tried inserting the complete view directly into the query?

That's what was already suggested in the comment... 🙂