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

[former_SQLA_member1694881]

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.

[chris_keating]

The simplified case has very high selectivity so would unlikely benefit from using an index. I suggest collecting a graphical plan with statistics for the query for the 3.68 million row case and looking at the cache and selectivities of the nodes.

[former_SQLA_member1694881]

I don’t agree with you, in the first query, using an index on the primary key greatly speeded up the query execution.

I see no reason why a second query using the same index would not be just as fast.

[VolkerBarth]

Will the index be used within the particular query when you do not use the view itself but replace it by its defining SQL statement?

I've had problems with views that include WINDOW or GROUP BY clauses: When I used those in queries and had additional conditions that should make use of the indexes of the view's base table, the indexes were apparently not used, and I had to use stored procedures instead of views. Not saying that this relates to your issue, just very wild guessing...

[chris_keating]

I mocked up a test (latest EBF) where TEXTS_CA_1 has 3 million rows, TEXTS_CA_2 has 2.5 million, and DOCUMENTTEXT 6 million rows. I am seeing consistently 1.2 seconds for the query. The query has a primary key index scan of DOCUMENTTEXT building a hash table for joining the view. The hash table contains a single row. My guess is that the plan you are seeing is different. If I do force the PK index to be used on TEXTS_CA_1, TEXTS_CA_2, or both, the query runtime increases slightly to 1.4 seconds.

[former_SQLA_member1694881]

If you abandon dba.CaTextView and use this query:

select
 DOCUMENTTEXT.IDENTITY,
 DOCUMENTTEXT.DOCNAME,
 (case DOCUMENTTEXT.TEXTFILENUMBER 
   when 1 then TEXTS_CA_1.DOCDATE
   when 2 then TEXTS_CA_2.DOCDATE
  else
    null
  end) as DOCDATE,
from dba.DOCUMENTTEXT
join dba.TEXTS_CA_1 on (TEXTS_CA_1.IDENTITY = DOCUMENTTEXT.TEXTCODE and DOCUMENTTEXT.TEXTFILENUMBER = 1) 
join dba.TEXTS_CA_2 on (TEXTS_CA_2.IDENTITY = DOCUMENTTEXT.TEXTCODE and DOCUMENTTEXT.TEXTFILENUMBER = 2)
where DOCUMENTTEXT.IDENTITY = 1

then index "PK_TEXTS_CA_1" is used.

[former_SQLA_member1694881]

My server version is 17.0.10.5866. Perhaps in a newer version of the server, the work of the query optimizer has been improved.

Did I understand you correctly that when performing the second blocking you used the index “PK_TEXTS_CA_1” ?

[chris_keating]

I am not aware of any optimizer changes that would affect this query. But I did test with 17.0.10.5866 and I see the same behavior as with 17.0.11.7458. The query runs in 1.2s and uses the index PK_DOCUMENTTEXT but no indexes are used for the tables TEXTS_CA_... Perhaps you can share the plan that you are seeing. You can capture a text plan in request logging :

call sa_server_option( 'RequestLogFile', '<file_spec>' ); call sa_server_option( 'requestlogging', 'plan' );

<query> call sa_server_option( 'requestlogging', 'none' ); call sa_server_option( 'RequestLogFile', '' );

In the file generated, look for PREPARE,select "DOCUMENTTEXT"."IDENTITY and post the line that starts with P,, for example:

P,2,Work[ TEXTS_CA_1<seq> UA TEXTS_CA_2<seq> JH* HF[ HF[ DOCUMENTTEXT<pk_documenttext> ] ] ]

I want to see if there are differences in the plan your query uses and the plan that I see that takes 1.2s to execute.

[former_SQLA_member1694881]

The "PK_DOCUMENTTEXT" index should be used for the condition according to the main table "DOCUMENTTEXT" - and I have it used.

But the "PK_TEXTS_CA_1" index should be used for the reinforcement of the main table "DOCUMENTTEXT" and "CatextView". But for some reason it is not used for me. This is a request plan:

https://drive.google.com/file/d/1fua-Tduw_j40N36scgSAcjYtJdgeA2ND/view?usp=drive_link

[chris_keating]

So the plan that I was getting differs from your yours. The reason is that the optimization_goal is 'First-row' for your plan vs. 'All-rows'. I tested with 'First-row' and see the same plan as you posted but with a run time of just over a second. The only other difference is that TEXTS_CA has 128 million extension pages in your plan. Otherwise, there is nothing obvious in the plan to explain the performance that you are seeing.

[awitter]

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]

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

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