Hello,
The view VRAND below generates a series of 3 randomly chosen integers -
CREATE VIEW vrand as with r(num, rand) as (
select 1, cast(round(abs(random())/9223372036854775808) as int)
union all
select num+1, cast(round(abs(random())/9223372036854775808) as int) from r)
select num from r where
Thank you Keith for your useful advice. I am considering to organize the
columns based on BCNF.
I guess that table t3 is needed to remove functional dependency, which means
I should use table t2 and t3 instead of one table t2 with 4 columns a-d. Is
that right?
I am not familiar with the concept B
Hello,
To summarize:
On 2018-01-07 19:09, x wrote:
Because reading the whole record (all 3 fields) is more expensive than
just reading the index which has all the info you need to give a correct
answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
Yes, but the covering index has 2 fields (X & ID)
... and the downside that it's just linear overhead for i.e. an unique index,
it works best for indexes with low cardinality... win some, lose some :)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailing
What I do notice reading https://www.sqlite.org/fileformat.html (if I get it
right) is that index lines are in the form (for an index on a,b,c ie):
Whereas they could be represented as:
[ , , ](3)
whith [pk_list] being a subtree; reverse lookup from table record to index
record
Hi, Cezary et al,
On Mon, Dec 11, 2017 at 5:48 PM, Cezary H. Noweta wrote:
> Hello,
>
> On 2017-12-11 04:29, Igor Korot wrote:
>>
>> Thank you, but I need to keep the official SQLite code.
>
> Anyway, for the people who are interested in foreign key names:
> http://sqlite.chncc.eu/fknames/. Certa
Please try again with the latest trunk of SQLite
(https://www.sqlite.org/src/timeline?c=2018-01-07+21:58:17 or later)
and let me know whether or not your a still having problems.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-use
OK, I can see your point and I am sure you are right.
All this has to do with the question (discussed not long ago) how one
should know how the output from a statement should be handled by
an application receiving the data. In my case that application is Excel.
Take for example an integer. Excel ne
On 2018/01/07 10:11 PM, Bart Smissaert wrote:
Sure, in that case there can be no sensible column name.
In the great majority of cases though the select field will be of a single
table column, with or without
an expression. In those cases it will be helpful to get the non alias
column name.
To
Sure, in that case there can be no sensible column name.
In the great majority of cases though the select field will be of a single
table column, with or without
an expression. In those cases it will be helpful to get the non alias
column name.
RBS
On Sun, Jan 7, 2018 at 7:59 PM, Simon Slavin
On 7 Jan 2018, at 6:16pm, Bart Smissaert wrote:
> Would it be possible somehow to get the column full name if there is a
> column alias?
>
> For example if we have:
>
> select max(ID) as MaxID from table1
>
> then I need to get max(ID) from the statement pointer.
>
> One would think that if
Hi David,
I started from scratch with a new database and confirmed your findings -
v_count_leaves_new is actually faster than leafCounts.
My error in the original database was neglecting to specify type of the
columns in the EDGES table -
CREATE TABLE edges(parent not null references nodes, chi
Integer primary key is by definition not null, so looking for a null value
on an index can't work. I guess there exists an optimization opportunity to
just return an emotional set, though it seems easier to not specify an
impossible condition.
As to why it does a table scan, the primary key isn't
On 07-01-18 19:09, x wrote:
>> Because reading the whole record (all 3 fields) is more expensive than
>> just reading the index which has all the info you need to give a correct
>> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
> Yes, but the covering index has 2 fields (X & ID). The pk has only
Would it be possible somehow to get the column full name if there is a
column alias?
For example if we have:
select max(ID) as MaxID from table1
then I need to get max(ID) from the statement pointer.
One would think that if sqlite3_column_name can get the alias name then
somehow it must
be poss
>Because reading the whole record (all 3 fields) is more expensive than
>just reading the index which has all the info you need to give a correct
>answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
Yes, but the covering index has 2 fields (X & ID). The pk has only 1 field (ID)
so why not use that,
On 07-01-18 18:49, x wrote:
> Luuk and Cezary, my apologies. Looking at my opening post(s) I didn’t make it
> clear that Tbl had numerous secondary indexes attached to it. The table has
> 2.4 million records and 13 columns. There is a non-unique index on one of the
> other columns (x integer) wh
Luuk and Cezary, my apologies. Looking at my opening post(s) I didn’t make it
clear that Tbl had numerous secondary indexes attached to it. The table has 2.4
million records and 13 columns. There is a non-unique index on one of the other
columns (x integer) which is the one appearing in my expla
On 06-01-18 19:00, x wrote:
>>> sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
>>> selectid|order|from|detail
>>> 0|0|0|SCAN TABLE Tbl
>> I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is
>> returned in case of not ``NOT NULL'' field.
>
>
> Yeah, I
19 matches
Mail list logo