Hi Tom
Thanks. Here you go:-
CREATE TABLE Table1 (A INTEGER ,B INTEGER)
(10 rows)
CREATE TABLE Table2 (A INTEGER ,B INTEGER, C TEXT, D TEXT)
(100 rows)
CREATE TABLE Table3 (A INTEGER ,B INTEGER, C TEXT)
(50 rows)
Create Index X1 on Table1 (A, B)
Create Index X2 on Table2 (A, B)
Create Index X3 on Table3 (A, B)
Create View MyView1 as
SELECT
t1.A + t2.A as Col1
, t1.B + t2.B as Col2
, t2.C as Col3
FROM
Table1 t1
, Table2 t2
(1000 rows)
Create View MyView2 as
SELECT
t1.A + t3.A as Col1
, t1.B + t3.B as Col2
, t3.C as Col3
FROM
Table1 t1
, Table3 t3
(500 rows)
(Assuming that users cannot access the actual tables, they are accessing the
data via views.)
In the users' query:-
SELECT
v1.Col1
, v1.Col2
, v1.Col3
, v2.Col3 as Col4
FROM
MyView1 v1
JOIN MyView2 v2 ON v1.Col1 = v2.Col1
AND v1.Col2 = v2.Col2
Let's say we have a huge data set, the users' select stmt could be extremely
slow without indexing in the views.
Any suggestion?
On Tue, May 26, 2009 at 6:47 PM, BareFeet <list....@tandb.com.au> wrote:
> Hi wying,
>
> > May I know if we can create index on a View?
> > Otherwise, is there any recommendation to speed up the query
> > involving join
> > between two Views?
>
>
> No you can't create an index on a view, but you can create an index on
> the underlying tables that the view uses.
>
> Two preliminary suggestions:
>
> 1. Stop repeating the same question.
>
> 2. Post the schema of your tables and views and the query.
>
> Tom
> BareFeet
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
