on 2024 Jan 15 3:20 AM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
64 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.