_WA indexes

[markp]

Hi
Everything I've ever found, including on this site, says that _WA_sys indexes are in fact not indexes but system statistics that can be ignored.
I am looking at one of our systems and a lot of tables have these shown as indexes within enterprise maanger.
Taking one table as an example. The data takes up 152Mb. The indexes take up 700Mb. Creating my own copy of the table and creating indexes just on the non _WA_sys things gives me a total index(es) size of 80Mb. In addition when I execute a simple query against the original table the execution plan shows that an _WA_Sys index is used to satisfy the query.
Have I misunderstood something along the line somewhere? Are these really real indexes? - they take up space and are used to satisfy queries

[skhanal]

These are column statistics created for columns used in your queries. They get created for columns which do not have indexes on them to satisfy the query.

You can disable them by setting 'auto create statistics' to off for the database

[markp]

That is exactly what I thought. Why then, when running a query against a table containing one of these columns, do I see an execution plan with an index seek that names one of the _WA_Sys object.
Also why do these statistics take up so much room. As stated earlier if I copy the table and create only the two genuine indexes they take up 80Mb, but the index space used for the original table is 700Mb. Table size 152Mb.
Thanks
Mark

[rmiao]

_WA_Sys objects take very few room. You may have fragmentation in index pages, rebuild index should be able to defrag them.

[markp]

Thanks Ray, I'll do this to the indexes I know are genuine.
Do you have any idea why an _Wa_Sys object would show up used in an index seek in an exucution plan.
The text from the index seek box is
Object:[[ColumbusLive].[sysdba[.[Availableadvertstemp].[_WA_Sys_OPPADVERTID_007FFA1B]], Seek:[[Availableadvertstemp].[oppadvertid]='QHJZ50023800] Ordered.
Many thanks
Mark

[rmiao]

Because query optimizer uses this info to get query result.

[Ads]

How can I find out how much space an index is using ???

Thanks!

[rmiao]

Try sp_spaceused or view it in em.