|
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 Plan Viewer: Index Scan (1 row) Scan 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 Plan Viewer: Table Scan (3.68M rows) Scan 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. |
|
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.
That's what was already suggested in the comment... :)
(19 Jan, 08:29)
Volker Barth
|
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.
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.
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...
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.
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 = 1then index "PK_TEXTS_CA_1" is used.
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” ?
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.
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
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.