I have a SQL query with this condition:

WHERE  ("erSentTime" IS NULL)

I have an index on erSentTime. But this is not optimised, because it has to do a row scan. I can't find a way to tell ESB to make an index that will tell it the not-null entries. Is this possible?

--

Matthew Jones

Matthew


I've just tried here on a table. I added an index to a timestamp column and the index is used quite happily. Is the SQL you've shown the full query or is it a part of a sub query?

Roy Lambert

Roy Lambert wrote:

> I've just tried here on a table. I added an index to a timestamp column and the index is used quite happily. Is the SQL you've shown the full query or is it a part of a sub query?

Full SQL:

SELECT ALL
"erID" AS "erID",
"erEmailType" AS "erEmailType",
"erEmailDestination" AS "erEmailDestination",
"erEmailContent" AS "erEmailContent",
"erRequestTime" AS "erRequestTime"
FROM "EmailRequest"
/*
WHERE ("erSentTime" IS NULL) AND ("erRequestTime" > TIMESTAMP '2016-12-23 10:41:45.870') AND "erHoldForAdmin" <> TRUE
WHERE ("erSentTime" IS NULL) AND ("erRequestTime" > TIMESTAMP '2016-12-23 10:41:45.870') AND "erHoldForAdmin" <> TRUE
*/
WHERE  ("erSentTime" IS NULL)
ORDER BY "erRequestTime"


The table:

CREATE TABLE "EmailRequest"
(
"RecordID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 32, INCREMENT BY 1),
"erID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 32, INCREMENT BY 1),
"erEmailType" VARCHAR(24) COLLATE "UNI",
"erEmailDestination" VARCHAR(254) COLLATE "UNI",
"erEmailContent" CLOB COLLATE "UNI",
"erRequestTime" TIMESTAMP,
"erSentTime" TIMESTAMP,
"erSentResult" INTEGER,
"erSentContent" CLOB COLLATE "UNI",
"erReplicateIndex" BIGINT,
"erHoldForAdmin" BOOLEAN DEFAULT FALSE,
"erActionRef" INTEGER,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("RecordID")
)
DESCRIPTION 'DatabaseFile'
VERSION 0.02
READWRITE
ENCRYPTED
INDEX PAGE SIZE 2048
BLOB BLOCK SIZE 2048
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768!

Indices:

CREATE INDEX "idxHoldForAdmin" ON "EmailRequest" ("erHoldForAdmin")!
CREATE INDEX "idxRequestTime" ON "EmailRequest" ("erRequestTime")!
CREATE INDEX "idxID" ON "EmailRequest" ("erID")!
CREATE INDEX "idxActionRef" ON "EmailRequest" ("erActionRef")!
CREATE INDEX "idxSendTime" ON "EmailRequest" ("erSentTime")!



I just copied these from an existing table, and saw this issue. Then found it was there on the original too.


--

Matthew Jones

Hmm, just ran it again and didn't get the plan telling me it was a row scan.

Enable one of the full lines commented out instead to see the problem.

--

Matthew Jones

Matthew


I'm trying it here (using ANSI rather than UNI for my convenience)

I enabled

WHERE ("erSentTime" IS NULL) AND ("erRequestTime" > TIMESTAMP '2016-12-23 10:41:45.870') AND "erHoldForAdmin" <> TRUE

I do get a partial row scan on the coule of rows of data I entered

Row scan with one or more index scans eliminated due to cost (EmailRequest): 1
rows, 376B estimated cost

which is fair enough - Tim's optimiser thinks that will be more efficient.

I'm not going to spend all day entering data just to see if its a volume issue so can you post the full execution plan, and, just in case it is volume can you export the data and post that into the binaries?

Export rather than dump the table because I can then suck it this end both as unicode and ansi


Roy Lambert

Hmm, okay, I think you have explained it! My table is either empty or only
has a few rows. The optimiser is being clever and skipping the index need.
But my guard is looking for that text as a sign that I missed an index so I
went looking. I presume that once it gets a real amount of data the index
is worth it and the warning goes away.

Thanks for your help in my learning.

Matthew

>Hmm, okay, I think you have explained it! My table is either empty or only
>has a few rows. The optimiser is being clever and skipping the index need.

Ah - so you you were to lazy to enter wadges of data as well


>But my guard is looking for that text as a sign that I missed an index so I
>went looking. I presume that once it gets a real amount of data the index
>is worth it and the warning goes away.

Its a good idea to have that signal. What's a good idea is to check out each part separately and see what happens.

Roy Lambert

Roy Lambert wrote:

> Ah - so you you were to lazy to enter wadges of data as well

8-)  Well actually, this was taken from a live application, and the tables emptied for re-use in another. Which is partly why I was surprised, given that I'd not noticed this warning in the running one, and it is shown once a second, so you'd think I would. It being empty/low volume is obvious for optimisation once it is pointed out.

--

Matthew Jones