Select query - new

[unknown]

I have "transpetrol" table in "tptrldb" database(500+
million rows)
I have "transpetrol" view in "ptrldb" database
view is: select * from tptrldb..transpetrol

Table "tptrldb"."transpetrol" has following indexes:
1) tpindx1 unique clustered on (depo_num,
account,currtype... more cols)
2) tpindx2 nonclustered nonunique (pay_date, batchnum)
3) tpindx3 nonclustered nonunique (bpnum, buydate)

select:
-------
select * from transpetrol
where pay_date>='20080101'
and pay_date<='20081231'
and depo_num='500'
and account='50505'

If running above select from "tptrldb" database it uses
tpindx2 index
If running above select from "ptrldb" database(this has
view) it uses tpindx1 index

Why the query against "ptrldb.transpetrol" view is picking
up clustered index?
Or
Why the query against "tptrldb.transpetrol" table is not
using clustered index(its using tpindx2 index)?

[unknown]

Steve,
Does the view in the ptrldb have other objects in the view definition?
--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

Post by unknown
I have "transpetrol" table in "tptrldb" database(500+
million rows)
I have "transpetrol" view in "ptrldb" database
view is: select * from tptrldb..transpetrol
1) tpindx1 unique clustered on (depo_num,
account,currtype... more cols)
2) tpindx2 nonclustered nonunique (pay_date, batchnum)
3) tpindx3 nonclustered nonunique (bpnum, buydate)
-------
select * from transpetrol
where pay_date>='20080101'
and pay_date<='20081231'
and depo_num='500'
and account='50505'
If running above select from "tptrldb" database it uses
tpindx2 index
If running above select from "ptrldb" database(this has
view) it uses tpindx1 index
Why the query against "ptrldb.transpetrol" view is picking
up clustered index?
Or
Why the query against "tptrldb.transpetrol" table is not
using clustered index(its using tpindx2 index)?