Sure James.
I think I also figured why rowkeyindex is used here. The global index rule for
having all columns in the query applies to secondary indexes only, I suppose
and not to primary key based lookups so which is why explain plan correctly
reports using rowkey in this case.
From: James Heather <[email protected]>
To: [email protected]
Sent: Wednesday, September 30, 2015 8:08 PM
Subject: Re: Explain plan over primary key column
I don't see why it wouldn't be able to use the index for that.
In any case, if that's what "explain" is telling you, I think you can trust
it...
James
On 30/09/15 15:36, Sumit Nigam wrote:
Thanks so much James ...
I am sorry to be asking so many questions ... But last one -
When I - EXPLAIN SELECT ID, TEXT FROM EXP WHERE ID > 5 AND ID < 10
here TEXT is not a part of PK, then also I get the same plan - RANGE SCAN
OVER EXP [6] - [10]
Which I suppose just means that rowkey index is still used. I thought that
global indexes are only used when all columns in the query are a part of it.
Sorry again. Sumit
From: James Heather <[email protected]>
To: [email protected]
Sent: Wednesday, September 30, 2015 7:58 PM
Subject: Re: Explain plan over primary key column
Yup
On 30/09/15 15:25, Sumit Nigam wrote:
Thanks James.
So, if I did a range lookup like - EXPLAIN SELECT ID FROM EXP WHERE ID > 5
AND ID < 10 then I get .... RANGE SCAN OVER EXP [6] - [10]
Is that indication enough that PK/ index is used?
From: James Heather <[email protected]>
To: [email protected]
Sent: Wednesday, September 30, 2015 7:49 PM
Subject: Re: Explain plan over primary key column
You're asking for every single row of the table, so nothing's going to avoid
a full scan. The index wouldn't help.
On 30/09/15 15:18, Sumit Nigam wrote:
Hi,
I have a table as:
CREATE TABLE EXP (ID BIGINT NOT NULL PRIMARY KEY, TEXT VARCHAR);
If I explain the select:
EXPLAIN SELECT ID FROM EXP;
Then it shows CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER EXP ....
I assume it still uses rowkey. Or is it should have shown using rowkey in
explain plan?
Also, is it a good idea to declare VARCHAR(n) vs plain VARCHAR? Does it save
some space?
Thanks, Sumit
